February 7, 2011 at 12:57 am
Diagnose data insert issue into IRIS table in MS SQL server
The ETL application is getting data from the QAD NA, so_mstr table and loading to the dbo.so_mstr table in the MS SQL server.
If commit is made every 100 records, the 1399 record is getting loaded at 6mins.
If commit is made every 200 records, the 1399 record is getting loaded at 10mins.
If commit is made every 1000 records, the 1399 record is getting loaded at 45mins.
The data will increase in future, so we need to check why the commit at 1000 takes so long to complete.
Please let me know if more details required.
How can i proceed with this issue?
February 7, 2011 at 7:12 am
I need tons more information to even attempt to give you answer. A few places you should look, what process are you using to load? Is it a batch oriented process or are you using loops? That's the first place. Loops are bad, try to convert it to batches. After that, what kind of queries are you using to load the data? Have you looked at the execution plans for those queries for tuning opportunities? Gotta check that. How about indexes and constraints. It can be faster (it isn't always) to disable the indexes and constraints during the load and reenable them once the load is completed.
There just isn't enough information here to tell you what to do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply