Query Tuning - SOS

[karthik]

Hello,

Its been a long time since I posted something here...
I am into a deep problem at the moment.
Looking forward for best solutions at progresstalk.

Scenario:
I have a table which contains 22 Million Records in it.
Which contains data that can't be archived or deleted but to be kept in there and unfortunately the table needs to be searched and its a Online service(Hmmm....that makes it a uphill task).

The table structure looks like this.

Tablename: searchtable
Field & DataType:
st1 integer
st2 character
st3 character
st4 character
st5 character
st6 decimal
st7 character
st8 character
st9 character
st10 character

Please note that there is no primary unique index on this table.
st6 stores both date and time in the form of decimal.

Index details

idxindex1 components(all in ascending order)
(st1,st2,st3,st4,st5,st6)
idxindex2 components(all in ascending order)
(st1,st3,st4,st2,st5,st6)

The Index is built keeping in mind the sorted order required
when the search results needs to be displayed and enables
me to write restrictive query.

my search queries looks like this..

Query1:

for each tablename use-index idxindex1
no-lock
where tablename.st1 = <value-from-variable>
and tablename.st2 >= <alphabet-to-start search>
and tablename.st2 < <next-letter just after search alphabet>
and tablename.st3 = '<value-from-variable>':

<statements-purely-display the results no computations>.

end.

Query2:

for each tablename use-index idxindex2
no-lock
where tablename.st1 = <value-from-variable>
and tablename.st3 = '<value-from-variable>'
and tablename.st2 >= <alphabet-to-start search>
and tablename.st2 < <next-letter just after search alphabet>:

<statements-purely-display the results no computations>.

end.

Now, the problem is comes to Bracketing the values by the index.

Query1 works well if there is a large number of records which
satisfies the condition tablename.st3 = '<value-from-variable>'
in the query.However it fails if the number of records which satisfy the above condition are too few.This is where Query2 works well,in other words if there are too few rows which satisfy the above condition
records are returned quickly.

However, to return the results in a 20-30 second span of TIME from a volumnous table like tablename I need to build the above 'Logic' in a program which has the intelligence to first try Query1 if it takes more than 10 seconds to return results, abort Query1 and try Query2 until the timeout.

I am Looking forward for some brlliant ideas at progress talk of accomplishing this.

Many Thanks,
Karthik

 

[LarryD]

While you can use a temp-table and elapsed time to do what you want, there is one simple "obvious" answer to make it as fast as possible, and that is to add this index:

new-index (in ascending order)
(st1,st3,st2,st4,st5,st6)

Other than the time involved to do it, is there a reason why you don't add that index to satisfy both queries and you can just have one query to use?

There are a number of other possible improvements, but from the index standpoint my understanding is that in general when using indexes Progress brackets based on the last equality/range match, then 'sweeps' the data rows to satisfy the rest.

So in your example, when using idxindex1, it will be able to retrieve all st1 values, then use the range in st2, within the indexes... then it will have to sweep the rest of the table rows in that st2 range to look for st3 equals something.

When using idxindex2, it will be able to retrieve all st1 values, then the st3 values within st1 using the indexes... then it will have to sweep the rest of the table rows to find data matching the st2 range.

If you add the new index, it will be able to bracket the index searching to the query without sweeping the data rows when using 'for each' with the 'use-index'.

I believe (and others may clarify/correct me here), that if you use the "OPEN QUERY" method it would use both existing indexes to satisfy the query. However, this may or may not be faster depending on the number of rows that satisfy each index.

Other questions:

What version of Progress are you on? Are you using type II storage areas for this table and it's indexes? And just for curiousity, why no primary key?

 

[karthik]

Thanks Larry.Could please tell me what exactly you mean by type II storage area of an Index.You mean a area of storage other than "Schema Area"?.Currently I am on 9.1D09 Its more like a audit trial table hence there is no primary key because every record varies by only date and time info

 

[LarryD]

Sorry, Type II storage areas are only in 10.x (I believe), so that won't help. If you do migrate to OE10, it would be something you will definitely want to look into for large tables. It's improvements in speed of processing (especially reports and queries) are quite impressive.

In your situation, adding the suggested index should show some decent improvement.