Given a simple three table join, query performance changes drastically when ORDER BY is included even with no rows returned. Actual problem scenario take 30 seconds to return zero rows but is instant when ORDER BY not included. Why?
SELECT *
FROM tinytable t /* one narrow row */
JOIN smalltable s on t.id=s.tinyId /* one narrow row */
JOIN bigtable b on b.smallGuidId=s.GuidId /* a million narrow rows */
WHERE t.foreignId=3 /* doesn't match */
ORDER BY b.CreatedUtc /* try with and without this ORDER BY */
I understand that I could have an index on bigtable.smallGuidId, but, I believe that would actually make it worse in this case.
Here's script to create/populate the tables for test. Curiously, it seems to matter that smalltable has an nvarchar(max) field. It also seems to matter that I'm joining on the bigtable with a guid (which I guess makes it want to use hash matching).
CREATE TABLE tinytable
(
id INT PRIMARY KEY IDENTITY(1, 1),
foreignId INT NOT NULL
)
CREATE TABLE smalltable
(
id INT PRIMARY KEY IDENTITY(1, 1),
GuidId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
tinyId INT NOT NULL,
Magic NVARCHAR(max) NOT NULL DEFAULT ''
)
CREATE TABLE bigtable
(
id INT PRIMARY KEY IDENTITY(1, 1),
CreatedUtc DATETIME NOT NULL DEFAULT GETUTCDATE(),
smallGuidId UNIQUEIDENTIFIER NOT NULL
)
INSERT tinytable
(foreignId)
VALUES(7)
INSERT smalltable
(tinyId)
VALUES(1)
-- make a million rows
DECLARE @i INT;
SET @i=20;
INSERT bigtable
(smallGuidId)
SELECT GuidId
FROM smalltable;
WHILE @i > 0
BEGIN
INSERT bigtable
(smallGuidId)
SELECT smallGuidId
FROM bigtable;
SET @i=@i - 1;
END
I've tested on SQL 2005, 2008 and 2008R2 with same results.
