Long running SQL stored procedure, Timeout, Locks, and Limitations

**Let me first say that changing the table structure by adding certain indexes is not an option at this time due to not knowing the impact it will cause to other existing processes against those tables. So I have to deal with what is currently available for me.

 

Scribe version: 6.5.2

Scribe Console: Integration Server processor configuration = 8 Active processors(maximum)

CRM Adapter version: 4.3.1

SQL Server 2005 (source and target):

Source = SQL Server stored procedure

Target = CRM Dynamics Adapter

 

I created a fairly big stored procedure that has multiple table joins(at least 10). This stored procedure, if ran on SQL Server Management Console, would run for 15 minutes or more. The reason is that we are using a view that that multiple table unions that actually crosses over to another database to pick up some archived data (I didn' t write the view). The resultset will have more than 500,000 records. I tried creating a messaging queue type but due to the long running stored procedure, it times out. I think there is a limitation on how long the job will run for before it fails (What is the Scribe threshold??? 10 minutes?) So instead, I created a Scribe DTS to execute this stored procedure and using CRM adapter to get to the target. Then I created a " Time" integration process to execute this DTS. Even then the job would fail with an error stating " Message Processor is unresponsive" . There was a registry entry that was required to be able to run this job (see

https://success.scribesoft.com/s/question/0D53200003tVOHeCAO/scribe-even...

). I set the value to 1000. Now the job seems to be responding properly and loading data into CRM.

 

The problem is that the data loads very slow (single threading and 1 dedicated processor for Time based integration???). I started this job at 8:00am and at 12:45pm only 185,000 out of 500,000 records were loaded to CRM table. We had to kill the process in SQL server because it was locking our source tables and causing other processes to suffer. When Scribe executes the job, what is it doing? I thought it would execute the stored procedure for 20 minutes and has the resultset ready for loading into CRM. But it seems that it keeps a lock on all the tables involved from the source the whole time. What can I do to fix this slow loading issue and locking issue? Where can I find information as to Scribe' s limitations in regards to timeouts, locks, processors, memory, and the inner workings of how data is " processed" ? I was hoping that I could use messaging but it will not allow long running stored procedures, what' s the maximum time it allows? Is there a way to do queue based messaging with a 15(+) minutes query execution time?

 

Need solution fairly quickly....Thanks in advance!

 

Posted by Lisa T. on Jan 19 2010 7:30PM { U1430 , F8 , T1094 , C3175 }

(5) Answers

Login