Transact SQL – Partial Matches and Indexes

Background

We have a table that has zip codes and wanted to weigh the different approaches to match the first five characters against the Zip Code Lookup table.

 

Patterns Considered

 

SQL Meaning
LEFT(pk_o.contextzip, 5) = convert(varchar,tblGA.zipcode) Take first 5 characters using the left function
SUBSTRING(pk_o.contextzip, 1, 5) = convert(varchar,tblGA.zipcode) Take first 5 characters using Substring(<col1>, 1, 5)
SUBSTRING(pk_o.contextzip, 6, 5) = convert(varchar,tblGA.zipcode) Take next 5 characters starting from character 6 using Substring(<col1>, 6, 5)
like convert(varchar,tblGA.zipcode) + ‘%’  Uses like function to compare data1 against begining characters in data2

 

Code




declare @startDT   datetime
declare @finishDT  datetime

set @startDT = '2015-01-01';
set @finishDT = dateadd(day, 1, @startDT)

print 'Query - 01 - Using Left'

select [count] = count(pk_o.contextzip)

from Products.OrderedInCarTrainingPackage pk_o 

inner join 	[dbo].[BTWTInstructorGroupAreas] tblGA
	on 	LEFT(pk_o.contextzip, 5) = convert(varchar,tblGA.zipcode) 

print replicate('*', 120)

print 'Query - 02 - Using Substring,1,5'

select [count] = count(pk_o.contextzip)
from  Products.OrderedInCarTrainingPackage pk_o 

inner join 	[dbo].[BTWTInstructorGroupAreas] tblGA
	on 	SUBSTRING(pk_o.contextzip, 1, 5) = convert(varchar,tblGA.zipcode)

print replicate('=', 120)

print 'Query - 03 - Using Substring,6,5'

select [count] = count(pk_o.contextzip)
from  Products.OrderedInCarTrainingPackage pk_o 

inner join 	[dbo].[BTWTInstructorGroupAreas] tblGA
	--on 	SUBSTRING(pk_o.contextzip, 1, 5) = convert(varchar,tblGA.zipcode)
	on 	SUBSTRING(pk_o.contextzip, 6, 5) = convert(varchar,tblGA.zipcode)


print replicate('*', 120)

print 'Query - 04 - Using Like'

select [count] = count(pk_o.contextzip)

from  Products.OrderedInCarTrainingPackage pk_o 

inner join 	[dbo].[BTWTInstructorGroupAreas] tblGA
	on pk_o.contextzip like convert(varchar,tblGA.zipcode) + '%'


 

Instrumentation

dbo.BTWTInstructorGroupAreas

INDX_DBA_ZipCode

IndexProperties-INDX_DBA_ZipCode

Explanation

  1. Leaf-Level rows :- 2091

 

Metrics

Statistics Time

Raw Data



SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Query - 01 - Using Left

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 94 ms.
************************************************************************************************************************

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Query - 02 - Using Substring,1,5

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 93 ms,  elapsed time = 87 ms.
========================================================================================================================

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Query - 03 - Using Substring,6,5

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 46 ms.
************************************************************************************************************************

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Query - 04 - Using Like

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 171 ms,  elapsed time = 72 ms.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


 

Tabulated

 

SQL Statistics I/O
LEFT(pk_o.contextzip, 5) = convert(varchar,tblGA.zipcode)  CPU time = 94 ms,  elapsed time = 92 ms.
SUBSTRING(pk_o.contextzip, 1, 5) = convert(varchar,tblGA.zipcode)  CPU time = 93 ms,  elapsed time = 84 ms.
SUBSTRING(pk_o.contextzip, 6, 5) = convert(varchar,tblGA.zipcode)  CPU time = 31 ms,  elapsed time = 46 ms.
like convert(varchar,tblGA.zipcode) + ‘%’  CPU time = 171 ms,  elapsed time = 73 ms.

 

 

Statistics I/O

Raw Data


^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Query - 01 - Using Left
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 'OrderedInCarTrainingPackage'. Scan count 1, logical reads 368, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BTWTInstructorGroupAreas'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
************************************************************************************************************************
Query - 02 - Using Substring,1,5
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 'OrderedInCarTrainingPackage'. Scan count 1, logical reads 368, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BTWTInstructorGroupAreas'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
========================================================================================================================
Query - 03 - Using Substring,6,5
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 'OrderedInCarTrainingPackage'. Scan count 1, logical reads 368, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BTWTInstructorGroupAreas'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
************************************************************************************************************************
Query - 04 - Using Like
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 'OrderedInCarTrainingPackage'. Scan count 2091, logical reads 8685, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BTWTInstructorGroupAreas'. Scan count 11, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


 

Tabulated

 

SQL Worktable OrderedInCarTrainingPackage BTWTInstructorGroupAreas
LEFT(pk_o.contextzip, 5) = convert(varchar,tblGA.zipcode) Scan count 0, logical reads 0  Scan count 1, logical reads 368 Scan count 1, logical reads 5
SUBSTRING(pk_o.contextzip, 1, 5) = convert(varchar,tblGA.zipcode) Scan count 0, logical reads 0  Scan count 1, logical reads 368 Scan count 1, logical reads 5
SUBSTRING(pk_o.contextzip, 6, 5) = convert(varchar,tblGA.zipcode) Scan count 0, logical reads 0  Scan count 1, logical reads 368 Scan count 1, logical reads 5
like convert(varchar,tblGA.zipcode) + ‘%’ Scan count 0, logical reads 0  Scan count 2091, logical reads 8685 Scan count 11, logical reads 10

 

Explanation:

  1. Query 4
    • OrderedInCarTrainingPackage
      • The number of scan count is 2091
        • The scan count matches the number of leaf level rows in the table

Query Plan

Query Plan – 1

Query-1-QueryPlan

 

Query Plan – 4

Query Plan

Query-4-QueryPlan

 

Degree of Parallelism

Query-4-QueryPlan-Parallelism-DegreeOf

Explanation

  1. Left & Substring
    • The functions left and substring utilize Index Scan Operations and join the tables using an Hash Match Operator
  2. Like
    • The like function uses Index Scan Operators, performs parallel queries, and uses a Nested Loop Operator

 

Summary

So we can see that both the left and substring functions are able to use Index Scans.

The Like function, on the other hand, is far more weightier.  It appears that is accesses the entire Index structure.

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s