Link to home
Start Free TrialLog in
Avatar of FranklinRaj22
FranklinRaj22

asked on

Multiple calls to same procedure

All we have 2 process invoking the same stored procedure at the same time and both of them are hung . They start yielding other resources but they never progress or complete .

Is this something to do with deadlock or resourcing locking , has anybody come across it or throw an insight of how the whole thing works ? Any solutions or suggestions ?

The sp uses cursors !!
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

indeed, removing cursors can help alot; but the programming needs to be made correctly.
without seeing the code, impossible to proceed, except that indeed this should like a "deadlock", which normally are detected, but with some complex scenario it might not be detected ...
it might also be some 3rd connection (like the one you use to query for the status/progress) that could add up on the blocking ...
Avatar of OriNetworks
OriNetworks

Cursors are evil in most cases. Its a little difficult to identify where you might be going wrong without seeing the code but you should read up on cursors, especially why in most cases they should not be used. Also you might be able to change your approach in how the sp is working with the data by possibly batching which is a technique used to work with large recordsets and allowing delays for other transactions to squeeze in. If one sp is run by itself does it actually finish and how long does it take. Do they use the same resources?
Avatar of FranklinRaj22

ASKER

ok can i replace the cursor with temp tables that should help right ? or any other better suggestions ?
What says sp_who2 about the two frozen processes? Are they still running are they suspended? Does the procedure finish if you execute it just once?
I wrote up this quick article to show the difference in coding with cursors and temp tables:
https://www.experts-exchange.com/Database/MS-SQL-Server/A_13640-processing-cursor-vs-temp-table-syntax.html
hope this finds you well
@pcelbha The sp_Who2 does not show any blocks or idle process .
Guy hengel . Thanks  but i am unable to open you link
SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I was able to replace cursors with temp tables to good improvement .Thanks a lot .