ODBC Performance?

[Guest]

Is the performance of ODBC to SQL Server comparable to that of a Jet database
in Access 2003?
ADO or DAO?
Never used ODBC but might need to.
Many thanks.
Ron

 

[Tom Ellison]

Dear Ron:

Technically that's a complex question. A SQL Server database's performance,
properly constructed and tuned, can be quite fast. If there are extensive
query processes, the SQL Server database can be expertly manipulated to
provide vastly superior services. It is, after all, rated as one of the
fastest in the world.

I expect there is some penalty due to the ODBC layer, but I've never really
noticed it. I'd call that negligible.

If the SQL Server database is not particularly well constructed, you lose
these benefits, but it is still generally on a par with Jet.

All this is from my own experience, and someone may find a way of creating
an opposite result to what I've stated.

If you truly exploit the power of SQL Server, you won't be going wrong.

Tom Ellison

 

[Albert D.Kallal]

I have to concur with Tom's response.

The query engine in sql server is smarter then JET.

In addition, often the server has more memory, and processing then your
desktop computer.

However, if you just talking about raw record transfer rate, then JET tends
to be a GOOD deal faster then sol server.

After all, JET just has scrape data right off of the disk drive With sql
server, you are USUALLY going over a network, and it is also a socket
connection to the server.

So, it really depends on the setup. Without network, and just a local file
on the hard disk, then JET is going to be faster then sql by a good amount.

Throw in a network, and multi-user, then obviously, the advantages swing
towards sql server.

Without question, you see weekly posts in the sql newsgroups about how
moving data from JET to sql server slowed down a application by a lot.

So, it is not a cut and dry answer. And, simply moving data to sql server
will NOT boast your performance unless your setup and designs can take
advantage of the new setup....

So, while JET does not compare to sql server in terms of scalability, and
the ability to work with many users, JET on its own is very fast......

 

[Tom Ellison]

Thanks Albert,

However, you can also have a single computer setup, with MSDE or SQL Express
on the workstation with the application, and no network.

The worst choice you mention would be "sol server". We should stay away
from that. : )

Tom Ellison