0

Is it best practice and/or is there any performance benefits of creating temporary tables at the beginning of a SQL query versus as they're needed?

I checked an execution plan and the create table pieces of the query were not listed.

Here's an example of the 2 scenarios: (just an example of the layout, not real query)

-- Create temp tables as needed.
CREATE TABLE #TempA (test int, ...);
INSERT INTO #TempA (test, ...)
SELECT test, ... 
FROM TableA;

CREATE TABLE #TempB (test int, ...);
INSERT INTO #TempB (test, ...)
SELECT test, ... 
FROM TableB;

CREATE TABLE #TempC (test int, ...);
INSERT INTO #TempC (test, ...)
SELECT test, ... 
FROM TableC;

DROP TABLE #TempA;
DROP TABLE #TempB;
DROP TABLE #TempC;


--Versus - Create temp at beginning of query
CREATE TABLE #TempA (test int, ...);
CREATE TABLE #TempB (test int, ...);
CREATE TABLE #TempC (test int, ...);

INSERT INTO #TempA (test, ...)
SELECT test, ... 
FROM TableA;

INSERT INTO #TempB (test, ...)
SELECT test, ... 
FROM TableB;

INSERT INTO #TempC (test, ...)
SELECT test, ... 
FROM TableC;

DROP TABLE #TempA;
DROP TABLE #TempB;
DROP TABLE #TempC;
clovola
  • 378
  • 1
  • 14
  • 1
    https://stackoverflow.com/questions/18321245/temporary-table-in-sql-server-causing-there-is-already-an-object-named-error/18323248#18323248 – granadaCoder Aug 14 '20 at 18:00

2 Answers2

1

First, I don't advocate creating temporary tables unnecessarily. SQL Server has a good optimizer, so it should be able to handle complicated queries.

If you are using temporary tables, then the only performance advantage is when something fails. If the failure is in creating a temporary table, then having them up front is good because you'll get the error quickly. If the failure is in the rest of the code, then having all table creations first takes longer.

There might be a small gain to creating the tables just when they are needed if the tables are big and you are in a memory limited environment. However, if you are memory limited, the expense of reading the data is probably pretty small compared to the overall processing.

But all this said, you may not need temporary tables at all.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This was just an example the actual query is more robust and I do need the temporary tables. These are both good points I hadn't considered though, thank you. – clovola Aug 14 '20 at 16:04
1

it depends..... since the scenario you gave doesn't perform real work, this doesn't count (and in you're scenario, i don't think one scenario will perform better than the others.)

By "creating a temp table", i assume you mean "creating and filling a temp table". Generally speaking, the create table isn't a bottleneck if there's no lock.

in a real world scenario, it would depends of your script / SP. if you have some conditional logic ( if / while / ...), it is better to fill the temp table as they are needed, otherwise, you ask SQL Server to perform work that is not necessary.

MLeblanc
  • 1,816
  • 12
  • 21