Re: Query performance MS-Access vs. SQL-server 2000

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 01/13/05 

Date: Thu, 13 Jan 2005 10:16:24 -0500

Marco Lorenz wrote:
> Hi Bob
>
> thanks for Your comments.
>
> In the meantime I found out, that the main (performance) difference
> between the two alternatives is the use of DAO vs ADO:
>
> Using ADO on the MS-Access table takes about 100x longer, than using
> DAO!!!
>
> Using ADO on SQL-Server is approx. 10x slower than DAO, but still 10x
> faster than ADO on the Access-table....

This may be due to the cursor type/location you chose to use in ADO. With
the Jet OLE DB provider, if you use a client-side cursor, you get two cursor
libraries involved due to the cache. Using the default server-side,
forward-only cursor will yield better performance. However, it still may not
be as good as DAO, which was designed to work directly with Jet databases.

You should be aware that the use of DAO in IIS applications (ASP) is
strongly discouraged due to its thread usage. Desktop applications only for
DAO.

You should also be aware that SQL Server supports stored procedures which
can utilize output parameters. In your test, you are retrieving results in a
single record. Since you don't need the functionality of a cursor when
processing a single record, you should consider retrieving the results as
less expensive output parameters (using an explicit Command object) rather
than an expensive cursor (recordset). See:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.general/msg/2c935bd7c531d82b

>
> So the question is: is there a better library available for VB
> accessing SQL-server than ADO - as far as I found out, DAO cannot be
> used for SQL-server?

DAO can use ODBC to access SQL Server, but the use of an intermediate API
will effect performance. Plus, given that DAO was designed from the ground
up to work with Jet databases, you will not have all the functionality
provided by ADO. As for the existence of other data-access libraries, unless
you wish to write your own, this is the only game in town.

Make sure you use the native OLE DB provider for SQL Server (SQLOLEDB),
which I see that you did, rather than the deprecated OLE DB Provider for
ODBC databases.

I'm thinking that your test is not really that realistic. Are you really
planning to hammer the database with 5000 consecutive queries, with no
processing of results taking place? I'm thinking that the true bottleneck
for your application performance will be the processing of the results from
the query, not the time taken to retrieve the results from the database.
>
>
>
> "Bob Barrows [MVP]" wrote:
>
>> Alejandro Mesa wrote:
>>> I can not tell you anything about ms access, but about sql server,
>>> the statement is being parse and compile every time you send it to
>>> sql server in order to get a good execution plan.
>>
>> It's the same in Access. Saved queries are pre-parsed and compiled.
>> Dynamic SQL (ugh!!!) needs to be parsed and compiled every time.
>>
>> Bob Barrows
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM" 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Relevant Pages

  • Re: Why not use DAO?
    ... You have been refering to DAO vs. ADO when these two things (in the context ... you must have ALREADY used a provider to get to. ... interface for working with JET databases. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Why not use DAO?
    ... interface for working with JET databases. ... discussion about that (DAO was also faster as classic ADO) ... DAO is especially made and optimized for ACCESS database ADO is a more ... universal provider so ofcourse DAO wil outperform ADO in most ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Why not use DAO?
    ... All reall life programmers i know of use the jet oledb 3.5 or 4.0 provider ... ADO for ACCESS ... The TS asked "Why not used DAO ?" ... interface for working with JET databases. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Access View
    ... DAO works with Jet databases ... DAO should* be able to allow a CREATE VIEW ... I was referring to using it from ADO in ASP, not as a function in Access. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Count number of Records Query always returns -1
    ... DAO Count will return the correct number of rows in the Recordset (after ... ADO RecordCount may not return the actual number of rows in the Recordset ... depending on the cursor type. ...
    (microsoft.public.access.queries)