0

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.

I am hoping for help of db experts (administrators), I know this is not simple question.

Thank you

Stefke
  • 141
  • 8
  • 19

1 Answers1

0

The bufferpool is important and the sort heap parameter (along sheapthres and sheapthres_shr) At the same time, check if there a sort overflow, because this will mean the sort will be written into disk because of lack of memory, and for this a system temporary tablespace is necesary. Check where are they stored, and if the disk are fast enough.

Take a look at the access plan, and check if the index is taking into account.

The first query is very fast because it does not need any sort, just a table scan.

For the second one, an index does not do anything, because you will retrieve all data from the table, so it does not access the index (there is nothing in the 'where', no nothing is filtered)

Both queries need table scan, but the first one needs to be sorted, and that is the problem, the sort.

AngocA
  • 7,655
  • 6
  • 39
  • 55
  • Hi thank you very much for response.I have few more questions - Buffer pool is by defauklt set to 128MB (number of pages*page size) and I adjusted it to be 1GB but I have liittle more progress. – Stefke Feb 08 '13 at 13:42
  • but how do you mean both queries need table scan? Doesn't index have effect and on ORDER BY CLAUSE? Thank you for your help this is making me so much trouble in my business – Stefke Feb 08 '13 at 13:45
  • Can you pelase give me some preffered values for SORTHEAP,SHEAPTHRES_SHR ? My server has 8GB of RAM. Currently values are (SORTHEAP) = AUTOMATIC(164) and (SHEAPTHRES_SHR) = AUTOMATIC(824). Also I do not see SHEAPTHRES parameter? – Stefke Feb 08 '13 at 13:50
  • I found (SHEAPTHRES) = 0 . So by your opinion are those good values? – Stefke Feb 08 '13 at 13:54
  • If you are using a recent DB2 version, you can put all these parameters (sortheap, sheapthres) and even buffer pools in automatic. DB2 will change the values dynamically. In this way you do not bother with tunning. – AngocA Feb 09 '13 at 12:32
  • The index is used ONLY if a few rows are returned (access the index, and then seek for the pages in the table). Here you are retrieving the whole row (*, no filter by index (sargable posibility)), and all rows (no where clausule). That means that in both cases you are going to read the whole table, and that is transalated in IO operations. DB2 will see that looking at the index implies more IO operations (Index thus table), therefore it only does a table scan in both cases, with an extra operation when using the order by (sort). There is a difference between index and order by. – AngocA Feb 09 '13 at 12:38
  • Hello, thank you for your response..ok my paramters are set to AUTOMATIC as you can see..but what is the soluition than? As I understood you this powerfull IBM application using this powerful IBM database needs 20 seconds to retieve ONLY 200K records (which will be millions in the future maybe) and that is quite normal?? There is nothing it can be done to mprove this??? What can I done to make it fatser please do you have some suggestion? I am using version 9.7. There is something that can be done.. I am not db2 expert but I set db2 paramters, and still no progress :( – Stefke Feb 09 '13 at 20:17
  • Retrieve only the columns that you need, do not use *. (For example select empid, empname from employees). Filter early with the database (For example select empid, empname from employees where empid < 1000). That is the conclusion! How do you apply it for you case, it depends, but I am pretty sure that you do not need to PROCESS millions of records (at least you perform an ETL), you only need a part of them. SELECT * FROM TABLE is normally considered as an conception/design error. – AngocA Feb 11 '13 at 12:15
  • http://stackoverflow.com/questions/3388571/sql-using-select http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful – AngocA Feb 11 '13 at 12:17
  • Hi, thanks for reply. My statement must begin wth SELECT * and unfortunately I do not have impact on that because this is within some powerfull IBM Maximo application in List tab and there by default statmenet starts with SELECT *. People who worked with that know what I mean. So I can't change this for sure. That is some List tab application and operators want to list all records and than to filter them on different way as they wish but there is a huge number of possible combinations for filtering. So unfortunately nothing can be done on parameters db side? – Stefke Feb 11 '13 at 15:18
  • 1
    I really can't change SELECT *.It must begin with that. But this is powerful IBM Maximo applications which uses many huge companies over the world and for sure their statmements also begins like that. It can not be chnaged. I am sure that they have millions of records (tickets, configuration assets etc). I must mention that SELECT * is executed quite fast so database can retrive those records and columns fastly but SELECT * ORDER BY is very slow- how can I found where is stagnation? Sorry really for your time – Stefke Feb 11 '13 at 15:23
  • 1
    Create a cluster index in table, to sort the data before, but with something similar to ID (bigger granularity). Well, these changes should help to improve your query. – AngocA Feb 11 '13 at 16:46
  • SSD is not opion becuase it is on my customer servers in other country.Cluster index is cretaed by default on its primary key, this ID is not primary key and I can't create other cluster. You said big BP- I adjusted it on 1GB from 8GB- for which value I should set it?Do you have some link maybe how to dedicate, and create new buffer pool only for ONE table? I will appreciate link , I couldn't find any but maybe that will be the solution? SORTHEAP I should set on which value by your recommandation? Also preftecth size is at the moment set to automatic..on which size should I set it? Thank you – Stefke Feb 12 '13 at 09:01
  • Well, I cannot give any specific numbers because I do not know the environment. That is the objetive of a consultant, to see the env a propose a solution. Here, it is just global solutions. About the BP, just create a new one (db2 create bp BP pagesize 32 K) and then, alter the tablespace where the table is, in order to use the new bufferpool (I hope the table is in a dedicated tablespace, if not, move it to other tablespace). – AngocA Feb 12 '13 at 22:43