SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query taking non-consistent time to execute


Query taking non-consistent time to execute

Author
Message
IT researcher
 IT researcher
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1798
ChrisM@Work (7/26/2013)
IT researcher (7/26/2013)
ChrisM@Work (7/23/2013)
-- why cast [Phone no] AS VARCHAR(MAX)?
-- why use CHARINDEX? You don't need the character position, you only need to know if
-- one string exists inside the other.
SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 )

-- use this instead
SELECT c.[Customer name],c.[Sl_No],c.[Id]
FROM [company].dbo.[customer] c
WHERE c.[Phone no] LIKE '%9000413237%'

-- then try this, which is SARGable (can use a suitable index)
SELECT c.[Customer name],c.[Sl_No],c.[Id]
FROM [company].dbo.[customer] c
WHERE c.[Phone no] LIKE '9000413237%'




You have mentioned that using LIKE '9000413237%' is SARGable .There are many articles stating 'abc%' will use index but '%abc%' not.But using LIKE '%9000413237%' also helped me a lot.
Please see my thread regarding this here


It's quicker (in this case) to scan the phone_no index than to scan the whole table, because the index only contains one (key) column. That's less data to scan through. The point about SARGability is not that an index will be used, but that the index will be used for seeks or range scans. If you look at the second plan on your ScatExchange post you see an index scan for LIKE '%...%'. Look at what happens if you take out the leading % sign.

Yes if i use 'abc%' then it will be index seek as it requires searching for only part of index which make performance a lot better.But still using %abc% helped in my case.I was thinking like leading % will make index to not use.So many articles in internet make confusion regarding it.Also i found this wikipedia link about Sargable.(which states %abc% non-sargable).Thank you..
IT researcher
 IT researcher
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1798
ChrisM@Work (7/26/2013)
IT researcher (7/26/2013)
ChrisM@Work (7/23/2013)
-- why cast [Phone no] AS VARCHAR(MAX)?
-- why use CHARINDEX? You don't need the character position, you only need to know if
-- one string exists inside the other.
SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 )

-- use this instead
SELECT c.[Customer name],c.[Sl_No],c.[Id]
FROM [company].dbo.[customer] c
WHERE c.[Phone no] LIKE '%9000413237%'

-- then try this, which is SARGable (can use a suitable index)
SELECT c.[Customer name],c.[Sl_No],c.[Id]
FROM [company].dbo.[customer] c
WHERE c.[Phone no] LIKE '9000413237%'




You have mentioned that using LIKE '9000413237%' is SARGable .There are many articles stating 'abc%' will use index but '%abc%' not.But using LIKE '%9000413237%' also helped me a lot.
Please see my thread regarding this here


It's quicker (in this case) to scan the phone_no index than to scan the whole table, because the index only contains one (key) column. That's less data to scan through. The point about SARGability is not that an index will be used, but that the index will be used for seeks or range scans. If you look at the second plan on your ScatExchange post you see an index scan for LIKE '%...%'. Look at what happens if you take out the leading % sign.

Yes if i use 'abc%' then it will be index seek as it requires searching for only part of index which make performance a lot better.But still using %abc% helped in my case.I was thinking like leading % will make index to not use.So many articles in internet make confusion regarding it.Also i found this wikipedia link about Sargable.(which states %abc% non-sargable).Thank you..
anupinder.rai
anupinder.rai
Right there with Babe
Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)Right there with Babe (779 reputation)

Group: General Forum Members
Points: 779 Visits: 411
SQL environment should be kept same for each execution.First time you run the query ,query plan is cached .Next time it will use that query plan.
Time also depends upon statistics of the table.
IT researcher
 IT researcher
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1798
ChrisM@Work (7/26/2013)
IT researcher (7/26/2013)
ChrisM@Work (7/23/2013)
-- why cast [Phone no] AS VARCHAR(MAX)?
-- why use CHARINDEX? You don't need the character position, you only need to know if
-- one string exists inside the other.
SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 )

-- use this instead
SELECT c.[Customer name],c.[Sl_No],c.[Id]
FROM [company].dbo.[customer] c
WHERE c.[Phone no] LIKE '%9000413237%'

-- then try this, which is SARGable (can use a suitable index)
SELECT c.[Customer name],c.[Sl_No],c.[Id]
FROM [company].dbo.[customer] c
WHERE c.[Phone no] LIKE '9000413237%'




You have mentioned that using LIKE '9000413237%' is SARGable .There are many articles stating 'abc%' will use index but '%abc%' not.But using LIKE '%9000413237%' also helped me a lot.
Please see my thread regarding this here


It's quicker (in this case) to scan the phone_no index than to scan the whole table, because the index only contains one (key) column. That's less data to scan through. The point about SARGability is not that an index will be used, but that the index will be used for seeks or range scans. If you look at the second plan on your ScatExchange post you see an index scan for LIKE '%...%'. Look at what happens if you take out the leading % sign.


You have mentioned that
" The point about SARGability is not that an index will be used, but that the index will be used for seeks or range scans"

In wikipedia i can see that leading % in like is non-sargable. But where it is mentioned that SARGability is only seek or range scan?
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2135 Visits: 872
Coming late to the party, but...

The charindex expression is bound to scane the entire table.

LIKE '9000413237%' will seek the index in a normal way.

LIKE '%9000413237%' will also result in an Index Seek operator, but if you look closer there is a RangeScan function in the search predicate. And of course, while a Seek in the name, the only way to find the arbitrary string is to scan the entire index. But assuming that this only hits a few rows, this is more efficient than scanning the clustered index, since there are fewer pages to scan. SQL Server maintains string statistics, so it has a hinch about the hit rate you will get in the index.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search