May 12, 2003 at 3:46 am
Hi there
See my other post:
But for this specific problem, here is a classic which I cant explan.
I have a stored proc (see below). Dont worry about the SQL. What is strange about it:
a) takes 30sec to run in a stored proc
b) take code out into query analyser, takes 2 seconds
Plans generated are very different, to a point where one is doing two sets of 8mill row returns verses 1000 rows over the equivalent tables !
Now here is the fix:
I take the passed in parameter to the proc, place it into a local variable and suddenly the speed returns back to 2 seconds!
Anyone come across problems like this? MS support??
It is very strange, there seems to be a cutoff point in terms of row count, for example, some lodgements take 4-5sec returning 5 rows, this one that returns 25 is completely different giving wild performance differences.
Running SS2k EE with SP3
Here is the modified SQL, if you take the parameter definition and change it back to (@lodgeno varchar(10)) and of course remove the local variable, the plan is completely screwed, no matter the recompiles etc.
CREATE PROCEDURE Rapt_ReadFinancial (@clodgeno varchar(10))
AS
/***********************************************************************************************
Procedure Name: Rapt_ReadFinancial
Application:RAPT
Purpose:Validate all the student reference data
Called In:Dll- RAPT DLL , Class- Lodgement, Function - ReadFinancial
Parameters:
NameIN/OUTPurpose
record setOUT
@lodgenoIn
Change Control:
NameDateDetails
Min Chenv1.0
***********************************************************************************************/
Declare @PaymentDate smalldatetime
declare @lodgeno varchar(10)
set @lodgeno = @clodgeno
SET NOCOUNT ON
BEGIN
Set @PaymentDate = ( Select top 1 payment_date from payment (nolock) where payment_lodgement_no =@lodgeno )
If @PaymentDate is Null
set @paymentDate= GetDate()
-- Main Query
SELECT DISTINCT LE.lodgeenrol_match_cps_no AS CPS_NO,
QC.public_desc + ' ' + TP.registered_name AS COURSE_QUAL,
(SELECT
'Accepted' =
CASE
WHEN d1.payment_claim_accepted_ind =1 THEN d1.payment_amount
ELSE 0
End
FROM payment AS d1 (nolock)
WHERE d1.payment_cps_no = LE.lodgeenrol_match_cps_no
AND d1.payment_lodgement_no = @lodgeno ) AS ACCEPTED_AMOUNT,
(SELECT ISNULL(SUM(b.lodgeenrol_calc_fee_amount),0)
FROM lodgement_enrolment AS b (nolock)
WHERE b.lodgeenrol_lodgement_no = @lodgeno
AND b.lodgeenrol_match_cps_no = LE.lodgeenrol_match_cps_no) AS LESS_FEES,
(SELECT ISNULL(SUM(c.lodgeenrol_calc_pay_amount),0)
FROM lodgement_enrolment AS c (nolock)
WHERE c.lodgeenrol_lodgement_no = @lodgeno
AND c.lodgeenrol_match_cps_no = LE.lodgeenrol_match_cps_no) AS CPS_VALUE_NET,
(SELECT ISNULL(SUM(d.payment_amount),0)
FROM payment AS d (nolock)
WHERE d.payment_cps_no = LE.lodgeenrol_match_cps_no
AND d.payment_raptpaystatus_id = 2
AND (d.payment_lodgement_no is Null or d.payment_lodgement_no <> @lodgeno )
AND (d.payment_approval_decision_date <= @PaymentDate ) ) AS PREVIOUSLY_APPROVED
FROM Course C (nolock) INNER JOIN
Training_Product TP (nolock) ON C.training_product_id = TP.training_product_id INNER JOIN
Qualification_Category QC (nolock) ON C.qual_cat_code = QC.qual_cat_code RIGHT OUTER JOIN
contracted_program_study CPS (nolock) ON TP.training_product_id = CPS.cps_training_product_id
INNER JOIN lodgement_enrolment LE(nolock)
ON CPS.cps_no = LE.lodgeenrol_match_cps_no
INNER JOIN lodgement (nolock)
ON LE.lodgeenrol_lodgement_no = lodgement.lodgement_no
Where (LE.lodgeenrol_lodgement_no = @lodgeno)
GROUP BY LE.lodgeenrol_match_cps_no, QC.public_desc,TP.registered_name
ORDER BY LE.lodgeenrol_match_cps_no
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
RETURN(0)
END
END
GO
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 13, 2003 at 7:50 am
Hi All
See this:
and this from Brian Moran:
Actually... this is expected behavior and has been since SQL 4.2.
Here's what is probally happening...
Oddly enough... changing the number of rows returned by a query can have a
HUGE impact on the plan chosen. In many cases you'll find that non-clustered
indexes become ineffecient when returning even just 1% of the data in some
cases...
Proc plans are compiled the first time the procedure is run. (it's really
more complex than that but that works for this email...)
So... you might end up with a situation where one set of params produce a
plan that uses say a non-clustered index, while other parms would be
ineffecient if using the nc index. Unfortunately... the plan is already in
cache and you'll get a slow plan.
Personally... I think this is a situation that MS could and should deal with
better, but... for now... you need to be careful when you have a proc that
can generate wildly different plans based on input params. Try executing the
proc using the with recompile option and I suspect you'll see that you get
better exec times for all the procs...
<
I take the passed in parameter to the proc, place it into a local variable
and suddenly the speed returns back to 2 seconds!
>
Yes, that's part of the fun of the SQL optimizer too. It's a known design
issue and would not be fixed by using 'with recompile'. The problem is that
SQL doesn't know the value of the params when it optimizes the plan and it
might well pick a bad plan since it can't accurately guess how many rows are
coming back... the solution you outline above is a common technique for
dealing with it...
Brian
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply