Hi all,

We are running a simple query against a table which is taking to long to
execute... Details are as follows:

Table has about 2 million records which is growing all the time (system
been running for 8 months), if anything, the growth rate will increase.
The table has about 40 fields of time stamps, integers, floats, a few
var chars and some blobs for any large text fields.

When we run a query such as
Select *
from TheTable
where TheTable.Type in (342, 234, 6372, 234)
and TheTable.Status = 5
and InUse = 'T'

The type field is an integer foreign key with perhaps 20000 different
values, The Status field is a small int with about 10 different values
and InUse is a Char(1) field with two values (T and F).

This query would typically return between 50 and 500 rows. We get a
response time of > 1 minute. Is this normal for a 2 million record
table, I believe it is using the foreign key index in the query (it
mentions it in the plan).

Any suggestions on how I should refine my query, indexes, table
structure. The last system used two tables to store the same data... One
table held data that was at a status < finished which is where most of
the queries were run. Once the data was 'finished' it was moved to the
larger table holding all the old data. I was hoping to get away from
this as there would be a bit more involved in behind the scenes in the
moving of data between the two tables. The performance figures might
suggest that we need to make the move to two tables.

Any suggestions appreciated.

Regards
C Fraser



######################################################################
Attention:
The information in this email and in any attachments is confidential.
If you are not the intended recipient then please do not distribute,
copy or use this information. Please notify us immediately by return
email and then delete the message from your computer.
Any views or opinions presented are solely those of the author.
######################################################################