November 2, 2011 at 10:25 am
Hi,
I have a query thats runs very very slow .
select distinct
R.A, R.B, R.C,
(SELECT TOP 1 TrWeek FROM XXXX O2 WHERE O2.Start_date <= R.TrDate AND O2.End_date >= R.TrDate)
from recon R
LEFT OUTER JOIN XXXX O ON
O.Trweek = (SELECT TOP 1 TrWeek FROM XXXX O2 WHERE O2.Start_date <= R.TrDate AND O2.End_date >= R.TrDate) AND
O.A = R.A AND
O.B = R.B
where R.trweek = 201139
AND O.recid IS NULL
Where can I tweak so that it will take less time and provide correct results ?
Thanks,
November 2, 2011 at 10:33 am
Would help if you provided us with the DDL for the table(s), sample data, and expected results based on the sample data.
Please read the first article I reference below in my signature block regarding asking for help to see how this info should be posted to get you the best responses possible.
November 2, 2011 at 10:45 am
I agree with Lynn, we need something to go on here.
However, just in the skeleton you posted you can increase performance AND accuracy to at least some degree by getting rid of the subquery in multiple places.
declare @TrWeek int
select top 1 @TrWeek = TrWeek FROM XXXX O2 WHERE O2.Start_date <= R.TrDate AND O2.End_date >= R.TrDate
ORDER BY SomeField
select distinct
R.A, R.B, R.C, @TrWeek
from recon R
LEFT OUTER JOIN XXXX O ON
O.Trweek = @TrWeek AND
O.A = R.A AND
O.B = R.B
where R.trweek = 201139
AND O.recid IS NULL
--Note there is now an order by clause on the top 1. Without the order by you are just getting whatever record sql feels like giving you. It could in fact be a different record in the select list from the one in the join condition. :w00t:
Without some details I don't think anybody is going to come up with much.
_______________________________________________________________
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 2, 2011 at 12:30 pm
Sean Lange (11/2/2011)
I agree with Lynn, we need something to go on here.However, just in the skeleton you posted you can increase performance AND accuracy to at least some degree by getting rid of the subquery in multiple places.
declare @TrWeek int
select top 1 @TrWeek = TrWeek FROM XXXX O2 WHERE O2.Start_date <= R.TrDate AND O2.End_date >= R.TrDate
ORDER BY SomeField
select distinct
R.A, R.B, R.C, @TrWeek
from recon R
LEFT OUTER JOIN XXXX O ON
O.Trweek = @TrWeek AND
O.A = R.A AND
O.B = R.B
where R.trweek = 201139
AND O.recid IS NULL
--Note there is now an order by clause on the top 1. Without the order by you are just getting whatever record sql feels like giving you. It could in fact be a different record in the select list from the one in the join condition. :w00t:
Without some details I don't think anybody is going to come up with much.
The problem is that this query won't work, because you don't have a table R defined in your first statement.
select top 1 @TrWeek = TrWeek FROM XXXX O2 WHERE O2.Start_date <= R.TrDate AND O2.End_date >= R.TrDate
ORDER BY SomeField
The question I have is if O.RecID IS NULL, doesn't that indicate that there are no records that meet the criteria, so the subquery in the SELECT clause will also return NULL since it uses the same criteria and will therefore also return no records?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 2, 2011 at 12:34 pm
O.RecID being null doesn't necessarily mean a missing row. Might, but could also just be a nullable columns in the table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 1:21 pm
GSquared (11/2/2011)
O.RecID being null doesn't necessarily mean a missing row. Might, but could also just be a nullable columns in the table.
Not by itself, but the fact that it's called RecID indicates that it's probably the primary key, and therefore, not nullable.
I did notice after I posted that the conditions are not exactly the same, so the question is immaterial anyhow.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 2, 2011 at 1:37 pm
drew.allen (11/2/2011)
Sean Lange (11/2/2011)
I agree with Lynn, we need something to go on here.However, just in the skeleton you posted you can increase performance AND accuracy to at least some degree by getting rid of the subquery in multiple places.
declare @TrWeek int
select top 1 @TrWeek = TrWeek FROM XXXX O2 WHERE O2.Start_date <= R.TrDate AND O2.End_date >= R.TrDate
ORDER BY SomeField
select distinct
R.A, R.B, R.C, @TrWeek
from recon R
LEFT OUTER JOIN XXXX O ON
O.Trweek = @TrWeek AND
O.A = R.A AND
O.B = R.B
where R.trweek = 201139
AND O.recid IS NULL
--Note there is now an order by clause on the top 1. Without the order by you are just getting whatever record sql feels like giving you. It could in fact be a different record in the select list from the one in the join condition. :w00t:
Without some details I don't think anybody is going to come up with much.
The problem is that this query won't work, because you don't have a table R defined in your first statement.
select top 1 @TrWeek = TrWeek FROM XXXX O2 WHERE O2.Start_date <= R.TrDate AND O2.End_date >= R.TrDate
ORDER BY SomeField
The question I have is if O.RecID IS NULL, doesn't that indicate that there are no records that meet the criteria, so the subquery in the SELECT clause will also return NULL since it uses the same criteria and will therefore also return no records?
Drew
It certainly was not meant as a complete solution. Given that the OP had nothing more than a skeleton and rough example I provided a rough example. I should have looked a little closer before posting.
Speaking of the OP...they seem to have left the building.
_______________________________________________________________
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply