Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk insert taking longer in 2008 R2 after upgrade form 2005 Expand / Collapse
Author
Message
Posted Thursday, August 16, 2012 8:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 11, 2012 10:33 AM
Points: 144, Visits: 77
I have upgraded my Production database from sql server 2005 to SQL 2008 with same configured Windows server. After upgraded to SQl 2008 R2 bulk insert is taking longer time. We update stats and rebuild index every day on this specific table.

From application in 2005 it was taking 22 mins to bulkinsert 10,000 Items, but in 2008 its taking 39 mins. While insert is running I am not able to select anything from the database.


Thanks
Dev.
Post #1346312
Posted Thursday, August 16, 2012 8:21 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:52 AM
Points: 716, Visits: 350
Please send more detail.

I can say please check or compare with temp space and memory there will be a diffrence .
Post #1346314
Posted Thursday, August 16, 2012 8:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 11, 2012 10:33 AM
Points: 144, Visits: 77
Sandeep,

what details you need let me know? We did an in-place upgrade. Run time comparison is from the application log.
Post #1346316
Posted Friday, August 17, 2012 3:54 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:52 AM
Points: 716, Visits: 350
Plz send bcp query
Post #1346437
Posted Friday, August 17, 2012 8:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 1,426, Visits: 3,224
Dev-812464 (8/16/2012)

From application in 2005 it was taking 22 mins to bulkinsert 10,000 Items, but in 2008 its taking 39 mins. While insert is running I am not able to select anything from the database.


10,000 rows is not that many. 22 minutes seems like a long time for this many rows. (I bulk insert millions of rows and rebuild indexes in less than 5 minutes) How many columns and what data types are there? What batch size are your using and what about the recovery mode of the database? (are your using a staging database?)

What kind of indexes are on the table? are you bulk inserting into stage table or the actual table?

So many things can affect bulk insert performance, we would need to know all of the details.




The probability of survival is inversely proportional to the angle of arrival.
Post #1346565
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse