DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow 
Author Message
 DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow

Ive recently been involved in a project to convert an application from
DAO/JET databases to the Microsoft SQL 2000 server (version 8 Personal
Edition).  The application works well with DAO/JET, except it has hit the
1GB JET database limit, forcing us to look for alternatives.

Our speed tests comparing DAO/JET with ADO/SQL are shocking, so I would like
to describe them to see if anyone else can confirm the types of results we
are getting.

Our application does hundreds of thousands of individual record operations
(1) lookup a record using an integer (long) key and (2) add a record with
about 8 columns one of which is indexed.  The SQL server is running on the
same computer as the test application that we are using for the timing.
These times were obtained using a TCP/IP connection to the SQL server;
switching to using named pipes seemed to reduce the time by about 2%.

 --  DATABASE BUILD TEST TIMES  --

Add a record using DAO/JET = 0.35 milliseconds/record

ADO/SQL Add a record through a record set = 12.0 milliseconds/record

ADO/SQL add using begin/end transaction and batch update = 10.0
milliseconds/record

ADO/SQL using Insert Into SQL statement with batch update = 4.4
milliseconds/record

So the best method is the Insert Into SQL statement, and it takes 12.6
times as long as adding records using DAO/JET.

 -- RECORD LOOKUP TEST TIMES --

Record lookup using DAO/JET Seek operation on indexed field with random
index numbers = 0.05 milliseconds/lookup (thats 50 microseconds/lookup)

ADO/SQL lookup using Select Where SQL statement = 19 milliseconds/lookup

So the ADO/SQL lookups using the Select SQL statement takes 380 times as
long as a DAO/JET Seek operation.

I assume most of the speed difference comes from the fact that DAO/JET
functions are invoked as a .DLL loaded with our program, whereas ADO/SQL
operations must be packetized and sent to a separate task -- the SQL server.

Are we doing something dreadfully wrong, or are these reasonable relative
speeds? Can anyone suggest alternative approaches using the SQL server to
speed up the types of operations that were doing?

Thank you for your comments and suggestions.



Sun, 24 Oct 2004 05:49:18 GMT  
 DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow
I have found ADO to be slow at times, but how are you connecting to the SQL
Server? I don't know very much about this subject, but in the interest of
trying to help as much as I can, I found that how you work with connections
in ADO has a huge impact on speed...

Jason


Quote:
> I've recently been involved in a project to convert an application from
> DAO/JET databases to the Microsoft SQL 2000 server (version 8 Personal
> Edition).  The application works well with DAO/JET, except it has hit the
> 1GB JET database limit, forcing us to look for alternatives.

> Our speed tests comparing DAO/JET with ADO/SQL are shocking, so I would
like
> to describe them to see if anyone else can confirm the types of results we
> are getting.

> Our application does hundreds of thousands of individual record
operations -
> (1) lookup a record using an integer (long) key and (2) add a record with
> about 8 columns one of which is indexed.  The SQL server is running on the
> same computer as the test application that we are using for the timing.
> These times were obtained using a TCP/IP connection to the SQL server;
> switching to using named pipes seemed to reduce the time by about 2%.

>  --  DATABASE BUILD TEST TIMES  --

> Add a record using DAO/JET = 0.35 milliseconds/record

> ADO/SQL Add a record through a record set = 12.0 milliseconds/record

> ADO/SQL add using begin/end transaction and batch update = 10.0
> milliseconds/record

> ADO/SQL using "Insert Into" SQL statement with batch update = 4.4
> milliseconds/record

> So the best method is the "Insert Into" SQL statement, and it takes 12.6
> times as long as adding records using DAO/JET.

>  -- RECORD LOOKUP TEST TIMES --

> Record lookup using DAO/JET Seek operation on indexed field with random
> index numbers = 0.05 milliseconds/lookup (that's 50 microseconds/lookup)

> ADO/SQL lookup using "Select. Where" SQL statement = 19
milliseconds/lookup

> So the ADO/SQL lookups using the Select SQL statement takes 380 times as
> long as a DAO/JET Seek operation.

> I assume most of the speed difference comes from the fact that DAO/JET
> functions are invoked as a .DLL loaded with our program, whereas ADO/SQL
> operations must be packetized and sent to a separate task -- the SQL
server.

> Are we doing something dreadfully wrong, or are these reasonable relative
> speeds? Can anyone suggest alternative approaches using the SQL server to
> speed up the types of operations that we're doing?

> Thank you for your comments and suggestions.



Sun, 24 Oct 2004 06:15:01 GMT  
 DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow
You will be able to gain significant improvements using stored-procedures.

If your lookup only returns one row, you can call a stored-procedure
(prepare one for reuse for more speed) and return the data via parameters
instead of a recordset. (It is reckoned to be approx 1000 times faster).

Similar can be done with your inserts.

Alternatively, to maximise the journey from your process to SQL Server, you
can submit multiple INSERT statements to the connection wrapped in begin/end
trans. e.g. 500 at a time. This will perform significantly better than one
at a time.

HTH

Tim


Quote:
> I have found ADO to be slow at times, but how are you connecting to the
SQL
> Server? I don't know very much about this subject, but in the interest of
> trying to help as much as I can, I found that how you work with
connections
> in ADO has a huge impact on speed...

> Jason



> > I've recently been involved in a project to convert an application from
> > DAO/JET databases to the Microsoft SQL 2000 server (version 8 Personal
> > Edition).  The application works well with DAO/JET, except it has hit
the
> > 1GB JET database limit, forcing us to look for alternatives.

> > Our speed tests comparing DAO/JET with ADO/SQL are shocking, so I would
> like
> > to describe them to see if anyone else can confirm the types of results
we
> > are getting.

> > Our application does hundreds of thousands of individual record
> operations -
> > (1) lookup a record using an integer (long) key and (2) add a record
with
> > about 8 columns one of which is indexed.  The SQL server is running on
the
> > same computer as the test application that we are using for the timing.
> > These times were obtained using a TCP/IP connection to the SQL server;
> > switching to using named pipes seemed to reduce the time by about 2%.

> >  --  DATABASE BUILD TEST TIMES  --

> > Add a record using DAO/JET = 0.35 milliseconds/record

> > ADO/SQL Add a record through a record set = 12.0 milliseconds/record

> > ADO/SQL add using begin/end transaction and batch update = 10.0
> > milliseconds/record

> > ADO/SQL using "Insert Into" SQL statement with batch update = 4.4
> > milliseconds/record

> > So the best method is the "Insert Into" SQL statement, and it takes 12.6
> > times as long as adding records using DAO/JET.

> >  -- RECORD LOOKUP TEST TIMES --

> > Record lookup using DAO/JET Seek operation on indexed field with random
> > index numbers = 0.05 milliseconds/lookup (that's 50 microseconds/lookup)

> > ADO/SQL lookup using "Select. Where" SQL statement = 19
> milliseconds/lookup

> > So the ADO/SQL lookups using the Select SQL statement takes 380 times as
> > long as a DAO/JET Seek operation.

> > I assume most of the speed difference comes from the fact that DAO/JET
> > functions are invoked as a .DLL loaded with our program, whereas ADO/SQL
> > operations must be packetized and sent to a separate task -- the SQL
> server.

> > Are we doing something dreadfully wrong, or are these reasonable
relative
> > speeds? Can anyone suggest alternative approaches using the SQL server
to
> > speed up the types of operations that we're doing?

> > Thank you for your comments and suggestions.



Sun, 24 Oct 2004 16:03:22 GMT  
 DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow
It's hard to say exactly what is wrong but I'll try to give some hints:

1) Make sure that you use OLEDB resource pooling (in other words connection
pooling). Look up the MSDN how to accomplish that. In MTS/COM+ this is
automatic, but if you are not using that you'll have to use other tricks
that are described in the MSDN to enable it.

2) Do NOT share a global connection between objects and threads.

3) Use parameters in queries instead of filling in the values in the string.
Have a look at the ADO Command object for more information. Using parameters
results in reuse of query plans which results in better performance.

4) If your statements are static (in other words, they don't change
depending on passed parameters) the put them in stored procedures.

5) In stored procedures, the first statement should be 'SET NOCOUNT ON' and
the last should be 'SET NOCOUNT OFF'. This prevents SQL server from
generation the 'x rows affected' messages and results in better ADO/SQL
performance.

6) Try to batch your processing. For instance, get multiple rows, do
business processing on them and insert them. This will save you numerous
round trips.

I hope this might help.

Quote:

>Ive recently been involved in a project to convert an application from
>DAO/JET databases to the Microsoft SQL 2000 server (version 8 Personal
>Edition).  The application works well with DAO/JET, except it has hit the
>1GB JET database limit, forcing us to look for alternatives.

>Our speed tests comparing DAO/JET with ADO/SQL are shocking, so I would
like
>to describe them to see if anyone else can confirm the types of results we
>are getting.

>Our application does hundreds of thousands of individual record
operations
>(1) lookup a record using an integer (long) key and (2) add a record with
>about 8 columns one of which is indexed.  The SQL server is running on the
>same computer as the test application that we are using for the timing.
>These times were obtained using a TCP/IP connection to the SQL server;
>switching to using named pipes seemed to reduce the time by about 2%.

> --  DATABASE BUILD TEST TIMES  --

>Add a record using DAO/JET = 0.35 milliseconds/record

>ADO/SQL Add a record through a record set = 12.0 milliseconds/record

>ADO/SQL add using begin/end transaction and batch update = 10.0
>milliseconds/record

>ADO/SQL using Insert Into SQL statement with batch update = 4.4
>milliseconds/record

>So the best method is the Insert Into SQL statement, and it takes 12.6
>times as long as adding records using DAO/JET.

> -- RECORD LOOKUP TEST TIMES --

>Record lookup using DAO/JET Seek operation on indexed field with random
>index numbers = 0.05 milliseconds/lookup (thats 50 microseconds/lookup)

>ADO/SQL lookup using Select Where SQL statement = 19 milliseconds/lookup

>So the ADO/SQL lookups using the Select SQL statement takes 380 times as
>long as a DAO/JET Seek operation.

>I assume most of the speed difference comes from the fact that DAO/JET
>functions are invoked as a .DLL loaded with our program, whereas ADO/SQL
>operations must be packetized and sent to a separate task -- the SQL
server.

>Are we doing something dreadfully wrong, or are these reasonable relative
>speeds? Can anyone suggest alternative approaches using the SQL server to
>speed up the types of operations that were doing?

>Thank you for your comments and suggestions.



Sun, 24 Oct 2004 22:30:17 GMT  
 DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow


Quote:
> You will be able to gain significant improvements using stored-procedures.

I have serious doubts about this.

My theory is that the speed problem is due to the fact that with ADO my
client application has to send a packet through TCP/IP to a separate process
that must be awakened, service the request and then send back its reply. The
process of making a packet, sending it through TCP/IP (or a named pipe) two
ways and scheduling processes for execution involves a LOT of system
overhead.  With DAO, the linkage is through a few function calls directly
into a .dll that is loaded with my program.  There is no involvement of
TCP/IP and no scheduling of multiple tasks.

Even if there is zero time consumed in the SQL server to process the
request, I believe the overhead involved in sending a packet to the SQL
server (which is running on the same computer) may involve several
milliseconds.

Quote:
> Alternatively, to maximize the journey from your process to SQL Server,
> you
> can submit multiple INSERT statements to the connection wrapped in
> begin/end
> trans. e.g. 500 at a time. This will perform significantly better than one
> at a time.

We did exactly that in the best case (please reread my message).  Yet doing
that still resulted in ADO being 12 times slower than DAO for record
insertion.


Sun, 24 Oct 2004 22:35:12 GMT  
 DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow


Quote:
> I have found ADO to be slow at times, but how are you connecting to the
> SQL
> Server? I don't know very much about this subject, but in the interest of
> trying to help as much as I can, I found that how you work with
> connections
> in ADO has a huge impact on speed...

We are using a client-side cursor and optimistic locking. We have tried both
TCP/IP and named pipes as the transport mechanism and that makes only a few
percent difference in speed.  If you can recommend a connection that you
think would be faster, we will be happy to try it.

I believe the speed problem is caused by the overhead involved in making a
packet with the command, sending it through TCP/IP (or named pipes),
scheduling the SQL server to execute, then making the return result packet
(i.e., the record set), sending it back through TCP/IP and scheduling my
client task for execution to process the result.  I think this overhead
swamps the time required in SQL to parse the command and perform the
operation of looking up a single indexed record.



Sun, 24 Oct 2004 22:39:59 GMT  
 DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow
Some pointers that might help:

1) Make sure that OLEDB/ADO resource (connection) pooling is working. See
MSDN Library.
2) Use parameters in your select and insert statements. Do not paste the
values in your query string. See MSDN Library for the Command object.
3) Use stored procedures if your select and insert statements are always the
same, thus not changing because of parameters.
4) Use SET NOCOUNT OFF as first statement in stored procedures to disable
the sql server 'x rows affected' responses.
5) Use output parameters in stored procedures instead of returning a
resultset with always just one row.
6) Tell the ADO command object exactly what you are doing by setting the
CommandType property correctly.
7) When executing your command object, pass adExecuteNoRecords for the
Options parameter when you have stored procedures that don't return a
resultset.
8) Do not use the Refresh method on your Parameter collection when using
stored procedures. Explicitely declare them.
9) Try to batch your processing to save roundtrips.

Hope this might help.

Quote:

>Ive recently been involved in a project to convert an application from
>DAO/JET databases to the Microsoft SQL 2000 server (version 8 Personal
>Edition).  The application works well with DAO/JET, except it has hit the
>1GB JET database limit, forcing us to look for alternatives.

>Our speed tests comparing DAO/JET with ADO/SQL are shocking, so I would
like
>to describe them to see if anyone else can confirm the types of results we
>are getting.

>Our application does hundreds of thousands of individual record
operations
>(1) lookup a record using an integer (long) key and (2) add a record with
>about 8 columns one of which is indexed.  The SQL server is running on the
>same computer as the test application that we are using for the timing.
>These times were obtained using a TCP/IP connection to the SQL server;
>switching to using named pipes seemed to reduce the time by about 2%.

> --  DATABASE BUILD TEST TIMES  --

>Add a record using DAO/JET = 0.35 milliseconds/record

>ADO/SQL Add a record through a record set = 12.0 milliseconds/record

>ADO/SQL add using begin/end transaction and batch update = 10.0
>milliseconds/record

>ADO/SQL using Insert Into SQL statement with batch update = 4.4
>milliseconds/record

>So the best method is the Insert Into SQL statement, and it takes 12.6
>times as long as adding records using DAO/JET.

> -- RECORD LOOKUP TEST TIMES --

>Record lookup using DAO/JET Seek operation on indexed field with random
>index numbers = 0.05 milliseconds/lookup (thats 50 microseconds/lookup)

>ADO/SQL lookup using Select Where SQL statement = 19 milliseconds/lookup

>So the ADO/SQL lookups using the Select SQL statement takes 380 times as
>long as a DAO/JET Seek operation.

>I assume most of the speed difference comes from the fact that DAO/JET
>functions are invoked as a .DLL loaded with our program, whereas ADO/SQL
>operations must be packetized and sent to a separate task -- the SQL
server.

>Are we doing something dreadfully wrong, or are these reasonable relative
>speeds? Can anyone suggest alternative approaches using the SQL server to
>speed up the types of operations that were doing?

>Thank you for your comments and suggestions.



Sun, 24 Oct 2004 22:38:57 GMT  
 DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow
You're totally right about the in/out-of process thing.

I was concentrating on maximising SQL performance. DAO is well known to beat
ADO.

Sorry I wasted everybody's time ;-)


Quote:


> > You will be able to gain significant improvements using
stored-procedures.

> I have serious doubts about this.

> My theory is that the speed problem is due to the fact that with ADO my
> client application has to send a packet through TCP/IP to a separate
process
> that must be awakened, service the request and then send back its reply.
The
> process of making a packet, sending it through TCP/IP (or a named pipe)
two
> ways and scheduling processes for execution involves a LOT of system
> overhead.  With DAO, the linkage is through a few function calls directly
> into a .dll that is loaded with my program.  There is no involvement of
> TCP/IP and no scheduling of multiple tasks.

> Even if there is zero time consumed in the SQL server to process the
> request, I believe the overhead involved in sending a packet to the SQL
> server (which is running on the same computer) may involve several
> milliseconds.

> > Alternatively, to maximize the journey from your process to SQL Server,
> > you
> > can submit multiple INSERT statements to the connection wrapped in
> > begin/end
> > trans. e.g. 500 at a time. This will perform significantly better than
one
> > at a time.

> We did exactly that in the best case (please reread my message).  Yet
doing
> that still resulted in ADO being 12 times slower than DAO for record
> insertion.



Sun, 24 Oct 2004 22:49:23 GMT  
 DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow
Unfortunately, I don't think I can offer much more to help you out than what
Tim and Frank have already mentioned. I am not very knowledgable on the
intricacies of ADO, and the only thing I can toss off (and someone please
correct/clarify me if I'm way off base) is that perhaps server-side cursors
and a different locking method may help?

I came into VB from an Access/DAO background, and I must admit, I still use
DAO in Access2K whenever I have to work in it - although 95% of our backends
are Jet databases...

Sorry I couldn't be more of a help,

Jason

Quote:
> We are using a client-side cursor and optimistic locking. We have tried
both
> TCP/IP and named pipes as the transport mechanism and that makes only a
few
> percent difference in speed.  If you can recommend a connection that you
> think would be faster, we will be happy to try it.

> I believe the speed problem is caused by the overhead involved in making a
> packet with the command, sending it through TCP/IP (or named pipes),
> scheduling the SQL server to execute, then making the return result packet
> (i.e., the record set), sending it back through TCP/IP and scheduling my
> client task for execution to process the result.  I think this overhead
> swamps the time required in SQL to parse the command and perform the
> operation of looking up a single indexed record.



Sun, 24 Oct 2004 22:55:57 GMT  
 DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow

On  8-May-2002, "Frank Schoondermark"

Quote:

> It's hard to say exactly what is wrong but I'll try to give some hints:

> 1) Make sure that you use OLEDB resource pooling (in other words
> connection
> pooling). Look up the MSDN how to accomplish that. In MTS/COM+ this is
> automatic, but if you are not using that you'll have to use other tricks
> that are described in the MSDN to enable it.

Thank you for your comments.

We are holding the connection to the server open across multiple operations,
so I don't think connection pooling is going to affect things, but I'm open
to your comments.  If you think I'm wrong, please explain why.

Do you have any ballpark numbers as to how many milliseconds are required to
send a trivial (null) operation from a client, through TCP/IP to the SQL
server, have it do essentially nothing on the SQL server (return no
records), and send its success status back via TCP/IP to the client?  Let's
say we're running on a 1.2 GHz Pentium III.  I believe the time is in the
range of 3-4 milliseconds.

The nature of my application has data coming in from an external source one
record at a time.  I must look up each record in a database and take action
based on whether the record's key exists in the database. So I have to do
many (hundreds of thousands) single-lookup operations.  If the overhead for
a single operation is 4 milliseconds then the time to process 500,000
records will be at least 500000*.004 = 2000 seconds.

In comparison, I believe the overhead involved in sending a no-op operation
through DAO/JET and getting the result back is in the range of 40
microseconds.



Sun, 24 Oct 2004 23:28:20 GMT  
 DAO/JET speeds vs. ADO/SQL - ADO is shockingly slow
Hi,

If you are approaching the convertion as if you were using a new access
database then that might of course be one explanaition why things aren't
running as smoothly as you would like. (SQL and access are quite different)

Oke; you are absolutelly right to think that a dll is faster then a
communication with sql-server.
So, knowing that, please keep your round trips as low as possible. Try to
obtain as many data as possible 1 one request. On the other hand don't push
it. (No complete batch processing)

I once had to optimize some code that was recursive and in every recursive
call it did a SQL-SELECT.
It should build up a menu or something. The optimization was quite simple. I
retrieved all the data with 1 query and afterworths I build the menu
recursivelly.The perfomance was no longer an issue.

The second thing that comes to mind is where your cursor is located. You can
create a clientside or serverside cursor. Although, it doesn't matter much
in access because it runs all in the same proces-space, it does matter in
SQL-SERVER.

A serverside cursor (default) will keep all the requested data in the
proces-space of sql-server and returns a row when you move through your
ado-recordset.
Yep this is a round-trip and communication-overhead is expensive. If you
lock (batch)optimistic (and you should) and you are not using Keyset or
Dynamic cursors for a particulair reason (like changes by other users), you
stick to:

- A serverside cursor, adOpenForwardOnly and LockReadOnly. Deafult the
cachesize is on. It's called the firehose and it returns a row one by one
very rapidy.
- A clientside cursor, adOpenStatic and LockBatchOptimistic. When you query,
you retieve all data at once and ado will keep a cursor.  (When you have the
data you can disconnect the recordset to allow other users to use this
connection)

If you are using SQL-SERVER only to store more data then access can, you
won't gain very much. SQL-SERVER strength is concurrency (multi-user),
security, robustness, scalabiliy and maintainability.

And if you want to know about performance and sql-server, you might consider
the books from William R. Vaughn. Like "ADO Examples and Best Pratices". See
his site at www.betav.com.

Good Luck


Quote:
> I've recently been involved in a project to convert an application from
> DAO/JET databases to the Microsoft SQL 2000 server (version 8 Personal
> Edition).  The application works well with DAO/JET, except it has hit the
> 1GB JET database limit, forcing us to look for alternatives.

> Our speed tests comparing DAO/JET with ADO/SQL are shocking, so I would
like
> to describe them to see if anyone else can confirm the types of results we
> are getting.

> Our application does hundreds of thousands of individual record
operations -
> (1) lookup a record using an integer (long) key and (2) add a record with
> about 8 columns one of which is indexed.  The SQL server is running on the
> same computer as the test application that we are using for the timing.
> These times were obtained using a TCP/IP connection to the SQL server;
> switching to using named pipes seemed to reduce the time by about 2%.

>  --  DATABASE BUILD TEST TIMES  --

> Add a record using DAO/JET = 0.35 milliseconds/record

> ADO/SQL Add a record through a record set = 12.0 milliseconds/record

> ADO/SQL add using begin/end transaction and batch update = 10.0
> milliseconds/record

> ADO/SQL using "Insert Into" SQL statement with batch update = 4.4
> milliseconds/record

> So the best method is the "Insert Into" SQL statement, and it takes 12.6
> times as long as adding records using DAO/JET.

>  -- RECORD LOOKUP TEST TIMES --

> Record lookup using DAO/JET Seek operation on indexed field with random
> index numbers = 0.05 milliseconds/lookup (that's 50 microseconds/lookup)

> ADO/SQL lookup using "Select. Where" SQL statement = 19
milliseconds/lookup

> So the ADO/SQL lookups using the Select SQL statement takes 380 times as
> long as a DAO/JET Seek operation.

> I assume most of the speed difference comes from the fact that DAO/JET
> functions are invoked as a .DLL loaded with our program, whereas ADO/SQL
> operations must be packetized and sent to a separate task -- the SQL
server.

> Are we doing something dreadfully wrong, or are these reasonable relative
> speeds? Can anyone suggest alternative approaches using the SQL server to
> speed up the types of operations that we're doing?

> Thank you for your comments and suggestions.



Thu, 28 Oct 2004 21:09:27 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. DAO vs ADO on Jet

2. ADO Jet 4.0 performance problem vs Jet 3.51

3. DAO -vs- ADO, SQL Parsing

4. ADO vs DAO vs RDO

5. VB5 vs VB6, DAO vs ADO?

6. ADO vs RDO vs DAO?

7. VB5 vs VB6, DAO vs ADO?

8. speed with ADO Jet 4.0

9. Slow ADO Command Cancel using JET OLEDB

10. ADO BatchUpdate w/ Jet 4.0 way too slow...

11. slow ADO performance with Jet 4.0

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

 

 
Powered by phpBB® Forum Software