Measuring Subquery Performance
-
G'day guys,
I have a fairly large sproc (our main Customer Select sproc, consists of some setup, and then 30 or so subqueries, each just doing selects from the child data tables) that has suffered a perf regression, and I'm trying to work out why.
The sproc as a whole is showing significantly more reads than I would expect in Profiler, but I'm having trouble tracking down which of the subqueries within it are causing the problems... Profiler insists on showing 0 or very low values for the individual subquery reads and duration, but then total reads is very high, so there's obviously a discrepency there (ie, the whole is much larger than the sum of the parts).
There's a fair bit of setup stuff prior to the selects (variables, Temp tables) that mean I can't just run each subquery in turn without a lot of hassle.
Is there a neat/easy way of getting correct profile results for the subqueries, or am I about to do a stack of copy/paste SQL execution?
Cheers
Tim
-
>>consists of some setup, and then 30 or so subqueries, each just doing selects from the child data tables
Are these sub-queries actually sub-SELECTs within the main SELECT, or are they in the FROM part of the query ?
If in the SELECT, you have a cursor-like situation that is expected to degrade badly with data volumes.
In in the FROM, do you have sub-queries expressed as IN (SELECT FROM) ? If so, could they be re-expressed as WHERE EXISTS () without changing query results ?
-
Sorry, they are seperate queries, the one sproc returns multiple result sets
Select x from tbl1
Select y from tbl2
select z from tbl3
Slightly more complex obviously
Cheers for the quick reply
Tim
-
>>The sproc as a whole is showing significantly more reads than I would expect in Profiler,
Do all of your tables have a clustered index to prevent the tables from becoming fragmented heaps ?
-
Yep, they do.
The good news is, I tracked down the problem yesterday!!
I grabbed a 'fast' older version of the sproc off another server for comparison purposes. When I forced a recompile, the fast version ran identically to the slow for the first run....
I added SP:Recompiles to the profile trace, and it was pretty obvious the slow version was being recompiled each execution.
A 'Select [...] into #TempTable' in the fast version was replaced with
Create Table #temp
if (@Var)
Insert into #temp select [...] from A
Else
Insert into #temp select [...] from B
End
Replacing the if block with:
Select [...]
into #temp
from a
where @Var
UNION
Select [...]
From B
where NOT @var
Brought back the original performance.
If someone can explain it that would sate my curiosity, but otherwise I'm stoked with the perf.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply