September 15, 2005 at 5:39 am
Has anyone else come across this or know of any reason why it might be happening?
We are currently working on a series of procedure which act as filters for data collection, one of these (the now infamous filter 9) runs 4 delete statement which look like the following (The NOLOCK is there to improve speed and the way in which it is run is not likely to cause a probelm):
DELETE
FROM [mailing_sals_data]
WHERE [mailing_jobid] = @mailing_jobid
AND [delidcode] NOT IN (
SELECT DISTINCT [delidcode]
FROM [mailing_base_data] (NOLOCK)
WHERE [mailing_jobid] = @mailing_jobid
AND [delidcode] IS NOT NULL
)
The @mailing_jobid parameter is a BIGINT being passed into the procedure.
If I run the 4 deletes against about 167,000 records it takes on average 2.5 minutes, when I run these in a procedure it takes alot longer, I can't say how long as I cancelled it after 2 hours!!! When we have let it run to completion we have checked the actual execute plan which is bringing up some strange results, using hash tables when they're not needed, creating it's own indexes etc...
If someone can help I will be forever in their debt.
September 15, 2005 at 5:53 am
Try this (Query was not tested)
DELETE MSD
FROM
[mailing_sals_data] MSD
RIGHT OUTER JOIN
[mailing_base_data] MBD
ON
MSD.[delidcode] = MBD.[delidcode] AND
MBD.[mailing_jobid] = @mailing_jobid AND
MSD.[mailing_jobid] = @mailing_jobid
WHERE
MSD.[delidcode] IS NULL
Regards,
gova
September 15, 2005 at 6:25 am
The problem is with SQL "parameter sniffing"
Not often this occurs, but when it does, the performance of a stored procedure can degrade quite dramatically.
You can usually tell when this occurs by trying the following:
Take the code directly out of the stored procedure, DECLARE the parameter variables being passed and SET them to the same values you are trying to pass in.
Execute the query and then substitute the parameter values of the query directly with the values and try again. the execution plan will probably change.
e.g.
DECLARE @mailing_jobid BIGINT
SET @mailing_jobid = 123
DELETE
FROM [mailing_sals_data]
WHERE [mailing_jobid] = @mailing_jobid
AND [delidcode] NOT IN (
SELECT DISTINCT [delidcode]
FROM [mailing_base_data] (NOLOCK)
WHERE [mailing_jobid] = @mailing_jobid
AND [delidcode] IS NOT NULL
)
Then try
DELETE
FROM [mailing_sals_data]
WHERE [mailing_jobid] = 123
AND [delidcode] NOT IN (
SELECT DISTINCT [delidcode]
FROM [mailing_base_data] (NOLOCK)
WHERE [mailing_jobid] = 123
AND [delidcode] IS NOT NULL
)
Check the estimated execution plans and you'll probably find that they have changed quite dramatically
The problem is that when the procedure was compiled and executed, SQL reads the first value it sees as a parameter and builds it's execution plan based on this. Unfortunately, this plan may not be the optimal one for further queries causing the performance to degrade, sometimes dramatically.
The solution is to prevent SQL from doing this by effectively hiding the value of the parameter.
Do this by passing in the parameter and then assigning it to another variable. Use this variable as parameters for your query
e.g.
CREATE PROCEDURE filter9 @mailing_jobid BIGINT
AS
DECLARE @procparam BIGINT
SET @procparam = @mailing_jobid
DELETE
FROM [mailing_sals_data]
WHERE [mailing_jobid] = @procparam
AND [delidcode] NOT IN (
SELECT DISTINCT [delidcode]
FROM [mailing_base_data] (NOLOCK)
WHERE [mailing_jobid] = @procparam
AND [delidcode] IS NOT NULL
)
This prevents SQL from parameter sniffing and will often cure the problem.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply