July 13, 2011 at 4:39 am
Hi Dudes,
I have This Query And I want to improve it because it is really slow:
SELECTtk.Id
,tk.Ref
,tk.ClassId
,tk.SourceSystemId
,ISNULL(tm.IdentifierCode,'')AS IC
,ISNULL(tm.Indicator,'')AS Indicator
,ISNULL(tm.Capacity,'')AS Capacity
,ISNULL(tm.[Status],'')AS [Status]
,ISNULL(tm.RootCode,'')AS [Primary]
,ISNULL(tm.CodeType,'')AS [PrimaryType]
,ISNULL(tm.SecondRootCode,'')AS [Secondary]
,ISNULL(tm.SecondCodeType,'')AS [SecondaryType]
,tk.DateId
,ISNULL(CONVERT(smallint, DATEPART(hh, tk.DateSubmitted)),-1)AS SubmittedHr
,ISNULL(CONVERT(smallint, DATEPART(hh, tm.[DateTime])),-1)AS DateTimeHr
,tk.DateId
,tr.ReportDateId
,ISNULL(dcy.Id, 1) AS CcyId
,1 AS ImportedCount
,CASE (SELECT COUNT(1) FROM ValidationError WHERE Id = tk.Id) WHEN 0 THEN 0 ELSE 1 END AS InvalidCount
,CASE (SELECT COUNT(1) FROM V_ValidationError WHERE Id = tk.Id AND RuleTypeId = 1) WHEN 0 THEN 0 ELSE 1 END AS HasInternalValidationErrorCount
,CASE (SELECT COUNT(1) FROM V_ValidationError WHERE Id = tk.Id AND RuleTypeId = 2) WHEN 0 THEN 0 ELSE 1 END AS HasEnrichmentErrorCount
,CASE (SELECT COUNT(1) FROM V_ValidationError WHERE Id = tk.Id AND RuleTypeId = 3) WHEN 0 THEN 0 ELSE 1 END AS HasFormatErrorCount
,CASE (SELECT COUNT(1) FROM V_ValidationError WHERE IId = tk.Id AND RuleTypeId = 4) WHEN 0 THEN 0 ELSE 1 END AS HasSequenceErrorCount
,CASE WHEN tk.IsReportable = 1 AND tk.IsValid = 0 THEN 1 ELSE 0 END AS OpenInvalidCount
,CASE WHEN tk.IsValid = 1 THEN 1 ELSE 0 END AS ValidCount
,CASE WHEN tk.IsValid = 1 AND (SELECT COUNT(1) FROM ValidationError WHERE Id = tk.Id) = 0 THEN 1 ELSE 0 END AS AutoValidCount
,CASE WHEN tk.IsValid = 1 AND (SELECT COUNT(1) FROM ValidationError WHERE Id = tk.Id) > 0 THEN 1 ELSE 0 END AS ManValidCount
,(SELECT COUNT(1) FROM ValidationError WHERE Id = tk.Id AND ValidationRuleId NOT IN (8001,8002)) AS ValidationErrorsCount
,CASE tk.IsReported WHEN 1 THEN 1 ELSE 0 END AS ReportedCount
,CASE tk.IsReported WHEN 0 THEN 1 ELSE 0 END AS NotReportedCount
,CASE WHEN tk.IsReported = 0 AND tk.IsValid = 1 AND tk.IsReportable = 1 THEN 1 ELSE 0 END AS ReportPendingCount
,CASE tk.IsReportable WHEN 1 THEN 1 ELSE 0 END AS ReportableCount
,CASE tk.IsReportable WHEN 0 THEN 1 ELSE 0 END AS NotReportableCount
,CASE ISNULL(override.Id,0) WHEN 0 THEN 1 ELSE 0 END AS AutoFsaNonReportableCount
,CASE ISNULL(override.Id,0) WHEN 0 THEN 0 ELSE 1 END AS ManFsaNonReportableCount
,ISNULL((SELECT count(1) FROM ReportError WHERE Id = tk.Id AND Filename like '%_trax%'),0) ARejectedCount
,ISNULL((SELECT count(1) FROM ReportError WHERE Id = tk.Id AND Filename like '%_FSA%'),0) AS RejectedCount
,CASE WHEN (tk.TradeDateId + 1) < tk.HubDateId AND tm.ReportStatus = 'N' THEN 1 ELSE 0 END AS LateSubmissionCount
,tm.Quantity * tm.Price AS Value
,DATEDIFF(MI, tm.DateTime, tk.DateSubmitted) AS SubmissionTime
,DATEDIFF(MI, tk.DateSubmitted, tr.DateSubmitted) AS ArmSubmissionTime
,0 AS ResolutionTime
, '' AS ResolvedUsername
,NULL AS ResolvedDateId
,CASE WHEN ( SELECT COUNT(1) FROM ValidationError WHERE Id = tk.Id AND ValidationRuleId IN (8001,8002)) > 0 THEN 1 ELSE 0 END AS ResolvedCount
FROM dbo.TKey tk
INNER JOIN dbo.TMain tm ON tk.Id = tm.Id
INNER JOIN Dimccy dcy on tm.Currency=dcy.ccy
LEFT OUTER JOIN dbo.V_Report tr ON tk.Id = tr.Id AND tr.RegId = 1
LEFT OUTER JOIN dbo.ReportingExclusion override ON tk.Id = override.Id AND override.ReportingRuleId = 65
WHERE tk.DateID=@DateId
I have 2 questions:
1- If I use a computed column against SELECT COUNT(1) FROM ValidationError ... What will happen? which one is faster?
2- How can I get more speed? I need it to work faster...:(
Best Regards,
Ashkan
July 13, 2011 at 5:03 am
OMG! so many subqueries in one query. it's gonna die :w00t:!
Abhijit - http://abhijitmore.wordpress.com
July 13, 2011 at 5:16 am
ashkan siroos (7/13/2011)
1- If I use a computed column against SELECT COUNT(1) FROM ValidationError ... What will happen? which one is faster?
The only way to know is to try both versions of the query. Your system has different capabilities than our systems. We can't do your performance testing for you.
ashkan siroos (7/13/2011)
2- How can I get more speed? I need it to work faster...:(
Do an execution plan and post the results so we can go through it with you and point out parts where you can improve the query. EDIT: Table DDL and sample data would be helpful to us. It will give us something to test so we know our advice actually works.
Abhijit More (7/13/2011)
OMG! so many subqueries in one query. it's gonna die :w00t:!
Abhijit, it's one thing to joke after the problem is solved or if the OP has made a joke, but if you can't post something helpful in the first response to someone's question, please don't post at all.
July 13, 2011 at 5:27 am
Please don't call us "dudes" since this is not a low level chat room. It is a forum for professionals. I kindly ask you to respect it.
Regarding the query itself:
As already mentioned, the numerous subqueries almost all referencing the same table together with the reference to V_ValidationError (what might be a view again pointing to the very same table) most probably is the root cause for the poor performance.
Also, V_Report might have a negative influence, too.
I recommend to take a look into the CROSS APPLY approach or use ValidationError in another LEFT JOIN and use SUM(CASE WHEN THEN 1 ELSE 0 END) instead of COUNT(1) to get the conditional aggregation you're looking for.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply