Limit on SQLCommand instances

[Joanna Carter [TeamB]]

Hi

We are considering creating a large number of parameterised SQLCommand
objects (approx 4000). The point is that we can preset the SQL text once
only and gain performance by only populating the parameters prior to
execution.

We were thinking of creating one command for each of the four SQL statements
and then repeating these for each of around 900 types.

We may use lazy instantiation to avoid creating them all at once, but are
still worried about taking too many unmanaged resources for the entire run
of the program.

Joanna

 

[W.G. Ryan - MVP]

Joanna:

Are you essentially having one command object and then changing the
parameter values 900 times? If so, that's exactly what a DataAdapter does
when it updates 900 rows (actually, it would depend on rowstate, so there
would be four commands but my point is the same). Specifically though,
which are the unmanaged resources you're concerned with?

 

[Miha Markic [MVP C#]]

Hi Joanna,

I don't think that Prepare stores unmanaged resources at all.

 

[Mark Ashton]

With SqlClient, the only unmanaged resource is the connection itself.

 

[Joanna Carter [TeamB]]

"Mark Ashton" <[email protected]> a écrit dans le message de
%[email protected]...

| With SqlClient, the only unmanaged resource is the connection itself.

Then could you tell me why SQLCommand implements IDisposable ? I thought
that this was only necessary when an unmanaged resource was allocated within
an instance of a class that implements IDisposable.

Joanna

 

[Joanna Carter [TeamB]]

"W.G. Ryan - MVP" <[email protected]> a écrit dans le message de
%23%[email protected]...

| Are you essentially having one command object and then changing the
| parameter values 900 times? If so, that's exactly what a DataAdapter does
| when it updates 900 rows (actually, it would depend on rowstate, so there
| would be four commands but my point is the same). Specifically though,
| which are the unmanaged resources you're concerned with?

We have read that assigning a parameterised SQL statement to a SQLCommand
will significantly save time over building SQL statements and setting the
text of the command each time we need to execute it.

As I said, we have about 900 different types in our system and we want to
have one of each of the four SQL statements (select, insert, update &
delete) parameterised where necessary, prepared and ready to execute.

Now the Select statements for each type do not need parameterising but, of
course, the value lists for the Update and Insert will need parameters, as
will the Delete to cater for the Where clause.

Because SQLStatement implements IDisposable, we are concerned that creating
900 x 4 statements will cause resource problems if these statements are kept
in memory for the duration of the program's execution (typically all day).

In Delphi for Win32, we could explicitly free objects whenever we wanted to
to help manage resources, GC is a whole other ball game; we are just
concerned that we may be sacrificing one performance problem to gain another
performance benefit.

This also raise the spectre of having a Connection active all day as well
versus incurring a time penalty every time we open the Connection whenever a
Command is to be executed.

Joanna

 

[Cor Ligthert [MVP]]

Joanna,

In long discussion in this newsgroup (Mostly with Frans Bouma in it), gives
me the idea that there is a little bit misleading in your message.

We have read that assigning a parameterised SQL statement to a SQLCommand
the
text of the command each time we need to execute it.

The word "significantly", can you show us where you have read that.

I don't discuss with you that doing it this way the best, that I agree (in
the production situation). However 'significantly' gives me an idea as
forever and not a slight amount of time.

It can be my idea about the word 'significantly' (which is in my own natural
language almost the same as in English before you misunderstood that).

Cor

 

[Cor Ligthert [MVP]]

Joanna,

Then could you tell me why SQLCommand implements IDisposable ? I thought
that this was only necessary when an unmanaged resource was allocated
within
an instance of a class that implements IDisposable.

There are a lot of websites where is written. "If a class implements dispose
than use it". I found it really misleading and a way of telling "I don't
know what it does".

Most used classes and actually 20% implements Idisposable. That is because
dispose in is it just because of the fact that they all derived from the
component.class.

http://msdn.microsoft.com/library/d...ref/html/frlrfSystemIDisposableClassTopic.asp

Use dispose when it is explicitly needed to release unmanaged resources.

There are some other public methods which are in every class. See for that
this.

http://msdn.microsoft.com/library/d.../cpref/html/frlrfsystemobjectmemberstopic.asp

I assume that you don't use those as well in every method that you write
where you use an object.

I hope this gives an idea,

Cor

 

[Joanna Carter [TeamB]]

"Cor Ligthert [MVP]" <[email protected]> a écrit dans le message de
%[email protected]...

| It can be my idea about the word 'significantly' (which is in my own
natural
| language almost the same as in English before you misunderstood that).

By significantly, I was implying that, assigning SQL text every time you
wanted to change the SQL statement to be executed, would be slow to the
extent that it would cause the user of the application to notice that it was
running slowly.

Let me explain the reason for this concern :

Under Delphi Win32, we were able to create a single query component for the
entire system and simply assign SQL statements to it, execute them and then
free the component. This did not seem to cause too much of a speed problem.
We are trying to find out if ADO.NET will be any slower than its Win32
couterpart.

Further, in order to improve the execution speed, we understand that setting
up a SQLCommand with a parameterised SQL statement will provide a noticeable
speed increase.

If the speed increase is really that noticeable, then we would need to
"prepare" the four essential statements required to CRUD instances of each
of 900 different types.

We are seeking to clarify if the speed advantages gained by creating about
4000 SQLCommands, each with their own prepared, parameterised SQL statement,
would be outweighed by memory/resource problems incurred by creating such a
large number of SQLCommand instances.

Joanna

 

[Cor Ligthert [MVP]]

Joanna,

Search this newsgroup for Frans Bouma and by instance Crud, he has written
(discussed) a lot about this subject.

Here your first start.

http://groups.google.com/group/micr...bouma+crud&qt_g=1&searchnow=Search+this+group

I hope this helps,

Cor

 

[W.G. Ryan - MVP]

"W.G. Ryan - MVP" <[email protected]> a écrit dans le message
de
%23%[email protected]...

| Are you essentially having one command object and then changing the
| parameter values 900 times? If so, that's exactly what a DataAdapter
does
| when it updates 900 rows (actually, it would depend on rowstate, so
there
| would be four commands but my point is the same). Specifically though,
| which are the unmanaged resources you're concerned with?

We have read that assigning a parameterised SQL statement to a SQLCommand
will significantly save time over building SQL statements and setting the
text of the command each time we need to execute it.

--Essentially this is true. Paramaterized queries save a lott of time over
concatenated strings so in this respect, you're right.

As I said, we have about 900 different types in our system and we want to
have one of each of the four SQL statements (select, insert, update &
delete) parameterised where necessary, prepared and ready to execute.

--I'm still not following you. Do you want one command for each crud
operation and you'll just change the parameters 900 times, or are you going
to have 900 different crud commands (* 3)?

Now the Select statements for each type do not need parameterising but, of
course, the value lists for the Update and Insert will need parameters, as
will the Delete to cater for the Where clause.-

---THey still should be for many reasons, injection (intentional) is one,
but if you get a value with an apostrophe or something else, it will take a
work around to make it happen and you'll only discover this in all
likelihood at runtime. IMHO - paramaterizing queries is the only option.

Because SQLStatement implements IDisposable, we are concerned that
creating
900 x 4 statements will cause resource problems if these statements are
kept
in memory for the duration of the program's execution (typically all day).

-- Bothe the SqlCommand and SqlConnection implement IDisposable, but that
doesn't mean that SqlCOmmands are unmanaged resources and in regard to
SqlCommands - IDisposable is there for internal reasons only. SqlConnection
objects are unmanaged but SqlCOmmand object are managed - so no worries
there.

In Delphi for Win32, we could explicitly free objects whenever we wanted
to
to help manage resources, GC is a whole other ball game; we are just
concerned that we may be sacrificing one performance problem to gain
another
performance benefit.

--From what I am reading, this fortunately doesn't appear to be the case.

This also raise the spectre of having a Connection active all day as well
versus incurring a time penalty every time we open the Connection whenever
a
Command is to be executed.

-Connection pooling makes opening and closing connections very cheap. If you
have x operations in a loop, then you may want to leave the connection open,
but you wouldn't want to leave it open all day. If you don't close it, it
doesn't return to the pool. As long as pooling is on and you close
connection (make sure they are closed in a finally block or in a using
statement), then you should be fine.