Why not use DAO?

[TC]

I love ADO.NET, but I find OLEDB to be unacceptably slow. The last time
I tried to use a Jet database for local data storage, I gave up and
wrote my data to a binary flat file instead. This time, I really need
to use a relational database, so I've been making the best of OLEDB and
cursing its performance.

A few days ago, however, I tried an experiment and wrote a
DAODataAdapter class, which implements DataAdapter using DAO. For
updates to an .mdb file, it is 4x faster than OLEDbDataAdapter!

So now I have a dilemma. Should I use DAO in my shiny new NET
application? I know DAO is deprecated; I know COM objects have memory
issues and use a different architecture than NET objects; I know this,
but I'm not going to ignore a 4x difference in performance.

My plan, therefore, is to use DAO and damn the consequences. I'm
posting here just to find out what other experienced NET developers
think about this. Certainly, I'm not the first person to go down this
road, and I'm curious what choices others have made.


-TC

 

[Stephany Young]

Well you just do that

I hope that nobody has ever told you "Don't use DAO." but if they did I hope
they really meant "It is recommended that you don't use DAO.", which are two
totally different statements.

I, myself, am surprised that you report such a 'speed' difference but then
"For updates to an .mdb file, it is 4x faster" is a pretty broadbrush
statement.

I, for one would like to see the code you are comparing both for OleDB and
DAO.

 

[Cor Ligthert [MVP]]

TC,

I have an opposite opinion as Stephany, I surely would not go the road from
DAO. Even moreover, I would not go any direction using the Jet Engine for
new applications. We don't know how long it will be on newer OS. Therefore
just go to SQLExpress as is often told in these newsgroups is in my idea a
better approach.

I am with Stephany curious about your code, although it is known that DAO is
slightly quicker than all other that is made after it.

Cor

 

[Michel Posseth [MCP]]

Well especially for Stephany ..

Let me be the one that tells you "don`t use DAO" and with that i mean stay
away of it there is only one exception in my opinion when you should still
use it
"You are a VB6 programmer , and need to maintain a prog written with DAO "

In VB.Net ( all versions ) it is a definite no go
Why ?? well because it is absolutely undeniable obsolete , i would also not
recomend classic ADO cause it is obsolete because we have ADO.Net
DAO is in most situations faster on a Access database ,,, there is no
discussion about that ( DAO was also faster as classic ADO )

However i would recomend you to use a mdf in your projects , this will blast
away the perfomance of Access and is the bether way as using obsolete
products in your newly to deploy application.

By the way a standard ADO recordset with a firehose cursor is also much
faster as a typical ADO.Net table adapter aproach however if you use a
datareader they perform equall ADO.Net might even be faster on SQL server
cause it uses a optimized driver so don`t compare pears with apples


regards

Michel Posseth [MCP]

 

[Scott M.]

I'll second that!

Well you just do that

I hope that nobody has ever told you "Don't use DAO." but if they did I
hope they really meant "It is recommended that you don't use DAO.", which
are two totally different statements.

I, myself, am surprised that you report such a 'speed' difference but then
"For updates to an .mdb file, it is 4x faster" is a pretty broadbrush
statement.

I, for one would like to see the code you are comparing both for OleDB and
DAO.

 

[Scott M.]

Michael,

I ignored most of your message because you are not providing any real
infromation. Simply saying "don't use this because the powers that be say
it's obsolete" doesn't quite cut it.

What is not in doubt is that DAO has historically been the fastest interface
for working with JET databases. Saying otherwise is just not true.

Also, speaking of apples and pears, why are you talking about TableAdapters
and recordsets, when we are talikng about data access and not data
storage/manipulation?

Having said all of this, I personally use the OLE DB Provider for JET when
using Access databases because I prefer the availability of the provider.
But, let's be clear...whatever provider/driver you use, the data can be
loaded up into whatever container you wish.

Well especially for Stephany ..

Let me be the one that tells you "don`t use DAO" and with that i mean stay
away of it there is only one exception in my opinion when you should
still use it
"You are a VB6 programmer , and need to maintain a prog written with DAO "

In VB.Net ( all versions ) it is a definite no go
Why ?? well because it is absolutely undeniable obsolete , i would also
not recomend classic ADO cause it is obsolete because we have ADO.Net
DAO is in most situations faster on a Access database ,,, there is no
discussion about that ( DAO was also faster as classic ADO )

However i would recomend you to use a mdf in your projects , this will
blast away the perfomance of Access and is the bether way as using
obsolete products in your newly to deploy application.

By the way a standard ADO recordset with a firehose cursor is also much
faster as a typical ADO.Net table adapter aproach however if you use a
datareader they perform equall ADO.Net might even be faster on SQL server
cause it uses a optimized driver so don`t compare pears with apples


regards

Michel Posseth [MCP]

TC,

I have an opposite opinion as Stephany, I surely would not go the road
from DAO. Even moreover, I would not go any direction using the Jet
Engine for new applications. We don't know how long it will be on newer
OS. Therefore just go to SQLExpress as is often told in these newsgroups
is in my idea a better approach.

I am with Stephany curious about your code, although it is known that DAO
is slightly quicker than all other that is made after it.

Cor

 

[Cor Ligthert [MVP]]

Scott,

What is the sense of this message from you bad humeur,

Michael,

I ignored most of your message because you are not providing any real
infromation. Simply saying "don't use this because the powers that be say
it's obsolete" doesn't quite cut it.

I completely disagree with you I see Michael as one of the most important
contributers to this newsgroup.

Not to disapoint you, you are as well in those. However I don't see any
sense of the need of the message from you. I 100% agree with what Michael
has written about this.

Cor

 

[Michel Posseth [MCP]]

First of all Cor thank you for your support in this mather it is clear to
me that you completely read it and interpreted it in the right way

Second :

Scott ...

You :

What is not in doubt is that DAO has historically been the fastest
interface for working with JET databases. Saying otherwise is just not
true
Me:
DAO is in most situations faster on a Access database ,,, there is no
discussion about that ( DAO was also faster as classic ADO )

To be more clear :

I said most situations cause if you do data manipulation on different
databases ACCESS to SQL for instance ADO is the bether and faster choice
DAO is especially made and optimized for ACCESS database ADO is a more
universal provider so ofcourse DAO wil outperform ADO in most situations
on ACCESS databases

Cause in my experience i actually found situations were ADO was faster due
to its superior caching mechanism , in my previous employment i worked 8
years for a company who deployed there applications with ACCESS 97 / 2000
databases and i did a lot of speed comparisation between all sorts of
databases and providers that are availlable , and sometimes i discovered
some surprising results when you actually do multiple selects on hughe
resultsets ADO will be faster on the second select , in our situation ADO
became interesting due to this fact as we wrote catalog software where we
actually queryed with hughe resultsets spanned on multiple databases (
multiple access database because of the size limit in this occasion ) and
did these query`s more than once ( actually a few dozen during runtime of
the program ) .


You

I ignored most of your message because you are not providing any real
infromation. Simply saying "don't use this because the powers that be say
it's obsolete" doesn't quite cut it.
And

Having said all of this, I personally use the OLE DB Provider for JET when
using Access databases because I prefer the availability of the provider.
But, let's be clear...whatever provider/driver you use, the data can be
loaded up into whatever container you wish.
Me

Let me be the one that tells you "don`t use DAO" and with that i mean stay
away of it there is only one exception in my opinion when you should still
use it
"You are a VB6 programmer , and need to maintain a prog written with DAO "

In VB.Net ( all versions ) it is a definite no go
Why ?? well because it is absolutely undeniable obsolete

To be more clear :

Well actually i wouldn`t know how i could be more clearer about this but i
try to convince you , DAO was already declared obsolete by MS on the VB6
platform so using it now in VB.Net would be a valid way of coding ? , well i
would laugh the person away who would come with this sort of coding to my
desk

About the availlability of the provider ,, funny as far as i know mdac 2.5
is the latest version that actually included Jet Oledb drivers so i wish
you lots of luck on fresh installed systems , in the company i previously
mentioned i was also responsible for the deploy packaging and XP doesn`t
like Mdac 2.5 as it is standard equiped with a newer version so you have
to find the seperate installation package ( that i now can`t even find on
the MS site , but it sure was there a few years ago ) or have as dependancy
of your app that an office product should be installed .


You

Also, speaking of apples and pears, why are you talking about TableAdapters
and recordsets, when we are talikng about data access and not data
storage/manipulation?
Me

By the way a standard ADO recordset with a firehose cursor is also much
faster as a typical ADO.Net table adapter aproach however if you use a
datareader they perform equall ADO.Net might even be faster on SQL server
cause it uses a optimized driver so don`t compare pears with apples

To be more clear :

Well ..... i just wanted to make clear that you should compare these on
there equivalant , a firehose cursor opened recordset should be compared to
a datareader
and a updatable recordset should be compared to a table adapter

This brings me to the following statement , and i must say that i was
convinced about this by Bill Vaughn in a few discussions i had in the past
with him while i was also sticking to Access .

That if you use a MDF in your projects , wich is nowadays so easy to do and
to deploy etc etc you wil blast away the old perfomance of Access , as far
as i know the now recomended way of MS is to use this database in
combination with .Net there is now actually no reasson to stick with Access

The support for Access is fading away , why isn`t there a 64 bits provider ?
, why isn`t ADOX ported to .Net ?


Well i hope i hope this makes things clearer

regards

And merry christmas to anyone who reads this

Michel Posseth

 

[Scott M.]

Cor,

I didn't say anything about Michael's status or knowledge. What I did say
was that a good portion of his post had no real basis in fact and was not
relevant to the OP question.

Happy Holidays.

 

[Scott M.]

Hi Michel,

There were 2 things about your fist message that troubled me and you've
resolved one of them (putting some reasoning behind your blanket
statements). But I still have to say that you are still confusing apples
and pears or more like apples and watermelons.

You have been refering to DAO vs. ADO when these two things (in the context
of this discussion) are very different. DAO is a database connection
provider/interface used by various controls, but DAO is not the controls
themselves. ADO is a set of objects that provide an API to a database that
you must have ALREADY used a provider to get to. ADO doesn't imply the
underlying provider (as I'm sure you know you can use several different
providers to get to Access and still be using ADO). Did you mean to say OLE
DB Providers instead of ADO because DAO and OLE DB Providers are apples to
apples and DAO compared to ADO is apples to watermelons.

Additionally, it still doesn't make sense to talk about data containers
(DataAdapters, TableAdapters, DataReaders) here since that leads to
confusion between data providers and data API's (which you yourself have
already confused when comparing DAO and ADO). And data containers don't
have anything to do with the speed of the communication via the
driver/provider, which was the whole point of the post. Your recordset,
tableadapter, datareader comments are simply comparisons of ADO.NET's
connected vs. disconnected objects, but talking about the performance of
these objects doesn't speak to the speed of the underlying provider.

In the end, I agree with you about which road to take (as I stated in my
first message) and for many of the same reasons, but I felt compelled to
play devil's advocate here because your first post began with more of a
sermen (just don't use it because I & MS said so), than any facts and both
your posts get off-topic by DAO and ADO and talking about data APIs and
contaners, which don't have anything to do with the OP question.


Happy holidays.

- Scott

 

[Michel Posseth [MCP]]

Well .....

All reall life programmers i know of use the jet oledb 3.5 or 4.0 provider
i.c.w. ADO for ACCESS

Ofcourse i am aware of what you stated however i thought it would be
obvious ( by the way ODBC outperforms OLEDB as long as you do single
selects ) however having to tell all this information could fill a book .

The TS asked "Why not used DAO ?" i gave valid reassons not to use DAO in
..Net ( it is oficially declared Obsolete a long while ago and you are thus
not able to support it to your customers )

As extra i mentioned the stuff regarding classic ADO and ADO.Net and again
i thought it would be obvious in what i mean .. i know a lot of people are
still using classic ADO because of the exact reasson i mentioned .

It could sure be that the TS is falling back to legacy ( pre .Net ) due to
the fact that he believes the older technolgies are perfoming better however
they do absolutely not if you use there counterparts on the .Net platform
( MDB = MDF , a firehose cursor opened recordset should be compared to
a datareader and a updatable recordset should be compared to a table
adapter )

That is what i was trying to do ... i understand now that you misunderstood
my intentions

Well maybe it was a language or cultural thingy , but i hope it is cleared
up now

I don`t know where you are from but i live in the Netherlands with a wife
who is from Southern America this means ( lucky me i have to celebrate
3 christmas evenings and dishes ( 24 christmas SA , 25 NL and 26 NL ) ,
so i have now to get ready before the diners guests arrive

regards
michel

 

[RobinS]

Michel,

Not that my opinion matters, but I was really impressed by your
response. It must have taken you a while to create, and I thought
it really explained things well, along with explaining *why* you
were explaining them because of the history of the posting.

Have a nice 3 Christmases.

Robin S.

 

[Guest]

I have an opposite opinion as Stephany, I surely would not go the road
from DAO. Even moreover, I would not go any direction using the Jet
Engine for new applications. We don't know how long it will be on
newer OS. Therefore just go to SQLExpress as is often told in these
newsgroups is in my idea a better approach.

I agree - why continue to use Access unless absolutely necessary? MSDE or
SQL Server Express are probably better solutions.

Or even Firebird Embedded.

 

[Michel Posseth [MCP]]

Hello Robin S.

it is now 3:40 AM the people have just left

To me your opinion sure does mather , cause as i am not a native English
speaker and live in a country where something that is spoken may mean
something totally different when translated direcly to other languages, i
am sometimes confused if it is my style of writing or just the other side
that is missing my point .

So thank you verry much


regards

Michel

 

[RobinS]

Well, if they stayed that late, it sounds like you had a
good time. Or at least, *they* did! That's a good thing.
I hope there was pie involved. :-D

I understand about the English thing; I can generally understand
what you say, if that makes you feel any better.

Have a merry Christmas!

Robin S.

 

[Cor Ligthert [MVP]]

Michel,

Don't listen to those who say something about your English. Especially Scott
had sent many complaints about my use of English (which can be very bad but
the same is with Dutch when I do it quick). What he does not know that the
status of English in our country is the same as in the USA. It has no
official status. The only difference is probably that percentage wise in
Holland more people can and do speak English than in the USA. And like those
Americans do we have our own dialect if we are speaking or writing quick.

Cor

 

[Scott M.]

Cor, the only thing I've ever said about your English is that, at times, I
don't know what you are trying to say. I've never insulted you about it or
tried to put you down about it, I just can't reply to a post that I can't
make out is all.

Happy holidays!

-Scott

 

[Cor Ligthert [MVP]]

Scott,

No problems at all, we both are already so long at these boards, it is
Christmas evening here and have just fun. You should see the smile on my
face when I write things like this.



Happy Christmas an fine holidays for you too.

Cor