needs to return a resultset based on some search criteria. There are around
20 possible search criteria. Below is the SQL query used in my Stored
procedure. Any help to optimize the search will be great:
--get LOV details in table variables
INSERT INTO @.tblLov (LovCode, LovDesc, ParamCode)
SELECT LovCode, LovDesc, ParamCode FROM tp_Lov WITH (NOLOCK)
WHERE ParamCode IN('FileSrc', 'CommTrailInd', 'CommTxnStatus',
'AgencyPrincipalInd','ProdSubType','AuditTransStatus')
--get commission transaction according to the search criteria
INSERT INTO @.tblSearchResults
SELECT l1.LovDesc AS TransSource,
l2.LOVDesc AS CommTrailInd,
r.RemitCode as RemitNumber,
t.IntTransId as TransNumber,
CONVERT(VARCHAR, t.TrdDt, 110) AS TradeDate,
CONVERT(VARCHAR, t.SettlementDt, 110) AS SettlementDate,
rp.RepCode,
(ISNULL(rp.LstNm,'') + ', ' + ISNULL(rp.FstNm,'')) AS RepName,
(CASE WHEN ISNULL(t.IntClntId,0)=0
THEN ISNULL(t.ClntShortNM, '') +
(CASE WHEN (t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND
ISNULL(t.ProdType,'') <> 'VA')) AND ISNULL(t.FundAcctNum,'')<>'' THEN ' - ' +
ISNULL(t.FundAcctNum,'')
WHEN (t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'')
= 'VA')) AND ISNULL(t.PolicyNum,'')<>'' THEN ' - ' + ISNULL(t.PolicyNum,'')
WHEN t.TransSrc IN('PSH','MSR') AND ISNULL(t.ClrHouseAcctNum,'')<>'' THEN
' - ' + ISNULL(t.ClrHouseAcctNum,'')
ELSE '' END)
ELSE dev.udf_COMM_PCD_GetClientName(t.IntClntId, t.IntTransId)
END) AS Client,
(CASE WHEN ISNULL(t.CUSIP,'')='' THEN t.ProdName ELSE p.ProdNm END) AS
[Product],
t.InvAmt AS InvestmentAmt,
t.GDC AS GDC,
t.ClrChrg AS ClearingCharge,
t.NetComm AS NetCommission,
(CASE WHEN t.Status IN(@.strLov_TxnStatus_Tobepaid, @.strLov_TxnStatus_Paid)
THEN dev.udf_COMM_PCD_GetPayoutRateString(t.IntTransId) ELSE '' END) AS
PayoutRate,
(CASE WHEN t.Status IN(@.strLov_TxnStatus_Tobepaid, @.strLov_TxnStatus_Paid)
THEN dev.udf_COMM_PCD_GetPayoutAmountString(t.IntTransId) ELSE '' END) AS
Payout,
l3.LOVDesc AS TransStatus,
t.Comments,
t.OrderMarkup AS BDMarkup,
t.IntTransId,
rp.IntRepId,
sch.SchCode,
t.IntClntId,
t.CUSIP,
t.RepIdValue AS RepAlias,
t.RepIdType,
t.SplitInd,
l4.LOVDesc AS AgencyPrincipalInd,
t.AgencyPrincipalFee,
t.EmployeeTradeInd,
t.ShareMarkup,
t.UnitsTraded,
s.SponsorNm,
CASE WHEN t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND
ISNULL(t.ProdType,'') <> 'VA') THEN ISNULL(t.FundAcctNum,'') --Production
Defect #873 & 877
WHEN t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'') =
'VA') THEN ISNULL(t.PolicyNum,'')
ELSE t.ClrHouseAcctNum END,
CASE WHEN ISNULL(t.ProdSubType,'') IN ('', 'Z') THEN 'Not Defined'
ELSE l6.LovDesc END AS ProdSubType, --t.ProdSubType,
l5.LOVDesc AS TransAuditStatus, --t.TransAuditStatus,
t.TransAuditStatus AS TransAuditStatusCode,
t.OriginalTransId,
t.RowId,
t.Status,
t.intParentTransId,
t.CancelTrdInd,
t.ClrChrgOverrideInd,
9999 AS AuditKey
FROM tr_CommTrans t WITH (NOLOCK)
INNER JOIN @.tblLov l1 ON t.TransSrc = l1.LOVCode and l1.ParamCode = 'FileSrc'
INNER JOIN @.tblLov l2 ON t.CommTrailInd = l2.LOVCode and l2.ParamCode =
'CommTrailInd'
INNER JOIN @.tblLov l3 ON t.Status = l3.LOVCode and l3.ParamCode =
'CommTxnStatus'
INNER JOIN td_Remit r WITH (NOLOCK) ON t.IntRemitId = r.IntRemitId
LEFT OUTER JOIN @.tblLov l4 ON t.AgencyPrincipalInd = l4.LOVCode and
l4.ParamCode = 'AgencyPrincipalInd'
LEFT OUTER JOIN @.tblLov l5 ON t.TransAuditStatus = l5.LOVCode AND
l5.ParamCode = 'AuditTransStatus'
LEFT OUTER JOIN @.tblLov l6 ON t.ProdSubType = l6.LOVCode AND l6.ParamCode =
'ProdSubType'
LEFT OUTER JOIN tm_BDProd p WITH (NOLOCK) ON t.CUSIP = p.CUSIP
LEFT OUTER JOIN tm_BDSponsors s WITH (NOLOCK) ON t.IntBDSponsorId =
s.IntBDSponsorId
LEFT OUTER JOIN tm_Reps rp WITH (NOLOCK) ON t.IntRepId = rp.IntRepId
LEFT OUTER JOIN tm_PayoutSch sch WITH (NOLOCK) ON t.IntSchId = sch.IntSchId
WHERE t.IntTransId = (CASE WHEN @.intTransId IS NULL THEN t.intTransId ELSE
@.intTransId END) AND
t.TransSrc = @.strTransSrc AND
r.RemitCode = (CASE WHEN ISNULL(@.strRemitCode,'')='' THEN r.RemitCode ELSE
@.strRemitCode END) AND
ISNULL(t.SettlementDt,'01-01-1900') BETWEEN @.dtmFromSettlementDt AND
@.dtmToSettlementDt AND
ISNULL(t.TrdDt,'01-01-1900') BETWEEN @.dtmFromTradeDt AND @.dtmToTradeDt AND
t.CommTrailInd = (CASE WHEN @.chrShowTrails='Y' THEN t.CommTrailInd ELSE 'C'
END) AND
t.Status = (CASE WHEN ISNULL(@.strStatus,'')='' THEN t.Status ELSE
@.strStatus END) AND
ISNULL(t.ClrHouseAcctNum,'') LIKE (CASE WHEN ISNULL(@.strAccountId,'')=''
THEN ISNULL(t.ClrHouseAcctNum,'')
WHEN (@.strTransSrc = 'PSH' OR @.strTransSrc = 'MSR' OR @.strTransSrc
= 'MSA') THEN @.strAccountId
ELSE ISNULL(t.ClrHouseAcctNum,'') END) AND
ISNULL(t.FundAcctNum,'') LIKE (CASE WHEN ISNULL(@.strAccountId,'')='' THEN
ISNULL(t.FundAcctNum,'')
WHEN @.strTransSrc = 'NSM' THEN @.strAccountId
WHEN @.strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')<>'VA' THEN
@.strAccountId
ELSE ISNULL(t.FundAcctNum,'') END) AND
ISNULL(t.PolicyNum,'') LIKE (CASE WHEN ISNULL(@.strAccountId,'')='' THEN
ISNULL(t.PolicyNum,'')
WHEN @.strTransSrc = 'NSV' THEN @.strAccountId
WHEN @.strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')='VA' THEN
@.strAccountId
ELSE ISNULL(t.PolicyNum,'') END) AND
ISNULL(t.IntBDSponsorId,-1) = (CASE WHEN @.intSponsorId IS NULL THEN
ISNULL(t.IntBDSponsorId,-1) ELSE @.intSponsorId END) AND
ISNULL(t.ProdType,'') = (CASE WHEN ISNULL(@.strProdType,'')='' THEN
ISNULL(t.ProdType,'') ELSE @.strProdType END) AND
ISNULL(t.ProdSubType,'') = (CASE WHEN ISNULL(@.strProdSubType,'') ='' THEN
ISNULL(t.ProdSubType,'') ELSE @.strProdSubType END) AND
ISNULL(t.CUSIP,'') = (CASE WHEN ISNULL(@.strCUSIP,'')='' THEN
ISNULL(t.CUSIP,'') ELSE @.strCUSIP END) AND
ISNULL(rp.SSN, 0) = (CASE WHEN @.numRepSSN IS NULL THEN ISNULL(rp.SSN, 0)
ELSE @.numRepSSN END) AND
ISNULL(rp.RepCode,'') = (CASE WHEN ISNULL(@.strRepCode,'')='' THEN
ISNULL(rp.RepCode,'') ELSE @.strRepCode END) AND
ISNULL(rp.LstNm, '') = (CASE WHEN ISNULL(@.strRepLstNm,'')='' THEN
ISNULL(rp.LstNm,'') ELSE @.strRepLstNm END) AND
ISNULL(rp.FstNm, '') = (CASE WHEN ISNULL(@.strRepFstNm,'')='' THEN
ISNULL(rp.FstNm,'') ELSE @.strRepFstNm END) AND
ISNULL(rp.RepStatus,'') <> (CASE WHEN @.chrIncludeTerminated='Y' THEN 'Z'
ELSE 'T' END) AND
ISNULL(t.IntClntId,-1) = (CASE WHEN @.intClientId IS NULL THEN
ISNULL(t.IntClntId,-1) ELSE @.intClientId END) AND
( (@.chrAuditReportFlag = 'N' AND
t.Status NOT IN(@.strLov_TxnStatus_Loaded, @.strLov_TxnStatus_Cancelled) AND
ISNULL(TransAuditStatus,@.strLov_TransAuditStatus_Active) =
@.strLov_TransAuditStatus_Active
)
OR
(@.chrAuditReportFlag = 'Y' AND
t.Status NOT IN(@.strLov_TxnStatus_Loaded)
DefectID# 880,895
IN(@.strLov_TransAuditStatus_Active, @.strLov_TransAuditStatus_Cancelled)
)
)
The 12 table join will without a doubt, cause slow downs with any significant amount of data. A few items to consider would be:
dynamic sql based on the requested search criteria. This would be good if you are familiar with dynanmic SQL and would be able to elliminate many joins based on only having specific criteria. For example, if you don't have fields pertaining to the tm_PayoutSch table, then could you remove that join? This would be be done in dynamic sql since you would not be able to determine that ahead of time. Also, elliminating the CASE statements in the JOIN clause will significally help you out if this is an option.|||
First, at the end of this statement, a few lines looks irragular to me:
(@.chrAuditReportFlag = 'Y' AND
t.Status NOT IN(@.strLov_TxnStatus_Loaded)
DefectID# 880,895
IN(@.strLov_TransAuditStatus_Active, @.strLov_TransAuditStatus_Cancelled)
I am not sure how SQL will interprate it. Beside all Chris said, you may consider to use temprary table to limit the size of intermediate dataset. SQL optimization can do some thing, but we better to help it with our own effort. Get the output sub set from the core table or the lasrgest table(tr_CommTrans) first. In that way, we can avoid generate a even larger intermediate data set when left join other tables. Also, it apperantly you a dealing with a lot of garbage data. When you creates the temperary table, you can get a data clean table and without change the original data.
|||Continuing on the previous two posts, here are a couple of things I see:
Joining to a table variable can be an expensive process. The query optimizer assumes that table variables will have just one row in them (check out your execution plan). Since table variables contain no statistics, there is nothing to make it assume otherwise. Consider a temporary table or a subquery in these places.
Using ISNULL in your WHERE clause is not preferrable on either side of the "Equals". ISNULL on the column could cause SQL Server to perform an INDEX SCAN on the column in question (assuming it's indexed) or TABLE SCAN (clustered index scan), as it has to perform this calculation on each row.
Using ISNULL on the parameter side (I.E. ISNULL(@.strAccountId,'') ) results in a non-SARGable query. SQL Server will be unable to determine the cardinality of your request and could also result in an INDEX SCAN or TABLE SCAN.
No comments:
Post a Comment