Speed up insert

SQL 2005, The data comes from other tables on the same DB.

On 1 מרץ, 04:55, Erland Sommarskog wrote:
> OM ( ) writes:
> > is there an option to increase the time of inserting 1M rows to a
> > table using stored procedure?
>
> > Already removed all the indexes and the database is on Raid 10/
>
> There are certainly several options. BULK INSERT or BCP is probably
> the fastest. But you say "with a stored procedure". Where does the
> data come from? How does your current solution look like? And which
> version of SQL Server are you using?
>
> --
> Erland Sommarskog, SQL Server MVP, esq... DeleteThis @sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
 >> Stay informed about: Speed up insert 

omtechguy ( ) writes:
> SQL 2005, The data comes from other tables on the same DB.

Pity you are not on SQL 2008, since INSERT can be minimally logged on SQL
2008.

There are not really that many options, but it may help to do it in batches
of 10000, 50000 or 100000 rows. It is essential that if you batch, that you
select the batches so that the selection itself does not take too much
time.

But then again, maybe that is the problem? That is, it is not the insertion
that actually takes time, but it is the selection of it. Could you post an
example of your procedures?



--
Erland Sommarskog, SQL Server MVP, esquel.DeleteThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
 >> Stay informed about: Speed up insert