Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Speed up insert

 
   Database Forums (Home) -> General Discussions RSS
Next:  static vs global variable  
Author Message
OM

External


Since: Feb 28, 2011
Posts: 1



(Msg. 1) Posted: Mon Feb 28, 2011 6:06 pm
Post subject: Speed up insert
Archived from groups: comp>databases>ms-sqlserver (more info?)

Hi,

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/

Thanks

 >> Stay informed about: Speed up insert 
Back to top
Login to vote
Erland Sommarskog2

External


Since: May 30, 2004
Posts: 1658



(Msg. 2) Posted: Tue Mar 01, 2011 3:25 am
Post subject: Re: Speed up insert [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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, esquel.RemoveThis@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 
Back to top
Login to vote
omtechguy

External


Since: Mar 01, 2011
Posts: 1



(Msg. 3) Posted: Tue Mar 01, 2011 12:17 pm
Post subject: Re: Speed up insert [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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... RemoveThis @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 
Back to top
Login to vote
Erland Sommarskog2

External


Since: May 30, 2004
Posts: 1658



(Msg. 4) Posted: Wed Mar 02, 2011 12:25 am
Post subject: Re: Speed up insert [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
Access and SQL Server Speed Comparison - Hi, I have a table with 8 millions records. Some of the fields needed to be parse and modified. Example, "ABC DEF" will be parsed into two fields "ABC" and "DEF". And If there is "123" in the field, if will be ...

Query Optimization: CPU speed or Logical Reads better? - How do I determine which method I should use if I want to optimize the performance of a database. I took Northwind's database to run my example. My query is I want to retrieve the Employees' First and Last Names that sold between $100,000 and $200,000....

ROWTERMINATOR in bulk insert query. - We have created CSV files on HPUX 11.0 and transferred them via ASCII ftp to our SQL Server machine file store to load large amounts for data using the BULK INSERT command. This is the command: BULK INSERT db..table FROM 'S:\path\filename.csv' WITH ( ...

error handling and bulk insert - I do not understand the error handling of SQL Server here. Any error in bulk insert seems to halt the current T-SQL statement entirely, rendering it impossible to log an error. The first statement below executes as expected, and were I to replace..

how to call a stored procedure in an insert command -
   Database Forums (Home) -> General Discussions All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]