473,573 Members | 2,889 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need help with query performance.


Folks,

I am having problem with an application that uses static SQL,
the application basically browses through the table given start and
end key most of the time it is processed from begining to end.

The db2expln tells me that there is no Data or Index prefetch.

I am running DB2 UDB 8 on AIX 5.3, and I am using DMS tablespace on
raw logical volumes on a SAN.

# db2level
DB21085I Instance "db2dev1" uses "32" bits and DB2 code release "SQL08023"
with level identifier "03040106".
Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and FixPak
"10".
Product is installed at "/usr/opt/db2_08_01".
This is the ddl for the table:

CREATE TABLE SCHEMA.EADMFX00
(KEY0 CHAR (29) NOT NULL,
KEY1 CHAR (26 ) NOT NULL,
KEY2 CHAR (30 ) NOT NULL,
KEY3 CHAR (50 ) NOT NULL,
REC VARCHAR(1000) NOT NULL)
IN TBSP1
INDEX IN TBSP1
LONG IN TBSP1;
CREATE UNIQUE INDEX EADMFX00_ASC ON SCHEMA.EADMFX00 (KEY0 ASC) ALLOW REVERSE SCANS;
CREATE INDEX EADMFX01_ASC ON SCHEMA.EADMFX00 (KEY1 ASC);
CREATE INDEX EADMFX02_ASC ON SCHEMA.EADMFX00 (KEY2 ASC);
CREATE INDEX EADMFX03_ASC ON SCHEMA.EADMFX00 (KEY3 ASC);
Here is the output of db2expln:

SQL Statement:
DECLARE KEY0_ASCBR CURSOR
FOR
SELECT KEY0, REC
FROM cfgdev1.EADMFX0 0
WHERE KEY0 >=:H00006 AND KEY0 <=:H00001
ORDER BY KEY0 ASC
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS

Intra-Partition Parallelism Degree = 4

Section Code Page = 819

Estimated Cost = 166.983475
Estimated Cardinality = 828.506165

Process Using 4 Subagents
| Table Constructor
| | 1-Row(s)
| Residual Predicate(s)
| | #Predicates = 1
| Nested Loop Join
| | Access Table Name = SCHEMA.EADMFX00 ID = 4,23
| | | Index Scan: Name = SCHEMA.EADMFX00 _ASC ID = 1
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: KEY0 (Ascending)
| | | #Columns = 2
| | | Parallel Scan
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | | 1: ?
| | | Data Prefetch: None
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| Insert Into Asynchronous Local Table Queue ID = q1
Access Local Table Queue ID = q1 #Columns = 2
| Output Sorted
| | #Key Columns = 1
| | | Key 1: (Ascending)
Return Data to Application
| #Columns = 2
Here is how I bind the package:

db2 PREP ${SqcCode} BINDFILE USING ${BindFile} ISOLATION CS \
BLOCKING NO DATETIME ISO EXPLAIN ALL EXPLSNAP ALL LANGLEVEL SAA1 \
OPTLEVEL 1 \
OUTPUT ${CCode} SQLERROR NOPACKAGE TARGET C

db2 BIND ${BindFile} BLOCKING NO DATETIME ISO EXPLAIN ALL EXPLSNAP ALL \
GRANT PUBLIC ISOLATION CS SQLERROR NOPACKAGE


Here is the output of reorgchk:

SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: SCHEMA.EADMFX00
SCHEMA EADMFX00 2413580 0 31529 31529 - 1.03e+09 0 99 100 ---
----------------------------------------------------------------------------------------

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: SCHEMA.EADMFX00
SCHEMA EADMFX00_ASC 2e+06 3119 0 3 29 14 2e+06 100 90 22 0 0 -----
SCHEMA EADMFX01_ASC 2e+06 2060 0 3 26 0 2e+06 63 89 37 0 0 *----
SCHEMA EADMFX02_ASC 2e+06 3202 0 3 30 0 2e+06 100 90 21 0 0 -----
SCHEMA EADMFX03_ASC 2e+06 4658 0 3 50 0 2e+06 97 89 10 0 0 -----
-------------------------------------------------------------------------------------------------
It takes 3 minutes to browse through the whole table. I have other tables that
are bigger then this one and I can browse through them in few seconds.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Dec 5 '06 #1
4 1843
Hi.
Hemant Shah wrote:
...
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
...
It takes 3 minutes to browse through the whole table. I have other tables that
are bigger then this one and I can browse through them in few seconds.
Why do you have the OPTIMIZE FOR 15 ROWS in the query? I would expect
that DB2 will actually pay attention to that, and only allocate space
for for 15 rows being returned - which won't be optimal if you are
returning significantly more rows than that.

