Stored Procedure speed versus Select Statement

  • I have an asp application which uses SQL server 2K as backend. All queries are accessed via stored procedures, the db user only has exec rights on the procedures and no select, update, insert, delete rights on the tables.

    I have some procedures which i consider simple (joining 5 tables) returning mayby 10-12 records/with 3 columns. When I run the app, and it accesses this particular procedure it takes about 8 - 10 seconds to return the data. Testing, I ran the same procedure in the SQL Analyzer and it also took about 8 -10 seconds. I then ran it using the select statement in the procedure and it returned in less then a second. To confirm, I gave the db user select rights to the tables in question then changed the app to pass a select string instead of a stored procedure and again it returned in less then a second.

    Because this occurs in both the application, and the SQL analyzer, it lends me to believe that the issue is related to SQL Server and how/if/when the stored procedure is compiled and not the app(ADODB).

    Why does it take this long to run the procedures? Is there any way to speed them up?

    Patrick

  • Hi Patrick,

    It certainly looks like you are getting different query plans (you can verify this by selecting "Show Actual Execution Plan" in the Query menu or press ctrl+K before you execute the query).

    It might be that the cached plan for this procedure was compiled with "atypical" parameters (or vice versa) so you are getting a plan that is not optimal for the parameters you are passing whereas your select is not using a cached plan (so there is no "Parameter Sniffing" going on).

    You can read more about Parameter Sniffing in Bart Duncan's blog: http://blogs.msdn.com/bartd/archive/2006/07/27/wide-vs-narrow-plans.aspx.

    You can easily verify this by doing the following (do this off-hours, it throws out all cached plans from the procedure cache):

    DBCC FREEPROCCACHE -- empty procedure cache

    go

    SET STATISTICS TIME ON

    go

    run your sproc here with SomeParams -- will put a new plan in cache

    -- note the output from SET STATISTICS IO ON

    run your sproc here with TheSameParamsAsAbove -- will use the newly cached plan

    compare the result from SET STATISTICS IO ON (http://support.microsoft.com/kb/65181 "INFO: Interpretation of SET STATISTICS TIME ON Results" explains the output).

    If this is what is happening, you have a few options. One you have already discovered yourself, some others are described in the above blog.

    If it is not this, it could be that compilation time is higher (SET STATISTICS IO will tell you that as well), there is a slight overhead for stored procedures but usually not at all to the degree you are talking about.

    Oh, and run a UPDATE STATISTICS on the tables/indexes involved (or sp_updatestats on the entire database) before you start. You should probably do this off-hours as well if your tables/indexes are large.

    HTH!

    /Elisabeth

    .

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • hi patrick,

    i totally agree with elisabeth.

    in my opinion it is no good idea to give a user permission to tables because of security problems.

    a proc creates an execution plan when it is created and then only uses this plan to be executed. so normally it is not that slow. please make sure, that you have checked if the table needs an index.

    you can alter the proc and implement the "with recompile" clause and then exec the proc once. after this, a new execution plan should be generated . then alter the procedure again, so that you don't have the with recomplie clause in your procedure. (otherwise it will always compile, which is not the effect you want to have).

    alter proc xy

    @x int

    with recompile

    as

    good luck,

    sue

    Susanne

  • Patrick Russell (11/7/2008)


    I have some procedures which i consider simple (joining 5 tables) returning mayby 10-12 records/with 3 columns. When I run the app, and it accesses this particular procedure it takes about 8 - 10 seconds to return the data. Testing, I ran the same procedure in the SQL Analyzer and it also took about 8 -10 seconds. I then ran it using the select statement in the procedure and it returned in less then a second. To confirm, I gave the db user select rights to the tables in question then changed the app to pass a select string instead of a stored procedure and again it returned in less then a second.

    Sounds like parameter sniffing. Can you post the code of the procedure?

    For some more info on parameter sniffing, see -

    http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

    http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/

    http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • First I would like to thank you all for your replies. It took me a bit to digest all of the information, and read the articles on Parameter Sniffing.

    After clearing the cache (DBCC FREEPROCCACHE ), and trying to force recompile (WITH RECOMPILE) I am not seeing any improvement. I did run the UPDATE STATISTICS first as suggested by Elizabeth, and I also tried altering the procedure to use local variables as described in the Parameter Sniffing articles suggested by Gail. Still no improvement. Below is the procedure code, and the output from STATISTICS TIME & STATISTICS IO.

    I ran both in the SQL Analyzer with Show Execution Plan, and they are very much different. You can view screen shots here. If there's a better way to output the plan in 2K please let me know.

    Patrick

    CREATE PROCEDURE dbo.GetDetailEquipTypes

    @REPORTID int = NULL,

    @BUCKETTOISSUEID int = NULL,

    @SHOWID int = NULL,

    @SITEID int

    AS

    SELECT DISTINCT ET.iId, ET.cName, C.cCategoryName

    FROM tbl_ReportIssues RI FULL OUTER JOIN

    tbl_EquipmentTypes ET INNER JOIN

    tbl_Categories C ON ET.iCategoryId = C.iCategoryId INNER JOIN

    tbl_Issues I ON ET.iCategoryId = I.iCategoryId INNER JOIN

    tbl_EquipTypeToShow ETS ON ET.iId = ETS.iEquipTypeId INNER JOIN

    tbl_BucketsToIssues BI ON I.iIssueId = BI.iIssueId LEFT OUTER JOIN

    tbl_ReportIssueDetails RID ON ETS.iEquipTypeId = RID.iEquipTypeId ON RI.iReportIssueId = RID.iReportIssueId

    AND RI.iBucketToIssueId = BI.iBucketToIssueId

    WHERE (ET.iSiteId = @SITEID) AND (ETS.iShowId = @SHOWID OR @SHOWID IS NULL)

    AND (BI.bDeleted = 0) AND (ETS.bDeleted = 0) AND (BI.iBucketToIssueId = @BUCKETTOISSUEID OR @BUCKETTOISSUEID IS NULL)

    OR (ET.iSiteId = @SITEID) AND (ETS.iShowId = @SHOWID OR @SHOWID IS NULL)

    AND (BI.iBucketToIssueId = @BUCKETTOISSUEID OR @BUCKETTOISSUEID IS NULL)

    AND (RI.iReportId = @REPORTID OR @REPORTID IS NULL)

    ORDER BY ET.cName

    GO

    Here is using local variables

    CREATE PROCEDURE dbo.GetDetailEquipTypes

    @REPORTID int = NULL,

    @BUCKETTOISSUEID int = NULL,

    @SHOWID int = NULL,

    @SITEID int

    AS

    DECLARE @locREPORTID int

    DECLARE @locBUCKETTOISSUEID int

    DECLARE @locSHOWID int

    DECLARE @locSITEID int

    SET @locREPORTID = @REPORTID

    SET @locBUCKETTOISSUEID = @BUCKETTOISSUEID

    SET @locSHOWID = @SHOWID

    SET @locSITEID= @SITEID

    SELECT DISTINCT ET.iId, ET.cName, C.cCategoryName

    FROM tbl_ReportIssues RI FULL OUTER JOIN

    tbl_EquipmentTypes ET INNER JOIN

    tbl_Categories C ON ET.iCategoryId = C.iCategoryId INNER JOIN

    tbl_Issues I ON ET.iCategoryId = I.iCategoryId INNER JOIN

    tbl_EquipTypeToShow ETS ON ET.iId = ETS.iEquipTypeId INNER JOIN

    tbl_BucketsToIssues BI ON I.iIssueId = BI.iIssueId LEFT OUTER JOIN

    tbl_ReportIssueDetails RID ON ETS.iEquipTypeId = RID.iEquipTypeId ON RI.iReportIssueId = RID.iReportIssueId

    AND RI.iBucketToIssueId = BI.iBucketToIssueId

    WHERE (ET.iSiteId = @locSITEID) AND (ETS.iShowId = @locSHOWID OR @locSHOWID IS NULL)

    AND (BI.bDeleted = 0) AND (ETS.bDeleted = 0) AND (BI.iBucketToIssueId = @locBUCKETTOISSUEID OR @locBUCKETTOISSUEID IS NULL)

    OR (ET.iSiteId = @locSITEID) AND (ETS.iShowId = @locSHOWID OR @locSHOWID IS NULL)

    AND (BI.iBucketToIssueId = @locBUCKETTOISSUEID OR @locBUCKETTOISSUEID IS NULL)

    AND (RI.iReportId = @locREPORTID OR @locREPORTID IS NULL)

    ORDER BY ET.cName

    GO

    Output from STATISTICS

    Running from stored procedure

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (9 row(s) affected)

    Table 'tbl_EquipTypeToShow'. Scan count 1, logical reads 143, physical reads 0, read-ahead reads 0.

    Table 'tbl_EquipmentTypes'. Scan count 1, logical reads 41, physical reads 0, read-ahead reads 0.

    Table 'tbl_BucketsToIssues'. Scan count 337, logical reads 1534, physical reads 0, read-ahead reads 0.

    Table 'tbl_Categories'. Scan count 337, logical reads 674, physical reads 0, read-ahead reads 0.

    Table 'tbl_Issues'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.

    Table 'tbl_ReportIssueDetails'. Scan count 1, logical reads 1965, physical reads 0, read-ahead reads 0.

    Table 'tbl_ReportIssues'. Scan count 1, logical reads 746, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 8484 ms, elapsed time = 9075 ms.

    SQL Server Execution Times:

    CPU time = 8484 ms, elapsed time = 9091 ms.

    SQL Server Execution Times:

    CPU time = 8484 ms, elapsed time = 9092 ms.

    SQL Server Execution Times:

    CPU time = 8484 ms, elapsed time = 9092 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Running from SELECT statement

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    (9 row(s) affected)

    Table 'tbl_ReportIssueDetails'. Scan count 1, logical reads 1965, physical reads 0, read-ahead reads 0.

    Table 'tbl_Issues'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.

    Table 'tbl_Categories'. Scan count 105, logical reads 210, physical reads 0, read-ahead reads 0.

    Table 'tbl_EquipmentTypes'. Scan count 105, logical reads 226, physical reads 0, read-ahead reads 0.

    Table 'tbl_EquipTypeToShow'. Scan count 1, logical reads 143, physical reads 0, read-ahead reads 0.

    Table 'tbl_BucketsToIssues'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

    Table 'tbl_ReportIssues'. Scan count 1, logical reads 730, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 85 ms, elapsed time = 85 ms.

    SQL Server Execution Times:

    CPU time = 94 ms, elapsed time = 106 ms.

    SQL Server Execution Times:

    CPU time = 94 ms, elapsed time = 106 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

  • Seeing the code makes all the difference. It's not technically parameter sniffing.

    That form of catch-all query does not perform well. Firstly the optimiser can't make accurate predictions with all of the constant comparisons scattered around. Second, there's no single optimal plan for this query. There can't be.

    It works better with variables because the optimiser uses a different method to estimate row counts. It still doesn't perform optimally

    My usual recommendations for a query like that - use dynamic SQL and only build in the where clause conditions you actually need. Use parameters and run the dynamic SQL using sp_execute_sql. The query will perform a lot better and the performance will be predictable.

    Full outer join?

    Distinct?

    Usually needing one of those indicates that the DB design is questionable or the data integrity is missing. Needing both .... ???

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (11/10/2008)


    Seeing the code makes all the difference. It's not technically parameter sniffing.

    That form of catch-all query does not perform well. Firstly the optimiser can't make accurate predictions with all of the constant comparisons scattered around. Second, there's no single optimal plan for this query. There can't be.

    It works better with variables because the optimiser doesn't try to guess row counts. It still doesn't perform optimally

    My usual recommendations for a query like that - use dynamic SQL and only build in the where clause conditions you actually need. Use parameters and run the dynamic SQL using sp_execute_sql. The query will perform a lot better and the performance will be predictable.

    Full outer join?

    Distinct?

    Usually needing one of those indicates that the DB design is questionable or the data integrity is missing. Needing both .... ???

    One other comment about using dynamic sql and sp_executesql, SQL Server can cache the execution plan and reuse it if you use sp_executesql.

  • Well that little recommendation is worth a million bucks! Appears to be as fast as running the SELECT statement. Thanks:)

    I guess I hadn't thought of that. I actually try to stay away from dynamic sql. I always thought there where sql injection issues with it. Only use it if its really necessary. This may qualify.

    Wouldn't the User still need Select permissions on the tables?

    Patrick

  • Patrick Russell (11/10/2008)


    I guess I hadn't thought of that. I actually try to stay away from dynamic sql. I always thought there where sql injection issues with it. Only use it if its really necessary. This may qualify.

    Providing you use sp_executesql properly with parameters, there's no sql injection issue here. Dynamic SQL is a use only when absolutely necessary technique, but this is one of those places where there really isn't a good alternative that performs well.

    Wouldn't the User still need Select permissions on the tables?

    Yes. Make sure it's just select and just on those tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (11/10/2008)


    Patrick Russell (11/10/2008)


    I guess I hadn't thought of that. I actually try to stay away from dynamic sql. I always thought there where sql injection issues with it. Only use it if its really necessary. This may qualify.

    Providing you use sp_executesql properly with parameters, there's no sql injection issue here. Dynamic SQL is a use only when absolutely necessary technique, but this is one of those places where there really isn't a good alternative that performs well.

    Wouldn't the User still need Select permissions on the tables?

    Yes. Make sure it's just select and just on those tables.

    Tell me if I am wrong, but wouldn't this be one of those times in SQL Server 2005 (Yes, I know this is a SQL Server 7, 2000 forum,but, have to ask) that using the EXECUTE AS and an "internal username" with select only permissions on the necessary tables would be worthwhile?

  • I've restructured the WHERE clause without changing any of the code... I think the problem sticks out like a sore thumb...

    WHERE (ET.iSiteId = @locSITEID)

    AND (ETS.iShowId = @locSHOWID OR @locSHOWID IS NULL)

    AND (BI.bDeleted = 0)

    AND (ETS.bDeleted = 0)

    AND (BI.iBucketToIssueId = @locBUCKETTOISSUEID OR @locBUCKETTOISSUEID IS NULL)

    OR

    (ET.iSiteId = @locSITEID)

    AND (ETS.iShowId = @locSHOWID OR @locSHOWID IS NULL)

    AND (BI.iBucketToIssueId = @locBUCKETTOISSUEID OR @locBUCKETTOISSUEID IS NULL)

    AND (RI.iReportId = @locREPORTID OR @locREPORTID IS NULL)

    I believe the intent of the code was actually the following...

    WHERE

    (

    (ET.iSiteId = @locSITEID)

    AND (ETS.iShowId = @locSHOWID OR @locSHOWID IS NULL)

    AND (BI.bDeleted = 0)

    AND (ETS.bDeleted = 0)

    AND (BI.iBucketToIssueId = @locBUCKETTOISSUEID OR @locBUCKETTOISSUEID IS NULL)

    )

    OR

    (

    (ET.iSiteId = @locSITEID)

    AND (ETS.iShowId = @locSHOWID OR @locSHOWID IS NULL)

    AND (BI.iBucketToIssueId = @locBUCKETTOISSUEID OR @locBUCKETTOISSUEID IS NULL)

    AND (RI.iReportId = @locREPORTID OR @locREPORTID IS NULL)

    )

    The unqualified OR was making a mess of things and is probably the reason for the need for DISTINCT as well as making the performance problems as indicated by the number of scans for several of the tables... even when it does run "fast".

    Could be wrong, though...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • to avoid select permissons to tables i always do the following with dyn sql code:

    just generate the where clause in the ui (if the tables stay the same) and call the following proc:

    create proc xy

    @where varchar(100)

    as

    declare @sql varchar(500)

    set @sql = 'select *

    from tblxy ....

    where ' + @where

    exec (@sql)

    go

    i don't know the difference between exec (@sql) and exec sp_executesql.

    @gila and lynn: the reuse of an execution plan?

    Susanne

  • ...and aonother idea after seeing jeffs code:

    why not create a view with your select statement (check distinct and joins) and

    WHERE (ET.iSiteId = @locSITEID)

    AND (ETS.iShowId = @locSHOWID OR @locSHOWID IS NULL)

    AND (BI.iBucketToIssueId = @locBUCKETTOISSUEID OR @locBUCKETTOISSUEID IS NULL)

    and then alter your proc to:

    select *

    from query

    where (BI.bDeleted = 0 AND ETS.bDeleted = 0) OR

    (RI.iReportId = @locREPORTID OR @locREPORTID IS NULL)

    Susanne

  • Be careful, Sue... having a fully dynamic WHERE clause is very suseptable to SQL Injection attacks if the code is exposed to the general public.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • thanks for your hint, Jeff.

    but isn't this better than to give users permissions on tables and generate the whole select statement dynamically as mentioned before?

    Susanne

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply