Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ravisankar
Starting Member
2 Posts |
 Posted - 2012-03-19 : 06:13:18
|
| Hi,I have a table with 150 Columns. I want to insert bulk data into this table. I have tried with single insert statement having multiple values as well as single insert statement using SELECT and UNION ALL. Its taking almost 10 seconds to insert 1000 Records. Will it take 10 seconds to 1000 records normally (for the table having 150 columns)?Ravi |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-19 : 07:44:58
|
| In most cases, it should not take 10 seconds to insert 1,000 records. If the table is being locked another spid, this might happen. You can try to find blocking processes if that indeed is the case, but, the easiest smoke test would be to create a temporary table with the same columns as your target table and trying to insert into that table. If that goes much faster, then investigate further on locking issues.If the data that you inserting is very large (giga bytes of XML data, for example), then again this could happen. If you have multiple indexes on an XML column that can slow down the insertion process as well. |
 |
|
|
NarenSQLTeam
Starting Member
2 Posts |
Posted - 2012-03-19 : 08:22:21
|
| If the table has indexes.. I suggest to drop and insert the data and re-create the index. Another approach is to create a staging table with the same metadata and the initial insert would be into staging table.Also..would like to know whether you are directly inserting the data or using some additional logic while inserting the data i.e. If exists then insert.. |
|
|
|
Ravisankar
Starting Member
2 Posts |
Posted - 2012-03-20 : 03:42:02
|
| Hi,Thanks for your suggestions. I had one nonclustered index on the original table. I have created a temporary table with the same structure of the original table and without any index. I have tried to insert the data into this table. still it was taking 10 seconds per 1000 records. I am creating the dynamic insert statement using VBA and executing the query using ADODB connection. Its taking more time for creating execution plan. If i execute the same query again its getting completed in 1 second. Is there any way to keep the execution plan for dynamic queries?Thanks in AdvanceRavi |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-20 : 07:22:43
|
| You might consider using prepared statements - see description and an example here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare(v=vs.100).aspxNormally I tend to create a stored procedure that contains all the SQL statements that I want to execute and then would call the stored proc from the .Net code. That would also allow plan reuse. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-03-20 : 09:54:46
|
| There are bulk import tools that are fast - but that assumes that your data is in CSV or somesuch. If it is coming from application then probably no help.Might be better to pass the 1,000 records as XML and import from XML using SQL (i.e. server-side) so that it is a SET-based operation |
|
|
|
|
|
|
|
|
Performace issue - SQL Server 2008 Insert