3

I have very simple query which returns 200K records very slow (20 seconds).

SELECT * FROM TABLE ORDER BY ID DESC

If I do just

 SELECT * FROM TABLE

it returns quick result.

I created INDEX on that field ID (ALLOW REVERSE SCANS) but still returns very similar response. Where can be the problem? What can be the cause of stagnation for this query? I updated statictics and index table metadata.

Shouldn't INDEX help me on this ORDER BY FIELD?

My server has 8GB RAM. Buffer pool has value of 128MB. I adjusted it to 1 GB but still no too much progress.

I am using DB2 database.

Configuration Parameters are with this values: (SORTHEAP) = AUTOMATIC(164) and (SHEAPTHRES_SHR) = AUTOMATIC(824). (SHEAPTHRES) = 0. Should I maybe adjust them and on what values?

This is live system in production so I will be very thankful for help.

Thank you

Improve this question
1
  • I dont know anything about db2, but assuming it is similar to SQL Server, did you try a clustered index instead of a non clustered index (since you are using a select * query)?
    – Akash
    Mar 10, 2013 at 20:27

1 Answer 1

Reset to default
3

Well, first of all a SELECT * will pretty much void using any indexes. DB2 will see that you require the entire table and thus do a table scan (which in itself can be expensive).

After that you are doing a an ORDER BY, which causes DB2 to do a SORT on the table it just scanned (another expensive operation). So you have two expensive operations one after the other.

Another thing.....if ID is the primary key...it already built an index for you under the covers. (which again it will ignore with a SELECT *). So you now have a second index that is probably just taking up disk space. (Just as an fyi any field that has a unique constraint-including the primary key-DB2 automatically builds a unique index for you.)

So first off, what this really speaks to is a bad query (no other way to put it). Do you have any predicates to add to the SQL? If you can cut down what you are selecting with a WHERE clause, or if you don't need every column in the SELECT, then you could build an index or indexes over those columns and that would help.

I would advise you to do an EXPLAIN over this query so you can see the cost and the query plan that DB2 used. Then as you test out indexes, etc, you can see if DB2 chooses to take advantage of them or not, and which is the most efficient method.

Improve this answer
14
  • Hi, thansk for response. ID is not primary column so DB does not already have created index for that field- I checked it. Unofortunalety I can not change query it must begin with SELECT * (this is some IBM Maximo appliction and this is in some List tab where by default it starts with SELECT * and trust me it can not be changed). SELECT * is executred quite fast but my customer wants to have ORDER BY ID DESC so to be able to see immediately new records in list tab. I understand what you are talking but this is only 200K records and for this powerfull DB2 database it should be exucuted fast
    – Dejan
    Feb 8, 2013 at 15:14
  • But query is not bad - it is the most simpel and most logic query- show me all records (which I can filter on List tab how I want) order firstly by new ones. This is not bad query definetly. I run EXPLAIN but it shows me that it is using index on some other fields (some ield STATUS and CLASS). Like it is using the wrong index - why and how to change it?? I have index for ID only but it is using other index. Also are my configuration paramteres and buffer pool set adequately? Thank you for effort and answers
    – Dejan
    Feb 8, 2013 at 15:19
  • Yeah...other IBM products do tend to use the SELECT * thing, which kills performance after a while. The hard thing is a SELECT * will mean a LOT of disk I/O for you (at least the first time and depending on if it stays in your buffer pool). The ORDER BY will cause a SORT, which will probably cause more I/O in your temporary tablespace. Again, do an EXPLAIN over the query. It will show you the truth of what DB2 is doing. Other things to take into consideration: are you at the latest fix packs? Can you call IBM about the Maximo issue? Maybe they can fix on their end_it"s their product after all.
    – Chris Aldrich
    Feb 8, 2013 at 15:32
  • 1
    Have you done an EXPLAIN yet on it?
    – Chris Aldrich
    Feb 8, 2013 at 15:58
  • 2
    SELECT * is fast because DB2 can just start scanning the table and returning rows. SELECT COUNT(*) is fast because DB2 can scan an index very quickly to get a row count. SELECT * ... ORDER BY is slow because DB2 has to fetch and sort the entire table.
    – Ian Bjorhovde
    Feb 8, 2013 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.