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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1692 Visits: 1798
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).
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63494 Visits: 13298
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42089 Visits: 20008
-- 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%'




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
IT researcher
 IT researcher
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1692 Visits: 1798
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?
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42089 Visits: 20008
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
IT researcher
 IT researcher
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1692 Visits: 1798
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.
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5386 Visits: 2767
you can also refer the execution plan for this.......

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99463 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
IT researcher
 IT researcher
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1692 Visits: 1798
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42089 Visits: 20008
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
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