title: Why does ad-hoc SQL run faster than executing the same code in a stored procedure?
body: <p>I have a stored procedure that processes phones and addresses in a batch in SQL server 2005</p>
  
  <p>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.</p>
  
  <p>I have try the following steps to make it faster but they have not worked:</p>
  
  <ul>
  <li>Re indexing the entire database</li>
  <li>SET ANSI_NULLS ON;</li>
  <li>DBCC FreeProcCache</li>
  <li>DBCC DROPCLEANBUFFERS</li>
  </ul>
  
  <p><strong>Here is the basic code</strong></p>
  
  <pre><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 &gt; 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 &lt; 1
       BEGIN
       RAISERROR('header id was not found', 16, 1)
       RETURN
       END   
  
  -- Place Commas for charindex     
  IF @MyList &gt; ''
      SET @MyList = ',' + @MyList + ','
  IF @MySICList  &gt; ''
      SET @MySICList =  ',' + @MySICList + ','
  IF @MyProvider &gt; ''
      SET @MyProvider = ',' + @MyProvider + ','
  IF @MyCityList &gt; '' 
      SET @MyCityList = ',' + @MyCityList + ','   
  IF @MyZipList &gt; '' 
      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) &gt; 0)) AND
  
      -- SIC Code. 
      (@MySICList = '' OR (charindex(',' + CAST(d.SicCode AS VARCHAR) + ',', @MySICList) &gt; 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) &gt; 0)) OR
      (@MyProvider = '' OR (charindex(',' + CAST(d.MyProviderTwo AS VARCHAR) + ',', @MyProvider) &gt; 0))) AND
  
      -- State. 
      (@MyState = '' OR d.[State] = @MyState) AND
  
      -- City.
      (@MyCityList = '' OR (charindex(',' + d.City + ',', @MyCityList) &gt; 0)) AND
  
      -- Zip Code. 
      (@MyZipList = '' OR (charindex(',' + d.Zip5 + ',', @MyZipList) &gt; 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 &gt; 0 OR d.CustomerUsageOne &gt; 0)) OR
      (@MyStyle = 'B' AND (d.MyProviderTwo IS NOT NULL OR d.MyProviderTwo &gt; 0 OR d.CustomerUsageTwo &gt; 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 &gt;'' OR d.MyPhone &lt;&gt; NULL) AND
      ((CAST(FLOOR( CAST( h.ExpirationDate AS FLOAT ) )AS DATETIME) &gt; 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                     
          T1.Zip5 = T2.Zip5 AND                     
          T1.MyPhone &lt;&gt; '' AND
          T1.Id = T2.Id AND -- This will flag the batch itself
          T1.Id = @BatchId AND
          T1.Id &lt; 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 &lt;&gt; '' AND
          T1.Id = @BatchId AND
          T1.Id &lt;&gt; T2.Id -- Process against other batches
  
  
  
  --
  -- 6. Active Flag
  --  
  
  IF @MyCampaign &lt;&gt; 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 &lt;&gt; '' AND
          T1.Id = @BatchId AND
          T1.Id &lt;&gt; T2.Id AND -- Process against other batches
          H.ActiveBatch = 1 -- Only Active
  
  
  
  --
  -- 7. Delete DeleteFlag rows. Check for Id just in case
  --  
  IF @BatchId &gt; 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
  </code></pre>
  
  <p>Thanks,
  Christian</p>