Strange LIKE behaviour - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Strange LIKE behaviour

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 08:51:17

I have a SELECT statement which involves 6 tables.

The WHERE part look like this
WHERE o.OrderNumber LIKE @Path -- @Path is VARCHAR(16) and equals to '55920[23456789]'

Running profiler gives me 8116 reads!

Substituting @Path and hardcode the LIKE value to this
WHERE o.OrderNumber LIKE '55920[23456789]'

now Profiler tells me there is only 62 reads.

What?

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 08:54:34

parameter sniffing maybe?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 09:18:30

Maybe. I can't see why.

DECLARE @path VARCHAR(16)
SET @Path = <business logic here>

select ... where o.ordernumber like @path --8116 reads

DECLARE @path2 VARCHAR(16)
SET @Path2 = '55920[23456789]'

select ... where o.ordernumber like @path2 --8116 reads

select ... where o.ordernumber like '55920[23456789]' --62 reads

I can't figure it out.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 09:47:44

so [ and ] don't act like special chars?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 09:56:58

No. The idea is to find subsequent orders
Much like

o.OrderNumber BETWEEN '559202' AND '559209'

Which hardcoded gives 62 reads and with parameters 8116 reads

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-30 : 10:10:23

It looks like SQL Server is picking the query plan with "LIKE @Path" based on no knowledge of the contents of @Path, but is picking the other plan based on knowledge of "LIKE '55920[23456789]'".

I have seen the before. Sometimes dynamic SQL is faster because SQL Server picks a better query plan.

Did you try something like this?

where o.OrderNumber between @Path1 and @Path2

CODO ERGO SUM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 10:10:44

interesting...

and for what are those reads used in exec plan?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 10:18:49

quote:
Originally posted by Michael Valentine Jones

Did you try something like this?
where o.OrderNumber between @Path1 and @Path2

Yes.
750 reads [by hinting an index with (index(..)), 8300 without index hint] with parameters and 65 reads with hardcoded "limits".

I have tried WITH RECOMPILE, but no change.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 10:22:15

quote:
Originally posted by spirit1

interesting...

and for what are those reads used in exec plan?

A clustered index scan and a bookmark lookup.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 10:24:26

With parameters (99% of batch)

  |--Compute Scalar(DEFINE:([Expr1015]=If [Expr1022] then -1 else 0))
       |--Hash Match(Inner Join, HASH:([tOrderType].[OrderTypeId])=([tOrder].[OrderTypeId]), RESIDUAL:([tOrder].[OrderTypeId]=[tOrderType].[OrderTypeId]))
            |--Bookmark Lookup(BOOKMARK:([Bmk1010]), OBJECT:([OFS_From_Production].[dbo].[tOrderType]))
            |    |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrderType].[AK_tOrderType_OrderTypeCode]), SEEK:([tOrderType].[OrderTypeCode] < 'BFT' OR [tOrderType].[OrderTypeCode] > 'BFT'),  WHERE:([tOrderType].[OrderTypeCode]<'BAS' OR [tOrderType].[OrderTypeCode]>'BAS') ORDERED FORWARD)
            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tOrder].[OrderNumber]), DEFINE:([Expr1022] = [PROBE VALUE]))
                 |--Hash Match(Inner Join, HASH:([tDepartment].[DepartmentId])=([tOrder].[DepartmentId]), RESIDUAL:([tOrder].[DepartmentId]=[tDepartment].[DepartmentId]))
                 |    |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([OFS_From_Production].[dbo].[tDepartment]))
                 |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tSection].[SectionId]))
                 |    |         |--Bookmark Lookup(BOOKMARK:([Bmk1004]), OBJECT:([OFS_From_Production].[dbo].[tSection]))
                 |    |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tDivision].[DivisionId]))
                 |    |         |         |--Bookmark Lookup(BOOKMARK:([Bmk1006]), OBJECT:([OFS_From_Production].[dbo].[tDivision]))
                 |    |         |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tCompany].[CompanyId]))
                 |    |         |         |         |--Table Scan(OBJECT:([OFS_From_Production].[dbo].[tCompany]), WHERE:([tCompany].[CompanyCode]='HM'))
                 |    |         |         |         |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tDivision].[IX_tDivision_CompanyId]), SEEK:([tDivision].[CompanyId]=[tCompany].[CompanyId]) ORDERED FORWARD)
                 |    |         |         |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tSection].[IX_tSection_DivisionId]), SEEK:([tSection].[DivisionId]=[tDivision].[DivisionId]) ORDERED FORWARD)
                 |    |         |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tDepartment].[IX_tDepartment_SectionId]), SEEK:([tDepartment].[SectionId]=[tSection].[SectionId]) ORDERED FORWARD)
                 |    |--Clustered Index Scan(OBJECT:([OFS_From_Production].[dbo].[tOrder].[PK_tOrder_OrderId]), WHERE:([tOrder].[OFSSystemCancelled]=0 AND like([tOrder].[OrderNumber], [@strOrderNo], NULL)))
                 |--Row Count Spool
                      |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrderJointPurchase].[IX_tOrderJointPurchase_OrderNumber]), SEEK:([tOrderJointPurchase].[OrderNumber]=[tOrder].[OrderNumber]) ORDERED FORWARD)

With hardcoded values (1% of batch)

  |--Compute Scalar(DEFINE:([Expr1015]=If [Expr1016] then -1 else 0))
       |--Filter(WHERE:([tCompany].[CompanyCode]='HM'))
            |--Bookmark Lookup(BOOKMARK:([Bmk1008]), OBJECT:([OFS_From_Production].[dbo].[tCompany]))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([tDivision].[CompanyId]))
                      |--Bookmark Lookup(BOOKMARK:([Bmk1006]), OBJECT:([OFS_From_Production].[dbo].[tDivision]))
                      |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tSection].[DivisionId]))
                      |         |--Bookmark Lookup(BOOKMARK:([Bmk1004]), OBJECT:([OFS_From_Production].[dbo].[tSection]))
                      |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tDepartment].[SectionId]))
                      |         |         |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([OFS_From_Production].[dbo].[tDepartment]))
                      |         |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tOrder].[DepartmentId]))
                      |         |         |         |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tOrder].[OrderNumber]), DEFINE:([Expr1016] = [PROBE VALUE]))
                      |         |         |         |    |--Filter(WHERE:([tOrderType].[OrderTypeCode]<>'BAS' AND [tOrderType].[OrderTypeCode]<>'BFT'))
                      |         |         |         |    |    |--Bookmark Lookup(BOOKMARK:([Bmk1010]), OBJECT:([OFS_From_Production].[dbo].[tOrderType]))
                      |         |         |         |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([tOrder].[OrderTypeId]))
                      |         |         |         |    |              |--Filter(WHERE:([tOrder].[OFSSystemCancelled]=0))
                      |         |         |         |    |              |    |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([OFS_From_Production].[dbo].[tOrder]))
                      |         |         |         |    |              |         |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrder].[IX_tOrder_OrderNo]), SEEK:([tOrder].[OrderNumber] >= '559202' AND [tOrder].[OrderNumber] < '55920A'),  WHERE:(like([tOrder].[OrderNumber], '55920[23456789]', NULL)) ORDERED FORWARD)
                      |         |         |         |    |              |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrderType].[PK_tOrderType_1]), SEEK:([tOrderType].[OrderTypeId]=[tOrder].[OrderTypeId]) ORDERED FORWARD)
                      |         |         |         |    |--Row Count Spool
                      |         |         |         |         |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrderJointPurchase].[IX_tOrderJointPurchase_OrderNumber]), SEEK:([tOrderJointPurchase].[OrderNumber]=[tOrder].[OrderNumber]) ORDERED FORWARD)
                      |         |         |         |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tDepartment].[PK_tDepartment]), SEEK:([tDepartment].[DepartmentId]=[tOrder].[DepartmentId]) ORDERED FORWARD)
                      |         |         |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tSection].[PK_tSection_1]), SEEK:([tSection].[SectionId]=[tDepartment].[SectionId]) ORDERED FORWARD)
                      |         |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tDivision].[PK_tDivision_1]), SEEK:([tDivision].[DivisionId]=[tSection].[DivisionId]) ORDERED FORWARD)
                      |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tCompany].[PK_tCompany_1]), SEEK:([tCompany].[CompanyId]=[tDivision].[CompanyId]) ORDERED FORWARD)

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 10:49:55

i'm a bit stumped

are any tables much larger that the others?
are all inner joins?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-30 : 10:51:21

What find of query plan do you get if you have a single table select with just the two types of where criteria?

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 10:52:57

quote:
Originally posted by spirit1

i'm a bit stumped

are any tables much larger that the others?
are all inner joins?

Yes.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 10:59:12

New test. All four queries returns same three records

4699B501-0986-4B42-A4A3-949F1B195E66
9D6947E8-70E5-465B-9C55-D03A51B5FF9D
BFE45D5E-1B7C-48F8-8BE1-54F3A9D8A407

This is the test code

DECLARE	@Path VARCHAR(16),
	@From CHAR(6),
	@To CHAR(6)

SELECT	@Path = '55920[23456789]',
	@From = '559202',
	@To = '559209'

-- Query 1, 6% of batch and 62 reads
SELECT	OrderID
FROM	tOrder
WHERE	OrderNumber BETWEEN '559202' AND '559209'

-- Query 2, 64% of batch and 8116 reads
SELECT	OrderID
FROM	tOrder
WHERE	OrderNumber BETWEEN @From AND @To

