Hi All,

We are experiencing high execution times some times on the following query.

SELECT COUNT(A.KEY),
B.DT,
HOUR(B.TM),
C.ID,
D.APPL
FROM CLIENT A,
DETAIL B,
EMPLOYEE C,
MAINTENANCE D
WHERE A.ID = :L-ID
AND A.KEY_NBR = B.KEY_NBR
AND B.DT >= DATE(:L-START-DT)
AND B.DT <= DATE(:L-END-DT)
AND A.KEY_NBR = C.KEY_NBR
AND A.KEY_NBR = D.KEY_NBR
GROUP BY B.DT, HOUR(B.TM),
C.ID, D.APPL

All the tables used in this Query have Multi Million Records, Is there a way that i can reduce the execution time of this Query.

I guess you could start here:

Writing and tuning queries for optimal performance - IBM DB2 9.7 for Linux, UNIX, and Windows
How to investigate poor query performance using section actuals - IBM DB2 9.7 for Linux, UNIX, and Windows

if you could, Plz post the access plan for the query ( db2exfmt output ) here.
also you can run db2advis on it to see whether there are some indexes you can create to improve query performance.

As others have mentioned, you have not supplied enough information. However, I would change the following:

1. SELECT COUNT(A.KEY) change to SELECT COUNT(*)

2. AND B.DT >= DATE(:L-START-DT) AND B.DT <= DATE(:L-END-DT)
change to AND B.DT BETWEEN :L-START-DT and :L-END-DT
(make sure the host variables are in char format of 'YYYY-MM-DD' in your program. No need to use DATE Function.

This definitely depends on what they want to count - the number of rows or the number of A.KEY values that are not null.

Actually, in many cases the optimizer will rewrite "B.DT BETWEEN :L-START-DT and :L-END-DT" back to "B.DT >= DATE(:L-START-DT) AND B.DT <= DATE(:L-END-DT)".

I am assuming that A.KEY is the PK and is defined as NOT NULL.

That may be, but I am also assuming because of host variables used in the SQL (with the colon preceeding the host variable in the SQL) that this is DB2 for z/OS (It could be DB2 LUW with embeded SQL in a language like C, but that is not very common these days). IBM may have changed it, but at one time I know for sure that when using host variables on DB2 for z/OS that the BETWEEN syntax has a higher filter factor than >= and <=, and is therefore more likely to use an index. Higher filter factor means that DB2 assumes few rows will be returned. It is certainly possible that this has been changed on DB2 for z/OS to convert it for you like on LUW.

[QUOTE=Marcus_A;6527901]I am assuming that A.KEY is the PK and is defined as NOT NULL.

QUOTE]


Iam not a expert, but generally SELECT COUNT(*) is said to cost more than
SELECT COUNT(column1)...??? (This applies in MS SQL Server sure)
In this particular case, how does it matter A.KEY or any other column is used in the count() as it merely counts the row? Select COUNT(1) would also do the job?

In some cases (not sure about this case) using count(*) is more efficient than count(column-name) since the query may use an index access where that column does not exist, and then DB2 also has to read the column off the table to do the counting.

In DB2, count(*) does not cost more than count(A.KEY) if A.KEY is a column on the table. In some cases they will be the same, but count(A.KEY) will not be faster.

As to SQL Server, I have no idea, but I would seem very strange to me that count(*) would be slower in any DBMS.