SQL Server Performance Forum – Threads Archive

Searching a date range

Hi,
I have a stored proc that takes several parameters including @FromDate and @ToDate, both of which are optional (can be NULL). In the WHERE statement, i am currently using the following to find things between the date: AND (@FromDate IS NULL OR (prs.SubmissionDate >= @FromDate))
AND (@ToDate IS NULL OR (prs.SubmissionDate <= @ToDate)) I find that this is not particularly fast, and am wondering if anyone has any other suggestions on how i could achieve the same thing Cheers,
Ben ‘I reject your reality and substitute my own’ – Adam Savage

The idea is to let the SQL use existing index/statistics/execution plan. Check the execution plan. Use the profiler to paste the text plan. You can also use the index tuning wizard if you have enought data. Try this: AND ((
prs.SubmissionDate >= @FromDate AND
prs.SubmissionDate <= @ToDate
)
OR (
prs.SubmissionDate BETWEEN ISNULL(@FromDate,prs.SubmissionDate) AND ISNULL(@ToDate, prs.SubmissionDate)
)) May the Almighty God bless us all!
www.empoweredinformation.com

http://www.sql-server-performance.com/fk_datetime.asp Madhivanan Failing to plan is Planning to fail

Hi ya, if pure performance is what you’re after then you are better to do the datetime null checks outside of the select statement as in if @fromdate is not null and @todate is not null
begin
end
else if @fromdate is null
begin
end
else if @todate is null
begin
end
else
begin
end
and put the appropriate select between the begin/end. This will result in repetition since the selects will be nearly the same but can give huge performance improvements. Also if the probability of each of the 4 paths is not evenly distributed, say it is 99% both present, 1% the other three, then you can get away with only the first and last block Indexing is going to be very important here… if you only use a couple of columns out of that table in the entire select, then create a nonclustered index on submissiondate and the additional columns. If you’re using most of the columns in the table then create a clustered index on submissiondate and whatever else you need to make it unique Cheers
Twan

…
and prs.SubmissionDate >= isNull(@FromDate, 0)
and prs.SubmissionDate <= isNull(@toDate, ‘99991231’) –or any date out of reach

Thanks guys,
Twan, unfortunately there are a number of other parameters in addition to the dates that can also be null (there is 11 in total!) so having a different version for each combination is not really an option! although, i guess since the dates are what is giving me grief at the moment, i could create different versions for the dates only…will need some more investigation on my part to see what parameters it is called with i think!
Ben ‘I reject your reality and substitute my own’ – Adam Savage

I haven’t seen Twan’s answer when posting mine. I agree with his approach. Mine would work decently only if at least one from/to date is provided (not null) and the condition on date is restrictive enough.

Hi Ben, Also please be aware of the problem with ‘parameter sniffing’ i.e. SQL will base its execution plan for a proc using the parameters passed to it the first time it is called (when it is pulled into the cache) After that time it will no longer look at the parameters and assume that the execution plan is going to be the same. This can be terrible for performance, since the first time through someone may have asked for something based on a specific value for param1 and a very wide daterange. SQL would use the index on the column for param1 (if there is one) A subsequent call with a narrow date range would not use an index on that date range, it would always use the index on the column for param1 Also beware that you don’t have too many OR conditions which will evntually just cause table scans, and try to not use functions on the columns in the where clause as these will then be excluded from index selection. What I’ve had to do in the past is have a catch all statement at the end, but then work out the most likely combinations of parameters, index those properly and have special cases for them using the if/else blocks as per above. Yes it is a pain, but ultimately you need happy users <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

Hi Twan,
1 further question- if i do split it up into a series of IF ELSE blocks, wont i have the same problem with SQL Server generating a query plan and using that in future regardless of which block of code the call to the procedure falls into? Thanks,
Ben ‘I reject your reality and substitute my own’ – Adam Savage

If the OR is slowing you down, you often speed up queries with OR in the WHERE clause, by running each criteria in a seperate select and doing a UNION ALL on them to get one resultset. Can you maybe paste your execution plan for this query? Are you sure its the date range which is causing you rthe performance problem ? Do SET SHOWPLAN_ALL ON
GO
<your query>
GO
SET SHOWPLAN_ALL OFF
GO and paste the results

