SELECT command very slow with ADO 
Author Message
 SELECT command very slow with ADO


DAO and ADO are totally differen technologies. DAO is
native to Access and uses its advantages. ADO is more
commont technology and is not oriented to particular type
of database. In some cases you can expect worse
performance then with DAO. But in many cases it depends on
how you implemented your code. In your particular case
when you open recordset using "SELECT * FROM Macros" SQL
statement it could take some time, because ADO retrieves
all records with all fields and transfers all this
information to client side. The main idea in that case
could be to select only records and fields, which you
really need in that particular moment. It will reduce time
of retrieving and transferring data from database to
client side. Also you can play with LockType, CursorType
and CursorLocation properties of recordset to improve
performance. For example if you are opening forward-only,
read-only cursor, then it work faster then oter types of


>-----Original Message-----
>Hi everybodyand thanks in advance!

>I hope I can get a little help from you!
>I wanted to changed my code from DAO to ADO due to the

poor performance that
>DAO has in some cases.

>I have a quiet simple code that accesses a database and
perfoms inserts and
>updates. Due to a not very good database design some

tables have up to 255
>fields. With DAO there was no problem accessing these
tables with SQL
>commands, but the problem came when updating or inserting
records. This took
>up to 10 secs for a record of a table.

>When we changed to ADO we thought our problems would be
solved. We have
>changed some code but the performance is worse than with
DAO when executing
>SELECT commands. It has improved a lot when updating or
inserting records.

>I enclose the code and an example project and hope you
can give us a hint.

>The commands we want to execute are as simple as you will
see, regular
>updating or accessing of single records in a table but
with a large number
>of fields

>    Dim rst As New ADODB.Recordset
>    Dim fld As ADODB.Field
>    Dim Data As String
>    Dim i As Integer
>    Dim Index As Integer
>    Dim arrayData() As String

>    Debug.Print "Before SQL " & Time 'Print time before

>    'Make de sql to get all values of Macros table, we
are not sure if
>    'the CursorType and LockType paramenters are correct
>    rst.Open "SELECT * FROM Macros", cnn, adOpenKeyset,
><====With 255 fields takes 10 seconds !!!!!

>    Debug.Print "After SQL " & Time 'Print time after sql

>    Index = 1

>    ReDim arrayData(1 To rst.Fields.Count) 'reserve an
array to introduce
>values of data

>    'Introduce the values of data to an array
>    While Not rst.EOF
>        For Each fld In rst.Fields
>            Data = fld.Value
>            arrayData(Index) = Data
>            Index = Index + 1
>        Next
>        rst.MoveNext
>    Wend

>    rst.Close   'Close recorset

>    ViewData = arrayData   'return as a result of

function the array of data

- Show quoted text -


>Thanks in advance


Sun, 27 Jun 2004 00:38:34 GMT
 SELECT command very slow with ADO

See the following Knowledge Base articles for more information on
performance and migrating from DAO to ADO:

Q225048: INFO: Issues Migrating from DAO/Jet to ADO/Jet

Q247613: Slow Performance in Jet 3.51 When Editing Recordset from SELECT

Q240434: HOWTO: Improve Performance of Applications Using Jet 4.0

Hope these help!

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit

Mon, 28 Jun 2004 02:44:22 GMT
 [ 2 post ] 

 Relevant Pages 

1. Select are slow after delete command

2. Slow, slow, slow search on VB6, ADO and SS7

3. Slow ADO Command Cancel using JET OLEDB

4. Update command is locked by a select command.

5. slow, slow search on ADO, VB6 and SS7

6. slow, slow search on SS7, ADO and VB6

7. Select * slower than Select champ1, champ2, ???

8. Select * slower than Select champ1, champ2, --Need other opinion--

9. Using the ADO Command Object to send an Execute command

10. ADO Command object and MSShape commands

11. ADO methods vs ADO commands - Which is faster?

Powered by phpBB® Forum Software