best practices with Command object 
Author Message
 best practices with Command object

BACKGROUND
I'm going to execute the same (insert) command  2 million+  times
Each time, a single row is inserted into a database table.
I've defined a Parameters collection for the command object.

QUESTIONS
Is it better (a) to instantiate and destroy the Command object once and only
once, giving the object class-level scope, and then in a member function to
change the CommandText and the parameter values, or (b) to instantiate and
destroy the command object several million times?

Are there any memory side-effects when the Command object is created only
once but the parameter values and command text are changed a few million
times, that is, each time the Command.ExecuteNonQuery method is invoked?

I understand that under approach (a) the Connection would remain open until
after the last row has been inserted, but this is a single-user (OLEDB JET
PROVIDER) application where the database resides on the local PC. It should
be able to hog the connection without any of the problems that are germane
to a disconnected/client-server scenario.
Thanks
Tim



Tue, 01 Feb 2005 07:02:22 GMT  
 best practices with Command object
Each of the Command objects support the Prepare method, implemented from the
IDbCommand interface's Prepare method.  Look at the .Prepare method of the
SqlCommand [1] and OleDbComamnd [2]object for information on how to call it.

The question is the impact of using Prepare on Access:  you should be able
to test its effectiveness against a Jet database simply through timing the
calls with using .Prepare and without using .Prepare.

Just curious, though:  do you have the capability of moving away from Access
ot manage that much data?  Assuming several million rows are inserted each
time this thing is run, can you upgrade at least to MSDE for managing that
volume of data?

--
Kirk Allen Evans
http://www.xmlandasp.net
"XML and ASP.NET", New Riders Publishing
 http://www.amazon.com/exec/obidos/ASIN/073571200X

[1]
ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfsystemdatasqlclientsqlcommandcla
sspreparetopic.htm
[2]
ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfsystemdataoledboledbcommandclass
preparetopic.htm


Quote:
> BACKGROUND
> I'm going to execute the same (insert) command  2 million+  times
> Each time, a single row is inserted into a database table.
> I've defined a Parameters collection for the command object.

> QUESTIONS
> Is it better (a) to instantiate and destroy the Command object once and
only
> once, giving the object class-level scope, and then in a member function
to
> change the CommandText and the parameter values, or (b) to instantiate and
> destroy the command object several million times?

> Are there any memory side-effects when the Command object is created only
> once but the parameter values and command text are changed a few million
> times, that is, each time the Command.ExecuteNonQuery method is invoked?

> I understand that under approach (a) the Connection would remain open
until
> after the last row has been inserted, but this is a single-user (OLEDB JET
> PROVIDER) application where the database resides on the local PC. It
should
> be able to hog the connection without any of the problems that are germane
> to a disconnected/client-server scenario.
> Thanks
> Tim



Wed, 02 Feb 2005 01:55:26 GMT  
 best practices with Command object
Kirk,
I will check out the Prepare method to see if it could possbly be beneficial
for use with Access2000, though I suspect it wouldn't, since it creates a
stored procedure on the datasource and there is no such thing in Access.
Perhaps it would create a parameter query?

My main concern is whether there are any memory leaks in the Command object,
since the ExecuteNonQuery method will be invoked several million times.
Thanks
Tim

P.S.As for moving away from Access to MSDE:  1) The retrieval times are
quite quick even on a database of this size: a fraction of a second on my
1.2ghz Pentium with 4 million rows.  It's the initial inserts that run slow
and could benefit from a native provider.  2) I need a minimalist install;
users have to have everything required for the app after downloading the
DOTNET runtime, MDAC 2.7, and my assemblies. Users will be running Win98
most likely.



Quote:
> Each of the Command objects support the Prepare method, implemented from
the
> IDbCommand interface's Prepare method.  Look at the .Prepare method of the
> SqlCommand [1] and OleDbComamnd [2]object for information on how to call
it.

> The question is the impact of using Prepare on Access:  you should be able
> to test its effectiveness against a Jet database simply through timing the
> calls with using .Prepare and without using .Prepare.

> Just curious, though:  do you have the capability of moving away from
Access
> ot manage that much data?  Assuming several million rows are inserted each
> time this thing is run, can you upgrade at least to MSDE for managing that
> volume of data?

> --
> Kirk Allen Evans
> http://www.xmlandasp.net
> "XML and ASP.NET", New Riders Publishing
>  http://www.amazon.com/exec/obidos/ASIN/073571200X

> [1]

ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfsystemdatasqlclientsqlcommandcla
Quote:
> sspreparetopic.htm
> [2]

ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfsystemdataoledboledbcommandclass

- Show quoted text -

Quote:
> preparetopic.htm



> > BACKGROUND
> > I'm going to execute the same (insert) command  2 million+  times
> > Each time, a single row is inserted into a database table.
> > I've defined a Parameters collection for the command object.

> > QUESTIONS
> > Is it better (a) to instantiate and destroy the Command object once and
> only
> > once, giving the object class-level scope, and then in a member function
> to
> > change the CommandText and the parameter values, or (b) to instantiate
and
> > destroy the command object several million times?

> > Are there any memory side-effects when the Command object is created
only
> > once but the parameter values and command text are changed a few million
> > times, that is, each time the Command.ExecuteNonQuery method is invoked?

> > I understand that under approach (a) the Connection would remain open
> until
> > after the last row has been inserted, but this is a single-user (OLEDB
JET
> > PROVIDER) application where the database resides on the local PC. It
> should
> > be able to hog the connection without any of the problems that are
germane
> > to a disconnected/client-server scenario.
> > Thanks
> > Tim



Wed, 02 Feb 2005 04:06:47 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Is it Good Practice to use a VBscript command in a VB App

2. Best practice for data-related business objects

3. Best Practice - Accessing Properties In Objects?

4. Variants vs. Types in COM objects...best practices

5. Instantiating & Releasing ActiveX EXE objects Best Practices

6. Access Development Best Practices Chat 10/03/02

7. change in join syntax best practices?

8. Good programming practice? - Generic options

9. best practice

10. Good practice question

11. VBA "good practice" re If statements

12. Good Design Practice?

 

 
Powered by phpBB® Forum Software