How can I make this faster?

Question

Post reply

How can I make this faster?

David-250683

Hall of Fame

Points: 3452
More actions
October 11, 2007 at 10:59 am

#62963

Values being pulled from temp table. The table has around 12,000 rows. There is a non-clustered index on eac mentioned column.
Can take over two minutes to complete.
SELECT a.ReceivedDate ss,
AVG(DATEDIFF(day, b.ReceivedDate, a.ReceivedDate)) [GreenAvg]
FROM #Data a
JOIN#Data b
ON b.ReceivedDate <= a.ReceivedDate
AND(b.ReturnDate >= a.ReceivedDate
OR b.ReturnDate IS NULL)
GROUP BY a.ReceivedDate

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

maybe 2 things:

1) provide indexes (2) for the dates, to support you and grouping

2) If you know you don't need to process the whole table , add an extra where clause to delimit the resultsets.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

How long does it take without the indexes?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

David-250683 Hall of Fame Points: 3452 More actions · Answer 3

Thanks for the suggestions.

ALZDBA, I was able to add a where clause and that did help a bit.

Greg, the times are all over.

With index I got 1:05 and 2:20.

Without I got 0:41, 0:35, 0:24.

I then dropped and re-created the table again without the indexes and got 0:24.

I then dropped and re-created the table back with the indexes and got 0:46 and 0:44.

I tried one idex at a time as well but the results are sporadic.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

You've managed, quite nicely, to build a partially exclusive Cartesian Join known as a "double triangle" join. Please see the following post...

http://www.sqlservercentral.com/Forums/Topic359124-338-1.aspx#bm360151

You need some other criteria, hopefully and equi-join, to get better performance. If no other criteria is possible, then you're kinda stuck with what you have.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)