Via en bloggpost från Adam Machanic har jag deltagit i en tävling om ett exemplar av Expert SQL Server 2005 Development. Frågan från Adam är:

Givet att du har två tabeller, som skapats med scriptet nedan:

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

Hur kan man skriva om nedanstående fråga så att den blir snabbare (den tar ungefär sex minuter på min PC):

SELECT *
FROM b1
JOIN b2 ON
    b2.blat2 LIKE b1.blat1 + '%'

Inga objekt-ändringar är tillåtna, dvs ingen indexering av tabellerna får göras.

Lösningen är att göra exakta jämförelser istället för ungefärliga. Dvs att använda "="-operatorn istället för "LIKE"-operatorn. Varför? Jo, därför att "LIKE"-operatorn gör att vi måste använda oss av en "NESTED LOOP" för att jämföra raderna. Dvs SQL Servers Query Processor (QP) måste titta på varje rad i den yttre tabellen i JOIN-operationen, och för varje rad i den yttre tabellen måste QP titta på varje rad i den inre tabellen för att jämföra data. Både b1 och b2 innehåller 19614 rader. Det är inga extrema datamängder, långt därifrån. För att köra ovanstående fråga måste QP titta på 19614 i b1, och för varje av dessa rader måste 19614 rader i b2 undersökas. Dvs 384 708 996 rader måste undersökas.

Lyckas vi istället använda en "="-operator kan QP använda en "MERGE" eller "HASH MATCH", vilket väsentligen reducerar antalet rader att titta på.

Min omskrivna fråga blir:

SELECT *
FROM b1
JOIN b2 ON
LEFT(b2.blat2,5) = b1.blat1

Det funkar därför att vi vet att b1.blat2 innehåller de första fem tecknen från b2.blat2. Tack vare den vetskapen kan frågan skrivas om, och köras på två sekunder. QP utför en "HASH MATCH" och antalet rader som måste undersökas för att utföra JOIN-operationen reduceras väsentligt.

Nedan ser du bilder av de estimerade planerna för de båda frågorna, och kan själv se skillnaden i estimerat antal rader för "NESTED LOOP" jämfört med "HASH MATCH" (Jämför "Estimated Number of Rows")

 
SELECT * FROM b1 JOIN b2 ON b2.blat2 like b1.blat1 +'%'

SELECT * FROM b1 JOIN b2 ON left(b2.blat2,5) = b1.blat1