ramziabk
asked on
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
Thanks
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
Thanks
try use BULK INSERT (but you need to set permission before running it), if you're importing records from a different source.
ASKER
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.
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.
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.
Migrating Sybase ASE Databases to SQL Server/Azure SQL DB (SybaseToSQL)
https://msdn.microsoft.com/en-us/library/hh302827(v=sql.110).aspx
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)
https://msdn.microsoft.com/en-us/library/hh302827(v=sql.110).aspx
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?
Do you have countless indexes and triggers in the table you want to insert?
ASKER
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.
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.
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?
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?
ASKER
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?
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?
other queries on the same server?
ASKER
The same very slow
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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.
Another possibility is that the data file or Transaction log is full and it is having to grow in (God forbid) 10% increments.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
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.
ASKER
I figured out the error myself
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......