Connection.Execute instead of Recordset.Open 1

[hdougl1]

All examples I've seen in returning one or more rows from a database using a Select query use the recordset.Open except in stored procedures.

I have found that using the Connection.Execute is SIGNIFICATLY faster.

Connection established
Set rst = Server.CreateObject("ADODB.Recordset&quot
SQL = “Select * from Table”
Set rst = Connection.Execute(SQL)
Do While Not rst.EOF
Display records
Loop

I don’t understand how and why it works but it does, setting the rst object as a recordset then the return value of the execute statement. Most of the documentation I’ve read says that you can’t do this, and some talk theory without examples on this issue. Any enlightenment would greatly be appreciated.

And I hope that this may speed up someone else’s result set.

 

[Tarwn]

First, you should not need to declare your recordset as a recordset object. When you use the Set function you are redeclaring your rst to be the object that is being returned from the execute, in this case also a recordset.
It is my understanding that .Execute works faster because it is only sending a single SQL query to the database where .Open actually queries the database more than once in order to formulate the query and execute it. One other way you could speed up this transaction is by selecting specific field names in your SQL statement. When you execute and SQL statement that is selecting * from a table the database turns this into two queries in order to first get all of the field names and then second get the information from the table. If you specify the field names you want then it will cut down on a step in the database processing. Another addition is that if you are querying for a field that has a large amount of text, it should be placed last in the list of fields your asking for. This speeds up the databases formulation of the recordset and it can then return it quicker.
-Tarwn The three most dangerous things in the world are a programmer with a soldering iron, a hardware type with a program patch, and a user with an idea
-computer saying (Wiz Biz - Rick Cook)

 

[sera]

Nice answer Tarwn, this post deserves a star!
Sera
I often believe that...
My computer is possessed!

 

[Tarwn]

Thanks, I had forgotten I posted this
Glad it helped out/was worth reading
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html