November 7, 2008 at 9:26 am
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
November 7, 2008 at 12:28 pm
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
November 10, 2008 at 5:10 am
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
November 10, 2008 at 6:22 am
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
November 10, 2008 at 8:45 am
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.
November 10, 2008 at 9:13 am
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
November 10, 2008 at 9:22 am
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.
November 10, 2008 at 10:08 am
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
November 10, 2008 at 10:43 am
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
November 10, 2008 at 1:48 pm
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?
November 10, 2008 at 5:44 pm
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
Change is inevitable... Change for the better is not.
November 11, 2008 at 3:09 am
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
November 11, 2008 at 3:19 am
...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
November 11, 2008 at 4:57 am
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
Change is inevitable... Change for the better is not.
November 11, 2008 at 5:19 am
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