Sargable question

[TysonLPrice]

I've been pointed to several links for here and this explanation:

Think of it this way. Suppose someone handed you a dictionary and told you to highlight (with a marker) all words that begin with 'tr'. What would you do? You would flip through the pages (very quickly) and find the first word that starts with TR. You would start highlighting them until you get to the first word that does NOT start with TR.

Now, imagine someone hands you a dictionary and says, highlight each word that contains TR. The only way to do this would be to start at the beginning and examine EVERY word. Obviously this would take a lot longer to accomplish.

By making the query sargable, it will use an existing index to identify the records faster, thus causing the entire operation to take less time. This ONLY works if there is an index.

For example, if someone hands you a 'normal' book and told you to highlight the words that start with TR, you would again have to examine every word. Again... this would take a long time to accomplish.

This confuses me:

Set SHOWPLAN_ALL On
go
select  * from claim where bwcclaimnum like '9701301834%'
select  * from claim where bwcclaimnum = '9701301834'
go
Set SHOWPLAN_ALL Off

bwcclaimnum is a non-unique non clustered index on the claim table. Both selects seem to generate the same execution plan. Souldn't the first select be doing a table scan? I'll try and show the execution plan but I don't know how it will post:

First select:
Nested Loops
Index Seek
RID Lookup

Second select:
Nested Loops
Index Seek
RID Lookup

Regarding the other information needed to answer I don't know what is relevent. I'll try and post it if requested.

The only real difference I see is:

First select:
OBJECT[Manhattan].[dbo].[Claim].[BwcClaimNum]), SEEK[Manhattan].[dbo].[Claim].[BwcClaimNum] >= '9701301833þ' AND [Manhattan].[dbo].[Claim].[BwcClaimNum] < '9701301835'), WHERE[Manhattan].[dbo].[Claim].[BwcClaimNum] like '9701301834%') ORDERED FORWARD

Second select:
OBJECT[Manhattan].[dbo].[Claim].[BwcClaimNum]), SEEK[Manhattan].[dbo].[Claim].[BwcClaimNum]='9701301834') ORDERED FORWARD

Thanks!

 

[gmmastros]

That quote sounds familiar.

Both of your queries are sargable. I would expect to see a small difference in the query plan because the queries are not the same. The first query (with the like comparison) could return additional rows that the second query doesn't return.

like '9701301834[!]%[/!]'

Since your wild card search is at the end, and you have an index on the column, SQL Server can use the index to quickly narrow down the possible rows to return.

Now suppose your query was like this:

like '[!]%[/!]9701301834[!]%[/!]'

Since there is now a wild card search at the start, every row must be evaluated (whether there is an index or not). Since there is an index, SQL will likely use an index scan (scanning all the rows in the index for matches). Index scans are many times slower than index seeks.

Basically, like comparisons that do NOT have a wildcard at the beginning are sargable.

Does it make sense now?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TysonLPrice]

gmmastros

I thought I got that from you and I saved it in our I/T "SQL tips & tricks" folder along with the links you provided but I wasn't sure. Otherwise I would have given you credit. We have a stored procedure that got hit with a data import that was larger than usual and it was just grinding away. I'm going to post it and it is long and ugly. If non sargable selects jump out at you please let me know. Meantime I will be testing for them individually myself. As always thanks!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/****** Object:  StoredProcedure [dbo].[cst_ImportDEMOCClaimReserve]    Script Date: 07/16/2008 16:06:53 ******/
ALTER PROCEDURE [dbo].[cst_ImportDEMOCClaimReserve] (
	@BwcClaimNumber varchar(20),
	@ReserveAmt int,
	@CompReserveAmt int,
	@MedReserveAmt int,
	@ReserveCode char(2),
	@ReducibleAmt int,
	@NonReducibleAmt int,
	@CompAmt int,
	@MedAmt int,
	@HandicapPercent smallint,
	@ExtractDate smalldatetime,
	@PolicyNumber int,
	@BusSeqNo int,
	@InjuryDate smalldatetime,
	@IWName varchar(20),
	@DeathDate smalldatetime,
	@ClaimRatingIndicator char(1),
	@ClaimStatus char(2),
	@ReducibleMedAmt int,
	@NonReducibleMedAmt int,
	@SubrogationAmt int,
	@ICCAI Char(1))

AS

set nocount on

