duane+sql (2/15/2013)
I'm seeing a huge performance difference between two queries that are almost identical. The only SQL difference is in the WHERE clause:Query1: QueryID=@QID
Query2: QueryID=ISNULL(@QID,A.QueryID)
Full Query 1 takes 100x + longer to execute. @QID is never null in this case b/c is set at the beginning. I would expect a declared variable (int) would be quicker than a function wrapping that declared variable. The actual execution plan says Query2 is more intensive, but actual time to process tells a much different story. Can anyone shed light on this?
Full Query1:
DECLARE @days INT; SET @days=7; DECLARE @date1 DATETIME; SET @date1=getdate(); DECLARE @date2 DATETIME; SET @date2=DATEADD(day,-@days,@date1); DECLARE @date3 DATETIME; SET @date3=DATEADD(day,-@days*2,@date1); DECLARE @QID INT; SET @QID = (SELECT id FROM Queries WHERE Query='abc');
SELECT COUNT(*) as Visits, Query, (SELECT AVG(CAST(Pos AS DECIMAL)) FROM GooglePos WHERE QueryID=A.QueryID AND PageID=A.PageID AND date>=@date2) AS Pos, (SELECT AVG(CAST(Pos AS DECIMAL)) FROM GooglePos WHERE QueryID=A.QueryID AND PageID=A.PageID AND date>=@date3 AND date<@date2) AS Pos2 FROM Visits A INNER JOIN Pages B ON A.PageID=B.id INNER JOIN Queries E ON E.id=A.QueryID WHERE date>=@date2 AND QueryID=@QID GROUP BY QueryID,Query,PageID;
Full Query2:
DECLARE @days INT; SET @days=7; DECLARE @date1 DATETIME; SET @date1=getdate(); DECLARE @date2 DATETIME; SET @date2=DATEADD(day,-@days,@date1); DECLARE @date3 DATETIME; SET @date3=DATEADD(day,-@days*2,@date1); DECLARE @QID INT; SET @QID = (SELECT id FROM Queries WHERE Query='abc');
SELECT COUNT(*) as Visits, Query, (SELECT AVG(CAST(Pos AS DECIMAL)) FROM GooglePos WHERE QueryID=A.QueryID AND PageID=A.PageID AND date>=@date2) AS Pos, (SELECT AVG(CAST(Pos AS DECIMAL)) FROM GooglePos WHERE QueryID=A.QueryID AND PageID=A.PageID AND date>=@date3 AND date<@date2) AS Pos2 FROM Visits A INNER JOIN Pages B ON A.PageID=B.id INNER JOIN Queries E ON E.id=A.QueryID WHERE date>=@date2 AND QueryID=ISNULL(@QID,A.VisitID) GROUP BY QueryID,Query,PageID;
The second query isn't SARGable which means it's not capable of using an INDEX SEEK because you have a column inside of a function.
Test and see. This makes a million rows of data with a clustered index on the only column. It takes scant seconds to run so don't let the big number scare you.
--===== Create a test table and populate it on the fly.
SELECT TOP 1000000
QueryID = IDENTITY(INT,1,1)
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Add the expected index.
ALTER TABLE #TestTable
ADD PRIMARY KEY CLUSTERED (QueryID)
;
Now, turn on the Actual Execution plan and let the following code rip. I've added another bit of code to show what else you don't want to do.
--===== Before you run this section, turn on the Actual Execution Plan.
-- Then run it and see the difference on the message tab and the AEP.
DECLARE @QID INT;
SELECT @QID = 999999;
SET STATISTICS IO, TIME ON;
SELECT QueryID FROM #TestTable WHERE QueryID=@QID;
SELECT QueryID FROM #TestTable WHERE QueryID=ISNULL(@QID,QueryID);
SELECT QueryID FROM #TestTable WHERE (@QID IS NULL OR QueryID = @QID);
SET STATISTICS IO, TIME OFF;
It sounds like you might be leaning toward a "catch all" query. Please see Gail Shaw's wonderful article on how to do such a thing correctly. Here's the link.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
As a bit of a sidebar, never trust execution plan comparisons to tell you which code will be the fastest. Even Actual Execution Plans have a lot of estimated information in them that throw things like % of Batch way out of whack.
--Jeff Moden
Change is inevitable... Change for the better is not.