I saw a question the other day, questioning why they’re creation of temporary tables was so slow. What they were doing was (a much more complicated version of) this:
SELECT soh.SalesOrderID,
sod.SalesOrderDetailID,
soh.SalesOrderNumber
INTO #MyTempTable
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE 1 = 0;Now, my immediate response, and no, I didn’t type it, was, “Hey, you’re not “creating” temporary tables. You’re using SELECT…INTO.”
Let’s be fair. That is a method to create temporary tables. Also, that method has some advantages. Biggest one being, you don’t have to know, or define, the data structure. You get it for free.
It does come down to one thing though. Why is “WHERE 1=0” slow?
WHERE 1=0
Math may be weird these days, but in good old SQL Server, one (1) does not equal zero (0). Period.
So, our query above will not return any rows. So why is it slow? Well, let’s change gears a little. Here’s another query:
CREATE TABLE #MyTempTable
(
SalesOrderID INT,
SalesOrderDetailID INT,
SalesOrderNumber NVARCHAR(25)
);And this is the execution plan from that query:
And here is the execution plan from the INSERT…SELECT query:
Now, what you didn’t get was a plan for the SELECT part of the query. Why? Because SQL Server knows that 1=0 is going to result in no rows. Instead, it builds the table of constants, that’s what a Constant Scan represents, which is just placeholders for columns. If you look, the output of the Constant Scan is this:
It’s just defining the data that would be inserted, if data was to get moved. However, since no data is being moved, all you need is what you see. It’s still running an INSERT, but for zero data. Performance for the SELECT…INTO is about 3.3ms with 198 reads on average. The simple CREATE query is 2.5ms on average with 145 reads.
Why is WHERE 1=0 slower? It’s doing more work.
Conclusion
Yeah, I know this one is easy to see, but you’d be surprised. People just think that everything gets figured out behind the scenes such that, two approaches, both with identical results, are done the same way. However, as we see above, that’s just not true. All approaches, even if they end in the same results, are just not equal. Is that inconvenient? Yeah, maybe. However, it’s still true.
When I juust need the table structure I use “SELECT TOP 0 …”. It’s still a fudge however.
select top 0 has always been my favorite kludge to get a structure built without any data. Not sure, performance-wise what its impact is though.
I suspect, like the 1=0, there’s still going to be a tiny amount of overhead.
I’m guilty of this. It’s usually someone sees it in source control and says “why are you doing 1=0 or TOP 0 here”. The answer is always “oops, my goal was to do that the right way but first I just wanted to see if I could use a temp table to make this process faster. Sorry about that…now, would you mind fixing that for me? I’m quite lazy”.
I’m glad you pointed this out, it’s a lazy short-hand and folks tend to think they are being “cute” and “smart” when they do it. Nope, it’s really just lazy.
Let’s not go too far. It’s efficient on one level, and not on another. In the example here, the difference is just under a millisecond. I’m not saying never do it. Just to do it with knowledge of the implications.
I suppose Grant means that it’s efficient on one level because it saves a tiny bit on maintenance. If the column definitions in the source tables are ever changed, the temporary table will adapt automatically, while the 1=0 makes sure that we won’t actually insert anything.
I’m the sort to dither over which approach is preferable…
I agree. Dither. Just know that one approach has some added overhead over the other.
I have to admit I will use a WHERE 0=1 AND followed by new lines of predicates all beginning with a comma for times when I am interactively exploring data and shuffling predicates in and out of the query. That way they can all begin with a comma. After doing PowerShell recently I’ve had to break the leading comma habit but sometimes include a bogus last item in a list just so all the items I am tinkering with above can terminate in commas. Don’t claim to know what is right or wrong which is why I enjoy these discussions and always learn something. 🙂
That’s the whole idea. Share the knowledge around.
It’s been a while since I used SQL Server. I wonder what impact SET FMT ONLY would have
After I looked it up, because I had no idea what it was, I ran a couple of quick tests. Basically, the temp table didn’t get created. So, it’s hard to compare it to the CREATE table one since nothing happened.
Grant,
It’s a great point and the conclusion is correct. (And thanks for writing this stuff; I’m sure I’ve learned things in the past)
I’m concerned a little for the less experienced programmer who may misunderstand, as I think the headline here is more provocative than accurate, ultimately blurring the point somewhat when we get to the end.
If we saw that SELECT … INTO … WHERE 1=0 was slower than SELECT … INTO … WHERE 1=1 I’d hear the question as written. But I doubt we’d find that that’s the case, because then most likely we’d also be writing data into the table.
The summary which would make sense is:
Why is SELECT … INTO slower? It’s doing more work.
The point you want to make is has got a bit lost. If I may:
As you stated, there are two ways (at least) of creating a table.
CREATE TABLE declares the table structure explicitly.
SELECT … INTO (whatever the WHERE has in it) declares it implicitly. Even if the DB engine doesn’t have to go looking for the data itself, it still has to work out what shape that data would take! You have shown us that it does the job efficiently (as we would hope), but it still shouldn’t be a surprise that this method takes longer than the other one. As you say, though, to some people it is.
Sorry that wasn’t completely clear. More than just show you that something that does more work has more overhead, I also hope I’ve shown a little bit of how to investigate this kind of thing. Everyone should be able to pull up an execution plan and slap on Extended Events to see the results for a comparison as to which approach is best.
So 3.3ms is slow? I’d like to take a look what kind of beast your production server is then 🙂
Ha! Well, the original query was WAY more complicated and ran much slower. But you can see a 25% improvement just on these tiny example queries.
I think the point is, how often it is executed. Imagin it is inside a procedure which is called 1,000 times per second…
Or maybe worser: a frontend developer takes his first steps on SQL and writes a while-loop and creates / drops the table for 10k iterations…
Oh yea.
That would be a problem.