Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Stored Procedure speed versus Select Statement Expand / Collapse
Author
Message
Posted Friday, November 7, 2008 9:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 20, 2008 7:24 AM
Points: 5, Visits: 120
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
Post #599065
Posted Friday, November 7, 2008 12:28 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:13 AM
Points: 175, Visits: 746
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
Post #599232
Posted Monday, November 10, 2008 5:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 22, 2013 4:06 AM
Points: 54, Visits: 64
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
Post #599769
Posted Monday, November 10, 2008 6:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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

Post #599811
Posted Monday, November 10, 2008 8:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 20, 2008 7:24 AM
Points: 5, Visits: 120
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.

Post #599927
Posted Monday, November 10, 2008 9:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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

Post #599951
Posted Monday, November 10, 2008 9:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 23,400, Visits: 32,256
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #599956
Posted Monday, November 10, 2008 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 20, 2008 7:24 AM
Points: 5, Visits: 120
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
Post #599998
Posted Monday, November 10, 2008 10:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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

Post #600038
Posted Monday, November 10, 2008 1:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 23,400, Visits: 32,256
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?




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #600186
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse