Why does ad-hoc SQL run faster than executing the same code in a stored procedure?

I have a stored procedure that processes phones and addresses in a batch in SQL server 2005

If I execute the stored procedure in takes 2 hours. But if I run the same code and the same batch ad hoc it takes 2 seconds.

I have try the following steps to make it faster but they have not worked:

• Re indexing the entire database
• SET ANSI_NULLS ON;
• DBCC FreeProcCache
• DBCC DROPCLEANBUFFERS

Here is the basic code

USE [MyDB]

GO



/****** Object:  StoredProcedure [myschema].[ProccesBatch]    Script Date: 06/30/2011 10:37:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [myschema].[ProccesBatch] 
-- Add the parameters for the stored procedure here

(@BatchId int)


AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;
SET ANSI_NULLS ON;

-- AD Hoc TESTING ONLY. This gets uncommented when running ad hoc.
-- DECLARE @BatchId Int
-- SET @BatchId = 59

DECLARE @MyList AS VARCHAR (500)
DECLARE @MySICList AS VARCHAR (500)
DECLARE @MyType AS CHAR (1)
DECLARE @MyProvider AS VARCHAR (500)
DECLARE @MyState AS VARCHAR (2)
DECLARE @MyCityList AS VARCHAR (500)
DECLARE @MyZipList AS VARCHAR (500)
DECLARE @MyStyle AS VARCHAR (1)
DECLARE @MySource AS VARCHAR (150)
DECLARE @MyStartDate AS DATETIME
DECLARE @MyEndDate AS DATETIME
DECLARE @MyCampaign  AS BIT
DECLARE @CheckExist AS INT
SET @CheckExist = 0
-- 
--  1. Check if Campaign Exist.
--  
SELECT 
    @CheckExist = Id 
FROM myschema.Destination
WHERE Id = @BatchId

IF @CheckExist > 0
     BEGIN
     RAISERROR('Creation has already been processed', 16, 1)
     RETURN
     END      

-- 
--  2. Get Header and parameters for controlling process.
-- 
SELECT       
    @MyList = ISNULL(LeadBatchHeaderList,''),       
    @MySICList = ISNULL(SICCodeList,''),
    @MyType = ISNULL(MyType,''),
    @MyProvider = ISNULL(LDCList,''),
    @MyState = ISNULL([State],''),
    @MyCityList = ISNULL(CityList,''),      
    @MyZipList = ISNULL(ZipCodeList,''),        
    @MyStyle = ISNULL(Commodities,''),
    @MySource = ISNULL(LeadSource,''),
    @MyStartDate = ISNULL(HeaderCreationStart,''),
    @MyEndDate = ISNULL(HeaderCreationEnd,''),
    @MyCampaign = ISNULL(AllCampaign ,'')
FROM myschema.Header
WHERE ID = @BatchId

IF @@ROWCOUNT < 1
     BEGIN
     RAISERROR('header id was not found', 16, 1)
     RETURN
     END   

-- Place Commas for charindex     
IF @MyList > ''
    SET @MyList = ',' + @MyList + ','
IF @MySICList  > ''
    SET @MySICList =  ',' + @MySICList + ','
IF @MyProvider > ''
    SET @MyProvider = ',' + @MyProvider + ','
IF @MyCityList > '' 
    SET @MyCityList = ',' + @MyCityList + ','   
IF @MyZipList > '' 
    SET @MyZipList = ',' + @MyZipList + ','    

--
-- 3. Add  qualifying leads.
--
INSERT INTO myschema.Destination    
(Id, LeadBatchDetailId, CustomerIdOne, CustomerIdTwo, MyProviderOne, MyProviderTwo, SicCode, SicDesc, SicCode2, SicDesc2, 
MyType, Company, CompanyURL, Title, Salutation, Suffix, FullName, FirstName, MiddleInitial, 
LastName, Email, MyPhone, Work, Cell, Home, Fax, Ext, Address1, Address2, City, [State], 
Zip5, Zip4, County, TSR, EmployeeSize, Revenue, MyProviderOne, MyProviderTwo, CustomerUsageOne, CustomerUsageTwo, MyExpenses, Remarks, Decline, 
WhyLeft, PCC, RCC, PCC, SCC)
SELECT 
    @BatchId, d.ID, d.CustomerIdOne, d.CustomerIdTwo, d.MyProviderOne, d.MyProviderTwo, d.SicCode, d.SicDesc, d.SicCode2, d.SicDesc2, 
    d.MyType, d.Company, d.CompanyURL, d.Title, d.Salutation, d.Suffix, d.FullName, d.FirstName, d.MiddleInitial, 
    d.LastName, d.Email, d.MyPhone, d.Work, d.Cell, d.Home, d.Fax, d.Ext, d.Address1, d.Address2, d.City, d.[State], 
    d.Zip5, d.Zip4, d.County, d.TSR, d.EmployeeSize, d.Revenue, d.MyProviderOne, d.MyProviderTwo,d.CustomerUsageOne, d.CustomerUsageTwo, d.MyExpenses, d.Remarks, d.Decline, 
    d.WhyLeft, d.PCC, d.RCC, d.PCC, d.SCC
FROM myschema.Source as d 
JOIN myschema.Summary as h ON d.MyId = h.ID
JOIN myschema.source AS s ON h.Id = s.ID
WHERE
    -- MyId. 
    (@MyList = '' OR (charindex(',' + CAST(d.MyId AS VARCHAR) + ',', @MyList) > 0)) AND

    -- SIC Code. 
    (@MySICList = '' OR (charindex(',' + CAST(d.SicCode AS VARCHAR) + ',', @MySICList) > 0)) AND

    -- My Types 
    (@MyType = '' OR @MyType = 'A' OR d.MyType = @MyType OR h.DefaultMyType = @MyType) AND

    -- MYProviders
    ((@MyProvider = '' OR (charindex(',' + CAST(d.MyProviderOne AS VARCHAR) + ',', @MyProvider) > 0)) OR
    (@MyProvider = '' OR (charindex(',' + CAST(d.MyProviderTwo AS VARCHAR) + ',', @MyProvider) > 0))) AND

    -- State. 
    (@MyState = '' OR d.[State] = @MyState) AND

    -- City.
    (@MyCityList = '' OR (charindex(',' + d.City + ',', @MyCityList) > 0)) AND

    -- Zip Code. 
    (@MyZipList = '' OR (charindex(',' + d.Zip5 + ',', @MyZipList) > 0)) AND

    -- LeadSource
    (@MySource = '' OR s.MySource = @MySource) AND

    -- Between Dates
    (@MyStartDate = '' AND @MyEndDate = '' OR h.CreationDate BETWEEN @MyStartDate AND @MyEndDate) AND 

     -- Mystyle
    ((@MyStyle = 'A' AND (d.MyProviderOne IS NOT NULL OR d.MyProviderOne > 0 OR d.CustomerUsageOne > 0)) OR
    (@MyStyle = 'B' AND (d.MyProviderTwo IS NOT NULL OR d.MyProviderTwo > 0 OR d.CustomerUsageTwo > 0)) OR
    (@MyStyle = '' OR @MyStyle IS NULL)) AND

    -- Source parameters are important. Only processed finished batches.
    (h.UseThisRecord = 1) AND
    (h.[status] = 'Finished') AND
    (d.MyDuplicate IS NULL) AND
    (d.DoNotUseFlag IS NULL) AND
    (d.DoNotUseIFlag IS NULL) AND
    (d.CustomerHome IS NULL) AND
    (d.CustomerWork IS NULL) AND
    (d.LeadDuplicate IS NULL) AND
    (d.MyPhone >'' OR d.MyPhone <> NULL) AND
    ((CAST(FLOOR( CAST( h.ExpirationDate AS FLOAT ) )AS DATETIME) > CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME)) OR 
    h.ExpirationDate IS NULL)



--
-- 4. Flag Phone Duplicates inside myschema.Destination
--

 UPDATE T1
  SET DeleteFlag = 1
  FROM myschema.Destination T1, myschema.Destination T2
  WHERE
        T1.MyPhone = T2.MyPhone AND                   
        T1.FullName = T2.FullName AND 
        T1.Address1 = T2.Address1 AND       
        T1.City = T2.City AND                     
        T1.[State] = T2.[State] AND             开发者_JAVA技巧        
        T1.Zip5 = T2.Zip5 AND                     
        T1.MyPhone <> '' AND
        T1.Id = T2.Id AND -- This will flag the batch itself
        T1.Id = @BatchId AND
        T1.Id < T2.Id  -- This will leave the highest Id unflagged (latest record) 



--
-- 5. Duplicate Contact Flag. All Records
--      
IF @MyCampaign  = 1
UPDATE T1
  SET DeleteFlag = 1
  FROM myschema.Destination T1, myschema.Destination T2
  WHERE
        T1.MyPhone = T2.MyPhone AND                   
        T1.FullName = T2.FullName AND 
        T1.Address1 = T2.Address1 AND       
        T1.City = T2.City AND                     
        T1.[State] = T2.[State] AND                     
        T1.Zip5 = T2.Zip5 AND                     
        T1.MyPhone <> '' AND
        T1.Id = @BatchId AND
        T1.Id <> T2.Id -- Process against other batches



--
-- 6. Active Flag
--  

IF @MyCampaign <> 1
UPDATE T1
  SET DeleteFlag = 1
  FROM myschema.Destination T1, myschema.Destination T2
  JOIN myschema.Header H ON T2.Id = H.ID
  WHERE
        T1.MyPhone = T2.MyPhone AND                   
        T1.FullName = T2.FullName AND 
        T1.Address1 = T2.Address1 AND       
        T1.City = T2.City AND                     
        T1.[State] = T2.[State] AND                     
        T1.Zip5 = T2.Zip5 AND                     
        T1.MyPhone <> '' AND
        T1.Id = @BatchId AND
        T1.Id <> T2.Id AND -- Process against other batches
        H.ActiveBatch = 1 -- Only Active



--
-- 7. Delete DeleteFlag rows. Check for Id just in case
--  
IF @BatchId > 0
    DELETE FROM myschema.Destination
        WHERE        
            (DeleteFlag = 1) AND (Id = @BatchId)



--
-- 8. Update header with date last run
--                  
UPDATE myschema.Header
SET DateLastRun = GETDATE()
WHERE ID = @BatchId



END






GO

Thanks, Christian

This often happens when you use parameters in the proc and constants ad-hoc

SQL Server tries to generate a re-usable plan usually. With constants it doesn't need to because it can never be re-used for different constants.

There are other options such as SET options for the stored procedure or datatype mismatches.. but we have no further information to go on.

1 - Show the code.

2 - without seeing the code, I'm going to guess you have multiple parameters you are passing to the stored proc, and you are having a parameter sniffing issue.

This may be due to parameter sniffing. Have you compared the execution plans? If you grab the free Plan Explorer from http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp you can see quite clearly, among other things, the runtime vs. compiled values for the parameters to your stored procedure. Sometimes adding WITH RECOMPILE to the procedure can help prevent bad plans from sticking around (at the potential cost of slightly higher CPU usage every time the procedure is executed). But it's not always the best answer - the plan that's currently cached may be atypical and RECOMPILE every time may be overkill. Sometimes the answer is in how the parameters are defined - e.g. if you pass a parameter in you can sometimes defeat parameter sniffing by declaring a local variable and passing the input param to that and using the local variable later in the code. Sometimes the answer is turning on the optimize for ad hoc setting. Tough to say without more details.

Investigating the plan is a good first step, though. If you see that the plans are different post back with the differences and we can help guide you.

It would help to see the sproc and the query but I'm guessing something 'bad' is cached about the stored procedure. Try clearing SQL's cache (assuming SQL Server from your tags). See this

There is probably a bad query plan for the stored procedure. Try dropping and recreating the stored procedure.

it may be that you put the driving query inside the loop in a funny way - making it execute too many times.

i agree with all others - show the code.