SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow self join


Slow self join

Author
Message
JJR333
JJR333
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 286
I have a stored procedure which updates a table with the code below.
It is rather slow (table with 360,000 rows).
Any suggestions?

Thanks,
Julian



-- ADD average FTE Hrs for the Week based table with daily data
UPDATE DRPDATA
SET FTEHrsAVG =
(
SELECT AVG(B.FTEHrs)
FROM DRPDATA B
WHERE A.MWID = B.MWID
AND
DATEPART(ISO_WEEK,A.BEGINDATUM)= DATEPART(ISO_WEEK,B.BEGINDATUM)
AND
A.[Org eenheid code] = B.[Org eenheid code]
AND
B.FlexVast = 'VAST'
GROUP BY B.MWID,b.[Org eenheid code], DATEPART(ISO_WEEK,B.BEGINDATUM)
)
FROM DRPDATA A;



Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71960 Visits: 40942
that looks like an accidental cross join to me.

it explicitly says UPDATE DRPDATA, but since the FROM has the table name as being aliased,and the unaliased table is not referenced anywhere else, there is an implied cross join like this:

UPDATE DRPDATA
SET ....
FROM DRPDATA A ,DRPDATA


the UPDATE should be UPDATE ALIASNAME explicitly.



-- ADD average FTE Hrs for the Week based table with daily data
UPDATE A
SET A.FTEHrsAVG =
(
SELECT AVG(B.FTEHrs)
FROM DRPDATA B
WHERE A.MWID = B.MWID
AND
DATEPART(ISO_WEEK,A.BEGINDATUM)= DATEPART(ISO_WEEK,B.BEGINDATUM)
AND
A.[Org eenheid code] = B.[Org eenheid code]
AND
B.FlexVast = 'VAST'
GROUP BY B.MWID,b.[Org eenheid code], DATEPART(ISO_WEEK,B.BEGINDATUM)
)
FROM DRPDATA A;



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
gbritton1
gbritton1
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1417 Visits: 879
Some other things to consider:

1. Do you have indices on the join columns? (MWID, [Org Eenheid Code], BEGINDATUM)
2. If you have an index on BEGINDATUM, the expression


DATEPART(ISO_WEEK,A.BEGINDATUM)= DATEPART(ISO_WEEK,B.BEGINDATUM)



will stop SQL server from using that index (Since it's a function call it's not a SARG). To get around that, try to reformulate it as


BEGINDATUM > <beginning of the week> AND BEGINDATUM < <beginning of following week>


Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40723 Visits: 19472
In this case I would consider using a temporary table
Cool
JJR333
JJR333
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 286
Hello Lowell,

Thanks for your response.
For some reason the original code is 15 seconds faster (2.25 min iso 2.5 min)....

Cheers,
Julian
JJR333
JJR333
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 286
The code is part of a stored procedure which bulk inserts data from a csv file, using a Staging table.
Thanks for the hint. Job for later.
Cheers,
Julian

gbritton1 (5/6/2014)
Some other things to consider:

1. Do you have indices on the join columns? (MWID, [Org Eenheid Code], BEGINDATUM)
2. If you have an index on BEGINDATUM, the expression


DATEPART(ISO_WEEK,A.BEGINDATUM)= DATEPART(ISO_WEEK,B.BEGINDATUM)



will stop SQL server from using that index (Since it's a function call it's not a SARG). To get around that, try to reformulate it as


BEGINDATUM > <beginning of the week> AND BEGINDATUM < <beginning of following week>



ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5208 Visits: 10607
Without sample data to test against...

Try this.
;WITH Updater AS (
SELECT
FTEHrsAVG,
NewFTEHrsAVG = AVG(CASE WHEN FlexVast = 'VAST' THEN FTEHrs ELSE NULL END)
OVER(PARTITION BY MWID, [Org eenheid code], DATEPART(ISO_WEEK,A.BEGINDATUM))
FROM DRPDATA o
WHERE EXISTS (
SELECT 1 FROM DRPDATA i
WHERE i.MWID = o.MWID
AND DATEPART(ISO_WEEK,i.BEGINDATUM)= DATEPART(ISO_WEEK,o.BEGINDATUM)
AND i.[Org eenheid code] = o.[Org eenheid code]
AND i.FlexVast = 'VAST')
)
UPDATE Updater SET FTEHrsAVG = NewFTEHrsAVG



You might not need the EXISTS check, it depends on what you want to do with unaffected partitions.
If the number of affected rows is relatively high and unaffected partitions should have FTEHrsAVG set to NULL, then try without the EXISTS check - it should be very quick.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Bouke Bruinsma
Bouke Bruinsma
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 603
ChrisM@home (5/6/2014)
Without sample data to test against...

Try this.
;WITH Updater AS (
SELECT
FTEHrsAVG,
NewFTEHrsAVG = AVG(CASE WHEN FlexVast = 'VAST' THEN FTEHrs ELSE NULL END)
OVER(PARTITION BY MWID, [Org eenheid code], DATEPART(ISO_WEEK,A.BEGINDATUM))
FROM DRPDATA o
WHERE EXISTS (
SELECT 1 FROM DRPDATA i
WHERE i.MWID = o.MWID
AND DATEPART(ISO_WEEK,i.BEGINDATUM)= DATEPART(ISO_WEEK,o.BEGINDATUM)
AND i.[Org eenheid code] = o.[Org eenheid code]
AND i.FlexVast = 'VAST')
)
UPDATE Updater SET FTEHrsAVG = NewFTEHrsAVG



You might not need the EXISTS check, it depends on what you want to do with unaffected partitions.
If the number of affected rows is relatively high and unaffected partitions should have FTEHrsAVG set to NULL, then try without the EXISTS check - it should be very quick.


That is amazing. Would you perhaps have a reference to other examples of CTE usage like these?
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41563 Visits: 20005
Bouke Bruinsma (5/6/2014)
ChrisM@home (5/6/2014)
Without sample data to test against...

Try this.
;WITH Updater AS (
SELECT
FTEHrsAVG,
NewFTEHrsAVG = AVG(CASE WHEN FlexVast = 'VAST' THEN FTEHrs ELSE NULL END)
OVER(PARTITION BY MWID, [Org eenheid code], DATEPART(ISO_WEEK,A.BEGINDATUM))
FROM DRPDATA o
WHERE EXISTS (
SELECT 1 FROM DRPDATA i
WHERE i.MWID = o.MWID
AND DATEPART(ISO_WEEK,i.BEGINDATUM)= DATEPART(ISO_WEEK,o.BEGINDATUM)
AND i.[Org eenheid code] = o.[Org eenheid code]
AND i.FlexVast = 'VAST')
)
UPDATE Updater SET FTEHrsAVG = NewFTEHrsAVG



You might not need the EXISTS check, it depends on what you want to do with unaffected partitions.
If the number of affected rows is relatively high and unaffected partitions should have FTEHrsAVG set to NULL, then try without the EXISTS check - it should be very quick.


That is amazing. Would you perhaps have a reference to other examples of CTE usage like these?


Thanks. That's why I lurk here - to learn stuff like this. It's what I'd recommend to you too. When something interesting or new comes up, copy it and pull it apart locally to see how it works - and if you can improve on it.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Bouke Bruinsma
Bouke Bruinsma
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 603
I totally agree. I pick up something useful everytime I browse around the forums. And I could give quite a bit of examples of practical usage in my work of stuff I learned here.
So yeah, i'll be lurking the forums.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search