ADO has three components for Connections and Execution against a database, the 

Command, Connection and Recordset objects.  I would like to find out the 

fastest (returning data not developing) way for execution for the following 

scenarios;

1.	A select from the database to populate a list box (10 to 50 rows max).

2.	An update of a small number of rows (1-10).

3.	A select from the database using parameters for a stored procedure.

4.	A delete to the database.



I know I can use the recordset object to do my connection and process my 

statement but is this more effective/faster than using the connection, command 

and recordset objects for their implied purposes to do the same thing?  Is 

their no difference?

Also is it possible to destroy the connection object used to get data from the 

database and put it into a recordset object and be able to continue to work 

with the recordset object?

Thanks for your help.

Dave

> ADO has three components for Connections and Execution against a database,

the

> Command, Connection and Recordset objects.  I would like to find out the

> fastest (returning data not developing) way for execution for the

following

> scenarios;



Assuming one can code properly, and have the requisite hardware then the

following are generally true:



For INSERTS and UPDATES

A stored procedure is faster than an SQL string which is faster than opening

recordset



For getting data out of a database you only have one choice - you can only

use a Recordset.

That said, there apparently is little or no difference between explicitly

creating a ForwardOnly, ReadOnly recordset and implcitly creating one ie:



set objRS = objConn.executes(strSQL) is the same speed as

objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText



A stored procedure may be faster, but unless you are returning more than 50

records it probably wont be measureable.



Using any other cursor type, or locktype will slow things down (but they are

only available if you create a recordset explicitly)



In terms of getting the data from the recordset onto the screen iterating

the recordset is the slowest, objRS.getrows is faster, and objRS.getstring

is the fastest



Again, with each type you loose flexibility.



> 1. A select from the database to populate a list box (10 to 50 rows max).

> 2. An update of a small number of rows (1-10).

> 3. A select from the database using parameters for a stored procedure.

> 4. A delete to the database.



a) Fastest - use a stored procedure to get the data, iterate the recordset

b) Use stored procedure, or use SQL string

c) um, I can't present you with the fastest here - you've already decided.

Use .getString if you can to get the resultant recordset onto the screen

d) Stored procedure, or SQL string



Note, .getRows only seems to be faster if you have more than 50 records or

so.



> Also is it possible to destroy the connection object used to get data from

the

> database and put it into a recordset object and be able to continue to

work

> with the recordset object?



With a recordset you *need* a connection object - either explicitly created

or implicitly created. You can't destroy the connection object and keep

working with the recordset...



Cheers

Ken

David,



	The fastest way I've found to SELECT data from a database and fill a

drop-down box is to declare a Command object and a Recordset object.  Set

the Command.CommandText to your SQL SELECT command.  Fill the Recordset by

setting it equal to the Command.Execute.  This allows you to not define a

Connection object and it automatically closes the connection to the

database as soon as the last record has been put into the Recordset.







>>>>>>>>>>>>>>>>Code
Snippet<<<<<<<<<<<<<<<<<<<<





Dim cmdWhatever, recWhatever



Set cmdWhatever = Server.CreateObject("ADODB.Command")

Set recWhatever = Server.CreateObject("ADODB.Recordset")



'The ActiveConnection property allows you to define

'the connection implicitly rather than by creating

'a Connection object.  As I understand it, this saves

' on server resources.

cmdWhatever.ActiveConnection = "DSN=myDSN;UID=;PWD=;"

cmdWhatever.CommandText = "SELECT * FROM tablename WHERE pk=" &

Request.Form("itemUserSelected")



'The following line is the "magical" part of this 

'whole approach.  The connection to the database

'is only open long enough to fill the Recordset

'object.  After that, you can iterate through the

'Recordset object to your heart's content.

'NOTICE - You must use the "Set" command again.

'Even though you initially defined the recWhatever

'object as an ADODB.Recordset, you have to redefine

'it because it is filled from the Execution of the

'Command object.

Set recWhatever = cmdWhatever.Execute



While Not recWhatever.EOF



	Response.Write "<option value='" & recWhatever_

	("pk_field") & "'>" & recWhatever("name_field")_

	 & "</option>"

	recWhatever.MoveNext

Wend



'It is imperative that you put the recWhatever.MoveNext method

'in the While..Wend section no matter what you expect the

'Execution method to return.  Several times I have mistakenly

'left out the .MoveNext method and realized, to my chagrin

'that the server will get tied up returning the same record

'numerous times.  Until the Recordset object hits the .EOF

'marker, it will iterate, returning the same record over and

'over if you have left out the .MoveNext method.





>>>>>>>>>>>>>>>>>>>>End
Code<<<<<<<<<<<<<<<<<<<<<<





	I hope this helps some.





KD





Kevin D Riggs



kevin.riggs@p...

AOL IM - "wsi tn"



Senior Network Administrator & Webmaster

Rhea County

Board of Education



http://www.rhea.k12.tn.us/

http://www.rheacounty.org/

Wow Kevin,



  I have always set the ActiveConnection property of a Command Object directly to a Connection Object.



   Never tried out Setting the Connection String this way.

   Yours is a better way of doing it definitely.

   Thanks for the Lovely Tip. Will use it hereafter.



           Cheers

           Subbu...





--



On Thu, 11 May 2000 12:37:16   Kevin D Riggs wrote:

>David,

>

>	The fastest way I've found to SELECT data from a database and fill a

>drop-down box is to declare a Command object and a Recordset object.  Set

>the Command.CommandText to your SQL SELECT command.  Fill the Recordset by

>setting it equal to the Command.Execute.  This allows you to not define a

>Connection object and it automatically closes the connection to the

>database as soon as the last record has been put into the Recordset.

>

>

>

>>>>>>>>>>>>>>>>>Code
Snippet<<<<<<<<<<<<<<<<<<<<

>

>

>Dim cmdWhatever, recWhatever

>

>Set cmdWhatever = Server.CreateObject("ADODB.Command")

>Set recWhatever = Server.CreateObject("ADODB.Recordset")

>

>'The ActiveConnection property allows you to define

>'the connection implicitly rather than by creating

>'a Connection object.  As I understand it, this saves

>' on server resources.

>cmdWhatever.ActiveConnection = "DSN=myDSN;UID=;PWD=;"

>cmdWhatever.CommandText = "SELECT * FROM tablename WHERE pk=" &

>Request.Form("itemUserSelected")

>

>'The following line is the "magical" part of this 

>'whole approach.  The connection to the database

>'is only open long enough to fill the Recordset

>'object.  After that, you can iterate through the

>'Recordset object to your heart's content.

>'NOTICE - You must use the "Set" command again.

>'Even though you initially defined the recWhatever

>'object as an ADODB.Recordset, you have to redefine

>'it because it is filled from the Execution of the

>'Command object.

>Set recWhatever = cmdWhatever.Execute

>

>While Not recWhatever.EOF

>

>	Response.Write "<option value='" & recWhatever_

>	("pk_field") & "'>" & recWhatever("name_field")_

>	 & "</option>"

>	recWhatever.MoveNext

>Wend

>

>'It is imperative that you put the recWhatever.MoveNext method

>'in the While..Wend section no matter what you expect the

>'Execution method to return.  Several times I have mistakenly

>'left out the .MoveNext method and realized, to my chagrin

>'that the server will get tied up returning the same record

>'numerous times.  Until the Recordset object hits the .EOF

>'marker, it will iterate, returning the same record over and

>'over if you have left out the .MoveNext method.

>

>

>>>>>>>>>>>>>>>>>>>>>End
Code<<<<<<<<<<<<<<<<<<<<<<

>

>

>	I hope this helps some.

>

>

>KD

>

>

>Kevin D Riggs

>

>kevin.riggs@p...

>AOL IM - "wsi tn"

>

>Senior Network Administrator & Webmaster

>Rhea County

>Board of Education

>

>http://www.rhea.k12.tn.us/

>http://www.rheacounty.org/