November 28, 2008 at 2:58 am
Hello there, i have the following code in my asp page:
SQLDetail = "SET NOCOUNT ON; SET DATEFORMAT DMY; " &_
" EXEC usp_SummaryOfCashReceiptsByCalMonth " &_
" @MonthAndYear = '" &StrMonth &"', " &_
" @CompanyID = '" &Request.form("ddl_Company") &"'"
db.CommandTimeout = "120" 'i only added this as a temporary work around for the sudden '80040e31' error
rs.Open SQLDetail, db, 1, 4
'set RS = db.Execute(SQLDetail) 'this takes just as long
Now this can take 50+ seconds to return!
However if i run the following in MSSMS it takes 9 seconds to return all rows:
SET DATEFORMAT DMY; EXEC usp_SummaryOfCashReceiptsByCalMonth @MonthAndYear = 'November 2008', @CompanyID = '1'
What on earth would cause this behaviour? It started to happen after a brief period of getting the '80040e31' error all of a sudden. (before this error it was fine, and we have restarted both the web server and the database server)
I am very stumped
PS: this is just one page of thousands on an internal intranet system and all other pages linking to that server seem to be fine.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
November 28, 2008 at 4:28 am
As a wild guess, parameter sniffing, but it's impossible to say anything for sure without seeing the code.
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 28, 2008 at 5:07 am
parameter sniffing? oooo new term for me, I had a quick read about P-Sniffing but I did not make much sense of it. The code of the SP in question is as follows:
USE [db_Cashsheets]
GO
/****** Object: StoredProcedure [dbo].[usp_SummaryOfCashReceiptsByCalMonth] Script Date: 11/28/2008 11:49:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_SummaryOfCashReceiptsByCalMonth]
@MonthAndYear varchar(25),
@CompanyID int
AS
BEGIN
SET NOCOUNT ON;
SET DATEFORMAT DMY ;
SELECT SHOPLIST.SHOPNO,
CASHSUMMARY.DATEADDEDMTHENDVAL,
ISNULL(CASHSUMMARY.SHOPNO,- 1) AS SHOPNOV2,
CASHSUMMARY.PARCOMPID,
CASHSUMMARY.RECEIPTSTOTAL,
CASHSUMMARY.SCRIPTTOTAL,
CASHSUMMARY.STANDARDSALES,
CASHSUMMARY.VAT5TOTAL,
CASHSUMMARY.VAT07TOTAL,
CASHSUMMARY.ZEROVATTOTAL,
CASHSUMMARY.VATONSTANDARDSALES,
CASHSUMMARY.STANDARDVATRATEUSED
FROM (SELECT CAST(SHOPNO AS INTEGER) AS SHOPNO
FROM SHOPS (NOLOCK )
WHERE PARCOMPID = @CompanyID) AS SHOPLIST
LEFT OUTER JOIN (SELECT *
FROM VW_CASHRECEIPTSBYCALMONTH
WHERE DATEADDEDMTHENDVAL = @MonthAndYear) AS CASHSUMMARY
ON SHOPLIST.SHOPNO = CASHSUMMARY.SHOPNO
END
GO
Does that help?
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
November 28, 2008 at 5:18 am
Hmmm......
If you run the following, does the performance improve?
sp_recompile 'usp_SummaryOfCashReceiptsByCalMonth'
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 28, 2008 at 5:22 am
p.s. Unrelated to the performance problem, but important.
Are you aware that your asp page is highly vulnerable to SQL injection?
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 28, 2008 at 5:55 am
GilaMonster (11/28/2008)
sp_recompile 'usp_SummaryOfCashReceiptsByCalMonth'
Had already tried that, made no major difference i'm afraid, i have just tried it again and it had no effect either.
GilaMonster (11/28/2008)
p.s. Unrelated to the performance problem, but important.Are you aware that your asp page is highly vulnerable to SQL injection?
hehe, knew that would come up, the request.form element in my example above is actually passed through a regexp that double quotes, removed key words etc, the results of which are used in the query, just changed it to try and keep my post small.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
November 28, 2008 at 6:21 am
jordonpilling (11/28/2008)
Had already tried that, made no major difference i'm afraid, i have just tried it again and it had no effect either.
Very odd. So it's not parameter sniffing then...
Can you run SQL profiler and then run that from the ASP page and see what the reads, writes, cpu and duration are. Run it from query analyser and check the same. See if there are major differences.
Check sysprocesses while the query is running from the ASP page, see if there's a wait type (last wait type I think is the column). If so, what is it?
hehe, knew that would come up, the request.form element in my example above is actually passed through a regexp that double quotes, removed key words etc, the results of which are used in the query, just changed it to try and keep my post small.
Ok. Better.
It's still vulnerable though. Keyword filters aren't perfect and I've seen very creative attacks that get around them. May I suggest using ADO parameters to pass the parameters to the proc? That way you are completely safe no matter what tricks are played.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply