RE: QUERY ON TIMESTAMP COLUMN

[Zabach, Elke]

Yusuf Gangat  wrote:   > I have a table called TRANSACTIONFILE that has 15 fields and about 3 million records > and will continue to grow. One of the columns is a timestamp column and even > though it is indexed it takes a long time to bring back a result based on that > column as a search criterion. An example of my query is : >  �SELECT * FROM TRANSACTIONFILE WHERE TRANSACTIONDATE >= �2000-01-01 00:00:00� >  AND TRANSACTIONDATE <= �2000-12-31 00:00:00� � >  which brings back all transactions for the year 2000. > > > Is there any way of speeding this query up or is 15 to 20 minutes > an acceptable time for the above query? Or is the volume of data in the table too much? > > Any help will be greatly appreciated How many of your 3 million of rows will belong to year 2000 ?   Depending on the number resp. the percentage of rows expected to belong to the result compared to the whole number of rows in your table the index is NOT used (and it is better that it is NOT used than using it).   Sometimes users are not aware of the fact, that select * is an easy way for the user, but will need more space per resultrow --> more time for bringing them back compared to select <only those columns really needed, which are not all columns>. Please provide the table-definition. Are LONG-columns included?   Do you use ORDER BY / GROUP BY or something of that kind or do you really use exactly the select given above?   Which version of kernel do you use, which client of what version of that client? Which operating system for client/server ?   Elke SAP Labs Berlin