A long time ago, in a galaxy exactly like this one....
My company decided to write an app for a third party interface which was to communicate with my trusty dusty SQL Server database. It's an ancient hand me down database with warts and bees in it, but its the hacked together duct taped thing I keep running.
At any rate, after several revisions of sprocs without me involved, and deploying said sprocs at the last minute we wound up with this. This wonderful 530,000 logical read mess.
SELECT TOP 100
m.Id,
c.CompanyName AS ClientName,
m.FirstName,
m.LastName,
m.MiddleName,
m.Address1,
m.Address2,
m.City,
m.Suffix,
m.StateProvince,
m.PostalCode,
m.HomePhone,
m.Email,
m.Active,
m.MemberShipNo
FROM Members m
JOIN Client c
ON m.ClientId = c.Id
AND m.FirstName LIKE '%' + @FirstName + '%'
AND m.LastName LIKE '%' + @LastName + '%'
AND m.PostalCode LIKE '%' + @ZipCode + '%'
AND (m.Void = 0 OR m.Void IS NULL)
JOIN Client_Vendor_Xref x
ON x.ClientId = c.ID
JOIN Vendor v
ON v.Id = x.VendorID
AND (v.Void = 0 OR v.Void IS NULL)
WHERE x.VendorId = @VendorId
END
How do you get the logical reads? Throw this in front of the thing you want to examine:
SET STATISTICS IO ON
After the above query I would get something like this returned:
Table 'Client_Vendor_Xref'. Scan count 9, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 9, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Members'. Scan count 26, logical reads
532616, physical reads 148, read-ahead reads 2088, 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.
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.
Table 'Vendor'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I have gone through and corrected all sprocs to do something like below. This falls in line with that made up word SARGability; the Search ARGuments supplied do not allow us to take advantage of index seeks. Instead we get scans and scans inside every column desperately looking for those matches. So, we got rid of preceding wildcard, and after assuring folks there aren't any nulls in these tables for the Void column (anymore), and there won't be(anymore), we got rid of the VOID quantum logic search:
SELECT TOP 100
m.Id,
c.CompanyName AS ClientName,
m.FirstName,
m.LastName,
m.MiddleName,
m.Address1,
m.Address2,
m.City,
m.Suffix,
m.StateProvince,
m.PostalCode,
m.HomePhone,
m.Email,
m.Active,
m.MemberShipNo
FROM Members m
JOIN Client c
ON m.ClientId = c.Id
AND m.FirstName LIKE @FirstName + '%'
AND m.LastName LIKE '@LastName + '%'
AND m.PostalCode LIKE '@ZipCode + '%'
AND m.Void = 0
JOIN Client_Vendor_Xref x
ON x.ClientId = c.ID
JOIN Vendor v
ON v.Id = x.VendorID
AND v.Void = 0
WHERE x.VendorId = @VendorId
END
The results? It now uses the covering index and performs seeks.
Table 'Client'. Scan count 0, logical reads 116, physical reads 1, 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.
Table 'Client_Vendor_Xref'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Members'. Scan count 1, logical reads
850, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Vendor'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Pretty freakin spiffy.
As to the NULLS... For some reason the table design was done in such a way where Void was a bit which could be NULL. Not sure what the point of Yes/No/Maybe was, but it seems to be something we are stuck on. Regardless, if I can just check for Void = 0 and not Void IS NULL or Void = 0, I am getting a little more towards that 'Simplicate and Add Lightness' model of thinking. Another item to point out, you do not want to search for Void <> 0 -- that's inequality. Indexing lets us seek to Void = 0, otherwise you have to compare all the rows for "Does this not equal zero?"
TLDR ; This is going to make my warty bees nest purr for a little while.
