Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

This Query is Filling up TEMPDB Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2014 10:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 2:03 PM
Points: 38, Visits: 595
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;
Post #1590429
Posted Tuesday, July 8, 2014 10:52 AM This worked for the OP Answer marked as solution


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 4,046, Visits: 9,202
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1590435
Posted Tuesday, July 8, 2014 10:59 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 3:58 PM
Points: 646, Visits: 2,994
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1590437
Posted Tuesday, July 8, 2014 11:03 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:26 AM
Points: 10,381, Visits: 13,436
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

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
Post #1590438
Posted Tuesday, July 8, 2014 12:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 20,861, Visits: 32,887
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.



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)
Post #1590457
Posted Tuesday, July 8, 2014 12:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 20,861, Visits: 32,887
Guess I should of checked before posting since I got distracted by a code walk through with my tech lead.



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)
Post #1590458
Posted Tuesday, July 8, 2014 2:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 2:03 PM
Points: 38, Visits: 595
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.
Post #1590522
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse