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
Post a Comment