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

Query is still taking same time. Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 5:32 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 15, 2014 3:25 AM
Points: 718, Visits: 547
Hi all,

I optimized a query by replacing table valued function with LEFT JOIN, and managed to reduce the Scan Count and Logical Reads from 1500,12000 to 25,150.

But Query is still taking same time for execution. What could be the possible reason!!!
Post #1446428
Posted Thursday, April 25, 2013 6:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 39,968, Visits: 36,327
Could be anything, and without more information there's no way that anyone's going to be able to offer useful help.

To start with, what's the wait types the query gets? What's the query? What are the table definitions? What indexes do those table have? What does the execution plan look like? What's the CPU and duration? Are there still scalar functions (that don't count towards the logical reads displayed) and if so what are their definitions?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1446450
Posted Thursday, April 25, 2013 8:38 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, September 26, 2014 9:28 AM
Points: 4,362, Visits: 6,204
I have seen quite a few refactors addressing huge logical reads from nested loops on lots of rows (bad estimates usually the root cause) changed to scans and hashes taking equal or even longer total time due to the CPU hit (or tempdb hit) for the hashing.

As Gail said there are a LOT of things that could be in play here and you gave us essentially nothing to go on.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1446539
Posted Thursday, April 25, 2013 11:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 13,776, Visits: 28,178
As the others have said, it could be anything. They didn't list contention, so I'll toss that out there too.

Posting the query & structure along with an actual execution plan will get you more and better information.


----------------------------------------------------
"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
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 #1446643
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse