SQL Server Performance

Code Runs Differently in QA and Stored P

Discussion in 'T-SQL Performance Tuning for Developers' started by Gambrinus, Feb 10, 2003.

  1. Gambrinus New Member

    Hi,

    First time poster and still very new to all of thsi -- I hope this is clear. My apologies if it isn't

    Without sending all the specific code, my boss has encountered this problem.

    He has some code that when run right in a Query Analyzer window explicitly assigning values to variables, takes 5 seconds to run. But when put the same code is used to create a stored procedure and then run using sp_exec (values passed in to parameters), it takes 11 seconds to run.

    We looked at the execution plan and it is different for each. One specific difference that I noticed is that when it is run as a stored procedure, it is using a table scan on the main table (85%) but when the same code is run in a QA window, it uses bookmarks.

    To be used for what he needs to use it for (query used for our web site), it obviously needs to be in the form of a stored procedure, but the performace is vastly worse that way.

    My question is, why would the execution plan differ between the two, and what suggetions would you have for getting around this problem?

    Thanks much,
    Dave
  2. bradmcgehee New Member

    Some questions, is the test from within QA on the server or on a remote machine? When you run the stored procedure, how many time have you run it, one after another? How often do you rebuild the statistics? Are your results 100% consistent, run after run?

    Generally, when you see a different execution plan for the same query, and assuming that you are using the exact same database, the excution plans should be identical. The only reason I know that this may not be the case is if the index statistics have changed between runs of the query, or the data is different within the query. Are either of these possible?

    Also, when testing, both for the query in QA and with the SP, consider the advice in this tip from my website:


    When experimenting with the tuning of your SQL Server, you may want to run the DBCC DROPCLEANBUFFERS command to remove all the test data from SQL Server's data cache (buffer) between tests to ensure fair testing. Keep in mind that this command only removes clean buffers, not dirty buffers. Because of this, before running the DBCC DROPCLEANBUFFERS command, you may first want to run the CHECKPOINT command first. Running CHECKPOINT will write all dirty buffers to disk. And then when you run DBCC DROPCLEANBUFFERS, you can be assured that all data buffers are cleaned out, not just the clean ones.

    If you want to clear out all of the stored procedure cache, use this command, DBCC FREEPROCCACHE. If you want to only clear out the stored procedure cache for a single database (not the entire server) use DBCC FLUSHPROCINDB. All of these commands are for testing purposes and should not be run on a production SQL Server. [7.0, 2000]







    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. Gambrinus New Member

    Hi Brad,

    Thanks for responding...

    To answer your questions: The test from within QA is being run from a client and not on the Server itself. I asked my boss about the times he's run the select and procedure and he has run both of them many times with consistent results.

    My boss had mentioned late yesterday that he had updated the statistics to see if that would help, and had gotten different, but still consistent results after that time. I asked him for specifics this morning and this was his reply:

    I ran the QA Select numerous times and the results were returned in 3 seconds.
    I ran the QA Select numerous times as an exec sp_* and the results were returned in 11 seconds.
    I ran Update_Statistics.
    I ran the QA Select numerous times and the results were returned in 3 seconds.
    I ran the QA Select numerous times as an exec sp_* and the results were returned in 11 seconds.
    They have run consistently like this since then.


  4. bradmcgehee New Member

    There is one other option to consider, although its a long shot. Sometimes the versions of MDAC used make a difference. Perhaps there are different versions of MDAC on the server and client. You can go to www.microsoft.com/data to get the MDAC checker to find out what versions are being used. You can also download the latest MDAC versions.

    Other than this, I am a little mystified about the results you report. Could you post the query to see if we see anything obvious?

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  5. When is the execution plan for a select query in a stored proc generated? Is it when the stored proc is created or is it when the select statement is executed.

    I have a similar example (abstracted from a larger example to make my point).

    create procedure test
    @p1 int,
    @p2 int
    begin
    ...
    select ....
    from largetable
    where c1 = @p1
    and c2 = @p2
    ....
    end

    When @p1 is passed certain values, I want SQL server to use the index on c1 because of better selectivity and for other values I want it to use the index on c2 because of better selectivity on c2. But I dont see this happening in practice. Looks like SQL server picks one index to use in the query plan and sticks to it. Is there anyway I can cajole SQL server to use the right index.

    Obviously, if I just execute the select statement from QA then it always picks the right index.

    Thanks in advance for any insights ...
  6. Argyle New Member

    Note that the following query in QA

    ---------------------
    DECLARE @p1 INT
    DECLARE @p2 INT

    SELECT @p1 = 8
    SELECT @p2 = 5

    SELECT * FROM MyTable WHERE column1 = @p1 AND column2 = @p2
    ---------------------

    Is different from this one in QA

    ---------------------
    SELECT * FROM MyTable WHERE column1 = 8 AND column2 = 5
    ---------------------

    When you hard code the values like in the second example it is much easier for SQL to find the correct index. In the first example with @p1 and @p2, SQL Server has to rely on the index statistics.

    /Argyle
  7. Gambrinus New Member

    Hi,<br /><br />I apologize for the size of the query, Brad, but I didn't want to post just part of it and leave out something that might be meaningful to you. (Incidentally, I'm sorry if this comes through with no indentation -- it had it when I copied it in and shows it now even when in the Edit screen, but when I first posted, there was none and I'm not sure how to get it in there.) Regarding your point, Argyle, when running the code from QA, my boss is declaring variables and then assignging values and not just hard-coding them in the selects, so I think it resembles how the stored proc works in that respect. The code he's running from QA is as follows (the stored procedure is exactly the same as this code, except that it has the Create Procedure code at the top and is in the DB):<br /><br />declare @Assoc_id varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />declare @Affil_id varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />declare @Pat_Name varchar(30)<br />declare @Pat_ssn varchar(20)<br />declare @Form_Type varchar(4)<br />declare @Serv_From datetime<br />declare @Pat_DOB datetime<br />declare @Claim_id varchar(12)<br /><br />--Fifth Claim -- Testing Super Accounts<br />set @Affil_id='03248'<br />set @Assoc_id=NULL<br />--set @Affil_id=NULL<br />--set @Assoc_id='97953'<br />set @Pat_Name='Duck Donald'<br />set @Pat_ssn=null<br />--set @Pat_ssn=''<br />set @Form_Type='HCFA'<br />set @Serv_From='06/13/2002'<br />set @Pat_DOB='07/01/1948'<br />--set @Claim_id='XXXXXXXXX'<br />set @Claim_id=null<br /><br /><br />set nocount on<br />set ansi_warnings off<br /><br /><br />declare @FT char(1)<br />IF UPPER(@Form_Type)='UB92'<br /> BEGIN<br /> set @FT='H'<br /> END<br />ELSE<br /> BEGIN<br /> IF UPPER(@Form_Type)='HCFA'<br /> BEGIN<br /> set @FT='D'<br /> END<br /> END<br /><br />create table #sonnyx (ProviderId varchar(10),<br /> AssocAffilId varchar(10),<br /> ProviderName varchar(70))<br /><br />IF @Assoc_id is not null<br /> BEGIN<br /> Insert Into #sonnyx (ProviderId,<br /> AssocAffilId,<br /> ProviderName)<br /> Select p.Providerid,<br /> p.Associationid,<br /> (Select CASE<br /> WHEN p.Rectype='D'<br /> THEN p.Docname<br /> WHEN p.Rectype='H'<br /> THEN p.Org_name<br /> END) as Providername<br /> From Provider p<br /> Where p.AssociationId=@Assoc_id<br /> and p.RecType=@FT<br /> END<br /><br />create table #sonny (Relationship_id varchar(10),<br /> Claim_id int,<br /> MedCost_Claim_Id char(9),<br /> Form_Type char(4),<br /> Claim_stat char(1),<br /> Service_From datetime,<br /> Service_To datetime,<br /> Admit_Date datetime,<br /> Discharge_Date datetime,<br /> Re_Priced_Date datetime,<br /> Total_Billed_Amount money,<br /> Total_User_Allowed_Amount money,<br /> Claims_Admin_Name varchar(40),<br /> Eligibility_Phone varchar(15),<br /> Pat_Name varchar(30),<br /> Pat_DOB datetime,<br /> Pat_SSN varchar(20),<br /> Pat_id varchar(20),<br /> Fsn_code varchar(200),<br /> Details_Indicator char(1),<br /> Provider_Name varchar(70))<br /><br /><br />IF ISNULL(@Claim_id,'')&lt;&gt;''<br /> BEGIN<br /> IF @Affil_id is not null<br /> BEGIN<br /> insert into #sonny (Relationship_id,<br /> Claim_id,<br /> MedCost_Claim_Id,<br /> Form_Type,<br /> Claim_stat,<br /> Service_From,<br /> Service_To,<br /> Admit_Date,<br /> Discharge_Date,<br /> Re_Priced_Date,<br /> Total_Billed_Amount,<br /> Total_User_Allowed_Amount,<br /> Claims_Admin_Name,<br /> Eligibility_Phone,<br /> Pat_Name,<br /> Pat_DOB,<br /> Pat_SSN,<br /> Pat_id,<br /> Fsn_code,<br /> Details_Indicator,<br /> Provider_Name)<br /> Select ch.Providerid,<br /> ch.Claim_id,<br /> ch.Mc_Claimid as MedCost_Claim_id,<br /> ch.Form_Type as Form_Type,<br /> ch.Claim_stat,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_From<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Admit<br /> END) as Serv_From,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_To<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Discharge<br /> END) as Serv_To,<br /> ch.Admit,<br /> ch.Discharge,<br /> ch.Re_Priced_Date,<br /> ch.Total_Billed_Amount,<br /> ch.Total_User_Allowed_Amount,<br /> ch.Claims_Admin_Name,<br /> ch.Eligibility_Phone,<br /> ch.Pat_Name,<br /> ch.Pat_DOB,<br /> ch.Pat_ssn,<br /> ch.Pat_id,<br /> ch.FsnCode,<br /> ch.Detail_Indicator as Details_Indicator,<br /> (Select CASE<br /> WHEN Dpm.Rectype='D'<br /> THEN Dpm.Docname<br /> WHEN Dpm.Rectype='H'<br /> THEN Dpm.Org_name<br /> END) as Providername<br /> FROM Claim_Header ch<br /> LEFT JOIN Provider Dpm<br /> ON ch.Providerid=Dpm.Providerid<br /> WHERE ch.Form_Type in ('HCFA','UB92')<br /> and ch.mc_claimid=@Claim_id<br /> and ch.claim_stat in ('C','D','F','P')<br /> and ch.AffiliationId=@Affil_Id<br /> ORDER BY ch.Claim_stat<br /> END<br /> ELSE<br /> BEGIN<br /> insert into #sonny (Relationship_id,<br /> Claim_id,<br /> MedCost_Claim_Id,<br /> Form_Type,<br /> Claim_stat,<br /> Service_From,<br /> Service_To,<br /> Admit_Date,<br /> Discharge_Date,<br /> Re_Priced_Date,<br /> Total_Billed_Amount,<br /> Total_User_Allowed_Amount,<br /> Claims_Admin_Name,<br /> Eligibility_Phone,<br /> Pat_Name,<br /> Pat_DOB,<br /> Pat_SSN,<br /> Pat_id,<br /> Fsn_code,<br /> Details_Indicator,<br /> Provider_Name)<br /> Select ch.Providerid,<br /> ch.Claim_id,<br /> ch.Mc_Claimid as MedCost_Claim_id,<br /> ch.Form_Type as Form_Type,<br /> ch.Claim_stat,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_From<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Admit<br /> END) as Serv_From,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_To<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Discharge<br /> END) as Serv_To,<br /> ch.Admit,<br /> ch.Discharge,<br /> ch.Re_Priced_Date,<br /> ch.Total_Billed_Amount,<br /> ch.Total_User_Allowed_Amount,<br /> ch.Claims_Admin_Name,<br /> ch.Eligibility_Phone,<br /> ch.Pat_Name,<br /> ch.Pat_DOB,<br /> ch.Pat_ssn,<br /> ch.Pat_id,<br /> ch.FsnCode,<br /> ch.Detail_Indicator as Details_Indicator,<br /> Dpm.ProviderName<br /> FROM Claim_Header ch<br /> INNER JOIN #sonnyx Dpm<br /> ON Ch.Providerid=Dpm.Providerid<br /> WHERE ch.Form_Type=@Form_Type<br /> and ch.mc_claimid=@Claim_id<br /> and ch.claim_stat in ('C','D','F','P')<br /> ORDER BY ch.Claim_stat<br /> END <br /><br /> Select * from #sonny<br /><br /> END<br />ELSE<br /> BEGIN<br /> IF ISNULL(@Pat_ssn,'')=''<br /> BEGIN<br /> set @Pat_name=Rtrim(@Pat_name)+'%'<br /> IF @Affil_id is not null<br /> BEGIN<br /> --print CONVERT (varchar(30), getdate(), 20)<br /> insert into #sonny (Relationship_id,<br /> Claim_id,<br /> MedCost_Claim_Id,<br /> Form_Type,<br /> Claim_stat,<br /> Service_From,<br /> Service_To,<br /> Admit_Date,<br /> Discharge_Date,<br /> Re_Priced_Date,<br /> Total_Billed_Amount,<br /> Total_User_Allowed_Amount,<br /> Claims_Admin_Name,<br /> Eligibility_Phone,<br /> Pat_Name,<br /> Pat_DOB,<br /> Pat_SSN,<br /> Pat_id,<br /> Fsn_code,<br /> Details_Indicator,<br /> Provider_Name)<br /> Select ch.Providerid,<br /> ch.Claim_id,<br /> ch.Mc_Claimid,<br /> ch.Form_Type,<br /> ch.Claim_stat,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_From<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Admit<br /> END) as Serv_From,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_To<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Discharge<br /> END) as Serv_To,<br /> ch.Admit,<br /> ch.Discharge,<br /> ch.Re_Priced_Date,<br /> ch.Total_Billed_Amount,<br /> ch.Total_User_Allowed_Amount,<br /> ch.Claims_Admin_Name,<br /> ch.Eligibility_Phone,<br /> ch.Pat_Name,<br /> ch.Pat_DOB,<br /> ch.Pat_ssn,<br /> ch.Pat_id,<br /> ch.Fsncode as Fsn_Code,<br /> ch.Detail_Indicator as Details_Indicator,<br /> (Select CASE<br /> WHEN Dpm.Rectype='D'<br /> THEN Dpm.Docname<br /> WHEN Dpm.Rectype='H'<br /> THEN Dpm.Org_name<br /> END) as Providername<br /> FROM Claim_Header ch<br /> LEFT JOIN Provider Dpm<br /> ON ch.Providerid=Dpm.Providerid<br /> WHERE ch.AffiliationId=@Affil_id<br /> and ch.Form_Type in ('HCFA','UB92')<br /> and (ch.Admit=@Serv_From<br /> or<br /> ch.Serv_From=@Serv_From)<br /> and ch.Pat_Dob=@Pat_DOB<br /> and ch.Pat_Name like UPPER(@Pat_Name)<br /> and ch.claim_stat in ('C','D','F','P')<br /> ORDER BY ch.Claim_stat<br /> --print CONVERT (varchar(30), getdate(), 20)<br /> END<br /> ELSE<br /> BEGIN<br /> insert into #sonny (Relationship_id,<br /> Claim_id,<br /> MedCost_Claim_Id,<br /> Form_Type,<br /> Claim_stat,<br /> Service_From,<br /> Service_To,<br /> Admit_Date,<br /> Discharge_Date,<br /> Re_Priced_Date,<br /> Total_Billed_Amount,<br /> Total_User_Allowed_Amount,<br /> Claims_Admin_Name,<br /> Eligibility_Phone,<br /> Pat_Name,<br /> Pat_DOB,<br /> Pat_SSN,<br /> Pat_id,<br /> Fsn_code,<br /> Details_Indicator,<br /> Provider_Name)<br /> Select ch.Providerid,<br /> ch.Claim_id,<br /> ch.Mc_Claimid,<br /> ch.Form_Type,<br /> ch.Claim_stat,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_From<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Admit<br /> END) as Serv_From,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_To<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Discharge<br /> END) as Serv_To,<br /> ch.Admit,<br /> ch.Discharge,<br /> ch.Re_Priced_Date,<br /> ch.Total_Billed_Amount,<br /> ch.Total_User_Allowed_Amount,<br /> ch.Claims_Admin_Name,<br /> ch.Eligibility_Phone,<br /> ch.Pat_Name,<br /> ch.Pat_DOB,<br /> ch.Pat_ssn,<br /> ch.Pat_id,<br /> ch.Fsncode as Fsn_Code,<br /> ch.Detail_Indicator as Details_Indicator,<br /> Dpm.ProviderName<br /> FROM Claim_Header ch<br /> INNER JOIN #sonnyx Dpm<br /> ON ch.Providerid=Dpm.Providerid<br /> WHERE ch.Form_Type=@Form_Type<br /> and (ch.Admit=@Serv_From<br /> or<br /> ch.Serv_From=@Serv_From)<br /> and ch.Pat_Dob=@Pat_DOB<br /> and ch.Pat_Name like UPPER(@Pat_Name)<br /> and ch.claim_stat in ('C','D','F','P')<br /> ORDER BY ch.Claim_stat<br /> END<br /><br /> Select * from #sonny order by Claim_stat<br /> END<br /> ELSE<br /> BEGIN<br /> IF ISNULL(@Pat_ssn,'')&lt;&gt;''<br /> BEGIN<br /> IF @Affil_id is not null<br /> BEGIN<br /> insert into #sonny (Relationship_id,<br /> Claim_id,<br /> MedCost_Claim_Id,<br /> Form_Type,<br /> Claim_stat,<br /> Service_From,<br /> Service_To,<br /> Admit_Date,<br /> Discharge_Date,<br /> Re_Priced_Date,<br /> Total_Billed_Amount,<br /> Total_User_Allowed_Amount,<br /> Claims_Admin_Name,<br /> Eligibility_Phone,<br /> Pat_Name,<br /> Pat_DOB,<br /> Pat_SSN,<br /> Pat_id,<br /> Fsn_code,<br /> Details_Indicator,<br /> Provider_Name)<br /> Select ch.Providerid,<br /> ch.Claim_id,<br /> ch.Mc_Claimid,<br /> ch.Form_Type,<br /> ch.Claim_stat,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_From<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Admit<br /> END) as Serv_From,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_To<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Discharge<br /> END) as Serv_To,<br /> ch.Admit,<br /> ch.Discharge,<br /> ch.Re_Priced_Date,<br /> ch.Total_Billed_Amount,<br /> ch.Total_User_Allowed_Amount,<br /> ch.Claims_Admin_Name,<br /> ch.Eligibility_Phone,<br /> ch.Pat_Name,<br /> ch.Pat_DOB,<br /> ch.Pat_ssn,<br /> ch.Pat_id,<br /> ch.fsncode as Fsn_Code,<br /> ch.Detail_Indicator as Details_Indicator,<br /> (Select CASE<br /> WHEN Dpm.Rectype='D'<br /> THEN Dpm.Docname<br /> WHEN Dpm.Rectype='H'<br /> THEN Dpm.Org_name<br /> END) as Providername<br /> FROM Claim_Header ch<br /> LEFT JOIN Provider Dpm<br /> ON ch.Providerid=Dpm.Providerid<br /> WHERE ch.Affiliationid=@Affil_id<br /> and ch.Form_Type in ('HCFA','UB92')<br /> and (ch.Admit=@Serv_From<br /> or<br /> ch.Serv_From=@Serv_From)<br /> and ch.Pat_ssn=@Pat_ssn<br /> and ch.Pat_Dob=@Pat_Dob<br /> and ch.claim_stat in ('C','D','F','P')<br /> ORDER BY ch.Claim_stat<br /> END<br /> ELSE<br /> BEGIN<br /> insert into #sonny (Relationship_id,<br /> Claim_id,<br /> MedCost_Claim_Id,<br /> Form_Type,<br /> Claim_stat,<br /> Service_From,<br /> Service_To,<br /> Admit_Date,<br /> Discharge_Date,<br /> Re_Priced_Date,<br /> Total_Billed_Amount,<br /> Total_User_Allowed_Amount,<br /> Claims_Admin_Name,<br /> Eligibility_Phone,<br /> Pat_Name,<br /> Pat_DOB,<br /> Pat_SSN,<br /> Pat_id,<br /> Fsn_code,<br /> Details_Indicator,<br /> Provider_Name)<br /> Select ch.Providerid,<br /> ch.Claim_id,<br /> ch.Mc_Claimid,<br /> ch.Form_Type,<br /> ch.Claim_stat,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_From<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Admit<br /> END) as Serv_From,<br /> (Select CASE<br /> WHEN @Form_Type='HCFA'<br /> THEN ch.Serv_To<br /> WHEN @Form_Type='UB92'<br /> THEN ch.Discharge<br /> END) as Serv_To,<br /> ch.Admit,<br /> ch.Discharge,<br /> ch.Re_Priced_Date,<br /> ch.Total_Billed_Amount,<br /> ch.Total_User_Allowed_Amount,<br /> ch.Claims_Admin_Name,<br /> ch.Eligibility_Phone,<br /> ch.Pat_Name,<br /> ch.Pat_DOB,<br /> ch.Pat_ssn,<br /> ch.Pat_id,<br /> ch.fsncode as Fsn_Code,<br /> ch.Detail_Indicator as Details_Indicator,<br /> Dpm.ProviderName<br /> FROM Claim_Header ch<br /> INNER JOIN #sonnyx Dpm<br /> ON ch.Providerid=Dpm.Providerid<br /> WHERE ch.Form_Type=@Form_Type<br /> and (ch.Admit=@Serv_From<br /> or<br /> ch.Serv_From=@Serv_From)<br /> and ch.Pat_ssn=@Pat_ssn<br /> and ch.Pat_Dob=@Pat_Dob<br /> and ch.claim_stat in ('C','D','F','P')<br /> ORDER BY ch.Claim_stat<br /> END<br /><br /> Select * From #sonny order by Claim_stat<br /> END<br /> END<br /> END<br /><br />drop table #sonny<br />drop table #sonnyx<br /><br /> <br />Incidentally, I just ran it myself both from QA and as a stored proc and got the same results that he got after updating statistics -- 3 seconds from QA and 11 seconds when running the stored proc.<br /><br />Thanks again, everybody, for your replies.<br /><br />Dave
  8. royv New Member

    Out of curiosity, what version of SQL Server are using? I guess if push comes to shove, you can use an optimizer hint so the code will be forced to generate/use the appropriate execution plan.


    "How do you expect to beat me when I am forever?"
  9. Gambrinus New Member

    We're using 7.0

Share This Page