Slow insert into table variable.

[Durkin]

Hi. I'm writing a proc in which I want to create a temporary table and insert into it using a select statement. The problem is that it takes twice as long to insert into the table variable as it does to run the select. This proc needs to have good performance so something like this is not really acceptable.
Any ideas?

Durkin

 

[vongrunt]

How many rows?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Durkin]

One of the tables has 5 million plus.

Durkin

 

[vongrunt]

Table variable with 5+M rows?

This doesn't work instantaneously - and can exhaust server memory. Is that the case here?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Durkin]

Sorry. Table variable should have about 15000 in the case I'm looking at. This is our most performance problem prone user. He's a member of many groups in the system.
One of the tables in the select has more the 5m rows.

Durkin

 

[vongrunt]

There is a good chance problem is not in table variable - unless it has text/ntext columns or something. Personally I'd focus on query optimization first.

Btw. what happens if you replace table variable with #temp table? This should be slower...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Durkin]

The query itself is fine. If I time the insert using the select statement and then time the select statement on it's own the insert is much slower. The table variable only contains an int.

Durkin

 

[hmckillop]

Have you looked at perf counters to see whats going on with IO, memory etc.
As vongrunt says, put the data into a #temp table and see what happens.
If a table variable starts to take up too much memory it will automatically write to a physical temp table, in which case its best having this from the start.
Also 15,000 rows in a temp variable seems excessive. DO you need to process them all?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[Durkin]

Figured it out. The table is being filled from a data migration from an older version of the app. There's a nasty bug in the older app which I found because of this issue where that big table is getting larger exponentially. The table should really only have seven hundred thousand rows. I've cleared it down and the proc runs like a (fairly fast) dream;-)
Thanks for your help.

Durkin