Re: MultiThreading against a Procedure

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

"William (Bill) Vaughn" wrote:

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
"David Jessee" <DavidJessee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:71854CA3-524C-4638-A654-A6FFCBCBEB53@xxxxxxxxxxxxxxxx
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.


"William (Bill) Vaughn" wrote:

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
"David Jessee" <DavidJessee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:88187AD9-1639-4FB5-8A3E-0A40215C0F0B@xxxxxxxxxxxxxxxx
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







.

Relevant Pages

  • Re: insert Q
    ... many sql statements on the page). ... When you use a connection's Execute method, a Command object is ... Always use an explicit recordset object: ...
    (microsoft.public.inetserver.asp.db)
  • Re: Running a program from a SQL job
    ... This will allow you to go to a SQL prompt:- ... >> Use XP_CMDSHELL to fire a OS command from command prompt. ... I've made a bat file that contains the string to run to ... When I set the SQL job to execute the same ...
    (microsoft.public.sqlserver.server)
  • Re: insert Q
    ... >> conn.execute SQL ... When you use a connection's Execute method, a Command object is ... Always use an explicit recordset object: ...
    (microsoft.public.inetserver.asp.db)
  • Re: Write Back To SQL from ASP .NET web Page
    ... > ยค I'm trying to execute a SQL Insert command using my existing SqlConnection ...
    (microsoft.public.dotnet.languages.vb)
  • Query performance
    ... reported by SQL Profiler for the same query when executed from 1) ADO.NET ... Two particular queries have the following figures reported in Profiler ... If I copy the sql for these queries from the profiler and execute it in SQL ... Management Studio, then profiler reports ...
    (microsoft.public.sqlserver.clients)
Loading