Databases
StackOverflow2013
Postsdbo
POWhy does ad-hoc SQL run faster than executing the same code in a stored procedure?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy does ad-hoc SQL run faster than executing the same code in a stored procedure?
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJNK
    1. USInternet Engineer
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy does ad-hoc SQL run faster than executing the same code in a stored procedure?
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POWhy does ad-hoc SQL run faster than executing the same code in a stored procedure?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhy does ad-hoc SQL run faster than executing the same code in a stored procedure?
      1. This table or related slice is empty.
      1. VTDownMod
    1. COShow us the code...
      singulars
      1. POWhy does ad-hoc SQL run faster than executing the same code in a stored procedure?
      1. USgbn
    2. COI think there is something terrible wrong with the stored procedure.... Can you show us the code?
      singulars
      1. POWhy does ad-hoc SQL run faster than executing the same code in a stored procedure?
      1. USdaniel.herken
    3. COIf that would be the case then why does the same code run 1000 times faster in ad hoc, using the same exact batch? I have provided the code above.
      singulars
      1. POWhy does ad-hoc SQL run faster than executing the same code in a stored procedure?
      1. USInternet Engineer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload