Insert into table variable is slow

Question

I have a dynamic sql in stored procedure like

@sql = 'SELECT a, 
       b, 
       c d 
FROM   table t 
       LEFT JOIN table2 t2 
              ON t.id = t2.id '

and then inserting into a table variable

Insert into @tblCustomer

EXECUTE sp_executesql @sql

The insert statement is taking long time to execute. Is there any way i can improve it?

I am using SQL Server 2008

Thanks

Have you tried researching anything? Do you have indexes? Show us *something*. — Kermit, Mar 28 '13 at 18:21
Is this just an example or why do you do this? There is a static SQL select statement that you execute, fetch the results and insert this into a table. Why so complicated? — YvesR, Mar 28 '13 at 19:48
why on earth would you be using dynamic sql for that? Dynamic Sql should be avoided if at all possible. — HLGEM, Mar 28 '13 at 21:47

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

though you cannot do much(simple sql query).

try using temp table instead

CREATE TABLE #TempTable(
 ID int,
 col2 <datatype>,
 col3 <datatype>)

and insert into it

see if it works faster. read more about it here

try indexing your database. it should give you visible performance boost.

edited May 23 '17 at 12:04

Community

1
1

answered Mar 28 '13 at 18:21

Manish Mishra

11,823
5
27
57

I think Select into #temptable will be faster option – rs. Mar 28 '13 at 18:26

score 0 · Answer 2 · edited Mar 29 '13 at 16:40

0

Try the following query :

@sql = SELECT a, b, c, d 
FROM table t (NOLOCK) LEFT JOIN table2 t2 (NOLOCK) ON t.id = t2.id

edited Mar 29 '13 at 16:40

Aleksandr Fedorenko

15,874
6
36
42

answered Mar 28 '13 at 20:31

Dev

1
1

score -2 · Answer 3 · answered Mar 28 '13 at 18:23

-2

It seems to mee that it is impossible to optimize. The query is straight forward. Temp tables if used may slow the process even more due to memory use.

answered Mar 28 '13 at 18:23

user1880670

152
2
2
10

Insert into table variable is slow

3 Answers3