July 18, 2008 at 1:29 am
Hi,
I have a DTS Package, which refreshes the data in sql server table from the source Oracle table. This sql server table is being used in an .net web application. And this refresh is happening on a daily basis and it is taking about 20-30 minutes to complete. Specifically, the sql server table is used in a search page.
Now the problem here is, during the refresh is going on, the users are not able use the search page, as the connection is very slow due to the underlying table is getting populated through the DTS. The users find this as an outage.
I was thinking of handling this scenario, using synonymns,. But unfortunately, synonymns are not available in SQL Server 2000. May be, Views can replace synonymns, but we would like to handle it in the DTS level itself.
I have gone through the options to handle this in DTS like ' Always Commit Final Batch ' and ' Insert Batch Size'.
Can you please help me out and give a feasible solution, if you have come across this situation? It would be of great help.
Thanks,
Daniel
July 18, 2008 at 2:44 am
Do you know what part of the DTS package takes the time? I suspect that it is the transfer of data from Oracle. Are the users experiencing blocking where there update is locking the SQL table and the users' requests are waiting for the package to complete? You can check this using SP_WHO2 whilst the package is running.
Have you considered loading the data into a staging table and then update the SQL table in a single query? This would not lock the SQL table until you do the update which should be much quicker and lead to reduced problems for the users.
Jez
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply