December 16, 2002 at 10:48 pm
Hi all
Have a stored proc, it creates a temp table (#), indexes it, all good so far. The code executes fine until we hit the delete statement below. When running the stored proc via query analyser (locally and remotely connected), plan A is generated below which is the optimal plan for the statement, if we call the same stored proc via VB, a completely different plan is generated (its like the index doesnt exist, but it creates fine when checked via profiler tracing).
Ive tried a variety of things related to rejigging the statement, forcing recompilation etc..
Thoughts?
SQL:
--Delete duplicate modenerol records for TRS students
DELETE #lodgement_enrolment
FROM module_enrolment
WHERE #lodgement_enrolment.lodgeenrol_match_cps_no = module_enrolment.modenrol_cps_no AND#lodgement_enrolment.lodgeenrol_match_trainprod_id = module_enrolment.modenrol_training_product_id AND#lodgement_enrolment.lodgeenrol_match_outcome_id = module_enrolment.modenrol_enroloutcome_id AND#lodgement_enrolment.lodgeenrol_match_studfeetype_id = module_enrolment.modenrol_studentfeetype_id AND
#lodgement_enrolment.lodgeenrol_match_tc_id = module_enrolment.modenrol_tc_id AND
#lodgement_enrolment.lodgeenrol_match_raptstudent_id is NULL
Plan A (optimal)
Execution Tree
--------------
Clustered Index Delete(OBJECT:([tempdb].[dbo].[#lodgement_enrolment]))
|--Top(ROWCOUNT est 0)
|--Hash Match(Right Semi Join, HASH:([module_enrolment].[modenrol_cps_no], [module_enrolment].[modenrol_training_product_id], [module_enrolment].[modenrol_enroloutcome_id], [module_enrolment].[modenrol_studentfeetype_id], [module_enrolment].[modenrol_raptstudent_id])=([#lodgement_enrolment].[lodgeenrol_match_cps_no], [#lodgement_enrolment].[lodgeenrol_match_trainprod_id], [#lodgement_enrolment].[lodgeenrol_match_outcome_id], [#lodgement_enrolment].[lodgeenrol_match_studfeetype_id], [#lodgement_enrolment].[lodgeenrol_match_raptstudent_id]), RESIDUAL:(((([module_enrolment].[modenrol_cps_no]=[#lodgement_enrolment].[lodgeenrol_match_cps_no] AND [module_enrolment].[modenrol_training_product_id]=[#lodgement_enrolment].[lodgeenrol_match_trainprod_id]) AND [module_enrolment].[modenrol_enroloutcome_id]=[#lodgement_enrolment].[lodgeenrol_match_outcome_id]) AND [module_enrolment].[modenrol_studentfeetype_id]=[#lodgement_enrolment].[lodgeenrol_match_studfeetype_id]) AND [module_enrolment].[modenrol_raptstudent_id]=[#lodgement_enrolment].[lodgeenrol_match_raptstudent_id]))
|--Table Scan(OBJECT:([corpsys].[dbo].[module_enrolment]))
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#lodgement_enrolment]), WHERE:([#lodgement_enrolment].[lodgeenrol_match_tc_id]=NULL) ORDERED)
Plan B (sub-optimal, slow as all buggery and generated when called via VB only)
Execution Tree
--------------
Clustered Index Delete(OBJECT:([tempdb].[dbo].[#lodgement_enrolment]))
|--Top(ROWCOUNT est 0)
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([#lodgement_enrolment].[lodgeenrol_match_tc_id], [#lodgement_enrolment].[lodgeenrol_match_studfeetype_id], [#lodgement_enrolment].[lodgeenrol_match_outcome_id], [#lodgement_enrolment].[lodgeenrol_match_trainprod_id], [#lodgement_enrolment].[lodgeenrol_match_cps_no]))
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#lodgement_enrolment]), WHERE:([#lodgement_enrolment].[lodgeenrol_match_raptstudent_id]=NULL) ORDERED)
|--Row Count Spool
|--Filter(WHERE:(((([module_enrolment].[modenrol_cps_no]=[#lodgement_enrolment].[lodgeenrol_match_cps_no] AND [module_enrolment].[modenrol_training_product_id]=[#lodgement_enrolment].[lodgeenrol_match_trainprod_id]) AND [module_enrolment].[modenrol_enroloutcome_id]=[#lodgement_enrolment].[lodgeenrol_match_outcome_id]) AND [module_enrolment].[modenrol_studentfeetype_id]=[#lodgement_enrolment].[lodgeenrol_match_studfeetype_id]) AND [module_enrolment].[modenrol_tc_id]=[#lodgement_enrolment].[lodgeenrol_match_tc_id]))
|--Sort(DISTINCT ORDER BY:([module_enrolment].[modenrol_cps_no] ASC, [module_enrolment].[modenrol_training_product_id] ASC, [module_enrolment].[modenrol_enroloutcome_id] ASC, [module_enrolment].[modenrol_studentfeetype_id] ASC, [module_enrolment].[modenrol_tc_id] ASC))
|--Table Scan(OBJECT:([corpsys].[dbo].[module_enrolment]))
The VB code call is:
With objCommand
.CommandType = adCmdStoredProc
.CommandTimeout = 30
.CommandText = "dbo.RAPT_CalculateEnrolmentPayments"
.Parameters.Append .CreateParameter("ReturnCode", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("FileUploadPath", adVarChar, adParamInput, Len(FileUploadPath), FileUploadPath)
.Parameters.Append .CreateParameter("EnrolFilename", adVarChar, adParamInput, Len(EnrolFilename), EnrolFilename)
.Parameters.Append .CreateParameter("UserName", adVarChar, adParamInput, 50, UserName)
Set .ActiveConnection = DBConnection
.Execute
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
December 16, 2002 at 11:01 pm
Hey Hey
Worked it out, but is a so and so to fix.
Basically, if you wrap the call in a transaction, statman wont run and therefore the optimizer will think there are no indexes to be used. We could repeat the problem in query analyser, so if i did this:
begin transaction
exec myproc
end transaction
the statman stats collection, wouldnt be run. Remove the trans and it would.
SELECT statman([lodgeenrol_match_tc_id],[lodgeenrol_match_cps_no],[lodgeenrol_match_outcome_id],[lodgeenrol_match_trainprod_id],[lodgeenrol_match_studfeetype_id],@PSTATMAN)
FROM (SELECT TOP 100 PERCENT [lodgeenrol_match_tc_id],[lodgeenrol_match_cps_no],[lodgeenrol_match_outcome_id],[lodgeenrol_match_trainprod_id],[lodgeenrol_match_studfeetype_id] FROM [dbo].[#lodgement_enrolment________________________________________________________________________________________________0001000027F9] WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [lodgeenrol_match_tc_id],[lodgeenrol_match_cps_no],[lodgeenrol_match_outcome_id],[lodgeenrol_match_trainprod_id],[lodgeenrol_match_studfeetype_id]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
December 17, 2002 at 12:14 am
because you cant to update stats and other assoc sp commands in the proc (or against a temp table), to get around the problem, we require hints:
DELETE #lodgement_enrolment
FROM
#lodgement_enrolment with (index(0)) inner hash join module_enrolment
on #lodgement_enrolment.lodgeenrol_match_cps_no = <<etc>>
utilising the HASH join hint and forcing the cluster index to be used.
That was bloody hard work!
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
December 17, 2002 at 4:57 am
Glad we could....help!
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply