DAO Vs. ODBC
Mackay -- gs94@dial.pipex.com
Monday, February 26, 1996
The project that I am working on currently uses ODBC to handle =
databases. Unfortunatly this is rather slow for the volume of data I =
need to handle (I give up one design because it took ten minutes to get =
2500 records - a relativly small dataset in this case). The project will =
be moved onto Visual C++ 4.0 around late spring / summer so I was =
wondering if DAO has any improved performance. Or if there are any other =
database accessing tools that can potentially easily handle databases =
containing GIGAbytes of information.
Thanks in advance,
Colin Angus Mackay -- gs94@dial.pipex.com
Martin Wawrusch -- wawrusch@accs.co.at
Wednesday, February 28, 1996
[Mini-digest: 7 responses]
On Feb 26, 12:04am, Mackay wrote:
> Subject: DAO Vs. ODBC
>
> [ text/plain
> Encoded with "quoted-printable" ] :
The project that I am working on currently uses ODBC to handle
databases. Unfortunatly this is rather slow for the volume of data I
need to handle (I give up one design because it took ten minutes to get
2500 records - a relativly small dataset in this case). The project
will be moved onto Visual C++ 4.0 around late spring / summer so I was
wondering if DAO has any improved performance. Or if there are any
other database accessing tools that can potentially easily handle
databases containing GIGAbytes of information.
We face the same problems here. We used a 4Gl (Progress) to develop
client server apllications with heavy transaction loads but switched
to VC++ because we are a Windows only shop now (on the client side).
Database access in general isn't very good in MFC simple because
it focuses on destop databases.
One option that you should consider is Rogue Wave's dbtools.h++.
They have a nice wrapper for the raw database apis that is
much more oo than mfc dao will ever be. It currently supports
ODBC, Oracle,Informix and Sybase as native interfaces.
However, it has it's problems too. Number 1 is the price. To support
all the platforms you will have to pay approximately 1500 to 2000 USD
per year (Initial price + annual support).
Second problem is their commitment to VC++. What the german sales people
told me is that we will have to wait 2 months from now on until they'll ship
a VC++ 4.0 version. You can of course buy a source license and compile
the library yourself but that will cost you about USD 3000.
Another option would be to use Subtlewares library. I do not have
performance figures handy but what I have heard is that they are
quite fast. Price is quite low, a free demo is available over the net.
Third option is to develop your own interface classes. That sounds tougher
than it is. Expect about two weeks of hard work to get a reusable class
library and perhaps one week per database api (assuming you know ood and
database api programming). Downside is that you will have to support it
and that you will never catch up with all the other's features.
My conclusion is that you should take a close look at Rogue Wave if
you have the money. Or perhaps wait some month if you can. C++ database
wrappers are really hot toppics now for small startup companies. Prices
will go down soon.
[Note: A better place for this discussion is comp.databases or
comp.databases.object]
HTH
Martin
--
Martin Wawrusch | ESBMASAP, BNS A. Einstein
ACCS Software | Tel. : +43 1 983 46 91
Huetteldorferstr. 163/23 | Fax. : +43 1 983 01 00
A-1140 Vienna, Austria | EMail: wawrusch@accs.co.at
-----From: mikeblas@interserv.com
On Mon, 26 Feb 1996, Mackay wrote:
>The project that I am working on currently uses ODBC to handle
>databases. Unfortunatly this is rather slow for the volume of data
>I need to handle (I give up one design because it took ten minutes to get
>2500 records - a relativly small dataset in this case).
This might be quite reasonable. What is your data source? Is it a server?
What hardware platform is it running? (After all, 2500 rows in 10 minutes
would be _awesome_ for a 286 machine, but pretty embarassing for a HP-9000 or
a multi-processor DEC Alpha.) Did you tune it? How? What other load is on
the server?
What is your SELECT statement like? When you run your SELECT statement with
PLANONLY, what do you learn? Are your indexes being used? Are you doing
table scans? Are you creating temporary tables that are larger than your
free space or unallocated memory?
Are you saying that you do a single SELECT and you get back 2500 rows and it
takes ten minutes for all that to happen? Or are you saying that you all of
(do a SELECT, do another SELECT, do an UPDATE, do a SELECT, and then do an
INSERT) 2500 times and it takes ten minutes? Are you using stored
procedures? Are you using precompiled statements? What kind of recordset
are you using? What kind of scrolling are you doing? Are you asking ODBC to
use its cursor library? What version of who's database server are you using?
Is access to the database through a gateway of any sort?
Regardless of DAO, ODBC, SQLLIB, SQL*API or DB-Library, I've _never_ seen a
PC-class system that took ten minutes to process 2500 records without having
a _serious_ design problem (ie, a bad database design or a poorly-written SQL
statement) or a bad implementation problem (ie, poorly indexed tables,
incorrectly tuned server software). There _are_ problems which could take
ten minutes to do 2500 times, certainly, but you need to go through a pretty
big decision tree to arrive at the conclusion that you're as fast as you can
be. And none of the decisions in that tree ask "What API are you using?"
Since you don't bother to make any mention of any specifics of your case, I
have to assume that you don't know what's going on past your calls to ODBC
and that you've not bothered to tune the design of your database or examine
the way that the server handles your queries. I make that assumption because
someone who was truly interested in solving this kind of problem would know
that there are a lot more variables--all of which are _FAR_ easier to
adjust--than the API you're using to reach the database. One simple test you
could have done is to compare the time it takes you to do an iteration of the
query with a direct query tool (eg, ISQL/w or SQL Talk or Access) and with
the Microsoft ODBC Query Tool. That would help you isolate ODBC. I think
it's far more appropriate, though, to look at the query plan.
If you're _not_ using a server and you think that 2500 tuples is a relatively
small result set, your application is probably outclassing the database it is
using. That's particularly likely if you're writing a multiuser application.
> The project will be moved onto Visual C++ 4.0 around late spring / summer
> so I was wondering if DAO has any improved performance.
Generally speaking, if you're going to a database format which DAO handles
naitively, DAO is faster. If you're going to a format which DAO doesn't
handle naitively, or your database is located on a remote machine from the
client, you're better off with ODBC.
My book offers lots of advice about database tuning. (The second edition of
the book does that and compares DAO and ODBC.)
> Or if there are any
> other database accessing tools that can potentially easily handle databases
> containing GIGAbytes of information.
DAO doesn't give a rat's patootie about database size. ODBC doesn't give a
rat's patootie, either. It's the underlying database management software
that's in question. If you're storing gigabytes of information in an Access
database, you're not going to meet with much success. If you're storing
gigabytes of information in SQL Server, you'll have much better luck, but
you'd better start worrying about and learning about database tuning and how
database access patterns really work.
You'll notice that my answer didn't say much about ODBC vs. DAO performance.
The reason is this: I think you're ignoring the issues that affect your
performance far more radically than the small overhead that your database API
might or might not be adding.
>Thanks in advance,
I doubt you liked my answer.
.B ekiM
--
TCHAR szDisc[] = _T("These words are my own; I do not speak for Microsoft.");
-----From:
I don't think your problem is caused by ODBC. We're using it here and
can read much faster than that - multiple thousands of rows per minute
(we were just benchmarking some stuff and could read 200 rows out of
a particular table in around 4 seconds). What kind of database are you
attaching to, and what does your query look like? Is there an index
available that matches the 'WHERE' clause on your 'SELECT' statement?
Are you connecting to your database over a high-speed network, or
over a serial connection (RAS)?
Bob Jarvis
-----From: Pierre De Boeck
I'm rather surprised for so bad performances with ODBC. We have
implemented NT rule-based servers (in Prolog) that use ODBC to
access an ACCESS 2.0 medical database (just for testing!). It
consists of a set of medical prestations (about 6000) organised
by STAYS, in turn organised by PATIENT. It took about 30 secs
for retrieving the whole set. Not so bad.
Note that our servers deal directly with the ODBC API, bypassing
the MFC ODBC classes.
--
Pierre De Boeck
Mission Critical, Wijnegemhofstraat 199, B-3071 Erps-Kwerps
(Belgium)
Phone: +32 2 759 95 60 Fax: +32 2 759 27 60
email: pde@miscrit.be
///
(. .)
---------------------------------oOO--(_)--OOo------------------
----
-----From: shane
If you already have access to the VC++ 4.0 environment, then there is much
info. on this question. I was originally using ODBC in VC++ 4.0 to query a
MS Access 2.0 database. I ran some tests and was able to get multiple
transactions a second(on a database and recordset with more than 17,000
records) so it could be that you are going about things the wrong way. 2500
records is in no way large and you should not have that kind of problem.
Another issue remains, however. If you will be using MS Access as the
database, then DAO is definitely the way to go. Once I found that MS Access
was going to give me the performance I needed for our project, I decided to
try DAO. I then got about twice the performance(transaction times). From
what I have read, DAO is optimized for MS Access although you will be able
to use it for any database that has an ODBC driver. This probably isn't
recommended though. So, from experience, if you are using MS Access, then
you should definitely be using DAO. If you aren't using MS Access, then
ODBC is probably the better tool although I'm not quite sure on that one.
You should definitely look into why you are getting some poor performance
using ODBC. It should take a fraction of a second to do what you want to
do.(once the recordsets and Database are already open).
Good-luck.
shane@datatree.com
-----From: Mathias Olausson
When you say that ODBC is slow I assume you're using MFC to access your
database. I've recently been involved in rewriting a program that used MFC.
When we used the direct ODBC API instead of MFC classed we noticed up to 5
times better performance. Whether DAO will improve performance in the same
way I doubt. However if you're using an MS Access database it probably will
do some good.
// Mathias Olausson.
/////////////////////////////////////////////////////
// Mathias Olausson Internet: mo@tripnet.se //
// Sankt Pauligatan 11 //
// S-416 60 G=F6teborg //
// SWEDEN //
/////////////////////////////////////////////////////
-----From: Keith Faulkner
On the few occasions I tryed (played with) it, I found ODBC slow. For
example, moving to the last record in a maillist with about 10000
entries took about 20 seconds+ on good hardware. DAO performs the same
function on the same data in about 1 second. Complex queries take only a
couple of seconds, even when non-indexed fields are used. I am by no
means expert since I have only written a couple of vc database type apps
but my general impression is its very good. I seem to recall reading
somewhere that ODBC comes into its own on big data sources on networks.
Hope this helps.
Cheers .. Keith
Keith Faulkner
keith@g4eqz.demon.co.uk
Compuserve [100024,506]
Demon Internet ... The M25 of the Information Super Highway ...
Mike Blaszczak -- mikeblas@interserv.com
Friday, March 01, 1996
>-----From: Keith Faulkner
>On the few occasions I tryed (played with) it, I found ODBC slow. For
>example, moving to the last record in a maillist with about 10000
>entries took about 20 seconds+ on good hardware. DAO performs the same
>function on the same data in about 1 second.
This suggests that you weren't very careful in selecting cursor options or a
recordset type for your query.
.B ekiM
--
TCHAR szDisc[] = _T("These words are my own; I do not speak for Microsoft.");
William Drew -- solset@cais.com
Tuesday, March 05, 1996
On Mon, 26 Feb 1996 00:04:12 -0000, you wrote:
>The project that I am working on currently uses ODBC to handle =
>databases. Unfortunatly this is rather slow for the volume of data I =
>need to handle (I give up one design because it took ten minutes to get =
>2500 records - a relativly small dataset in this case). The project will =
>be moved onto Visual C++ 4.0 around late spring / summer so I was =
>wondering if DAO has any improved performance. Or if there are any other =
>database accessing tools that can potentially easily handle databases =
>containing GIGAbytes of information.
>
>Thanks in advance,
>
>Colin Angus Mackay -- gs94@dial.pipex.com
Colin,
I am currently developing an app that accesses Oracle on an HP
database server from a Win 3.1 client. I use ODBC and have not noticed
a difference in the performance in the native OCI and ODBC. One
CRecordset derived class joins 7 tables and returns from 1000 to 2500
rows in less than 2 secs. In accessing another datasource, it takes a
less complicated query mins to complete. The difference is not the
network, DBMS or code but the design. Take a look at the logical
design and it's physical implementation. A well designed database can
make all the difference.
William J. Drew
Solution Set, Inc.
solset@cais.com
| Вернуться в корень Архива
|