Declare @pkClaim int,
	@MinYear smalldatetime,
	@TpaClaimStatus varchar(20), 
	@ClaimProfile int,
	@HandicapSum int,
	@pkEmployer int,
	@pkInjuredWorker int,
	@pkIwLocation int,
	@pkEmployerAddress int,
	@Today datetime,
	@ActiveEmployer char

Set @Today = getdate()

Set @pkClaim = 0

--First grab claim if Policynumber in file match what is in Team
Select @pkClaim = pkClaim from Claim c 
	join AccountsAddress a on c.fkEmployerAddress = a.pkAddress
	join AccountsEmployer e on a.fkEmployer = e.pkEmployer
	where BwcClaimNum = @BwcClaimNumber
	and e.PolicyNumber = Convert(Varchar(20),@PolicyNumber) + '-' + Convert(Varchar(20),@BusSeqNo)
	--and not exists (Select pkClaimReserveHist from ClaimReserveHist h where c.pkClaim = h.fkClaim and h.ExtractDate = @ExtractDate)
		--or exists in (Select ObjPolicyNo from v_BwcMaxSuccessor v where v.PolicyNo = e

--If claim was not found, grab claim if successor policy does not exist in Rate (this means that if a successor policy does exist, we did not request it in the file)
If @pkClaim = 0
	Select @pkClaim = pkClaim from Claim c
		join AccountsAddress a on c.fkEmployerAddress = a.pkAddress
		join AccountsEmployer e on a.fkEmployer = e.pkEmployer
		Where not exists (Select SuccPolicyNo from rating.dbo.v_MaxBWCCombination v 
				where Convert(Varchar(20),v.PolicyNo) + '-' + Convert(Varchar(20),v.BusSeqNo) = e.PolicyNumber)
		--and not exists (Select pkClaimReserveHist from ClaimReserveHist h where c.pkClaim = h.fkClaim and h.ExtractDate = @ExtractDate)
		and BwcClaimNum = @BwcClaimNumber

--If claim was not found, grab claim if successor policy is SI
If @pkClaim = 0
	Select @pkClaim = pkClaim from Claim c
		join AccountsAddress a on c.fkEmployerAddress = a.pkAddress
		join AccountsEmployer e on a.fkEmployer = e.pkEmployer
		join Rating.dbo.v_MaxBWCSuccessor v 
			on v.PolicyNo = @PolicyNumber and v.BusSeqNo = @BusSeqNo
			and v.ObjPolicyNo between 20000000 and 30000000
			and Convert(Varchar(20),v.ObjPolicyNo) + '-' + Convert(Varchar(20),v.ObjBusSeqNo) = e.PolicyNumber
		--and not exists (Select pkClaimReserveHist from ClaimReserveHist h where c.pkClaim = h.fkClaim and h.ExtractDate = @ExtractDate)
		and BwcClaimNum = @BwcClaimNumber

If @pkClaim > 0 
Begin
 
 
	Insert into ClaimReserveHist(
		fkClaim,
		ReserveAmt,
		ReducibleAmt,
		NonReducibleAmt,
		CompAmt,
		MedicalAmt,
		HandicapPercent,
		UserLUP,
		DateLUP,
		ExtractDate,
		PolicyNumber,
		CompReserveAmt,  
		MedReserveAmt,  
		ReserveCode,
		ClaimRatingIndicator, 
		ClaimStatus,
		ReducibleMedAmt, --jld 7.19.07
		NonReducibleMedAmt, --jld 7.19.07
		SubrogationAmt,
		appealindicator --RAV 7/1/08
		)
 
	Values(
		@pkClaim,
		@ReserveAmt,
		@ReducibleAmt,
		@NonReducibleAmt,
		@CompAmt,
		@MedAmt,
		@HandicapPercent,
		User_Name(),
		@Today,
		@ExtractDate,
		@PolicyNumber,
		@CompReserveAmt,
		@MedReserveAmt,
		@ReserveCode,
		@ClaimRatingIndicator,
		@ClaimStatus,
		@ReducibleMedAmt, --jld 7.19.07
		@NonReducibleMedAmt, --jld 7.19.07
		@SubrogationAmt,
		@ICCAI --RAV 7/1/08
		)
	Update 	
		Claim
	Set
		InjuryDate = @InjuryDate
	Where 
		pkClaim = @pkClaim
	and 	InjuryDate is null
	and 	@InjuryDate is not null

	-- Tyson Price 05/13/2008 - Added Update to Claim SubrogationAmount

	Update 	
		Claim
	Set
		SubrogationAmount = isnull(@SubrogationAmt,0)
	Where 
		pkClaim = @pkClaim
	and 	isnull(SubrogationAmount,0) = 0

End
Else --Claim does not yet exist
Begin

	Set @MinYear = '01/01/' + convert(varchar(4), datepart(year, @Today) - 6)

	If Exists(	Select 	pkEmployer 
			From	AccountsEmployer
			Where	PolicyNumber = Convert(Varchar(20),@PolicyNumber) + '-' + Convert(Varchar(20),@BusSeqNo))
		And Not Exists(	Select 	bwcClaimNum
			From 	Claim
			Where	@BwcClaimNumber = BwcClaimNum)
		And (@InjuryDate >= @MinYear or @ClaimRatingIndicator = 'R') --jdorazio 4.19.06
	Begin
		Begin tran

		--Find the primary key for the employer
		Select @pkEmployer = 	(	Select 	max(pkEmployer)
						From	AccountsEmployer
						Where	PolicyNumber = Convert(Varchar(20),@PolicyNumber) + '-' + Convert(Varchar(20),@BusSeqNo))
		--Find the active status of the employer
		select	@ActiveEmployer = (
						select	case 	when ae.TPATerminationDate >= GetDate() and ae.TPAOrgID in (54, 5917) then 'Y'
								when ae.TPATerminationDate is NULL and ae.TPAOrgID in (54, 5917) then 'Y'
								when ae.TPATerminationDate = '' and ae.TPAOrgID in (54, 5917) then 'Y'
								else 'N' end
						From 	AccountsEmployer ae (nolock)
						where	pkEmployer = @pkEmployer)
		--Find the employers address for the claim
		Select @pkEmployerAddress = (	Select 	max(pkAddress) 
						From	AccountsEmployer ae,
							AccountsAddress aa
						Where	ae.PolicyNumber = Convert(varchar(20), @PolicyNumber) + '-' + Convert(varchar(20),@BusSeqNo) and
							ae.pkEmployer = aa.fkEmployer)		
		--Find the primary key of the injured worker
		select @pkInjuredWorker = isnull(	(Select max(pkInjuredWorker)
							From InjuredWorker 
							With (updlock)),0) + 1 
	
		Set @ClaimProfile = IsNull( (	Select 	max(fkClaimProfile)
						From	AccountsEmployerClaimProfile
						Where	FkEmployer = @pkEmployer),121)
	
		--Set new claims to OPEN per Tara Heath - jdorazio 4.19.06
		SET @TpaClaimStatus = NULL
		IF @ClaimProfile = 121
		BEGIN
			IF ISNULL(@ClaimStatus, '') NOT IN ('DA', 'DP', 'DS', 'SI', 'SM', 'ST')
				SET @TpaClaimStatus = 'Open'
		END
	
		Insert InjuredWorker
			(pkInjuredWorker
			,Claimant
			,LastName
			,FirstName
			,MidName)
		Values
			(@pkInjuredWorker
			,@IwName
			--Lastname
			,Case Charindex(',',Isnull(@IwName,''))
				When 0 then 
					Case Patindex('% %', Isnull(@IwName, ''))
						When 0 then @IwName
						Else right(@IwName, patindex('% %',reverse(@IwName)) - 1)
					End
	

				Else IsNull(Left(ltrim(rtrim(@IwName)), Charindex(',', ltrim(rtrim(@IwName)))-1), '')
			end,
			--FirstName
			Case CharIndex(',', Isnull(@IwName,''))
				When 0 then 
					Case Patindex('% %', Isnull(@IwName, ''))
						When 0 then ''
						Else left(@IwName, len(@IwName) - patindex('% %',reverse(@IwName)))
					end
				Else 	Case Patindex('% %', IsNull(ltrim(rtrim(Right(rtrim(ltrim(@IwName)), (len(@IwName) - charindex(',',@IwName))))), ''))
						When 0 then IsNull(ltrim(rtrim(Right(rtrim(ltrim(@IwName)), (len(@IwName) - charindex(',',@IwName))))), '')
						Else Left(IsNull(ltrim(rtrim(Right(rtrim(ltrim(@IwName)), (len(@IwName) - charindex(',',@IwName))))), ''), Patindex('% %',IsNull(ltrim(rtrim(Right(rtrim(ltrim(@IwName)), (len(@IwName) - charindex(',',@IwName))))), '')))
					end
			end,
			--MidName
			LEFT(case Patindex('% %',
				isnull((Case CharIndex(',', Isnull(@IwName,''))
					When 0 then 
						Case Patindex('% %', Isnull(@IwName, ''))
							When 0 then ''
							Else 
								ltrim(rtrim(left(@IwName, len(@IwName) - patindex('% %',reverse(@IwName)))))  
						end
					Else IsNull(ltrim(rtrim(Right(rtrim(ltrim(@IwName)), (len(@IwName) - charindex(',',@IwName))))), '')
				end),''))
					When 0 then ''
					Else Right(
							(	Case CharIndex(',', Isnull(@IwName,''))
								When 0 then 
									Case Patindex('% %', Isnull(@IwName, ''))
										When 0 then ''
										Else 
											ltrim(rtrim(left(@IwName, len(@IwName) - patindex('% %',reverse(@IwName))))) 
									end
								Else IsNull(ltrim(rtrim(Right(rtrim(ltrim(@IwName)), (len(@IwName) - charindex(',',@IwName))))), '')
							end),
						(
						Len(			(	Case CharIndex(',', Isnull(@IwName,''))
										When 0 then 
											Case Patindex('% %', Isnull(@IwName, ''))
												When 0 then ''
												Else left(@IwName, len(@IwName) - patindex('% %',reverse(@IwName)))
											end
										Else IsNull(ltrim(rtrim(Right(rtrim(ltrim(@IwName)), (len(@IwName) - charindex(',',@IwName))))), '')
									end)))
						
						- Patindex('% %', 	(	Case CharIndex(',', Isnull(@IwName,''))
										When 0 then 
											Case Patindex('% %', Isnull(@IwName, ''))
												When 0 then ''
												Else left(@IwName, len(@IwName) - patindex('% %',reverse(@IwName)))
											end
										Else IsNull(ltrim(rtrim(Right(rtrim(ltrim(@IwName)), (len(@IwName) - charindex(',',@IwName))))), '')
									end))
						)
	
				end,1))

				Select 	@pkIwLocation 	= isnull(	(select max(pkIwLocation)
									From IwLocation 
									with (updlock)),0) + 1
	
				Insert	IwLocation
					(pkIwLocation
					,fkInjuredWorker) 
				values 
					(@pkIwLocation
					,@pkInjuredWorker)

				select 	@pkClaim 	= isnull(	(Select max(pkClaim) 
									From Claim 
									With (updlock)),0) + 1
				-- Tyson Price 05/13/2008 
				-- Added SubrogationAmount
				Insert Claim
					(pkClaim
					,BwcClaimNum
					,fkEmployerAddress
					,fkIwLocation
					,InjuryDate
					,DateEntered
					,EnteredBy
					,ClaimProfile
					,TPAClaimStatus
					,SubrogationAmount)
				Values
					(@pkClaim
					,@BwcClaimNumber
					,@pkEmployerAddress
					,@pkIwLocation
					,@InjuryDate
					,getdate()
					,User_Name()
					,@ClaimProfile
					,@TPAClaimStatus
					,isnull(@SubrogationAmt,0))
			Commit Tran
			if not exists(	Select 	fkClaim
					From	ClaimReserveHist
					Where	fkClaim = @pkClaim
					and	ExtractDate = @ExtractDate
					and	ReserveAmt = @ReserveAmt
					and	ReducibleAmt = @ReducibleAmt
					and	NonReducibleAmt = @NonReducibleAmt
					and	CompAmt = @CompAmt
					and	MedicalAmt = @MedAmt
					and	HandicapPercent = @HandicapPercent
					and	CompReserveAmt = @CompReserveAmt  --jfrease 9.21.05
					and 	MedReserveAmt = @MedReserveAmt  --jfrease 9.21.05
					and 	ReserveCode = @ReserveCode --jdorazio 4.19.06
					and	ClaimRatingIndicator = @ClaimRatingIndicator --jdorazio 4.19.06
					and 	ClaimStatus = @ClaimStatus --jdorazio 4.19.06
					and	ReducibleMedAmt = @ReducibleMedAmt --jld 7.19.07
					and NonReducibleMedAmt = @NonReducibleMedAmt --jld 7.19.07
					and SubrogationAmt = @SubrogationAmt) --jld 12.12.07
			begin
				Insert into ClaimReserveHist
					 (fkClaim,
					 ReserveAmt,
					 ReducibleAmt,
					 NonReducibleAmt,
					 CompAmt,
					 MedicalAmt,
					 HandicapPercent,
					 UserLUP,
					 DateLUP,
					 ExtractDate,
					 PolicyNumber,
					 CompReserveAmt,  --jfrease 9.21.05
					 MedReserveAmt,  --jfrease 9.21.05
					 ReserveCode, --jdorazio 4.19.06
					 ClaimRatingIndicator, --jdorazio 4.19.06
					 ClaimStatus, --jdorazio 4.19.06
					 ReducibleMedAmt, --jld 7.19.07
					 NonReducibleMedAmt, --jld 7.19.07
					 SubrogationAmt --jld 12.12.07
					)
				Values
					 (@pkClaim,
					 @ReserveAmt,
					 @ReducibleAmt,
					 @NonReducibleAmt,
					 @CompAmt,
					 @MedAmt,
					 @HandicapPercent,
					 User_Name(),
					 @Today,
					 @ExtractDate,
					 @PolicyNumber,
					 @CompReserveAmt,  --jfrease 9.21.05
					 @MedReserveAmt,  --jfrease 9.21.05
					 @ReserveCode, --dorazioj 4.19.06
					 @ClaimRatingIndicator, --jdorazio 4.19.06
					 @ClaimStatus,  --jdorazio 4.19.06
					 @ReducibleMedAmt, --jld 7.19.07
					 @NonReducibleMedAmt, --jld 7.19.07
					 @SubrogationAmt) --jld 12.12.07
			end
			if not exists(	Select 	fkClaim 
					From	ClaimHandicap
					Where	fkClaim = @pkClaim and
						HandicapType = 26)	
			begin
				Select @HandicapSum = (	Select 	Sum(AwardPercent)
							From	ClaimHandicap
							Where	fkClaim = @pkClaim)
				if isnull(@HandicapSum,0) < @HandicapPercent
				begin
					If @HandicapPercent <> 0
					begin
						Exec 	cst_SaveClaimHandicap 	0, 		--Insert Row
										@pkClaim, 	--Current claim
										26, 		--Unknown Type
										@ExtractDate, 
										@ExtractDate, 
										@ExtractDate, 	
										@HandicapPercent, 	
										'From BWC' , --User
										null, --Y1
										null, --Y2
										null, --Y3
										null  --Y4	
					end
				end

			end


		if ((@CompAmt > 5000 or
		   @MedAmt > 10000 or
		   (@ReserveAmt <> 0 and @CompAmt = 0) or
		   @ReserveAmt > 25000) and @ActiveEmployer = 'Y')
		begin
			Exec 	sp_CleanupClaimReserveHistWaitingAddNote	@pkClaim,
										@pkEmployer
		end
	end

end

 

[gmmastros]

Sargable certainly does apply to the where clause. That is obvious. But it also applies to joins because that's another spot where an index can speed up performance.

In your code, I see this:

[tt][blue]
join Rating.dbo.v_MaxBWCSuccessor v
on v.PolicyNo = @PolicyNumber
and v.BusSeqNo = @BusSeqNo
and v.ObjPolicyNo between 20000000 and 30000000
and [!]Convert(Varchar(20),v.ObjPolicyNo) + '-' + Convert(Varchar(20),v.ObjBusSeqNo) = e.PolicyNumber[/!]
[/blue][/tt]

Looking at the, there are several conditions on the v table (which I presume is a view). This view is joining to the e table based on a condition that is not sargable. However, SQL Server should be smart enough to use the other conditions to quickly narrow down the rows to join on, so this may not be the cause of your bad performance. But it might, too.

The other thing to realize is that... just because a query is sargable, that does not mean that SQL Server will use an index seek to speed things up. You see, if an index does not exist, it can't be used.

Since you appear to be using views (just a guess), it's possible that the view could contain conditions (joins and where clauses) that are not sargable.

My suggestion to you is to put some "debugging" code in to this procedure so you can isolate where the performance problems occur. Something like this...

Create procedure blah.....

Set NOCOUNT ON

Declare @Start DateTime
Set @Start = GetDate()

-- First block of code here

Select 'Block 1', DateDiff(Millisecond, @Start, GetDate())
Set @Start = GetDate()

-- next block of code

Select 'Block 2', DateDiff(Millisecond, @Start, GetDate())
Set @Start = GetDate()

-- next block of code

Select 'Block 2', DateDiff(Millisecond, @Start, GetDate())

This should allow you to determine which part of the code is taking the longest to execute so you know where to concentrate your efforts.

Hope this helps.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom