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

LVL 1
JDCamAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
That's not a lot of rows, but depending on other factors, like row length (which dictates the number of blocks that must be read) have a huge bearing on performance.

There needs to be a scalar index on column DELIVER_BY_END, or at least a composite index where the column is first in the list.  DB2 will read everything it needs from the index and won't have to read the row data.


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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?
0
 
JDCamAuthor Commented:
Not huge. Table has 531759 rows, query will return 100-150 records in 20 or so groups.
Not sure about the indexes
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It really looks like it needs at least an index on Deliver_by_end column.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Kent, only that column is used in the SELECT so a regular index on it should be enough.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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.
1
 
JDCamAuthor Commented:
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

0
 
Kent OlsenData Warehouse Architect / DBACommented:
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%.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.