asked on

DB2 Query too slow

I wrote the below query against a DB2 database.
It returns good results, but is incredibly slow (90 seconds).
What can I do to make the query more efficient?

SELECT deliver_by_end XVALUE, COUNT(*) YVALUE
FROM TLORDER
WHERE DELIVER_BY_END BETWEEN TIMESTAMP_ISO(CURRENT DATE) AND TIMESTAMP_ISO(CURRENT DATE + 3 DAYS)
GROUP BY Deliver_by_end ORDER BY 1

Open in new window

Kent Olsen

Hi JD,

That looks pretty streamlined. How big is the table, what count are you getting, and is the table properly indexed with current statistics?

JDCam

ASKER

Not huge. Table has 531759 rows, query will return 100-150 records in 20 or so groups.
Not sure about the indexes

Vitor Montalvão

It really looks like it needs at least an index on Deliver_by_end column.

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

Kent, only that column is used in the SELECT so a regular index on it should be enough.

Kent Olsen

Hi Vitor,

Agreed. I was trying to point out that either of the indexes described should be sufficient so there would be no need to create the scalar index if the composite index already existed or would be added.

JDCam

ASKER

Thanks I will have an index added for this date field.
In the interim, I added a subselect to pre-select from a column with an index.
Not ideal, but the time is now < 1 second

SELECT deliver_by_end XVALUE, COUNT(*) YVALUE
FROM (
Select deliver_by_End from TLORDER
order by detail_line_ID DESC
Fetch first 2000 rows only)
--WHERE DELIVER_BY_END BETWEEN TIMESTAMP_ISO(CURRENT DATE) AND TIMESTAMP_ISO(CURRENT DATE + 3 DAYS)
WHERE DATE(DELIVER_BY_END) = TIMESTAMP_ISO(CURRENT DATE)
AND TIME(DELIVER_BY_END) <> '00.00.00'
GROUP BY Deliver_by_end ORDER BY 1

Open in new window

Kent Olsen

Be careful that you're getting the correct results here and the "Fetch first 2000 rows" clause is certainly limiting the number of rows that will be counted in the result, especially if the runtime is being cut by 99%.