July 23, 2013 at 3:19 am
I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again executed with in 11 sec.Then i tried removing cast used in query and when executed it ran just in 8 seconds.But when i again the original query(with cast) it takes less time.I checked in several computers running the query.But in some cases i get output just in 1 or 2 seconds also.So same query takes different time to execute in different pc and in same pc also time required is not consistent.a So there is no consistency with the time taken to execute.So i am not able to find why this happens?
Below is the query i used to test
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 )
Here customer table has about 30 columns.In that 'Customer name' is of datatype varchar(255),'Sl_No' is of int,'Id' is of int and 'Phone no' is of varchar(255).
July 23, 2013 at 3:35 am
This is probably due to indexing (if you remove the cast the query optimizer might use an index, while first he didn't) and to caching.
You'll need to check out the actual execution plans to find out what's going on.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 23, 2013 at 3:37 am
-- 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%'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 23, 2013 at 4:01 am
Hi Koen Verbeeck and ChrisM@Work
I know that indexing may help in reducing the time required to run the query.
But we want to know for which query is slower and requires indexing.But when i execute the same query(with cast) time required to execute is different.It varies from 1 second to 14 second.First of all i want to know why this kind of variation is shown?
July 23, 2013 at 4:21 am
This simple query will always run as a table (or index) scan. How long it takes to complete will depend upon quite a few factors and some of these are variable, such as the current server load, how much of the customer table happens to be in cache, the state of the network between server and clients and how busy the clients are (you're returning and displaying all results, remember). Whilst it's not impossible to obtain some measure of these variables, you should ask yourself if it's a worthwhile exercise to conduct on a query which you know to be poorly written and in need of a little tweaking. Unless of course you're baselining, but that's a different story.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 23, 2013 at 6:55 am
I will try using index.Before that i want to know how can i find the reason for slow running of query.(It may be due to network latency,table in high usage etc etc).Is there any method to find the reason for it? I am interested to know why the same query showing much difference time to execute.
July 23, 2013 at 7:46 am
you can also refer the execution plan for this.......
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 23, 2013 at 9:29 am
You could be seeing a number of different things. It could be simple contention on resources, other processes are reading/writing to the table you need, so you're waiting, or the query uses a lot of CPU and some other query that needs the CPU is causing you to wait. That can be extremely variable and won't be immediately apparent from one running of a query to the next. It could be that, because you're scanning the table due to that function on the column in the WHERE clause, you have to load everything into cache and sometimes it's available in cache, so it returns quickly, but other times it has aged out of cache to support other processes that are also scanning the table (because if you don't know that functions on columns when filtering is a major performance hit, I'll bet it's all over your code) so it has to reload the cache, slowing things down. It could be other stuff. But everyone has already given you the best possible answers, get rid of the function on the WHERE clause, and start using the execution plan to understand what is happening within the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 26, 2013 at 2:11 am
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
July 26, 2013 at 2:20 am
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.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 26, 2013 at 8:28 am
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..
July 26, 2013 at 8:29 am
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..
July 26, 2013 at 8:37 am
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.
July 27, 2013 at 1:41 am
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?
July 27, 2013 at 2:02 am
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply