slower query performance when fetch first n specified 
Author Message
 slower query performance when fetch first n specified

using db2batch I notice a significant difference if I test the same
query (simple table scan) using fetch first N rows only vs SET
FETCH_ROWS n. Using UDB 8.1 on a 32-way box. The plans look the same
so not sure where the ~9 second delta is coming from.

1                     9.447       Not Collected         0         0
2                     0.229       Not Collected         0         0

Intra-Partition Parallelism Degree = 8
Estimated Cost        = 234007.765625
Estimated Cardinality = 25000.000000
Process Using 8 Subagents
|  Access Table Name = abc  ID = 3,6
|  |  #Columns = 24
|  |  Parallel Scan
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  Insert Into Asynchronous Local Table Queue  ID = q1
Access Local Table Queue  ID = q1  #Columns = 24
Return Data to Application
|  #Columns = 24

Intra-Partition Parallelism Degree = 8
Estimated Cost        = 234007.765625
Estimated Cardinality = 3409164.000000
Process Using 8 Subagents
|  Access Table Name = abc  ID = 3,6
|  |  #Columns = 24
|  |  Parallel Scan
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  Insert Into Asynchronous Local Table Queue  ID = q1
Access Local Table Queue  ID = q1  #Columns = 24
Return Data to Application
|  #Columns = 24



Sat, 30 Jul 2005 11:07:20 GMT
 slower query performance when fetch first n specified

Show me the result like this way:
Prepare Time is:           0.000      seconds
Execute Time is:           0.000      seconds
Fetch Time is:             291.159    seconds
Elapsed Time is:           291.159    seconds

I think the difference is in the fetch phase, not the execute phase.

Regards,
Fan Ruo Xin

Quote:

> using db2batch I notice a significant difference if I test the same
> query (simple table scan) using fetch first N rows only vs SET
> FETCH_ROWS n. Using UDB 8.1 on a 32-way box. The plans look the same
> so not sure where the ~9 second delta is coming from.

> 1                     9.447       Not Collected         0         0
> 2                     0.229       Not Collected         0         0

> Intra-Partition Parallelism Degree = 8
> Estimated Cost        = 234007.765625
> Estimated Cardinality = 25000.000000
> Process Using 8 Subagents
> |  Access Table Name = abc  ID = 3,6
> |  |  #Columns = 24
> |  |  Parallel Scan
> |  |  Relation Scan
> |  |  |  Prefetch: Eligible
> |  |  Lock Intents
> |  |  |  Table: Intent Share
> |  |  |  Row  : Next Key Share
> |  Insert Into Asynchronous Local Table Queue  ID = q1
> Access Local Table Queue  ID = q1  #Columns = 24
> Return Data to Application
> |  #Columns = 24

> Intra-Partition Parallelism Degree = 8
> Estimated Cost        = 234007.765625
> Estimated Cardinality = 3409164.000000
> Process Using 8 Subagents
> |  Access Table Name = abc  ID = 3,6
> |  |  #Columns = 24
> |  |  Parallel Scan
> |  |  Relation Scan
> |  |  |  Prefetch: Eligible
> |  |  Lock Intents
> |  |  |  Table: Intent Share
> |  |  |  Row  : Next Key Share
> |  Insert Into Asynchronous Local Table Queue  ID = q1
> Access Local Table Queue  ID = q1  #Columns = 24
> Return Data to Application
> |  #Columns = 24



Sat, 30 Jul 2005 13:07:36 GMT
 slower query performance when fetch first n specified

Quote:
> Show me the result like this way:
> Prepare Time is:           0.000      seconds
> Execute Time is:           0.000      seconds
> Fetch Time is:             291.159    seconds
> Elapsed Time is:           291.159    seconds

> I think the difference is in the fetch phase, not the execute phase.

partly caused by an issue in 8.1 (APAR IY41143) that fp2 is supposed to fix.


Wed, 24 Aug 2005 10:11:52 GMT
 slower query performance when fetch first n specified

Quote:


> > Show me the result like this way:
> > Prepare Time is:           0.000      seconds
> > Execute Time is:           0.000      seconds
> > Fetch Time is:             291.159    seconds
> > Elapsed Time is:           291.159    seconds

> > I think the difference is in the fetch phase, not the execute phase.

> partly caused by an issue in 8.1 (APAR IY41143) that fp2 is supposed to fix.

Thanks!


Wed, 24 Aug 2005 11:46:25 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. how to fetch the first n rows only for a query

2. Slow ADO/ASP performance when specifying DB Server by IP address

3. Very slow query performance for any query

4. Access Xp/sql2lk: slow first query run

5. slow first query

6. Poor Performance On First Query

7. Oracle 7.3.4 performance: First Query takes enormous amount of time

8. Row or column specified is outside the range of the specified query result set

9. fetching specified number of rows from a bigger set, where there are no identity columns

10. get only a specified number of rows (fetch?)

11. Slow query-performance

12. Slow query and performance counters


 
Powered by phpBB® Forum Software