Covering index & Aggregate


i'm doing perfomance analysis application , sql server 2005.

i'm inserting 400k rows, doing 20k selects (one each 20 inserts). edition 1 has non-unique clustered index (userid, productid, batchid, locationid), editon 2 has non-clustered index (userid, productid, batchid, locationid, quantity) in addition clustered 1 (same edition 1 got).

i'm testing because of article http://www.sql-server-performance.com/covering_indexes.asp says aggregate functions alot. i'm doing sum().

the sql i'm executing 20k times is:

select productid, batchid, locationid, sum(quantity) quantity, count(*) lines
from logistics
where userid = [client id] , productid in ([product list])
group productid, batchid, locationid

the problem edition 2 1-2 ms slower, when inserts has no differences (they equal). execution plan says editon 2 should perform better (less io , cpu operations) since has covering index.

 

why this? can't find logical explenations problem. can reason that the edition 2 has bigger defragmentation problems edition 1 since it's non-clustered index?



SQL Server  >  SQL Server Database Engine



Comments

Popular posts from this blog

Motherboard replacement

Cannot create Full Text Search catalog after upgrading to V12 - Database is not fully started up or it is not in an ONLINE state

Remote Desktop App - Error 0x207 or 0x607