-- Query 3, 6% of batch and 62 reads
SELECT	OrderID
FROM	tOrder
WHERE	OrderNumber LIKE '55920[23456789]'

-- Query 4, 24% of batch and 8116 reads
SELECT	OrderID
FROM	tOrder
WHERE	OrderNumber LIKE @Path

And these are the query plans


  |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrder].[IX_tOrder_OrderNo]), SEEK:([tOrder].[OrderNumber] >= [@1] AND [tOrder].[OrderNumber] <= [@2]) ORDERED FORWARD)


  |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrder].[IX_tOrder_OrderNo]), SEEK:([tOrder].[OrderNumber] >= [@From] AND [tOrder].[OrderNumber] <= [@To]) ORDERED FORWARD)


  |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrder].[IX_tOrder_OrderNo]), SEEK:([tOrder].[OrderNumber] >= '559202' AND [tOrder].[OrderNumber] < '55920A'),  WHERE:(like([tOrder].[OrderNumber], '55920[23456789]', NULL)) ORDERED FORWARD)


  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1008], [Expr1009], LikeRangeInfo([@Path], NULL)))
       |--Compute Scalar(DEFINE:([Expr1008]=Convert(LikeRangeStart([@Path], NULL)), [Expr1009]=Convert(LikeRangeEnd([@Path], NULL)), [Expr1006]=LikeRangeInfo([@Path], NULL)))
       |    |--Constant Scan
       |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrder].[IX_tOrder_OrderNo]), SEEK:([tOrder].[OrderNumber] > [Expr1008] AND [tOrder].[OrderNumber] < [Expr1009]),  WHERE:(like([tOrder].[OrderNumber], [@Path], NULL)) ORDERED FORWARD)

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-30 : 11:09:33

The query plans for the first 3 look the same, and the last doesn't look very different.

How many actual reads were there for each query?

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 11:13:01

Update above with values of reads according to Profiler.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 11:15:30

is this in a sproc?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 11:18:52

Here is another strange part. Hinting an index for the cases with hardcoded values gives no gain.
But with the cases with parameters


-- Query 1 with hint 0% of batch, 62 reads
SELECT	OrderID
FROM	tOrder WITH (INDEX(IX_tOrder_OrderNo))
WHERE	OrderNumber BETWEEN '559202' AND '559209'

-- Query 2 with hint 80% of batch, 749 reads
SELECT	OrderID
FROM	tOrder WITH (INDEX(IX_tOrder_OrderNo))
WHERE	OrderNumber BETWEEN @From AND @To

-- Query 3 with hint 0% of batch, 62 reads
SELECT	OrderID
FROM	tOrder WITH (INDEX(IX_tOrder_OrderNo))
WHERE	OrderNumber LIKE '55920[23456789]'

-- Query 4 with hint 20% of batch, 749 reads
SELECT	OrderID
FROM	tOrder WITH (INDEX(IX_tOrder_OrderNo))
WHERE	OrderNumber LIKE @Path

The query plans now look like


  |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrder].[IX_tOrder_OrderNo]), SEEK:([tOrder].[OrderNumber] >= '559202' AND [tOrder].[OrderNumber] <= '559209') ORDERED FORWARD)

  |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrder].[IX_tOrder_OrderNo]), SEEK:([tOrder].[OrderNumber] >= [@From] AND [tOrder].[OrderNumber] <= [@To]) ORDERED FORWARD)

  |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrder].[IX_tOrder_OrderNo]), SEEK:([tOrder].[OrderNumber] >= '559202' AND [tOrder].[OrderNumber] < '55920A'),  WHERE:(like([tOrder].[OrderNumber], '55920[23456789]', NULL)) ORDERED FORWARD)

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1008], [Expr1009], LikeRangeInfo([@Path], NULL)))
       |--Compute Scalar(DEFINE:([Expr1008]=Convert(LikeRangeStart([@Path], NULL)), [Expr1009]=Convert(LikeRangeEnd([@Path], NULL)), [Expr1006]=LikeRangeInfo([@Path], NULL)))
       |    |--Constant Scan
       |--Index Seek(OBJECT:([OFS_From_Production].[dbo].[tOrder].[IX_tOrder_OrderNo]), SEEK:([tOrder].[OrderNumber] > [Expr1008] AND [tOrder].[OrderNumber] < [Expr1009]),  WHERE:(like([tOrder].[OrderNumber], [@Path], NULL)) ORDERED FORWARD)

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 12:33:26

run this and see if you get 1 or 2 exec plans.


SELECT	text, *
FROM	sys.dm_exec_cached_plans AS cp
	CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
Where   text like '%condition%'

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 12:46:48

I'll try that tomorrow. Time to go to hotel now.
Thanks.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-31 : 18:09:53

any progress?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources