I have the query below running against a table with no indexes. This is the
only query that ever runs against it so I was planning on creating a
clustered index for it to optimize performance. The table does rarely
receives inserts or updates but it is queried 10 or so times per second.
Right now the performance is pitiful. Also note, I do not have the authority
to change the query, only add an index.
SELECT TOP 25 umBatchID, umDocumentNumber, umLineSequence, DSTINDX,
umTransactionAmount, DEX_ROW_ID from UMPST04 where umBatchID = @.BatchID and
umDocumentNumber = between @.Doc1 and @.Doc2 and umLineSequence between @.Line1
and @.Line2 order by umBatchID asc, umDocumentNumber asc, umLineSequence asc,
DEX_ROW_ID asc
should I create the clustered index based predicate? Meaning index on
umBatchID, umDocumentNumber, umLineSequence
Or based make a convering index to include all the columns in the select
portion
or based on the 'order by' meaning umBatchID, umDocumentNumber,
umLineSequence, DEX_ROW_ID.
Any suggestions are appreciated...The index you suggest should speed that query considerably; only thing would
be to decide the order of umDocumentNumber and umLineSequence in the index
-- the one that will narrow the result set more (if at all) should come
before the other. Guessing from the field names you've probably got the righ
t
order.
A clustered index is effectivly a covering index because it is not seperate
from the table as a nonclustered index is - when a clustered index is used i
t
does not need a pointer back to the original record to do a bookmark lookup;
you can see this in the query plan.
"Dean" wrote:
> I have the query below running against a table with no indexes. This is th
e
> only query that ever runs against it so I was planning on creating a
> clustered index for it to optimize performance. The table does rarely
> receives inserts or updates but it is queried 10 or so times per second.
> Right now the performance is pitiful. Also note, I do not have the authori
ty
> to change the query, only add an index.
>
> SELECT TOP 25 umBatchID, umDocumentNumber, umLineSequence, DSTINDX,
> umTransactionAmount, DEX_ROW_ID from UMPST04 where umBatchID = @.BatchID an
d
> umDocumentNumber = between @.Doc1 and @.Doc2 and umLineSequence between @.Lin
e1
> and @.Line2 order by umBatchID asc, umDocumentNumber asc, umLineSequence as
c,
> DEX_ROW_ID asc
> should I create the clustered index based predicate? Meaning index on
> umBatchID, umDocumentNumber, umLineSequence
> Or based make a convering index to include all the columns in the select
> portion
> or based on the 'order by' meaning umBatchID, umDocumentNumber,
> umLineSequence, DEX_ROW_ID.
> Any suggestions are appreciated...
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment