April 28, 2016 at 11:30 am
We are currently working on creating a portal website. The backend database will contain customer information. The current load process loads each table from different source systems one table at a time.
For instance, loads Customer Table, then loads Orders table, then loads Inventory table.
We have a SLA of 2.5seconds for a mouse click, to page rendered.
The Current load process takes approximately 30 minutes, and each of the above tables are locked for 10 minutes each while loading.
We do not want the endusers to see a new customer, but no orders from that customer because that table hasn't loaded yet.
Potential Solutions we've discussed
1. Using two databases, and then swapping between them. Have Web pointed to DB1, Update DB2, repoint Web to DB2, Update DB1, rinse repeat.
2. Same basic premise but use different tables and update view definitions to use table1, then table2.
3. Build a staging database, load all changes and then update one record at a time across all tables.
What other thoughts? This has to be a pretty normal need for web based applications.
April 28, 2016 at 11:59 am
A table lock (TABLOCK) will improve the performance of a bulk table insert, but it will also hold a lock on the entire table. If you can tolerate somewhat longer load times for the tables in exchange for less blocking on queries from the application, then insure that your ETL load process is not holding a TABLOCK. For example, the FastLoad option of an SSIS package will use TABLOCK, so consider turning that off.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 28, 2016 at 12:44 pm
Instead of processing all the records for each table then moving on to the next, can you process all the records in all the tables related to one customer?
Another option would be to change your load order to process from the lowest level entities to the top level entities which would prevent the problem your described.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply