CODEWITHSUNDEEP
sqlsql-server
Damian Jacobs
Damian Jacobs

Reputation: 538

SQL best practice/performance when inserting into a table. To use a temp table or not

I have a select query that's come about from me trying to remove while loops from an existing query that was far too slow. As it stands I first select into a temp table.

Then from that temp table I insert into the final table using the values from the temp table.

Below is a simplified example of the flow of my query

select 
    b.BookId, 
    b.BookDescription, 
    a.Name,
    a.BirthDate, 
    a.CountryOfOrigin,
into #tempTable
from library.Book b
left join authors.Authors a
on a.AuthorId = b.AuthorId

insert into bookStore.BookStore
    ([BookStoreEntryId]
    [BookId],
    [BookDescription],
    [Author],
    [AuthorBirthdate],
    [AuthorCountryOfOrigin])
select 
    NEWID(),
    t.BookId,
    t.BookDescription,
    t.Name,
    t.Birthdate,
    t.CountryOfOrigin

from #tempTable t

drop table #tempTable

Would it be better to move the select statement at the start, to below so that its incorporated into the insert statement, removing the need for the temp table?

Upvotes: 0

Views: 1843

Answers (2)

T.S.
T.S.

Reputation: 19350

Do in 1 step

insert into bookStore.BookStore
    ( /* [BookStoreEntryId] <-- assuming this is auto id*/ 
    [BookId],
    [BookDescription],
    [Author],
    [AuthorBirthdate],
    [AuthorCountryOfOrigin])
SELECT distinct 
    b.BookId, 
    b.BookDescription, 
    a.Name,
    a.BirthDate, 
    a.CountryOfOrigin,
from library.Book b
left join authors.Authors a
on a.AuthorId = b.AuthorId

your performance will depend on number of indexes in the target table. More indexes - slower insert. May be worth to disable them during insert and then rebuild them after the insert is completed

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

There is no advantage at all to having a temporary table in this case. Just use the select query directly.

Sometimes, temporary tables can improve performance. One method is that a real table has real statistics (notably the number of rows). The optimizer can use that information for better execution plans.

Temporary tables can also improve performance if they explicit have an index on them.

However, they incur overhead of writing the table.

In this case, you just get all the overhead and there should be no benefit.

Actually, I could imagine one benefit under one circumstance. If the query took a long time to run -- say because the join required a nested loops join with no indexes -- then the destination table would be saved from locking and contention until all the rows are available for insert. That would be an unusual case, though.

Upvotes: 1

Related Questions