November 4, 2011 at 5:43 am
Hi All,
The following stored proc is used to return the results in one of my report.
But the report fails becasue of a time out error.
Is there any way I can alter this stoed proc in order to resolve the error on the report?
CREATE PROCEDURE [dbo].[rsp_Emails_Sent]
(
@pCurrentUserIDINT,
@pEmailIDINT=NULL,
@pEmailAddressVARCHAR(250)= NULL,
@pSentDateFromVARCHAR(10)= NULL,
@pSentDateToVARCHAR(10)= NULL,
@pSubjectVARCHAR(256)= NULL,
@pBodyTextVARCHAR(MAX)= NULL,
@pContactFirstNameVARCHAR(256)= NULL,
@pContactLastNameVARCHAR(256)= NULL,
@pOrganisationNameVARCHAR(60)= NULL,
@pUserNameVARCHAR(250)= NULL
)
AS
BEGIN
OPEN SYMMETRIC KEY [RS_SharedKey]
DECRYPTION BY CERTIFICATE RS_KeyProtection;-- required to display encrypted details from table Email
DECLARE @pEffectiveDate smalldatetime
-- Set to today's date if not specified
SET @pEffectiveDate = ISNULL(@pEffectiveDate, dbo.udf_TodaysDate());
SET @pBodyText = REPLACE(@pBodyText,' ','%');
SELECTE.EmailID,
CONVERT(VARCHAR(250), DecryptByKey(E.EmailAddressEncrypted)) AS 'EmailAddress',
DateCreated,
CONVERT(VARCHAR(250), DecryptByKey(SubjectEncrypted)) AS 'Subject',
CONVERT(VARCHAR(MAX), DecryptByKey(CD.TitleEncrypted)) + ' ' +
CONVERT(VARCHAR(MAX), DecryptByKey(CD.FirstNameEncrypted)) + ' ' +
CONVERT(VARCHAR(MAX), DecryptByKey(CD.InitialsEncrypted)) + ' ' +
CONVERT(VARCHAR(MAX), DecryptByKey(CD.LastNameEncrypted)) AS 'ContactName',
OD.OrganisationName,
U.UserName,
@pSentDateFrom AS DateSentFrom,
@pSentDateTo AS DateSentTo,
serverproperty('MachineName') AS ServerName
FROMEmail AS E
LEFT OUTER JOINUserContact AS UC
ONE.UserID = UC.UserID
LEFT OUTER JOINContactDetail AS CD
ONUC.ContactID = CD.ContactID
LEFT OUTER JOINOrganisationDetail AS OD
ONE.OrganisationID = OD.OrganisationID
LEFT OUTER JOIN[User] AS U
ONE.UserID = U.UserID
--set up filters: where parameters are specified (i.e. not null) LIKE is used to return data containing the parameter
WHERE(E.EmailID = @pEmailID OR @pEmailID IS NULL)
AND(CONVERT(VARCHAR(250), DecryptByKey(E.EmailAddressEncrypted)) LIKE '%%' + @pEmailAddress + '%%' OR @pEmailAddress IS NULL)
--for sent date, there are three possible clauses depending on which parameters are specified:
--date range between the two specified dates
AND((DateCreated >= dbo.udf_VarcharToDate(@pSentDateFrom) AND DateCreated <= dbo.udf_VarcharToDate(@pSentDateTo)+ ' 23:59:00.000') OR (@pSentDateFrom IS NULL AND @pSentDateTo IS NULL)
--up to specified date
OR (DateCreated <= dbo.udf_VarcharToDate(@pSentDateTo)+' 23:59:00.000' AND @pSentDateFrom IS NULL)
--from specified date
OR (DateCreated >= dbo.udf_VarcharToDate(@pSentDateFrom)+' 23:59:00.000' AND @pSentDateTo IS NULL))
AND(CONVERT(VARCHAR(MAX), DecryptByKey(SubjectEncrypted)) LIKE '%%' + @pSubject + '%%' OR @pSubject IS NULL)
AND(CONVERT(VARCHAR(MAX), DecryptByKey(SubjectEncrypted)) NOT LIKE '%%Password%%')
AND(CONVERT(VARCHAR(MAX), DecryptByKey(BodyTextEncrypted)) LIKE '%%' + @pBodyText + '%%' OR @pBodyText IS NULL)
AND(CONVERT(VARCHAR(MAX), DecryptByKey(CD.FirstNameEncrypted)) LIKE '%%' + @pContactFirstName + '%%' OR @pContactFirstName IS NULL)
AND(CONVERT(VARCHAR(MAX), DecryptByKey(CD.LastNameEncrypted)) LIKE '%%' + @pContactLastName + '%%' OR @pContactLastName IS NULL)
AND(OD.OrganisationName LIKE '%%' + @pOrganisationName + '%%' OR (@pOrganisationName IS NULL))
AND(U.UserName LIKE '%%' + @pUserName + '%%' OR (@pUserName IS NULL))
AND(OD.StartDate <= @pEffectiveDate OR OD.StartDate IS NULL)
AND(OD.EndDate > @pEffectiveDate OR OD.EndDate IS NULL)
AND(CD.StartDate <= @pEffectiveDate OR CD.StartDate IS NULL)
AND(CD.EndDate > @pEffectiveDate OR CD.EndDate IS NULL)
AND(U.StartDate <= @pEffectiveDate OR U.StartDate IS NULL)
AND(U.EndDate > @pEffectiveDate OR U.EndDate IS NULL)
ORDER BY DateCreated
CLOSE SYMMETRIC KEY [RS_SharedKey]
END
Any help is really appreciated.
Thanks in advance.
November 4, 2011 at 7:34 am
This query is full of performance (scalability) issues. The where clause requires the decryption of virtually every encrypted column in your table then serached with "like '% something %' " which is very resource intensive. It does not help having all of the OR clauses in there as well.
For small amounts of data you can get away with this sort of thing but when your table gets huge this will not fly. If you need to do this sort of thing regularly you might try using a combination of masked columns and/or hashes that can be indexed or effectively searched without having to be decrypted first.
There are other ways to do this also but you have to be creative here if you want good performance on large tables having data like this.
The probability of survival is inversely proportional to the angle of arrival.
November 4, 2011 at 7:39 am
WOW I can see why that times out. Are you familiar with SARGability? You should google "sql server sargable" and spend some time researching that topic. You have so much stuff in this query that kills any hope of anything other table scans on all the tables.
Some fairly easy targets to get rid of, drop the function of dbo.udf_VarcharToDate and pass in a datetime. I count 4-5 times that is called in your where clause. If you can't drop the function then at least create a datetime variable and populate it with the function before this big select. That way you only have call it once.
Your where statement it totally full of "like" comparisons. Like means the engine has to look at every single row, meaning more table scans. Not that it affects performance but a single '%' is sufficient, there is no need to double them.
Just curious, how long does this procedure take to run in SSMS?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2011 at 8:07 am
Thanks both for you suggestions.
I'll research on sargable functions and will try to optimise the query.
It took 90 secs to execute the query on ssms.
November 4, 2011 at 8:29 am
90 seconds is an awful long time for a single query. That is a very clear indication that your execution plan is scanning over and over. As a short term "band-aid" you can probably set the connection time out to 2 minutes or something for your report. This really is a bandaid and NOT a solution. You need to find a way to get the query execution time a lot less.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2011 at 8:45 am
This might yield a little improvement:
FROM Email AS E
LEFT OUTER JOIN UserContact AS UC
ON E.UserID = UC.UserID
LEFT OUTER JOIN ContactDetail AS CD
ON UC.ContactID = CD.ContactID
AND CD.StartDate <= @pEffectiveDate
AND CD.EndDate > @pEffectiveDate
LEFT OUTER JOIN OrganisationDetail AS OD
ON E.OrganisationID = OD.OrganisationID
AND OD.StartDate <= @pEffectiveDate
AND OD.EndDate > @pEffectiveDate
LEFT OUTER JOIN [User] AS U
ON E.UserID = U.UserID
AND U.StartDate <= @pEffectiveDate
AND U.EndDate > @pEffectiveDate
--set up filters: where parameters are specified (i.e. not null) LIKE is used to return data containing the parameter
WHERE (E.EmailID = @pEmailID OR @pEmailID IS NULL)
AND (CONVERT(VARCHAR(250), DecryptByKey(E.EmailAddressEncrypted)) LIKE '%%' + @pEmailAddress + '%%' OR @pEmailAddress IS NULL)
--for sent date, there are three possible clauses depending on which parameters are specified:
-- date range between the two specified dates
AND ((DateCreated >= dbo.udf_VarcharToDate(@pSentDateFrom) AND DateCreated <= dbo.udf_VarcharToDate(@pSentDateTo)+ ' 23:59:00.000') OR (@pSentDateFrom IS NULL AND @pSentDateTo IS NULL)
-- up to specified date
OR (DateCreated <= dbo.udf_VarcharToDate(@pSentDateTo)+' 23:59:00.000' AND @pSentDateFrom IS NULL)
-- from specified date
OR (DateCreated >= dbo.udf_VarcharToDate(@pSentDateFrom)+' 23:59:00.000' AND @pSentDateTo IS NULL))
AND (CONVERT(VARCHAR(MAX), DecryptByKey(SubjectEncrypted)) LIKE '%%' + @pSubject + '%%' OR @pSubject IS NULL)
AND (CONVERT(VARCHAR(MAX), DecryptByKey(SubjectEncrypted)) NOT LIKE '%%Password%%')
AND (CONVERT(VARCHAR(MAX), DecryptByKey(BodyTextEncrypted)) LIKE '%%' + @pBodyText + '%%' OR @pBodyText IS NULL)
AND (CONVERT(VARCHAR(MAX), DecryptByKey(CD.FirstNameEncrypted)) LIKE '%%' + @pContactFirstName + '%%' OR @pContactFirstName IS NULL)
AND (CONVERT(VARCHAR(MAX), DecryptByKey(CD.LastNameEncrypted)) LIKE '%%' + @pContactLastName + '%%' OR @pContactLastName IS NULL)
AND (OD.OrganisationName LIKE '%%' + @pOrganisationName + '%%' OR (@pOrganisationName IS NULL))
AND (U.UserName LIKE '%%' + @pUserName + '%%' OR (@pUserName IS NULL))
--AND (OD.StartDate <= @pEffectiveDate OR OD.StartDate IS NULL)
--AND (OD.EndDate > @pEffectiveDate OR OD.EndDate IS NULL)
--AND (CD.StartDate <= @pEffectiveDate OR CD.StartDate IS NULL)
--AND (CD.EndDate > @pEffectiveDate OR CD.EndDate IS NULL)
--AND (U.StartDate <= @pEffectiveDate OR U.StartDate IS NULL)
--AND (U.EndDate > @pEffectiveDate OR U.EndDate IS NULL)
With all those optional parameters, you're likely to get a performance improvement by recompiling each time the procedure is run (see WITH RECOMPILE in BOL).
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 4, 2011 at 5:34 pm
Heh... I've seen people do this sort of thing before... decrypt the columns that are being joined on. Bloody expensive and usually not necessary at all. It's much cheaper to encrypt the parameters and compare against the encrypted columns than it is to decrypt the columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply