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?

Is there a difference in the access plan for both queries?

Andy

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

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

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...

Did you increase the correct bufferpool? Is the index in a separate one. If so, that is the BP you need to increase.

Andy

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

55,000 is a small BP. Assuming a 4K page size, it is only 225MB. How many pages are your tables and indexes?


Andy

plz publish the full access plan here ( both slow and fast , use db2exfmt )
and how many rows will the query get ( count(*) )?