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: Wednesday, April 16, 2014 10:04 AM
Points: 410, Visits: 996
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: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
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: Wednesday, April 16, 2014 10:04 AM
Points: 410, Visits: 996
Thanks
Post #1452992
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse