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


This Query is Filling up TEMPDB


This Query is Filling up TEMPDB

Author
Message
JeepHound
JeepHound
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1248
I am very new to SQL server Querying. I see the following Query causing my tempdb's to fill up dramatically in our production environment. Can someone please explain why the code looks differently than I have been trained? Can you explain why it is causing the TEMPDB to fill up dramatically. Can you please recommend how to write it properly with an explanation of the changes?

select DISTINCT B0.PARTNER, B0.NAME_ORG1, B0.NAME_ORG2
from pvc.CRMD_TERR_ACCREL TA, pvc.BUT000 B0, pvc.CRMM_TERRITORY ct, pvc.CRMM_BUT_LNK0031 CB,
pvc.HRP1000 HP, pvc.CRMD_TERR_LINK TL, pvc.BUT100 B1
WHERE TA.PARTNER_GUID = B0.PARTNER_GUID
and TA.TERR_GUID = ct.TERR_GUID
AND B0.PARTNER_GUID = CB.PARTNER_GUID
AND CB.SALES_ORG = HP.OTJID
AND TA.RULE_ID = TL.RULE_ID
AND B0.PARTNER = B1.PARTNER
AND TA.VALID_TO = '99991231'
AND ct.TERR_ID = '00099'
AND B0.XBLCK <> 'X'
AND B1.RLTYP ='ZFODLR' or B1.RLTYP ='ZINDLR'
ORDER BY B0.NAME_ORG1 ASC;
Luis Cazares
Luis Cazares
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22207 Visits: 19355
The reason might be the DISTINCT which has to sort all the values to eliminate duplicates. Another reason might be the OR you have in there which might be causing your query to return more rows than needed as it will return all rows where B1.RLTYP = 'ZINDLR' regardless of the other conditions.
You have more tables than the ones that you're using and that might make the query to return duplicate rows. I'm not sure if you can remove those tables or if you need them to filter some values.
I rearranged your query to use ANSI-92 Joins and I suggest you to use them to mantain a standard join when you need to use outer joins.

SELECT DISTINCT B0.PARTNER
,B0.NAME_ORG1
,B0.NAME_ORG2
FROM pvc.CRMD_TERR_ACCREL TA
JOIN pvc.BUT000 B0 ON TA.PARTNER_GUID = B0.PARTNER_GUID
JOIN pvc.CRMM_TERRITORY ct ON TA.TERR_GUID = ct.TERR_GUID
JOIN pvc.CRMM_BUT_LNK0031 CB ON B0.PARTNER_GUID = CB.PARTNER_GUID --Not used
JOIN pvc.HRP1000 HP ON CB.SALES_ORG = HP.OTJID --Not used
JOIN pvc.CRMD_TERR_LINK TL ON TA.RULE_ID = TL.RULE_ID --Not used
JOIN pvc.BUT100 B1 ON B0.PARTNER = B1.PARTNER
WHERE TA.VALID_TO = '99991231'
AND ct.TERR_ID = '00099'
AND B0.XBLCK <> 'X'
--AND (B1.RLTYP = 'ZFODLR' OR B1.RLTYP = 'ZINDLR')
AND B1.RLTYP IN('ZFODLR', 'ZINDLR') --Same as above
ORDER BY B0.NAME_ORG1 ASC;




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Alan.B
Alan.B
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6992 Visits: 7829
JeepHound (7/8/2014)
I am very new to SQL server Querying. I see the following Query causing my tempdb's to fill up dramatically in our production environment. Can someone please explain why the code looks differently than I have been trained? Can you explain why it is causing the TEMPDB to fill up dramatically. Can you please recommend how to write it properly with an explanation of the changes?

select DISTINCT B0.PARTNER, B0.NAME_ORG1, B0.NAME_ORG2
from pvc.CRMD_TERR_ACCREL TA, pvc.BUT000 B0, pvc.CRMM_TERRITORY ct, pvc.CRMM_BUT_LNK0031 CB,
pvc.HRP1000 HP, pvc.CRMD_TERR_LINK TL, pvc.BUT100 B1
WHERE TA.PARTNER_GUID = B0.PARTNER_GUID
and TA.TERR_GUID = ct.TERR_GUID
AND B0.PARTNER_GUID = CB.PARTNER_GUID
AND CB.SALES_ORG = HP.OTJID
AND TA.RULE_ID = TL.RULE_ID
AND B0.PARTNER = B1.PARTNER
AND TA.VALID_TO = '99991231'
AND ct.TERR_ID = '00099'
AND B0.XBLCK <> 'X'
AND B1.RLTYP ='ZFODLR' or B1.RLTYP ='ZINDLR'
ORDER BY B0.NAME_ORG1 ASC;


It would be helpful if you could post some DDL and include the query plan this query is creating. See this article for more details:
How to Post Performance Problems

Based on what you have posted, two possible problems is that DISTINCT clause and the ORDER BY. Each of these will cause a sort operation in your query plan which can be very expensive and can cause the tempdb to grow. Is the sort required?
Does removing that ORDER BY speed up the query?

If the ORDER BY is required you may benefit from a covering index; it would look something like this:


-- Keeping in mind that you are only returning data from pvc.BUT000 and sorting by NAME_ORG1
CREATE NONCLUSTERED INDEX nc_blahblah_covering ON pvc.BUT000 (NAME_ORG1)
INCLUDE (PARTNER, NAME_ORG1, NAME_ORG2)



Those are my initial thoughts...



Edit: note - I had not seen Louis' response when I posted this.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23842 Visits: 14905
I agree with what Luis and Alan have already shared. Anything that requires ordering (the distinct and order by) can spill to tempdb.

Also logically your query is this:


SELECT DISTINCT
B0.PARTNER,
B0.NAME_ORG1,
B0.NAME_ORG2
FROM
pvc.CRMD_TERR_ACCREL TA,
pvc.BUT000 B0,
pvc.CRMM_TERRITORY ct,
pvc.CRMM_BUT_LNK0031 CB,
pvc.HRP1000 HP,
pvc.CRMD_TERR_LINK TL,
pvc.BUT100 B1
WHERE
(TA.PARTNER_GUID = B0.PARTNER_GUID AND
TA.TERR_GUID = ct.TERR_GUID AND
B0.PARTNER_GUID = CB.PARTNER_GUID AND
CB.SALES_ORG = HP.OTJID AND
TA.RULE_ID = TL.RULE_ID AND
B0.PARTNER = B1.PARTNER AND
TA.VALID_TO = '99991231' AND
ct.TERR_ID = '00099' AND
B0.XBLCK <> 'X' AND
B1.RLTYP = 'ZFODLR') OR
B1.RLTYP = 'ZINDLR'
ORDER BY
B0.NAME_ORG1 ASC;



And I think you might really want this:

SELECT DISTINCT
B0.PARTNER,
B0.NAME_ORG1,
B0.NAME_ORG2
FROM
pvc.CRMD_TERR_ACCREL TA,
pvc.BUT000 B0,
pvc.CRMM_TERRITORY ct,
pvc.CRMM_BUT_LNK0031 CB,
pvc.HRP1000 HP,
pvc.CRMD_TERR_LINK TL,
pvc.BUT100 B1
WHERE
TA.PARTNER_GUID = B0.PARTNER_GUID AND
TA.TERR_GUID = ct.TERR_GUID AND
B0.PARTNER_GUID = CB.PARTNER_GUID AND
CB.SALES_ORG = HP.OTJID AND
TA.RULE_ID = TL.RULE_ID AND
B0.PARTNER = B1.PARTNER AND
TA.VALID_TO = '99991231' AND
ct.TERR_ID = '00099' AND
B0.XBLCK <> 'X' AND
(
B1.RLTYP = 'ZFODLR' OR
B1.RLTYP = 'ZINDLR'
)
ORDER BY
B0.NAME_ORG1 ASC;





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50938 Visits: 38665
First, I reformatted the code to use ANSI-92 style joins instead of ANSI-89 style joins. Did this to verify that there was no accidental cross join due to a missing join.



select DISTINCT
B0.PARTNER,
B0.NAME_ORG1,
B0.NAME_ORG2
from
pvc.CRMD_TERR_ACCREL TA
INNER JOIN pvc.BUT000 B0
on (TA.PARTNER_GUID = B0.PARTNER_GUID)
INNER JOIN pvc.CRMM_TERRITORY ct
on (TA.TERR_GUID = ct.TERR_GUID)
INNER JOIN pvc.CRMM_BUT_LNK0031 CB
on (B0.PARTNER_GUID = CB.PARTNER_GUID)
INNER JOIN pvc.HRP1000 HP
on (CB.SALES_ORG = HP.OTJID)
INNER JOIN pvc.CRMD_TERR_LINK TL
on (TA.RULE_ID = TL.RULE_ID)
INNER JOIN pvc.BUT100 B1
on (B0.PARTNER = B1.PARTNER)
WHERE
TA.VALID_TO = '99991231' AND
ct.TERR_ID = '00099' AND
B0.XBLCK <> 'X' AND
B1.RLTYP ='ZFODLR' or
B1.RLTYP ='ZINDLR'
ORDER BY
B0.NAME_ORG1 ASC;




Next, the WHERE clause is interesting since there is an OR in there. As written all rows where B1.RLTYP = 'ZINDLR' will be returned as part of the result set.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50938 Visits: 38665
Guess I should of checked before posting since I got distracted by a code walk through with my tech lead.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
JeepHound
JeepHound
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1248
The parenthesis around the "or" is what did it. I talked to a senior guy here and you both simultaneously came up with the same answer. Thanks for the help.
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