Also, since you are using parameter holders in the query, db2 can't
make quite a good a guess at what the query should do - so it makes a
best guess, and uses it in all cases. Where you are returning the
whole table (because the keys encompass the whole table), it isn't
going to know that - it will still assume that the index lookup is the
best choice.

Some ways around this might be to use a second query that just returns
the whole table where that makes sense - it will know how to do that.
Alternatively, if most queries will return a significant portion of the
table, you might try rebinding the query with REOPT ONCE (that is, it
will wait until the first call to the query to determine the plan, when
it has some actual values to use). If the results vary wildly from
call to call (some do return just 15 rows, others return millions) that
you could rebind with REOPT ALWAYS (that is, always rebuild the plan
when running the query). That last should give you the best possible
plan - at a cost of always rebuilding the plan.

-Chris

Dec 6 '06 #2
While stranded on information super highway ChrisC wrote:
Hi.
Hemant Shah wrote:
>...
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
...
It takes 3 minutes to browse through the whole table. I have other tables that
are bigger then this one and I can browse through them in few seconds.

Why do you have the OPTIMIZE FOR 15 ROWS in the query? I would expect
that DB2 will actually pay attention to that, and only allocate space
for for 15 rows being returned - which won't be optimal if you are
returning significantly more rows than that.

Also, since you are using parameter holders in the query, db2 can't
make quite a good a guess at what the query should do - so it makes a
best guess, and uses it in all cases. Where you are returning the
whole table (because the keys encompass the whole table), it isn't
going to know that - it will still assume that the index lookup is the
best choice.

Some ways around this might be to use a second query that just returns
the whole table where that makes sense - it will know how to do that.
Alternatively, if most queries will return a significant portion of the
table, you might try rebinding the query with REOPT ONCE (that is, it
will wait until the first call to the query to determine the plan, when
it has some actual values to use). If the results vary wildly from
call to call (some do return just 15 rows, others return millions) that
you could rebind with REOPT ALWAYS (that is, always rebuild the plan
when running the query). That last should give you the best possible
plan - at a cost of always rebuilding the plan.

I tried with and without OPTIMIZE clause with same results.
Unfortunately REOPT option is not available on LUW, it is only available
on OS/390.

>
-Chris
Any idea why does it not prefetch data?
Based upon the query optimizer must know that it is going to fetch data
sequentially.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Dec 7 '06 #3
Statistics from your original post indicate ~75 rows/page. The plan
shows that ~825 rows are expected. 825/75 = 11 pages of data to
retrieve, way to low to start the query with prefetch.

If you have the SEQDETECT configuration parameter set to NO, then you
will not see the I/O change to sequential prefetch when physical
sequential page retrieval is occurring. In this case, even if sequential
prefetch occurs, you may not have the optimum retrieval mechanism.

The explain shows that the retrieval is using the index for sequencing,
avoiding a sort. This is very good for a small number of rows but MAY be
less efficient than a table acan and sort. It's a balancing act between
the additional costs of the sort vs the overhead of the nested loop join
process to use the index to access the data.

Phil Sherman

Hemant Shah wrote:
While stranded on information super highway ChrisC wrote:
>Hi.
Hemant Shah wrote:
>>...
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
...
It takes 3 minutes to browse through the whole table. I have other tables that
are bigger then this one and I can browse through them in few seconds.
Why do you have the OPTIMIZE FOR 15 ROWS in the query? I would expect
that DB2 will actually pay attention to that, and only allocate space
for for 15 rows being returned - which won't be optimal if you are
returning significantly more rows than that.

Also, since you are using parameter holders in the query, db2 can't
make quite a good a guess at what the query should do - so it makes a
best guess, and uses it in all cases. Where you are returning the
whole table (because the keys encompass the whole table), it isn't
going to know that - it will still assume that the index lookup is the
best choice.

Some ways around this might be to use a second query that just returns
the whole table where that makes sense - it will know how to do that.
Alternativel y, if most queries will return a significant portion of the
table, you might try rebinding the query with REOPT ONCE (that is, it
will wait until the first call to the query to determine the plan, when
it has some actual values to use). If the results vary wildly from
call to call (some do return just 15 rows, others return millions) that
you could rebind with REOPT ALWAYS (that is, always rebuild the plan
when running the query). That last should give you the best possible
plan - at a cost of always rebuilding the plan.


I tried with and without OPTIMIZE clause with same results.
Unfortunately REOPT option is not available on LUW, it is only available
on OS/390.

>-Chris

Any idea why does it not prefetch data?
Based upon the query optimizer must know that it is going to fetch data
sequentially.
Dec 7 '06 #4
While stranded on information super highway Phil Sherman wrote:
Statistics from your original post indicate ~75 rows/page. The plan
shows that ~825 rows are expected. 825/75 = 11 pages of data to
retrieve, way to low to start the query with prefetch.

