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

reduce impact performance impact on the OLTP SQL database Expand / Collapse
Author
Message
Posted Wednesday, May 15, 2013 12:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 426, Visits: 1,054
Hi,

Our client occasionally needs to load very large transaction files into their OLTP SQL database during periods of heavy “live” API load. How would i structure these imports to reduce impact performance impact on the OLTP SQL database?


Thanks
Post #1452938
Posted Wednesday, May 15, 2013 1:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
The short answer is, it depends, on far too many factors for anyone to say definitively what would be best for your environment.

Generally speaking you'll want to keep blocking to a minimum in any high volume transaction processing system. This usually means limiting the number of rows you attempt to apply from your large files into your transactional tables within a single transaction to a minimum. Some systems completely avoid set-based processing meaning all rows entering the system enter one at a time regardless of whether they arrived in a file or from an interactive user hitting a front-end, and all data might even flow through the same set of interface stored procedures that effectively only process one row of data at a time.

Mostly what I have found though is that most systems support a blend of single-row transactional processing, usually coming from interactive applications (e.g. websites) where the bulk of activity occurs during normal business hours. Then, multi-row set-based batches are applied by backend processes during night-time hours as not to conflict with the interactive users as much as possible.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1452956
Posted Wednesday, May 15, 2013 3:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 426, Visits: 1,054
Thanks
Post #1452992
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse