how to speed up the adoConn.execute

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Smiles - 14 Apr 2007 05:54 GMT

Hi there,

this is probably a general question, but urgent to me. please help.

Senario:

Access DB with a table of about 1 million records, with VB 6.0 + AdoDB
adoConn.execute " delete * from tableA where (colA=1 or colA=2 or colA=3 or
... or colA=#X) and (colB = 9 or colB = 8 or ... or colB=#ValueX) and (colC
= bala or bala)

it takes about 50 seconds to complete that command,
with a PC of P4 3.0GHZ, 2GB Ram, SATA Harddisk of plenty space.

However, do that similar command with FoxPro
like:
delete all from tableA where (colA=1 or colA=2 or colA=3 or ... or colA=#X)
and (colB = 9 or colB = 8 or ... or colB=#ValueX) and (colC = bala or bala)
it takes ONLY several seconds, less than 10 seconds.

I have tried to add index to tableA in Access,
with index_ColA only, it takes 46 seconds
with index_colX to ALL colX, it takes about 59 seconds,
that is even worst.

my question is: how to speed up the adoConn.execute command in general.

Thanks a lot for help

Smiles :)

Reply to this Message

Ralph - 15 Apr 2007 02:53 GMT

> Hi there,
>
[quoted text clipped - 26 lines]
>
> Smiles :)

I can help shed some light on a few things.

1) You will never 'beat' FoxPro. It is perhaps a little known fact but when
it comes to inserts/deletes/etc on a single table. Nothing is faster than
dbf files and a proper driver. So you making an unfair comparison.
If you need speed for an isolated application (on one box) always use a
simple file and an ISAM provider. (For even faster speeds investigate
dedicated libraries like CodeBase - sort or delete a million records in less
than 3 seconds, or write your own.)

2) Which leads to my second suggestion. Don't use ADO. Use DAO if using a
simple client/server with a local MSAccess database. It will be faster. ADO
under these circumstances will always be slower.

3) If you have to use ADO, what Provider are you using with it? Hopefully
you are using a Jet OLEDB and not ODBC.

4) Just for clarity show the complete SQL statement. Perhaps we can see
something to optimize it. Also if you are stuck with ADO show us the
complete connection string and ADO library you are using.

-ralph

Reply to this Message

Franck - 16 Apr 2007 18:54 GMT

DAO should make a huge difference according to your number of field.
if you have to go for Ado go for Jet 4.0 (or 3.5) not oledb

Me too i dont think you could reach FoxPro speeds, unless you switch
to mssql, even there.

Reply to this Message

VB Forum / Database Access / April 2007

how to speed up the adoConn.execute