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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
It really looks like it needs at least an index on Deliver_by_end column.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Kent OlsenDBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 OlsenDBACommented:
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 OlsenDBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.