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


Slow self join


Slow self join

Author
Message
JJR333
JJR333
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 277
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
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28107 Visits: 39939
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
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 864
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
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15050 Visits: 18597
In this case I would consider using a temporary table
Cool
JJR333
JJR333
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 277
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 Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 277
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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2038 Visits: 10361
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
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 598
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16182 Visits: 19543
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
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 598
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