Insert Statement slow

I have a query of 40,000 record. I need to insert them into a new database.

Generating the 40,000 insert statements and running them is taking ages to execute. Any idea how I can speed up the insert process

The database is Microsoft SQL server

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:

You can directly insert into your table from the query...

Insert into table (col1,col2...)
select col1, col2 from .......

And generally the Insert takes time when the destination table have more indexes......
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try use BULK INSERT (but you need to set permission before running it), if you're importing records from a different source.
ramziabkAuthor Commented:
I cant use Bulk insert or BCP as the source database is Sybase and I'm inserting into Microsoft SQL.

That's why I'm generating the Insert statements and then applying them in MS SQL.

Note that initially it was taking less then 30 seconds. Out of a sudden, it start taking 30 minutes and more!!!!

I checked the destination table and found that no indexes or constraints available.
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
not too sure if should I recommend you to review your current solution architecture but you may read through this article and see there's anything you can improve.

SQL Server Migration Assistant (SSMA) for Sybase is a comprehensive environment that helps you quickly migrate Sybase Adaptive Server Enterprise (ASE) databases to SQL Server or Azure SQL DB.

Migrating Sybase ASE Databases to SQL Server/Azure SQL DB (SybaseToSQL)
Éric MoreauSenior .Net ConsultantCommented:
Is it the select that takes a long time or is it really the Insert?

Do you have countless indexes and triggers in the table you want to insert?
ramziabkAuthor Commented:
The select statement runs on SYBASE and it takes seconds. The result of the select statement using DBArtisian generates insert statements.

Then these insert statements are copied into SQL server for execution.

No triggers, indexes or any constraints on the tables. I even uninstalled SQL server and reinstall it only with one table and ran the insert and it took ages.

Note that the number of records being inserted doesn't exceed 30,000 records.
Éric MoreauSenior .Net ConsultantCommented:
From what you are saying, there is no reason to go from 30s to 30m.

Could you create a new table in a new database to insert the data in to be sure no constraints are applied to the columns?
ramziabkAuthor Commented:
Well I did that. I dropped the database and created a new one. I ran the insert with the same delay!

I tried to run the query on a different server but same specs, it took less than a minute. I wonder if this has to do with the server itself?
Éric MoreauSenior .Net ConsultantCommented:
other queries on the same server?
ramziabkAuthor Commented:
The same very slow
Éric MoreauSenior .Net ConsultantCommented:
so it seems you narrowed it down to the server installation! can't really say what it is without having physical access to the server.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeCommented:
Couple of thoughts...
Show us the code/process of how you're inserting these rows.  Perhaps there's something going on there, such as it's locking the destination, that's causing the problem.
Are you inserting into a single table, or a view with multiple tables? Views that include multiple tables may not be insertable/updateable.
Do you have the rights to insert, and if this is a view rights to insert into either the view or all tables in the view?
(Eric's point) How many indexes are we talking?  For example, if there are ten indexes on a single table, inserting a row will actually insert into 11 different places.
Are there other processes going on during this insert that would be locking tables and preventing the insert?  Conversely, does your insert lock out other users?
Jim HornMicrosoft SQL Server Data DudeCommented:
Keep in mind that experts here are both not connected to your data source and not mind readers, so 'my stuff is slow' is not the most actionable question for us.  You'll need to provide more details.
Anthony PerkinsCommented:
Another possibility is that the data file or Transaction log is full and it is having to grow in (God forbid) 10% increments.
ramziabkAuthor Commented:
I can provide you with remote access if that would help.

Just let me rephrase my incident. Seems I wasn't clear enough.

Simply I have couple of insert statements (20,000 records). I want to execute these insert statements into Microsoft SQL database 2008.

Running these statements used to take 41 seconds exactly. Now it is taking more than 30 minutes.

I copied these same statements to a separate server and it took 41 seconds.

I thought it might be a corruption in MS SQL installation. So I've uninstalled MS SQL and reinstall it again with the same delay!!!

Hope I'm clear now
ramziabkAuthor Commented:
The error was caused by hardware failure on one of the hosts as I'm running virtual machines
Anthony PerkinsCommented:
ramziabk requested that this question be closed by accepting emoreau's comment #a40950270 (500 points)
Did you pick a random comment?  I am sure Eric would agree that his comment was not intended as a solution.  You do realize that future readers will have no idea what you meant by that selection and experts will think twice about answering your questions?

The error was caused by hardware failure on one of the hosts as I'm running virtual machines
For future readers, this makes no sense whatsoever.
ramziabkAuthor Commented:
Here's how I solved the issue.

I created a new clean instance with only one table on the same Virtual machine and ran the insert statement. It took more than 30 minutes. The same thing was done on my
personal PC and ran the insert statement again. It took me less than a minute. This cleared the confusion that the error might have to do with the database configuration or table structure.
What I did then is move the Virtual machine to a different host and ran the insert statement. It took 30 seconds.
ramziabkAuthor Commented:
I figured out the error myself
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.