Im not 100% sure the date section is causing the problems…basically it is a horrible looking procedure and i am trying anything to get it to go faster Here is the text from setting showplan_all on…any tips would be great!: exec Run_LenderTerminalRunsheet N’d8a428bd’, N’NAB Personal Loan’, NULL, NULL, NULL, N’1/4/2006′, N’30/4/2006′, NULL, N’1′, NULL, NULL, NULL CREATE PROCEDURE [dbo].[Run_LenderTerminalRunsheet] @TerminalId_ NVARCHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @ProductName_ NVARCHAR(50) = NULL, @AssignmentStatus_ NVARCHAR(10) = NULL, @ApplicantName_ NVARCHAR(100) = NULL, @ReferenceNo_ NVARCHAR (9) = NULL, @FromDate_ NVARCHAR(10) = NULL, @ToDate_ NVARCHAR(10) = NULL, @ShowWithdrawn_ BIT = NULL, @ShowSettled_ BIT = NULL, @ShowPreferentialBrokers_ BIT = NULL, @LenderDecision_ NVARCHAR(20) = NULL, @ProductGroup_ NVARCHAR(20) = NULL AS SET CONCAT_NULL_YIELDS_NULL OFF SET NOCOUNT ON SET DATEFORMAT DMY DECLARE @DateFormatToDate DATETIME SET @DateFormatToDate = CONVERT (CHAR, @ToDate_, 103) SET @DateFormatToDate = @DateFormatToDate + 1 SELECT a.ApplicationStatus, a.TransactionId, a.ApplicationNo, CONVERT(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, prs.SubmissionDate, 112) AS SubmissionDate, prs.SubmissionDate AS OrderBySubmissionDate, UPPER(LEFT(dbo.view_PrimaryApplicantNameAndDOB.FirstName, 1)) + ‘. ‘ + dbo.view_PrimaryApplicantNameAndDOB.LastName AS DisplayName, LEFT(pr.ResultStatus, 3) AS ResultStatus, aps.LoanPrincipal AS SettlementAmount, pr.LoanPrincipal, Filtered_ProductResultInitialFeeSum.InitialFeeSum, aw.CancelledBy, pr.ProductName, rp.ProductShortName, t.InProgress, (SELECT LEFT(ContactFirstName, 1) + ‘. ‘ + ContactLastName AS Expr1 FROM dbo.tbl_Terminal AS tbl_Terminal_3 WHERE (TerminalId = prs.AssignedTerminalId)) AS AssignedName, CASE WHEN prs.PreferenceBrokerCancelledDate IS NOT NULL THEN 0 WHEN prs.PreferenceBroker IS NULL THEN 0 ELSE prs.PreferenceBroker END AS PreferenceBroker, prs.SubmissionStatus, CONVERT(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, prs.SubmissionStatusDate, 112) AS SubmissionStatusDate FROM dbo.tbl_ApplicationWithdraw aw RIGHT OUTER JOIN dbo.tbl_Application a INNER JOIN dbo.tbl_ProductResult pr ON a.LastApplicationResultId = pr.ApplicationResultId INNER JOIN dbo.tbl_ProductResultSubmission prs ON pr.ApplicationResultId = prs.ApplicationResultId AND pr.ProductName = prs.ProductName ON aw.TransactionId = a.TransactionId LEFT OUTER JOIN dbo.tbl_ApplicationSettlement aps ON prs.ProductName = aps.ChosenProduct AND a.TransactionId = aps.TransactionId LEFT OUTER JOIN (SELECT ApplicationResultId, ProductName, SUM(FeeAmount) AS InitialFeeSum FROM dbo.tbl_ProductResultFee WHERE (FeeType = N’Initial’) AND (NOT (FeeCode = N’NABFEE3′)) GROUP BY ApplicationResultId, ProductName) AS Filtered_ProductResultInitialFeeSum ON pr.ApplicationResultId = Filtered_ProductResultInitialFeeSum.ApplicationResultId AND pr.ProductName = Filtered_ProductResultInitialFeeSum.ProductName LEFT OUTER JOIN dbo.tbl_RegisteredProduct rp ON pr.ProductName = rp.ProductName LEFT OUTER JOIN dbo.tbl_Transaction t LEFT OUTER JOIN dbo.tbl_Terminal te LEFT OUTER JOIN dbo.tbl_TerminalProfileManagementDependency tpmd ON te.Profile = tpmd.Profile ON t.DistributorId = te.TerminalId ON a.TransactionId = t.TransactionId LEFT OUTER JOIN dbo.view_PrimaryApplicantNameAndDOB ON a.TransactionId = dbo.view_PrimaryApplicantNameAndDOB.TransactionId WHERE NOT(a.Demo = 1) AND prs.SubmissionDate IS NOT NULL AND tpmd.TerminalId = @TerminalId_ AND ( –If @ProductName and @ProductGroup are both null, include all products the terminal has access to (@ProductName_ IS NULL AND @ProductGroup_ IS NULL AND prs.ProductName IN (SELECT ProductName FROM dbo.udf_TerminalAvailableProduct (@TerminalId_))) –If @ProductName is specified and @ProductGroup is NULL show only replies for that product OR (@ProductName_ IS NOT NULL AND pr.ProductName = @ProductName_) –If @ProductGroup is specified and @ProductName is Null OR (@ProductGroup_ IS NOT NULL AND prs.ProductName IN (SELECT ProductName FROM dbo.udf_TerminalProductGroupProductList (@TerminalId_, @ProductGroup_))) ) AND (@AssignmentStatus_ IS NULL OR (@AssignmentStatus_ = ‘Unassigned’ AND prs.AssignedTerminalId IS NULL) OR (@AssignmentStatus_ = ‘Assigned’ AND prs.AssignedTerminalId = @TerminalId_) OR ( (@AssignmentStatus_ <> ‘Assigned’ AND @AssignmentStatus_ <> ‘Unassigned’ ) AND prs.AssignedTerminalId = @AssignmentStatus_ ) ) AND (@ApplicantName_ IS NULL OR (a.TransactionId IN (SELECT DISTINCT TransactionId FROM tbl_Applicant WHERE FirstName + N’ ‘ + MiddleName + N’ ‘ + LastName LIKE ‘%’ + @ApplicantName_ + ‘%’))) AND (@ReferenceNo_ IS NULL OR (te.TerminalReferenceNo = @ReferenceNo_)) AND (@ShowWithdrawn_ = 1 OR ((@ShowWithdrawn_ IS NULL OR @ShowWithdrawn_ = 0) AND aw.TransactionId IS NULL)) AND (@ShowSettled_ = 1 OR ((@ShowSettled_ IS NULL OR @ShowSettled_ = 0) AND aps.LoanPrincipal IS NULL)) AND (@FromDate_ IS NULL OR (prs.SubmissionDate >= @FromDate_)) AND (@ToDate_ IS NULL O |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1023]=Convert([prs].[SubmissionDate]), [Expr1024]=upper(substring([tbl_Applicant].[FirstName], 1, 1))+’. ‘+[tbl_Applicant].[LastName], [Expr1025]=substring([pr].[ResultStatus], 1, 3), [Expr1027]=[Expr1027], [Expr1029]=If ([prs].[PreferenceBrokerCancelledDate]<>NULL) then 0 else If ([prs].[PreferenceBroker]=NULL) then 0 else Convert([prs].[PreferenceBroker]), [Expr1030]=Convert([prs].[SubmissionStatusDate]))) |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[prs].[AssignedTerminalId])) |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[prs].[SubmissionDate] DESC)) | |–Hash Match(Left Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[TransactionId])=([tbl_Applicant].[TransactionId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[TransactionId]=[tbl_Applicant].[TransactionId])) | |–Hash Match(Right Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[rp].[ProductName])=([pr].[ProductName]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[pr].[ProductName]=[rp].[ProductName])) | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_RegisteredProduct].[PK_tbl_RegisteredProducts] AS [rp])) | | |–Merge Join(Right Outer Join, MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tbl_ProductResultFee].[ApplicationResultId], [tbl_ProductResultFee].[ProductName])=([pr].[ApplicationResultId], [pr].[ProductName]), RESIDUAL<img src=’/comm unity/emoticons/emotion-6.gif’ alt=’

