How creative are you with manipulating your queries to produce more efficient plans? Try the following puzzle and e-mail your solution to me at [<my last name> @ pythian.com]. Make sure to include an explanation of why it works, as well as your mailing address. The best two solutions/explanations win a free copy of Expert SQL Server 2005 Development, a wonderful feeling of accomplishment, plus eternal fame and glory when I reveal your solutions here on the blog.
Run the following T-SQL to create two tables in TempDB:
USE TempDB GO CREATE TABLE b1 (blat1 CHAR(5) NOT NULL) CREATE TABLE b2 (blat2 VARCHAR(200) NOT NULL) GO INSERT b1 SELECT LEFT(AddressLine1, 5) AS blat1 FROM AdventureWorks.Person.Address INSERT b2 SELECT AddressLine1 AS blat2 FROM AdventureWorks.Person.Address GO
Now consider the following query:
SELECT * FROM b1 JOIN b2 ON b2.blat2 LIKE b1.blat1 + '%'
This query takes around three minutes to run on my notebook, and does over 1.8 million logical reads. Can you figure out a way to re-write it so that it performs better? No modification of the base tables or addition of any other objects is allowed (sorry, no indexed views!) — the challenge is to tune this by doing nothing more than re-writing the query.
Good luck! I’ll leave the contest open for submissions until May 1.
FROM b1 WITH (NOLOCK)
JOIN b2 WITH (NOLOCK)
ON LEFT(b2.blat2,5) =b1.blat1