If you have the SEQDETECT configuration parameter set to NO, then you
will not see the I/O change to sequential prefetch when physical
sequential page retrieval is occurring. In this case, even if sequential
prefetch occurs, you may not have the optimum retrieval mechanism.
SEQDETECT is set to yes.

Sequential detect flag (SEQDETECT) = YES
>
The explain shows that the retrieval is using the index for sequencing,
avoiding a sort. This is very good for a small number of rows but MAY be
less efficient than a table acan and sort. It's a balancing act between
the additional costs of the sort vs the overhead of the nested loop join
process to use the index to access the data.
Thanks for the explaination.
Phil Sherman

Hemant Shah wrote:
>While stranded on information super highway ChrisC wrote:
>>Hi.
Hemant Shah wrote:
...
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
...
It takes 3 minutes to browse through the whole table. I have other tables that
are bigger then this one and I can browse through them in few seconds.
Why do you have the OPTIMIZE FOR 15 ROWS in the query? I would expect
that DB2 will actually pay attention to that, and only allocate space
for for 15 rows being returned - which won't be optimal if you are
returning significantly more rows than that.

Also, since you are using parameter holders in the query, db2 can't
make quite a good a guess at what the query should do - so it makes a
best guess, and uses it in all cases. Where you are returning the
whole table (because the keys encompass the whole table), it isn't
going to know that - it will still assume that the index lookup is the
best choice.

Some ways around this might be to use a second query that just returns
the whole table where that makes sense - it will know how to do that.
Alternatively , if most queries will return a significant portion of the
table, you might try rebinding the query with REOPT ONCE (that is, it
will wait until the first call to the query to determine the plan, when
it has some actual values to use). If the results vary wildly from
call to call (some do return just 15 rows, others return millions) that
you could rebind with REOPT ALWAYS (that is, always rebuild the plan
when running the query). That last should give you the best possible
plan - at a cost of always rebuilding the plan.


I tried with and without OPTIMIZE clause with same results.
Unfortunately REOPT option is not available on LUW, it is only available
on OS/390.

>>-Chris

Any idea why does it not prefetch data?
Based upon the query optimizer must know that it is going to fetch data
sequentially.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Dec 7 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3055
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me solve my problem. I'll turn to MySQL doc after getting through this pressing project. Thanks a lot Roger! Babale -----Urspr=FCngliche...
3
3039
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going on? I have tested this extensively and can say for certain that installing this hot fix is what has caused the performance problem. I just don't...
5
1340
by: Alexander Korovyev | last post by:
Suppose I have two tables: CREATE TABLE Tab1 ( NOT NULL, NOT NULL, NOT NULL, NOT NULL) CREATE TABLE Tab2 ( NOT NULL,
3
2774
by: gizmo | last post by:
I have a stored procedure that queries a database using a Select statement with some inner joins and conditions. With over 9 million records it takes 1 min 36 sec to complete. This is too slow for my requirements. Is there any way I can optimize this query. I have thought about using an indexed view. I haven't done one before, does...
4
2039
by: bhbgroup | last post by:
I have a query on one large table. I only add one condition, i.e. a date (the SQL reads like 'where date > parameterdate'. This query is rather quick if 'parameterdate' is either explicitly written into the query or if it is a 'normal' access parameter value that is entered during the query. If I however create a separate parameter table that...
11
2784
by: my-wings | last post by:
I think I've painted myself into a corner, and I'm hoping someone can help me out. I have a table of books (tblBooks), which includes a field (strPubName) for Publisher Name and another field (strPubCity) for Publisher City. These two fields have a many-to-one relationship with tables, (tlkpPubName and tlkpPubCity) respectively. The lookup...
59
7472
by: Rico | last post by:
Hello, I have an application that I'm converting to Access 2003 and SQL Server 2005 Express. The application uses extensive use of DAO and the SEEK method on indexes. I'm having an issue when the recordset opens a table. When I write Set rst = db.OpenRecordset("MyTable",dbOpenTable, dbReadOnly) I get an error. I believe it's...
14
3480
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These averages are compared to their expected productivity. However, the expectation changes - it may be 60% for a while, then change to 50%. Initially, I was averaging the expectation, along with the...
7
9682
by: tman | last post by:
I am generating a very large tree list in my program and while it's performance is great once loaded it takes a really long time to load. I create a root TreeNode "offline" and go through the process of creating building up the tree from there. Only when I am done do I go over to the actual TreeView object on my form and add the my root to...
8
2378
by: rshivaraman | last post by:
Hi : I have a TableA with around 10 columns with varchar and numeric datatypes It has 500 million records and its size is 999999999 KB. i believe it is kb i got this data after running sp_spaceused on it. The index_size was also pretty big in 6 digits. On looking at the tableA
0
7679
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7992
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7756
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8048
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6385
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5571
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3722
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3722
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1284
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.