I have a table myTable with myGuid (uniqueidentifier), myValues (float), myGroup (integer) and a bunch of other fields which are not important right now. I want to do something as simple as:
SELECT SUM(myValues)
FROM myTable
WHERE myGuid IN (SELECT * FROM ##test)
GROUP BY myGroup
##test is just a temporary table with a single field (guid_filter)
containing a bunch of uniqueidentifiers.
Now here's the strange thing:
When I create myTable with myGuid as the Primary Key (which seems like the obvious thing to do), the query is slow (<EDIT>8-12s</EDIT>).
When I create myTable with myAutoInc, an integer auto-increment field, as the Primary Key, the query is fast (~2s), even though the WHERE clause still filters by myGuid. (myGuid has just a "normal" non-clustered index in this scenario.)
Is there any logical explanation to this? My (naive) assumption was that the first option is faster, since SQL Server can use the guid to look up myValues rather than having to go through guid -> myAutoInc -> myValues. So, the result was very surprising for me.
Here's the SHOWPLAN_TEXT output. Slow scenario (XML query plan): (EDIT: Updated, thanks to Remus for noticing that there was an unnecessary additional non-clustered index on myGuid)
|--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [Expr1015]=(0) THEN NULL ELSE [Expr1016] END))
|--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([Expr1015]=COUNT_BIG([myDB].[dbo].[myTable].[myValues]), [Expr1016]=SUM([myDB].[dbo].[myTable].[myValues])))
|--Sort(DISTINCT ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC, [myDB].[dbo].[myTable].[myGuid] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[##test].[guid_filter], [Expr1014]) OPTIMIZED WITH UNORDERED PREFETCH)
|--Table Scan(OBJECT:([tempdb].[dbo].[##test]))
|--Clustered Index Seek(OBJECT:([myDB].[dbo].[myTable].[PK__myTable__2334397B]), SEEK:([myDB].[dbo].[myTable].[myGuid]=[tempdb].[dbo].[##test].[guid_filter]) ORDERED FORWARD)
Table 'myTable'. Scan count 0, logical reads 38046, physical reads 1, read-ahead reads 6914, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '##test'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Fast scenario (XML query plan):
|--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [globalagg1009]=(0) THEN NULL ELSE [globalagg1011] END))
|--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([globalagg1009]=SUM([partialagg1008]), [globalagg1011]=SUM([partialagg1010])))
|--Parallelism(Gather Streams, ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC))
|--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([partialagg1008]=COUNT_BIG([myDB].[dbo].[myTable].[myValues]), [partialagg1010]=SUM([myDB].[dbo].[myTable].[myValues])))
|--Sort(DISTINCT ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC, [myDB].[dbo].[myTable].[myAutoInc] ASC))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([myDB].[dbo].[myTable].[myGroup], [myDB].[dbo].[myTable].[myAutoInc]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([myDB].[dbo].[myTable].[myAutoInc], [Expr1017]) OPTIMIZED WITH UNORDERED PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[##test].[guid_filter], [Expr1016]) OPTIMIZED WITH UNORDERED PREFETCH)
| |--Table Scan(OBJECT:([tempdb].[dbo].[##test]))
| |--Index Seek(OBJECT:([myDB].[dbo].[myTable].[myGuid]), SEEK:([myDB].[dbo].[myTable].[myGuid]=[tempdb].[dbo].[##test].[guid_filter]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([myDB].[dbo].[myTable].[PK__myTable__2334397B]), SEEK:([myDB].[dbo].[myTable].[myAutoInc]=[myDB].[dbo].[myTable].[myAutoInc]) LOOKUP ORDERED FORWARD)
Table 'myTable'. Scan count 0, logical reads 66988, physical reads 48, read-ahead reads 2515, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '##test'. Scan count 5, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.