# Example 3: Query1 Taking Too Long to Run

#### Problem Description

CustomerC is running an application and has identified a particular query that is taking over 45 minutes to complete, returning only one row.

#### Problem Analysis and Resolution

##### Step 1: Identified the Query

The customer was able to determine the problem query, using the application snapshot and dynamic SQL snapshot. The problem query was identified as:

```select a.c2, b.c2, c.c3 from a,b,c
where a.c1=b.c1 and
b.c2=c.c2 and
c.c3=a.c3 and
c.c1=50193
```
##### Step 2: Run Explain for the SQL Statement

The explain plan for the SQL statement indicated the following:

```Estimated Cost = 2968724
Estimated Cardinality = 99996
```

The DBA knew that, because the column C1 in the table C is a primary key, there can be a maximum of only one row returned by the SQL statement. Therefore, the estimated cardinality in the access plan is not correct. A difference in the expected cardinality and the optimizer's estimated cardinality this large is normally due to old or nonexistent statistics.

##### Step 3: Check to Determine Whether the Tables' Statistics Are Current

To determine when the last time statistics were gathered for these three tables, use the following statement:

```select stats_time from syscat.tables where tabname in ('A','B','C')
```

The output of the above statement was:

```STATS_TIME
-------------------------
-
-
-

3 record(s) selected.
```

Therefore, the tables do not have current statistics.

##### Step 4: Gather Statistics for the Tables

To gather statistics, use the RUNSTATS command as follows:

```runstats on table user.a with distribution and detailed indexes all
runstats on table user.b with distribution and detailed indexes all
runstats on table user.c with distribution and detailed indexes all
```
##### Step 5: Retest

After running the RUNSTATS command for all of the tables in the statement, it was able to complete in less than two seconds, and the explain plan indicated the following:

```Estimated Cost = 183
Estimated Cardinality = 1
```

 IBM Press Series?Information Management
 About Prentice Hall Professional Technical Reference
 Foreword
 Preface
 Chapter 1. Data Storage
 Chapter 2. Data Manipulation
 Chapter 3. Database Availability
 Chapter 4. Database Security
 Chapter 5. Multi-Dimensional Clustering
 Chapter 6. The DB2 Optimizer
 Chapter 7. Federated Database Access
 Chapter 8. Performance Tuning
 Chapter 9. Database Communications
 Appendix A. Useful DB2 Commands
 Appendix B. parseSQL.pl
 Appendix C. exfmtDIF.pl
 Appendix D. Comparing Index Advisor Recommended Indexes with Existing Indexes
 Appendix E. Configuration Parameters That Can Be Changed Online
 Appendix F. Explain Tools
 Appendix G. Starting and Stopping a DB2 Instance
 Appendix H. LDAP Integration in DB2 UDB Using Microsoft Active Directory
 Appendix I. Tuning DB2 UDB in the IBM LDAP Environment
 Appendix J. DB2 Support Information
 Appendix K. DB2 Connect Implementation
 Appendix L. Sample Questions
 Appendix M. Answers to Sample Questions
 About the CD-ROM
 Article