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?

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.

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?

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