Re: confusing transactions with sqldmo

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/25/04 

Date: Sat, 25 Dec 2004 02:05:17 -0500

Neil wrote:
> Sorry, I had sqldmo on my mind. I meant ado.net (using C#). Here is
> the source code with everything but the essentials stripped out. The
> program creates a bunch of threads and then performs the code below
> in each thread.
>
> Like I said, when running from many different Query Analyzer windows,
> these appear to run simultaneously. However, when started in many
> threads from the same program, they seem to run single threaded.
>
> To refresh your memory, sp_test_proc contains the following inside a
> tight loop:
>
> begin trans
> update employees with (rowlock) set address = reverse(address) where
> employeeid = @random
> commit work
>
> I hope this is clear. Thanks for your input..
>
> --- code --
>
> // Connecting:
> string
> s=server=(local);Trusted_Connection=yes;database=master;connectiontimeout=10
> ;Net=dbmslpcn;
> return new SqlConnection(s);
>
> // Create SqlCommand:
> // conn is created connection
> conn.Open(); //open connection
> SqlCommand command = conn.CreateCommand(); //create command
>
> // Set up properties of SqlCommand:
> // this is the procedure that does the update loop I mentioned before
> command.CommandText = "sp_test_proc";
> command.CommandType = CommandType.StoredProcedure;
> command.CommandTimeout = 0; //never time out
>
> // Set up parameters to call stored procedure:
>
> SqlParameter returnval =
> command.Parameters.Add("RETURN_VALUE",SqlDbType.Int);
> returnval.Direction = ParameterDirection.ReturnValue;
>
> SqlParameter parm1 =
> command.Parameters.Add("@parm1",SqlDbType.NVarChar,50); parm1.Value =
> ... ;
>
> SqlParameter parm2 = ...
>
> // Call stored procedure:
> command.ExecuteNonQuery();
> object value = command.Parameters["RETURN_VALUE"].Value;
> // value is return value of stored procedure
>
> Close connection:
> conn.Close();
>

I don't think just because you are running multiple threads you can run
multiple SQL statements. AFAIK, one SQL statement per connection unless
you are using something like the Prepare/Execute model of execution.
Otherwise, one SQL statement per connection. In any case, you need to
make an asynchronous call. I don't see that you specified the call is
asynchronous anywhere in your code.

So, you'll need multiple connections and need to use asynchronous calls,
just like Query Analyzer does. 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • Re: confusing transactions with sqldmo
    ... Sorry if I wasn't clear - each thread IS opening its own connection. ... >> Like I said, when running from many different Query Analyzer windows, ... > multiple SQL statements. ... one SQL statement per connection unless ...
    (microsoft.public.sqlserver.programming)
  • Re: confusing transactions with sqldmo
    ... You may want to use SQL profiler to monitor the connection and commands ... |> multiple SQL statements. ... one SQL statement per connection unless ...
    (microsoft.public.sqlserver.programming)
  • Re: downloading a single file using multiple threads
    ... The multi part requests in HTTP1.1 are meant to request a single part or multiple parts in a single request, but you can't requests multiple parts in parallel from multiple client threads. ... I know that down-load managers claim to work over HTTP, but that doesn't mean they support multi-part parallel request handling over the same or multiple connections, I don't even know if the protocol allows you to issue new range request when you have a range requests pending. ... If on one connection the client asks for the first megabyte and on a second connection the same client asks for the second megabyte, then if the server is capable of servicing both requests at the same time, there's no reason the client can't wind up receiving both the first and second megabytes in parallel. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: downloading a single file using multiple threads
    ... The multi part requests in HTTP1.1 are meant to request a single part or multiple parts in a single request, but you can't requests multiple parts in parallel from multiple client threads. ... There's nothing about HTTP that requires servers to restrict their communications to a given client to a single connection, and there's nothing about HTTP that stipulates that an HTTP server needs to coordinate communications on independent connections. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQL wont work
    ... It seems like the basic component SQL statement is working but the code isn't. ... All you need is a TADOConnection, a TADODataSet and a TDataSource if you're using bound controls. ... Drop a TADOConnection on your form or in your data module and make sure the connection string to your MySQL database is correct. ... Add a TADODataSet. ...
    (comp.lang.pascal.delphi.databases)