‘ />[pr].[ApplicationResultId]=[tbl_ProductResultFee].[ApplicationResultId] AND [pr].[ProductName]=[tbl_ProductResultFee].[ProductName])) | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1002]=If ([Expr1084]=0) then NULL else [Expr1085])) | | | |–Stream Aggregate(GROUP BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tbl_ProductResultFee].[ApplicationResultId], [tbl_ProductResultFee].[ProductName]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1084]=COUNT_BIG([tbl_ProductResultFee].[FeeAmount]), [Expr1085]=SUM([tbl_ProductResultFee].[FeeAmount]))) | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_ProductResultFee].[PK_tbl_ProductResultFee]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tbl_ProductResultFee].[FeeType]=’Initial’ AND [tbl_ProductResultFee].[FeeCode]<>’NABFEE3′) ORDERED FORWARD) | | |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[LastApplicationResultId] ASC, [pr].[ProductName] ASC)) | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />Convert([@ShowSettled_])=1 OR ([@ShowSettled_]=NULL OR Convert([@ShowSettled_])=0 AND [aps].[LoanPrincipal]=NULL))) | | |–Hash Match(Right Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[aps].[ChosenProduct], [aps].[TransactionId])=([prs].[ProductName], [a].[TransactionId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[prs].[ProductName]=[aps].[ChosenProduct] AND [a].[TransactionId]=[aps].[TransactionId])) | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_ApplicationSettlement].[PK_tbl_ApplicationSettlement] AS [aps])) | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />Convert([@ShowWithdrawn_])=1 OR ([@ShowWithdrawn_]=NULL OR Convert([@ShowWithdrawn_])=0 AND [aw].[TransactionId]=NULL))) | | |–Merge Join(Right Outer Join, MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[aw].[TransactionId])=([a].[TransactionId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[aw].[TransactionId]=[a].[TransactionId])) | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_ApplicationWithdraw].[PK_tbl_ApplicationWithdraw] AS [aw]), ORDERED FORWARD) | | |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[TransactionId] ASC)) | | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tpmd].[Profile])=([te].[Profile]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[te].[Profile]=[tpmd].[Profile])) | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_TerminalProfileManagementDependency].[PK_TerminalProfileManagementDependency] AS [tpmd]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tpmd].[TerminalId]=[@TerminalId_])) | | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[te].[TerminalId])=([t].[DistributorId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[t].[DistributorId]=[te].[TerminalId])) | | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_Terminal].[tbl_Terminal_RefAndProfile] AS [te]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[@ReferenceNo_]=NULL OR [te].[TerminalReferenceNo]=[@ReferenceNo_])) | | |–Merge Join(Inner Join, MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[t].[TransactionId])=([a].[TransactionId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[TransactionId]=[t].[TransactionId])) | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_Transaction].[PK_tbl_Transaction] AS [t]), ORDERED FORWARD) | | |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[TransactionId] ASC)) | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />(([@ProductName_]<>NULL AND [pr].[ProductName]=[@ProductName_]) OR (([@ProductName_]=NULL AND [@ProductGroup_]=NULL) AND [Expr1071])) OR ([@ProductGroup_]<>NULL AND [Expr1072]))) | | |–Nested Loops(Left Semi Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />(([@ProductName_]<>NULL AND [pr].[ProductName]=[@ProductName_]) OR (([@ProductName_]=NULL AND [@ProductGroup_]=NULL) AND [Expr1071])) OR IsFalseOrNull([@ProductGroup_]<>NULL))OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[prs].[ProductName]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1072] = [PROBE VALUE])) | | |–Nested Loops(Left Semi Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />(([@ProductName_]<>NULL AND [pr].[ProductName]=[@ProductName_]) OR IsFalseOrNull([@ProductName_]=NULL)) OR IsFalseOrNull([@ProductGroup_]=NULL))OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[prs].[ProductName]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1071] = [PROBE VALUE])) | | | |–Merge Join(Inner Join, MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[pr].[ApplicationResultId], [pr].[ProductName])=([prs].[ApplicationResultId], [prs].[ProductName]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[prs].[ApplicationResultId]=[pr].[ApplicationResultId] AND [prs].[ProductName]=[pr].[ProductName])) | | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_ProductResult].[PK_tbl_ProductResult] AS [pr]), ORDERED FORWARD) | | | | |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[LastApplicationResultId] ASC, [prs].[ProductName] ASC)) | | | | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[prs].[ApplicationResultId])=([a].[LastApplicationResultId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[LastApplicationResultId]=[prs].[ApplicationResultId])) | | | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />STARTUP EXPR(((([@AssignmentStatus_]=NULL OR [@AssignmentStatus_]=’Unassigned’) OR [@AssignmentStatus_]=’Assigned’) OR ([@AssignmentStatus_]<>’Assigned’ AND [@AssignmentStatus_]<>’Unassigned’)) AND (([@ShowPreferentialBrokers_]=NULL OR Convert([@ShowPreferentialBrokers_])=0) OR Convert([@ShowPreferentialBrokers_])=1)))) | | | | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />((((([@LenderDecision_]=NULL OR ([@LenderDecision_]=’Worklist’ AND (([prs].[SubmissionStatus]=’Re-submitted’ OR [prs].[SubmissionStatus]=’Resumed’) OR [prs].[SubmissionStatus]=’Pending’))) OR ([@LenderDecision_]=’All Outstanding’ AND ((((([prs].[SubmissionStatus]=’Re-submitted’ OR [prs].[SubmissionStatus]=’Re-submission (FN)’) OR [prs].[SubmissionStatus]=’Re-submission’) OR [prs].[SubmissionStatus]=’Resumed’) OR [prs].[SubmissionStatus]=’On Hold’) OR [prs].[SubmissionStatus]=’Pending’))) OR ([@LenderDecision_]=’Not Approved’ AND ([prs].[SubmissionStatus]=’Cancelled’ OR [prs].[SubmissionStatus]=’Declined’))) OR ([@LenderDecision_]=’Hold’ AND ([prs].[SubmissionStatus]=’Resumed’ OR [prs].[SubmissionStatus]=’On Hold’))) OR ([@LenderDecision_]=’Resub’ AND (([prs].[SubmissionStatus]=’Re-submitted’ OR [prs].[SubmissionStatus]=’Re-submission (FN)’) OR [prs].[SubmissionStatus]=’Re-submission’))) OR [@LenderDecision_]=[prs].[SubmissionStatus])) | | | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_ProductResultSubmission].[PK_tbl_ProductResultSubmission] AS [prs]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />((([prs].[SubmissionDate]<>NULL AND ((([@AssignmentStatus_]=NULL OR ([@AssignmentStatus_]=’Unassigned’ AND [prs].[AssignedTerminalId]=NULL)) OR ([@AssignmentStatus_]=’Assigned’ AND [prs].[AssignedTerminalId]=[@TerminalId_])) OR (([@AssignmentStatus_]<>’Assigned’ AND [@AssignmentStatus_]<>’Unassigned’) AND [prs].[AssignedTerminalId]=[@AssignmentStatus_]))) AND ([@FromDate_]=NULL OR [prs].[SubmissionDate]>=Convert([@FromDate_]))) AND ([@ToDate_]=NULL OR [prs].[SubmissionDate]<=[@DateFormatToDate])) AND (([@ShowPreferentialBrokers_]=NULL OR Convert([@ShowPreferentialBrokers_])=0) OR ((Convert([@ShowPreferentialBrokers_])=1 AND Convert([prs].[PreferenceBroker])=1) AND [prs].[PreferenceBrokerCancelledDate]=NULL)))) | | | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[@ApplicantName_]=NULL OR [Expr1073])) | | | | |–Merge Join(Left Semi Join, MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[a].[TransactionId])=([tbl_Applicant].[TransactionId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />like([tbl_Applicant].[FirstName]+’ ‘+[tbl_Applicant].[MiddleName]+’ ‘+[tbl_Applicant].[LastName], ‘%’+[@ApplicantName_]+’%’, NULL) AND [a].[TransactionId]=[tbl_Applicant].[TransactionId]) OR [@ApplicantName_]=NULL) | | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_Application].[PK_tbl_Application] AS [a]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />Convert([a].[Demo])<>1) ORDERED FORWARD) | | | | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_Applicant].[tbl_Applicant_AppName]), ORDERED FORWARD) | | | |–Row Count Spool | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_TerminalProduct].[PK_tbl_TerminalProduct]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tbl_TerminalProduct].[TerminalId]=[@TerminalId_] AND [tbl_TerminalProduct].[ProductName]=[prs].[ProductName]) ORDERED FORWARD) | | |–Row Count Spool | | |–Nested Loops(Inner Join) | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_TerminalProduct].[PK_tbl_TerminalProduct]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tbl_TerminalProduct].[TerminalId]=[@TerminalId_] AND [tbl_TerminalProduct].[ProductName]=[prs].[ProductName]) ORDERED FORWARD) | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_RegisteredProduct].[PK_tbl_RegisteredProducts]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tbl_RegisteredProduct].[ProductName]=[prs].[ProductName]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tbl_RegisteredProduct].[ProductGroup]=Convert([@ProductGroup_])) ORDERED FORWARD) | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_Applicant].[tbl_Applicant_AppName]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />like([tbl_Applicant].[ApplicantType], ‘Primary’, NULL))) |–Hash Match(Cache, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[prs].[AssignedTerminalId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[prs].[AssignedTerminalId]=[prs].[AssignedTerminalId])) |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1027]=substring([tbl_Terminal_3].[ContactFirstName], 1, 1)+’. ‘+[tbl_Terminal_3].[ContactLastName])) |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[FN].[dbo].[tbl_Terminal].[index_tbl_Terminal_Contact] AS [tbl_Terminal_3]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tbl_Terminal_3].[TerminalId]=[prs].[AssignedTerminalId]) ORDERED FORWARD) ’I reject your reality and substitute my own’ – Adam Savage

You have to split the procedure, otherwise there is no way making it fast.

]]>