Need help with slow ExecuteNonQuery in C# Application

[doiley]

hi, i am currently writting a C# windows service that is time critical.

As a breif introduction, the application receives messages from a source, from that message, it is passed in a thread from the thread pool.

Here the message is decrypted, and has some processing done to it.

Once the processing is done the data is stored into a MS SQL 2005 Database using a stored proc, the whole process takes about 350 milliseconds, which is not accectable from my point of view. I would like it to take appox 100 milliseconds.

If i remove the line

dbQuery.ExecuteNonQuery();

it only takes about 80 - 100 milliseconds which would be great.

The connection is created, not reused, just before the execution but doesnt seem to slow the process.

is there any way of making this process faster, or is there any links i can use for Stored Procedure optimization?

and other ideas would be awesome as well.

Cheers

[TRScheel]

dbQuery.ExecuteNonQuery();

tells your SQL server to run the commands. Depending on what its doing and to how many values will determine how long this line takes.

[kenobewan]

Databases respond faster when the stored procedure is simpler or shorter - they like be 'chatty' - so multiple simple queries make for a faster application.

[TRScheel]

..they like be 'chatty'..

Is that the technical term? hehe

[kevinhint]

Is that the technical term? hehe

Beyond good old fashioned query optimization, you can minimize your sql compile time by using a stored proc or parameterized query (http://www.codeproject.com/cs/database/ParameterizingAdHocSQL.asp). If you're making the call inside a loop, use the Prepare() method before entering the loop to reduce "chattiness" (too much data going across the wire).

[doiley]

thanks for that, i made some changes to the stored proc and i got the times i was looking for