Re: Sql Server 2000 slow

• Previous message: James Chang: "Sql Server 2000 slow"
• In reply to: James Chang: "Sql Server 2000 slow"
• Next in thread: James Chang: "Re: Sql Server 2000 slow"
• Reply: James Chang: "Re: Sql Server 2000 slow"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 25 May 2004 01:15:12 -0400

Hi James:
There's a lot of stuff that could potentially cause this and in general,
since ADO.NET is a disconnected model, it's not as fast as ADO but there's a
lot of if's and but's there. I don't want to use too broad a brush here.

In ADO.NET 2.0 there will be batch updates which will speed things up but
that doesn you no good now. You can minimize your trips to the db by
appending the inserts to each other and sending them all at once. This may
speed things up. Another thing is how you are sending the updates. Are you
using Parameterized queries? If you use Dynamic SQL it can be much slower.
You may be able to take advantage of the Command's Prepare method which can
be an optimization (but can also be a disaster depending on how you use it).
If it's apples to apples comparison, I haven't seen the difference to be
this large although I've seen some definite degradation. If you use Dynamic
SQL then it can't take advantage of cached execution plans so this wouldn't
be surprising.

Another thing that's I've seen really slow stuff is (and Dont' take this as
me saying it's normally OK to leave your connections open) is opening and
closing a connection within the same execution block. If I have a loop that
fires executenonquery. If I close the connection (moving the Open/Close
logic in the loop and this loop is big) you'll probably notice a large
performance difference. I'm staying still close the connection the second
you are done with it, but if you are using it back to back for large
operations, no value is really there for closing and opening it if speed is
at a premium.

SqlCommand cmd = new SqlCommand();
SqlConnection cn = new SqlConnection("ConnectionString");
cmd.Parameters.Add("@SomeParam", SqlDbType.Varchar, 50);
try{
if(cn.State !=ConnectionState.Open){cn.Open();}
foreach(MyOBject b in MyObjectsCollection){

    cmd.CommandText = "SELECT * FROM MyTable Where Field1 = @SomeParam");
cmd.Parameters["@SomeParam"].Value = b.FieldName;
int i = cmd.ExecuteNonQuery();
Debug.Assert(i >0, "Nothing Inserted");
}
}
catch(SqlException ex){
Debug.Assert(false, ex.ToString();}
}
finally{
  if(cn.State != ConnectionState.Closed){cn.Close();}
  cn.Dispose();

}

Anyway, if you could post the code it'd help. There may be some server side
optimizations available too.

-- 
W.G. Ryan MVP Windows - Embedded
http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
"James Chang" <james.chang@quest.com.au> wrote in message
news:0C27F11F-10E2-4575-89AE-C784EF206489@microsoft.com...
> Hello,
> I have created a webservice deployed on a Windows 2003 server machine.
> Sql Server 2000 is also on the machine.
> I have tested with a given number of inserts into the database using the
SqlConnection and related objects.
> I have also tested doing the same inserts with a legacy dll (delphi using
ADO) and the dll gives twice the performance.
>
> Are there any known performance issues with .NET accessing Sql Server
under Windows 2003?
> Is there any way I can get my performance up to that of the dll?
> Could you reply to my email.
> Thanks.

• Previous message: James Chang: "Sql Server 2000 slow"
• In reply to: James Chang: "Sql Server 2000 slow"
• Next in thread: James Chang: "Re: Sql Server 2000 slow"
• Reply: James Chang: "Re: Sql Server 2000 slow"
• Messages sorted by: [ date ] [ thread ]