January 8, 2010 at 1:43 pm
I have used this Stored Procedure for a few months, without any issues. I recently added the Date validation and it sometimes times out.
Have i incorrectly use my brackets or is it something else ?
Thanks
USE [microsoft525]
GO
/****** Object: StoredProcedure [dbo].[usp_getEmails_New01042010] Script Date: 01/08/2010 15:31:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_getEmails_New01042010]
(
@strSearchParams varchar(1000),
@strFromDate varchar(50),
@strToDate varchar(50),
@strMailFilter varchar(200),
@pageNumber int,
@pageSize int,
@ShowCount bit
)
AS
BEGIN
----(1) search by Params only
IF (@strSearchParams <> '') AND (@strMailFilter = '')
BEGIN
IF @ShowCount = 1
BEGIN
SELECT COUNT(DISTINCT(Email)) FROM(
SELECT Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE ((contains (originalresume, @strSearchParams)) AND (currentdateout BETWEEN @strFromDate AND @strToDate))
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0
RETURN
END
SELECT DISTINCT TOP 1000 Email FROM(
SELECT Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE ((contains (originalresume, @strSearchParams)) AND (currentdateout BETWEEN @strFromDate AND @strToDate))
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0
Order BY Email
RETURN
END
---(2) search by mail and skills
IF (@strSearchParams <> '') AND (@strMailFilter <> '')
BEGIN
IF @ShowCount = 1
BEGIN
SELECT COUNT(DISTINCT(Email))
FROM(
SELECT Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE ((contains (originalresume, @strSearchParams)) AND (currentdateout BETWEEN @strFromDate AND @strToDate))
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0 AND Email LIKE '%'+@strMailFilter +'%'
RETURN
END
SELECT DISTINCT TOP 1000 Email FROM(
SELECT Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE ((contains (originalresume, @strSearchParams)) AND (currentdateout BETWEEN @strFromDate AND @strToDate))
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0 AND Email LIKE '%'+@strMailFilter +'%'
Order BY Email
RETURN
END
---(3) search by mail filter
IF (@strMailFilter <> '')
BEGIN
IF @ShowCount = 1
BEGIN
SELECT COUNT(DISTINCT(Email)) FROM(
SELECT Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE currentdateout BETWEEN @strFromDate AND @strToDate
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0 AND Email LIKE '%'+@strMailFilter +'%'
RETURN
END
SELECT DISTINCT TOP 1000 Email FROM(
SELECT Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE currentdateout BETWEEN @strFromDate AND @strToDate
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0 AND Email LIKE '%'+@strMailFilter +'%'
Order BY Email
RETURN
END
-- (4) Search All Criteria
IF (@strSearchParams = '') AND (@strMailFilter = '')
BEGIN
IF @ShowCount = 1
BEGIN
SELECT COUNT(DISTINCT(Email)) FROM(
SELECT distinct Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE currentdateout BETWEEN @strFromDate AND @strToDate
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0
RETURN
END
SELECT DISTINCT TOP 1000 Email FROM(
SELECT Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE currentdateout BETWEEN @strFromDate AND @strToDate
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0
Order BY Email
RETURN
END
END
January 8, 2010 at 1:54 pm
Do some analysis, work out which of the queries is the slow one. Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Why are there distincts all over the place? Is the data dirty?
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
January 9, 2010 at 5:21 am
Usually with this type of query the problem is that the optimizer underestimates the cost of the full-text query. My money is on the plan changing from being driven by the results of the FT query, to one where the full-text query would be run many times.
One approach would be to replace the CONTAINS with CONTAINSTABLE and use a join or query hint to force the optimizer to start with the FT results. In the example below I have used the FORCE ORDER query hint.
SELECT COUNT(DISTINCT Email)
FROM (
SELECT Email1,
Email2,
Email3,
Email4,
Email5,
Email6,
Email7,
Email8,
Email9,
Email10
FROM CONTAINSTABLE(dbo.outputresume3, originalresume, @strSearchParams) FTS
JOIN dbo.outputresume3 ON [primary key column] = FTS.
WHERE currentdateout BETWEEN @strFromDate AND @strToDate
) P
UNPIVOT
(
Email FOR Emails IN
(
Email1,
Email2,
Email3,
Email4,
Email5,
Email6,
Email7,
Email8,
Email9,
Email10
)
) U
WHERE LEN(Email) > 0
OPTION (FORCE ORDER);
January 9, 2010 at 6:16 am
Thanks!
January 9, 2010 at 7:51 pm
No worries.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply