TempDB

Question

TempDB

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 1

Understood... but you have no criteria for the table... that makes a cross-join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110207 More actions · Answer 2

There is in fact cross-join between D1 and D2.

I believe they both contain almost complete list of students, so matching criteria are not effective. Criteria like "" cannot use index, so it leads to table scans as well.

So optimizer chooses to join everything to everything and filter out the result.

This intermediate result table is stored in your TempDB and overloads it.

_____________
Code for TallyGenerator

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

A method to make it easier to avoid such accidental crossjoins etc, is to abandon the legacy join syntax used here, and rewrite it using ANSI join syntax.

/Kenneth

Heinz Behrendt SSC Enthusiast Points: 134 More actions · Answer 4

Heinz Behrendt

SSC Enthusiast

Points: 134

September 12, 2007 at 8:43 am

#733144

Thank you guys for your help.

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 5

Heh... or just pay attention to what you're doing Either way, a trip to the estimated execution plan probably would have shown the bazillions of rows this was going to generate internally.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Well, 'paying attention' only gets you that far, and going ANSI lessens that burden somewhat.

Apart from that, the legacy join syntax is way overdue by now, and really shouldn't be used anywhere.

Especially not when writing new code.

In SQL Server 2005 it's already halfway to total deprecation. Inner joins do still work, but if you suddenly need an outer join, that won't work anymore.

So I still recommend, switch style to ANSI.

It's easier to read, easier to understand, easier to write, and it's more predictable (regarding outer joins) how it works.

/Kenneth