Different indexes - performance analysis question

Hello

I'm doing some performance analysis for my application. I'm doing 7600 SQL queries based on the following SQL query:

SELECT LocationId, ProductId, BatchId, SUM(Quantity) AS Quantity FROM Logistics WHERE UserId = [number] AND ProductId IN ([productidlist]) GROUP BY LocationId, ProductId, BatchId;

Data in table Logistics has LocationId = 1 and BatchId = 0 for absolute all rows in this test, UserId and ProductId may be different. For each SQL Query it's doing, it's also inserting new rows in the table. The table starts with 0 rows for the first SQL query above, ends with 35 000 rows. Execution for both Editions below is exactly the same (same data inserts)

The graph below is showing the time used in milliseconds (y axis) for each query (x axis) - both editions is doing the exactly the same query but with different indexes.

URL to graph: http://www.lostfields.com/sqlindexing.gif

Edition 2 has the following priority on the PK: UserId, LocationId, ProductId, BatchId
Edition 2 Optimized has the following priority on the PK: UserId, ProductId, BatchId, LocationId.

How come Edition 2 have to scan over a lot more indexes than Edition 2 Optimized? As I can see it this shouldn't have happened since LocationId = 1 all the time. Or am I missing something?

[edit] <img> tag didn't work so I have to just paste the url

What happens if you put locationId = 1 in your query. Its all about selectivity.

What other columns are on the table. I would also look at what happens if you include userid in the group by.

Can you capture the two query plans.

|||

Yes, thank you

It did help a lot to include LocationId = 1 in the query, when I did this in Edition 2 it became just similar to Edition 2 Optimized. I couldn't see any performance increase by including UserId in the GROUP BY.

The other fields/columns are just a TransactionDate (date for the insert) and TransactionId (identity to make it unqiue for stopping a duplicate insert).

The two Execution Plans can be found at http://www.lostfields.com/sqlindexing_plan.gif

I'm not sure why Edition 2 Optimized has a sort method there though, as Edition 2 doesn't, but have a filter (since LocationId isn't in where clause I guess).