covering index performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

covering index performance

I have a performance issue with a basic query that does simple address lookups by postcode (UK zip code e.g. AB121AB) and identified it to be down to a single index on 2 columns. i have simplified the stored procedure queries here. Query1 takes ~20 seconds, query2 takes ~0.5 seconds. I dont understand why the first is so slow when the clustered index is on both columns. 0-100 records will be returned from the query in a ranged lookup. I would expect an index on PostOut and another on PostIn would be preferable to an index on both for query2. Can anyone explain why query2 is so much faster? table1 (~28 million rows)
ID int (non-clustered PK)
PostOut char(4) (clustered index)
PostIn char(3) (clustered index)
Address varchar (simplified to illustrate example) query1
select top 100 PostOut+PostIn as Postcode, Address
from dbo.table1
where PostOut+PostIn = @postcode query2
select top 100 PostOut+PostIn as Postcode, Address
from dbo.table1
where PostOut = left(@postcode,4)
and PostIn = substring(@postcode,5,3)
Query 2 is fast because sql server is able to jump on the first row that satisfies condition and to quit trying on the first row where column values are different. Imagine index is book index ordered by two attributes mentioned having information about page both words are mentioned. If you know both you can find it quickly in the index and then jump on a correct page. This is the way how mssql server use index when column values are explicitly provided. Query 1 uses function of two columns instead, and sql server is not inteligent enough to figure out that condition is equivalent to one from query 2.
]]>