I have a stored proc that we call for every row and the procedure writes to staging table for all the matching records. One particular row has about 365k matching records and its taking 40 min to write to staging table, the problem we have is we dont get any response back after some time.
its working fine when the query doesnt take long time to fetch rows, is there a timeout limit on the stored proc so it just freezes afterwards,
and the job becomes dead. we need to manually kill it. any ideas??
Stored Proc Issue
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Only the obvious one - don't call a stored procedure for every row. In addition to being slow, and depending upon what the stored procedure actually does, you risk the possibility of self-deadlock.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.