April 29, 2003 at 11:56 pm
Hi all
Well, SP3 of SQL Server is slowly killing me. Issues I am having:
a) Developer alters a view via EM, occasionally this completely screws the procedure cache and any subsequent stored proc execution or view selection returns column data in the wrong order (columns shift to the left by 1) ! a DBCC FREEPROCCACHE fixes the problem immediatley. THis all happens randomly and its difficult to repeat.
b) A single stored proc, after some period of heavy activity, suddenly decides to generate a completely difference execution plan for itself, random users will get the "working" plan (returning data in 1 sec) and others get the "bung" plan, returing data in 40secs. I had to add the with recompile option to it to stop the problem reoccuring. Doesnt happen anywhere else that I know of to date.
c) For some strange reason, this query is taking 30 to 40 sec to run. The issue is around the selects for the 3 sums we are doing. The first column (FILL_COUNT) is fine, add the next the query takes another 4 sec, add the third and it adds another 8 sec onto that!. The generated plan is optimal and uses index seeks and good use of the buffer cache and minimal physical IO when it needs to.
SELECT DISTINCT LE.lodgeenrol_match_cps_no AS CPS_NO,
ISNULL(CPS.cps_fee_exempt_ind, 0) AS IS_FEE_EXEMPT,
QC.public_desc + ' ' + TP.registered_name AS COURSE_QUAL,
(SELECT isnull(SUM(LE1.lodgeenrol_module_hours), 0) FROM lodgement_enrolment AS LE1 WHERE LE1.lodgeenrol_lodgement_no = L.LODGEMENT_NO AND LE1.lodgeenrol_match_cps_no = LE.lodgeenrol_match_cps_no AND LE1.lodgeenrol_match_studfeetype_id = 'Z') AS FULL_COUNT,
(SELECT isnull(SUM(LE1.lodgeenrol_module_hours), 0) FROM lodgement_enrolment AS LE1 WHERE LE1.lodgeenrol_lodgement_no = L.LODGEMENT_NO AND LE1.lodgeenrol_match_cps_no = LE.lodgeenrol_match_cps_no AND LE1.lodgeenrol_match_studfeetype_id NOT IN ('V', 'Z')) AS CONCESSION_COUNT,
(SELECT isnull(SUM(LE1.lodgeenrol_module_hours), 0) FROM lodgement_enrolment AS LE1 WHERE LE1.lodgeenrol_lodgement_no = L.LODGEMENT_NO AND LE1.lodgeenrol_match_cps_no = LE.lodgeenrol_match_cps_no AND LE1.lodgeenrol_match_studfeetype_id = 'V') AS FEE_EXEMPT
FROM
lodgement_enrolment LE (nolock) INNER JOIN contracted_program_study CPS (nolock) ON LE.lodgeenrol_match_cps_no = CPS.cps_no INNER JOIN Training_Product TP (nolock) ON CPS.cps_training_product_id = TP.training_product_id INNER JOIN lodgement L (nolock) ON LE.lodgeenrol_lodgement_no = L.lodgement_no INNER JOIN Course C (nolock) ON TP.training_product_id = C.training_product_id INNER JOIN Qualification_Category QC (nolock) ON C.qual_cat_code = QC.qual_cat_code
Where (L.lodgement_org_id = @OrgID) And (L.LODGEMENT_NO = @LodgementNumber)
Now, I reworked it as shown below and all runs in 800msec... im bamboozled as to what is going on here.
DECLARE @logdefee TABLE
(
lodgeenrol_match_cps_noint,
CPS_NOint,
IS_FEE_EXEMPTsmallint,
COURSE_QUALvarchar(1000),
FULL_COUNT int,
CONCESSION_COUNT int,
FEE_EXEMPT int
)
INSERT INTO @logdefee
(lodgeenrol_match_cps_no, cps_no, is_fee_exempt, course_qual)
SELECT DISTINCT
lodgeenrol_match_cps_no,
LE.lodgeenrol_match_cps_no AS CPS_NO,
ISNULL(CPS.cps_fee_exempt_ind, 0) AS IS_FEE_EXEMPT,
QC.public_desc + ' ' + TP.registered_name AS COURSE_QUAL
FROM
lodgement L
INNER JOIN lodgement_enrolment LE (nolock)
ON L.lodgement_org_id = @OrgID
and L.LODGEMENT_NO = @LodgementNumber
and LE.lodgeenrol_lodgement_no = L.lodgement_no
INNER JOIN contracted_program_study CPS (nolock)
ON LE.lodgeenrol_match_cps_no = CPS.cps_no
INNER JOIN Training_Product TP (nolock)
ON CPS.cps_training_product_id = TP.training_product_id
INNER JOIN Course C (nolock)
ON TP.training_product_id = C.training_product_id
INNER JOIN Qualification_Category QC (nolock)
ON C.qual_cat_code = QC.qual_cat_code
UPDATE @logdefee
SET
FULL_COUNT = isnull((select isnull(SUM(LE1.lodgeenrol_module_hours), 0)
FROM lodgement_enrolment AS LE1 (nolock)
WHERE LE1.lodgeenrol_lodgement_no = @LodgementNumber
AND LE1.lodgeenrol_match_cps_no = cps_no
AND LE1.lodgeenrol_match_studfeetype_id = 'Z'
),0)
FROM @logdefee
UPDATE @logdefee
SET CONCESSION_COUNT = isnull((select isnull(SUM(LE1.lodgeenrol_module_hours), 0)
FROM lodgement_enrolment AS LE1 (nolock)
WHERE LE1.lodgeenrol_lodgement_no = @LodgementNumber
AND LE1.lodgeenrol_match_cps_no = cps_no
AND LE1.lodgeenrol_match_studfeetype_id NOT IN ('V', 'Z')
),0)
FROM @logdefee
UPDATE @logdefee
SET FEE_EXEMPT = isnull((select isnull(SUM(LE1.lodgeenrol_module_hours), 0)
FROM lodgement_enrolment AS LE1 (nolock)
WHERE LE1.lodgeenrol_lodgement_no = @LodgementNumber
AND LE1.lodgeenrol_match_cps_no = cps_no
AND LE1.lodgeenrol_match_studfeetype_id = 'V'
),0)
FROM @logdefee
SELECT CPS_NO, IS_FEE_EXEMPT,COURSE_QUAL, FULL_COUNT, CONCESSION_COUNT, FEE_EXEMPT
FROM @logdefee
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
April 30, 2003 at 4:53 am
As for why you are seeing the issues I am not sure. I have SP3 on 3 servers and not one has shown a change in working.
However for situation "a" it may be your person needs to run sp_recompile against all views and Stored Procedures that use the view or SP they are altering. The reason i position changes are not reflected until a recompile takes place as the dependencies are recorded (for static sql) in a table and there ordinal position is marked. If the data changes it reflects incorrectly.
Now my theory on your change execution plan may be relative to your subqueries in some way but without hands on testing I am not sure wat I am looking for.
Finally based on your query I think this may work better for you without the subqueries. Unfortunately I have no way to test so I am working from the proper concepts. It may need a bit of tweaking.
SELECT
LE.lodgeenrol_match_cps_no AS CPS_NO,
ISNULL(CPS.cps_fee_exempt_ind, 0) AS IS_FEE_EXEMPT,
QC.public_desc + ' ' + TP.registered_name AS COURSE_QUAL,
SUM(CASE WHEN LE.lodgeenrol_match_studfeetype_id = 'Z' THEN LE.lodgeenrol_module_hours ELSE 0 END) AS FULL_COUNT,
SUM(CASE WHEN LE.lodgeenrol_match_studfeetype_id NOT IN ('V','Z') THEN LE.lodgeenrol_module_hours ELSE 0 END) AS CONCESSION_COUNT,
SUM(CASE WHEN LE.lodgeenrol_match_studfeetype_id = 'V' THEN LE.lodgeenrol_module_hours ELSE 0 END) AS FEE_EXEMPT
FROM
lodgement_enrolment LE (nolock)
INNER JOIN
contracted_program_study CPS (nolock)
INNER JOIN
Training_Product TP (nolock)
INNER JOIN
Course C (nolock)
INNER JOIN
Qualification_Category QC (nolock)
ON
C.qual_cat_code = QC.qual_cat_code
ON
TP.training_product_id = C.training_product_id
ON
CPS.cps_training_product_id = TP.training_product_id
ON
LE.lodgeenrol_match_cps_no = CPS.cps_no
INNER JOIN
lodgement L (nolock)
ON
LE.lodgeenrol_lodgement_no = L.lodgement_no
Where
L.lodgement_org_id = @OrgID And
L.LODGEMENT_NO = @LodgementNumber
GROUP BY
LE.lodgeenrol_match_cps_no,
ISNULL(CPS.cps_fee_exempt_ind, 0),
QC.public_desc + ' ' + TP.registered_name
Just a note Order of Operation can be affected by placement of the Join statements, generally SQL is good with the decision itself as long as the joins alll relate to a single table object and not multiple.
But I have found in situations where there is multiple relationships it is better to nest objects under their relations to ensure SQL will see this way and makes for better readability.
April 30, 2003 at 8:08 pm
Thanks Antares686, your a champion! ill give the SQL a go, funny enough I was pondering the case inside of the join but didnt go through with it.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply