Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query taking non-consistent time to execute Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 3:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 22, 2014 6:35 AM
Points: 257, Visits: 1,678
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).
Post #1476427
Posted Tuesday, July 23, 2013 3:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 12,201, Visits: 9,156
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1476435
Posted Tuesday, July 23, 2013 3:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 6,754, Visits: 12,854
-- 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
Post #1476438
Posted Tuesday, July 23, 2013 4:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 22, 2014 6:35 AM
Points: 257, Visits: 1,678
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?
Post #1476462
Posted Tuesday, July 23, 2013 4:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1476468
Posted Tuesday, July 23, 2013 6:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 22, 2014 6:35 AM
Points: 257, Visits: 1,678
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.
Post #1476553
Posted Tuesday, July 23, 2013 7:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:50 PM
Points: 1,867, Visits: 2,275
you can also refer the execution plan for this.......


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1476578
Posted Tuesday, July 23, 2013 9:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 PM
Points: 14,788, Visits: 27,266
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1476663
Posted Friday, July 26, 2013 2:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 22, 2014 6:35 AM
Points: 257, Visits: 1,678
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
Post #1477898
Posted Friday, July 26, 2013 2:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1477901
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse