If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > slow query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2012
Posts: 109
slow query

Hi,
I'm using DB2 10.1 ESE on Windows.

I have two tables, each one has 200K rows.

CREATE TABLE SYSREPORTS(
actId INTEGER NOT NULL,
propId INTEGER NOT NULL,
bckId INTEGER NOT NULL,
sysId INTEGER NOT NULL,
failed SMALLINT NOT NULL,
template SMALLINT NOT NULL,
description VARCHAR(8000),
rnum BIGINT NOT NULL,
address BIGINT NOT NULL
CONSTRAINT pk_sysreports PRIMARY KEY ( actId, propId, bckId, sysId )
);

CREATE TABLE SMART_PCS(
id INTEGER NOT NULL,
);

SYSREPORTS has a further index, not UNIQUE, on sysId.

If I run this query

select S.sysid FROM SMART_PCS C
JOIN SYSREPORTS S ON S.sysId= C.id
WHERE S.actId = p_actId AND S.propId = p_propId AND S.sysId = p_sysId for read only;

it takes less than a second, if I run

select S.sysid, S.description, S.rnum FROM SMART_PCS C
JOIN SYSREPORTS S ON S.sysId= C.id
WHERE S.actId = p_actId AND S.propId = p_propId AND S.sysId = p_sysId for read only;

it takes more than 400 seconds.

I cannot include description and rnum in the index since it would require the index to be unique (and I don't want it).

How can I solve this issue?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,123
Is there a difference in the access plan for both queries?

Andy
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2012
Posts: 109
Yes there is!
Could you please help me understand what's happening?

FAST query access plan:
--------------------------
Total Cost: 278.963
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
508.424
HSJOIN
( 2)
278.963
120.348
/------+-------\
127635 1000
IXSCAN TBSCAN
( 3) ( 4)
264.062 7.61769
119.348 1
| |
5.56488e+07 1000
INDEX: MYS TABLE: MYS
PK_SYSREPORTS SMART_PCS
Q2 Q1

-------------------------
SLOW query access plan:
-------------------------
Total Cost: 17696.8
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
508.424
NLJOIN
( 2)
17696.8
2509.42
/-------+--------\
1000 0.508424
TBSCAN FETCH
( 3) ( 4)
7.61769 17.7037
1 2.50842
| /---+----\
1000 0.508424 5.56488e+07
TABLE: MYS IXSCAN TABLE: MYS
FROM SMART_PCS ( 5) SYSREPORTS
Q1 14.1192 Q2
2
|
5.56488e+07
INDEX: MYS
PK_SYSREPORTS
Q2
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,123
You need an index on SMART_PCS. You are table scanning it in both cases. Because, for the fast query you only join to the other table on its index, it uses a has join. The bad query, you want other columns from the other table, so it uses a loop join. A table scan (SMART_PCS) with a loop join will always take a while.

Andy
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2012
Posts: 109
Thanks Andy.
I added an index on SMART_PCS and now my query runs in 100 seconds, great improvement!

However, I still have a problem.
If I run the same query on SQL Server the first time it takes 110 seconds, but the second time it takes only 2 seconds!!

If I run the same query on DB2 it always takes 100 seconds.
I expected data to be already present in bufferpool so to have the same results as SQL Server, I also tried enlarging the bufferpool size (now it's 27000), the result is the same...
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,123
Did you increase the correct bufferpool? Is the index in a separate one. If so, that is the BP you need to increase.

Andy
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Mar 2012
Posts: 109
I've got only one big bufferpool, the default one, and increased it to
30000
40000
45000
55000
with the command
db2 "alter bufferpool ibmdefaultbp immediate size xxx automatic".
The query is not affected at all by any of these change
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,123
55,000 is a small BP. Assuming a 4K page size, it is only 225MB. How many pages are your tables and indexes?


Andy
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 314
plz publish the full access plan here ( both slow and fast , use db2exfmt )
and how many rows will the query get ( count(*) )?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On