July 8, 2014 at 10:25 am
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;
July 8, 2014 at 10:52 am
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;
July 8, 2014 at 10:59 am
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[/url]
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.
-- Itzik Ben-Gan 2001
July 8, 2014 at 11:03 am
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
Consultant - Straight Path Solutions
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
July 8, 2014 at 12:03 pm
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.
July 8, 2014 at 12:04 pm
Guess I should of checked before posting since I got distracted by a code walk through with my tech lead.
July 8, 2014 at 2:19 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply