MultiThreading against a Procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Folks, I'm at the point where my cube has a dent on the side of it that is
shaped like my head........can't figure this one out.

I have a procedure that has ro be run for every month from 1/1/2001 to the
current month. It takes a @StartDate and @EndDate parameter.

I can't change the procedure to just execute from '1/1/2001' to GETDATE()
because it would take way too long to execute.

The proc is effectively just a complex select statement.

If I go into SQL Query Analyzer and say:
exec procname '1/1/2001','1/31/2001';
exec procname '2/1/2001','2/31/2001';
.....etc....etc....
exec procname '3/1/2006','3/31/2006';

Everything is fine...life is good

However if, from ADO.NET (SqlClient native provider), and I Execute this
command for each date range, it freezes up.
I've tried:
Preparing the command.....no change
Opening and closing/Disposing the connection for every command....no change
Having multiple threads execute the command for different months
simultaneously .... no change
Having the command execute a SQL statement where sql="exec procname
'1/1/2001','1/31/2001';exec procname '2/1/2001','2/31/2001' (etc etc) "....no
change
Creating a transaction (read uncomitted) ... no change

Any idea what could be causing the behavior? Everything works fine in Query
Analyzer, but it just freezed when I try to go through ADO.NET
 
Why not call the SP from a SP that sets the parameters using date
arithmetic?
If you're calling the SPs from ADO.NET, they should run synchronously (one
after the other). The CommandTimeout should be set high enough to run the
individual SPs.
You should not have to rebuild or dispose of the Command objects between
invocations--just change the Value property of the Command Parameter
instances.
Use the Profiler and SSMS to see what's going on and freezing up.

--
William (Bill) Vaughn
President and Founder Beta V Corporation
Redmond, WA
(425) 556-9205
Microsoft MVP, Author, Mentor
Microsoft MVP
 
I'm wanting to eventually go to a multi-threaded model so that the thing can
execute different months on different threads for performance purposes (some
of these things take a few minutes to run). Executing everything inside of a
proc with a data counter will not get me the performance that I'm needing.

I DID run profiler against the process to see what was happening. I had it
show locks, execution plans, the works.....nothing showed up that could have
caused the issue.
The Profiler (after adding in all available events) said that the RPC
started but then it did not show anything.

I'm really beginning to wonder if there's a bug in the provider.
 
In the SQL Server provider? I doubt it. I've done what you're attempting any
number of times. I expect you might have a locking or deadly embrace issue
that's blocking the operation...

--
William (Bill) Vaughn
President and Founder Beta V Corporation
Redmond, WA
(425) 556-9205
Microsoft MVP, Author, Mentor
Microsoft MVP
 
Then someone needs to tell the Sql Profiler team that the application does
not display locking information correctly because I couldn't find it in the
trace and none of our DBAs could either
 
Back
Top