asked
Insert varchar(max) column
Hi, I have a database with 10 tables and varchar(max) column which stores the image. These tables have 400K rows. I need to move the data based on condition into new database with same table structures. Right now i am using query like --- insert into tableA select column1,column2.... from db.dbo.tableB where name in (condition). Its taking almost 2 hours to load the data into new tables for 50000 records. I am wrapping all the queries for each table into a stored procedure and executing. I have also used SSIS but its taking more time than stored proc. Please suggest me some ideas to improve the performance.
By "wrapping all the queries for each table into a stored procedure", what do you mean? Do you mean each table is being populated by 50k calls to a particular stored procedure, one for each row? Or you've built a single stored procedure that contains 50k insert statements? Or something else?
Just curious, is the new database on the same server? If all ten tables have 400K rows, that's 4 million to move, with images. It's possible depending on your configuration that it's going to take time. I'd probably start with: 1. Are indexes on the new tables? If so, that could be slowing down the insert. 2. Do you have auto-growth set appropriately and/or have you sized the database for the load? 3. How about instant file initialization? 4. Assuming that it's on the same server, do you have the database files on separate drives/luns? 5. What is your recovery model set to? 6. Can you post an execution plan to see how it's selecting the data? 7. Have you looked at any monitoring statistics to see where a bottle neck might be? I/O? Memory? Etc? I'm sure that there are others but those just were from the top of my mind. Hope this helps!
As JohnM mentioned in #4, are these on the same server? If not, you may want to look at the network connecting the servers. Depending on the size of the images in question, you may truly be moving a large amount of data and a faster network may be in order. Also, do you or have you considered breaking this into multiple batches and limiting the number of rows in each transaction? In large insert operations that can help in a number of ways, and as a side effect it will reduce issues with locking and blocking if other users are trying to access those tables.
17 People are following this question.
