I am comparing two queries in SQL Server 2012. The goal is to use all of the pertinent information available from the query optimizer when choosing the best query. Both queries produce the same results; the maximum orderid for all customers.
Clearing out the buffer pool was done before executing each query with FREEPROCCACHE and DROPCLEANBUFFERS
Using the information provided below, which query is the better choice?
-- Query 1 - return the maximum order id for a customer SELECT orderid, custid FROM Sales.Orders AS O1 WHERE orderid = (SELECT MAX(O2.orderid) FROM Sales.Orders AS O2 WHERE O2.custid = O1.custid); -- Query 2 - return the maximum order id for a customer SELECT MAX(orderid), custid FROM Sales.Orders AS O1 group by custid order by custid
Query 1 STATISTICS TIME: CPU time = 0ms, elapsed time = 24 ms
Query 2 STATISTICS TIME: CPU time = 0 ms, elapsed time = 23 ms
Query 1 STATISTICS IO: Table 'Orders'. Scan count 1, logical reads 5, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Query 2 STATISTICS IO: Table 'Orders'. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SELECT properties Query 1
SELECT properties Query 2
- Batch cost 48%
- Logical Reads 5
- Physical Reads 2
- Read-ahead Reads: 0
- CPU Time: 0ms
- Elapsed Time 24ms
- Estimated subtree cost: 0.0050276
- CompileCPU: 2
- CompileMemory: 384
- CompileTime: 2
- Batch cost 52%
- Logical Reads 4
- Physcial Reads 1
- Read-ahead Reads: 8
- CPU Time 0
- Elapsed Time 23ms
- Estimated subtree cost: 0.0054782
- CompileCPU: 0
- CompileMemory: 192
- CompileTime: 0
Personally, even though Query 2 has a higher batch cost according to the graphical plan, I think its more efficent that Query 1. This because query 2 requires less logical reads, has a slightly lower elapsed time, the compilecpu, compilememory and compiletime values are lower. read-ahead reads are 8 for query 2 and 0 for query 1.
Clustered Index definition
ALTER TABLE [Sales].[Orders] ADD CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [orderid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Non-Clustered Index idx_nc_custid
CREATE NONCLUSTERED INDEX [idx_nc_custid] ON [Sales].[Orders] ( [custid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO