SQLTeam.com | Weblogs | Forums

Adding additional fields taking long time to insert


#1

Hello,

I am trying to insert some data in a table which consists of 40 fields. When i try to insert only 20 fields, it takes less than a minute but when i try to load all the 40 fields, it takes more than 20 min. Any ideas? This is a SELECT INTO.


#2

hard to say without knowing more. Can you post your table definition? Also, how many rows are we talking about? Does your table have any foreign keys in it?


#3

There is not sufficient information in what you posted to determine what might be causing the slow performance. Couple of things to check:

a) If you did not use SELECT INTO, and instead simply selected the 40 columns, does it take longer than selecting the 20 columns?

b) What is in the second 20 columns? If they are large value data types it might require off-row storage which could result in slow performance.

c) Does the issue scale? What I mean is, if your select had only 200 rows, is the performance similar between 20 columns and 40 columns?


#4

Yeah, i forgot to specify couple of things...

The table will have 1 million records.

a.) The SELECT(Just SELECT) takes less than 30 seconds.

b.) They are all different data types and mostly datetime and varchar(128)

c.) yeah, the SELECT has no issues whether it is 20 or 40 columns. the results show up in less than a sec and the whole query completes in less than 30 sec.


#5

OK -- any FKs in the last 20 columns? Lookup cost?


#6

no, inserting into a table which does not have any keys at all. Not even a PK...the performance might be slow because of no PK..but why only for extra fields.


#7

For one thing, if at all possible, explicitly lock the table being inserted into, as below. That will allow minimal logging, which could speed it up a lot.

INSERT INTO table_name WITH (TABLOCK)
SELECT ...

Also, be sure to pre-allocate enough log space to handle the INSERT. SQL will dynamically increse the log space if needed but that can be very slow.


#8

the table is a temp table and its a brand new table(no one even know that this table exist because this is a temp table used in the process and gets dropped after the process). Log space = we have may be more than 300 gig of free log space.