ADO twice as fast as DAO against native jet?
Manuel Lopez
I've noticed that now ADO is twice (!) as fast as DAO in executing parameter queries
(unless the code below is unfairly penalizing DAO somehow), but DAO is 30-40% faster
than ADO in seeks--now if only they could tighten the gap in seek.
Public gdbs as DAO.Database
Public gcnn as ADO.Connection
Public Function RsToQry(rs As DAO.Recordset, sQueryName As String, _
ParamArray av() As Variant) As Boolean
'Sets rs to the editable result set of query named sQueryName.
'av: If query takes parameters, they must be passed in ORDER!
Dim qry As DAO.QueryDef, i As Long, nMin As Long, nMax As Long, prm As
DAO.Parameter
If gdbs Is Nothing Then set gdbs = CurrentDB
If IsMissing(av) Then
Set rs = gdbs.OpenRecordset(sQueryName, dbOpenDynaset)
Else
Set qry = gdbs.QueryDefs(sQueryName)
nMin = LBound(av)
i = nMin
For Each prm In qry.Parameters
prm.Value = av(i)
i = i + 1
Next prm
Set rs = qry.OpenRecordset(dbOpenDynaset)
qry.Close
Set qry = Nothing
End If
RsToQry = True
End Function
Public Function SetRstToQuery(rs As ADODB.Recordset, sQueryName As String, _
ParamArray av() As Variant) As Boolean
'Sets rs to the editable result set of query named sQueryName.
'av: If query takes parameters, they must be passed in ORDER!
Dim cmd As ADODB.Command, i As Long, iType As Long
Set rs = New ADODB.Recordset
If gcnn Is Nothing Then Set gcnn = CurrentProject.Connection
If IsMissing(av) Then
rs.Open sQueryName, gcnn, adOpenKeyset, adLockOptimistic, adCmdStoredProc
Else
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = gcnn
.CommandType = adCmdStoredProc
.CommandText = sQueryName
For i = LBound(av) To UBound(av)
Select Case VarType(av(i))
Case vbLong
iType = adInteger
Case vbBoolean
iType = adBoolean
Case vbString
If Len(av(i)) Then iType = adVarWChar Else iType = adVariant
Case vbDate
iType = adDate
Case vbInteger
iType = adSmallInt
Case vbByte
iType = adUnsignedTinyInt
Case vbSingle
iType = adSingle
Case vbDouble
iType = adDouble
Case vbCurrency
iType = adCurrency
Case vbDecimal
iType = adNumeric
Case Else
iType = adVariant
End Select
.Parameters.Append .CreateParameter(Type:=iType, Size:=Len(av(i)),
Value:=av(i))
Next i
End With
rs.Open cmd, , adOpenKeyset, adLockOptimistic
Set cmd = Nothing
End If
SetRstToQuery = True
End Function
Manuel Lopez
that my procedure avoids calling ADOX, whereas DAO requires creating a querydef for
running a parameter query (as far as I know), and so ADO wins.
"Larry Linson" <larry....@ntpcug.org> wrote in message
news:86dvu9$tp3$1...@nnrp1.deja.com...
> Your findings contradict what I have seen reported about ADO, DAO, and
> native Jet.
>
> AAMOF, I have been told that ADO uses the ADODB Provider for Jet, which
> itself sits on top of ODBC because Microsoft decided not to take the
> time or expend the effort to build a "native ADODB" -- they appear to
> be less interested in Jet performance than in access to server
> databases with ADO. It is difficult for me to understand, if that is
> true, how it could possibly overcome the additional overhead to be
> faster.
>
> But, if they are able to overcome that additional overhead, perhaps
> there's yet some hope for our favorite database, after all.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Larry Linson
Manuel Lopez
ODBC (but in the early days, ado did sit on top of odbc). It's just "ADO to ole db to
jet" and ole db is faster than odbc. Still I assume there is overhead when compared
to "DAO to jet." (I agree that they don't seem to be as interested in jet performance
as they used to be, which is unfortunate because sql server is not anywhere as good
for us.)
"Larry Linson" <larry....@ntpcug.org> wrote in message
news:86dvu9$tp3$1...@nnrp1.deja.com...
Egbert Nierop
for performance and reliability.
So the discussion should only be about <<< possibilities and better
reliability >>> instead of performance.
Good performance is gotton from using a real dbms. but I don't want to spoil
your fun.
Regards
"Manuel Lopez" <mlo...@NoJunkMail.post.harvard.edu> wrote in message
news:86deu4$9i6$1...@bob.news.rcn.net...
Manuel Lopez
(and there are a few non-internet uses of databases, or so I've heard).
As far as reliability, there's absolutely no question that sql server is better than
jet. As far as speed and performance, sometimes jet is faster than sql server. I
know it would be nice to have one solution for everything, but it hasn't happened yet.
These are all factors:
1. small number of users
2. need for speedy non-set oriented access to data (Seeking is not available on Sql
server)
3. local or near-local (fast ethernet) database
The more all of these things are true, the greater the likelihood that jet will
outperform, sometimes greatly outperform, sql server. I have no special loyalty to
jet, but it's faster for us. You certainly shouldn't take my word for it, you can
test it out for yourself. But hey, I don't want to spoil *your* fun.
"Egbert Nierop" <e.ni...@nospam.net> wrote in message
news:#JLcGidZ$GA.321@cppssbbsa03...
Arvin Meyer
yet.
It's not even true for small internet databases. I sure don't know where he
got his ideas from, but it sure isn't from experience. More like he'd just
aping what somebody once said. Our company is in the process of developing 2
small databases which will be used on the internet by a few dozen users
spread over half a dozen states. So far they are blazingly fast. Reliable?
What can be more reliable than unbound forms and data which is updated by
insert statements? One of them should be finished by March, and I'll give
the URL and temp password to a trusted person here to check that my claims
are true.
I developed one several years ago which was also very fast. The company
later moved it to SQL-Server when their database requirements increased due
to their initial success. This database grew over the period of a year to 50
or so concurrent users before it's speed began to suffer significantly. Even
then, I am not so sure that the hardware or bandwidth wasn't to blame,
because they upgraded both at the same time, and the ISP added another T1,
just after the upgrade.
---
Arvin Meyer
Manuel Lopez wrote in message <86fpsf$ssh$1...@bob.news.rcn.net>...
Manuel Lopez
Directory are JET databases (not sql server). Active Directory, the centerpiece of
windows 2000, is perhaps the most important single software item for MS now (and
Exchange is not exactly a minor product either). Clearly if sql server had really
been faster than JET, they would have used that instead.
On the other hand, it must be a modified version of Jet, since it can go up to 15
terabytes (TERA) or so (presumably not by linked tables). I only have sketchy info
about this--do you know anything? If true, it's a good selling point for access
developers and consultants.
(I don't think he posted to cdma, I was replying to his post on the other newsgroups.)
"Arvin Meyer" <a...@m.com> wrote in message news:86gdie$605$1...@nntp2.atl.mindspring.net...
Michael (michka) Kaplan
different from the original Jet engine that they may as well be two
different products. It reminds me of a phrase by the comedian Emo Phillips:
"I am not as good of a swimmer as I used to be.... thanks to evolution!"
Something are just too far separate to consider them to really be connected
anymore.
In the case of Exchange, partially out of recognition of the difficulties
and perf. issues in Exchange stores, an entirely different storage is being
used for the new "Exchange 2000" server.
--
?MichKa
(insensitive fruitarian)
random junk of dubious value, a multilingual website, the
54-language TSI Form/Report to Data Access Page Wizard,
and lots of replication "stuff" at http://www.trigeminal.com/
?
"Manuel Lopez" <mlo...@NoJunkMail.post.harvard.edu> wrote in message
news:86fpsf$ssh$1...@bob.news.rcn.net...
Manuel Lopez
start off with that). (--Any other colors we should know about?)
"Michael (michka) Kaplan" <forme...@spamless.trigeminal.spamless.com> wrote in
message news:O#CjsxhZ$GA.292@cpmsnbbsa03...
Michael (michka) Kaplan
Jet engine which became so different from the "RED" Jet engine that they may
as well be considered different products.
And Exchange 2000 uses an entirely different data store, anyway.
--
?MichKa
(insensitive fruitarian)
random junk of dubious value, a multilingual website, the
54-language TSI Form/Report to Data Access Page Wizard,
and lots of replication "stuff" at http://www.trigeminal.com/
?
"Manuel Lopez" <mlo...@NoJunkMail.post.harvard.edu> wrote in message
news:86ggn5$96r$1...@bob.news.rcn.net...
Mario Osorio
--
HTH
'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'
Mario Osorio (Hot Budare)
'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'_'-'
===============================
PLEASE HELP MY VENEZUELAN BROTHERS by making a donation to the Red Cross
International.
Thousands of venezuelans are homeless due to an extremely rainy season
(some 250000 according to initial calculations).
Thousands have also died (some 50000 according to the Red Cross).
Dios te ayude
May God help you :-[
for more info goto:
http://fullcoverage.yahoo.com/fc/World/Venezuela/
www.help.org.ve/html/english.html
www.help.org.ve/html/deutsch.html
www.help.org.ve/html/francais.html
www.help.org.ve
www.ayuda.cantv.net
www.cavecom-e.org.ve
www.globovision.com
www.eud.com
www.chevere.com
www.analitica.com
www.rctv.net
www.venevision.com
www.televen.com
www.internautas-ve.org
===============================
Michael (michka) Kaplan <forme...@spamless.trigeminal.spamless.com>
escribió en el mensaje de noticias uIDf6mlZ$GA.117@cpmsnbbsa03...
Michel Walsh
How do you make the timing? If you repeat the code inside a loop, some
buffering implementations may kick-in and un-validate the result (in favour
of one side or another). Generally, you should run the test once with
appropriate performance counters, wait few seconds, repeat the test a second
time.
Furthermore, the DAO query, the code itself, is local to the application,
while the stored procedure referred in ADO is local to the server, that is
not strictly the same stuff (assuming the else portion is executed).
Your ADO coding seems to relay on the programmer to fill in, without error
and without automatic data conversion allowed, a specific list of
parameters... prone to error... like old C code... You write it probably
just for benchmarks, but in real life, consider bringing the set of
parameters required by the query/store procedure, rather than creating them
and "hoping" the programmer is right and had remembered to not use automatic
data conversion. Slower, but safer... (or just avoid that kind of generic
thin wrapper that does almost nothing and, in my humble opinion, terribly
error prone).
Vanderghast, Access MVP.
Manuel Lopez
My comments follow.
> Generally, you should run the test once with appropriate performance counters,
> wait few seconds, repeat the test a second time.
Same results. ADO twice as fast. (Is my "querydef required vs. adox not required"
explanation wrong?)
> Furthermore, the DAO query, the code itself, is local to the application,
> while the stored procedure referred in ADO is local to the server, that is
> not strictly the same stuff (assuming the else portion is executed).
No (unless I've misunderstood you): both the DAO and ADO code are running the same
query in the same location on the same local database (the current mdb). It's the
same stuff both times. It's not a Sql Server stored procedure, but a jet stored query
in both cases.
> Your ADO coding seems to relay on the programmer to fill in, without error
> and without automatic data conversion allowed, a specific list of
> parameters... prone to error... like old C code..
One knows the parameters ahead of time. After all, in the DAO code, I still need to
know what the parameters are and pass them in order (or, if not in order, it gets
worse, I have to pass in their names)! If I know the parameters ahead of time, then
all that means is I have to pass in the right values--and it's hard to see how any
query will work if you pass in the wrong values.
It's not prone to error. The query won't choke if I pass a short and the parameter is
defined as long. Strangely enough, it doesn't choke even if one passes a string, an
adVarWChar ("56"), for a parameter defined as long (56); the query runs correctly. As
far as I can tell the typing is there mainly to speed things up (I think), since
otherwise jet seems to know to convert what is passed in by ole db. The only
exception is empty strings--cannnot be passed as adVarWChar, but I check for that. In
fact, I once had this pass everything as adVariant but ran into a snag with booleans
(if the code passes True instead of -1, ado won't handle adVariant right for jet).
I'm not very familiar with this ADO stuff--so let me know if something seems suspect
in what I've said. However, I can tell you that I've been using this code on
thousands of queries over the last week, and without looking up the parameter
typing--just passing in the values to my procedure, and have had zero problems. This
is real life.
"Michel Walsh" <Vande...@email.msn.com> wrote in message
news:#$ac38oZ$GA.265@cppssbbsa04...
Trevor Best
news:e7iKfDnZ$GA.262@cppssbbsa04...
> now ... what's a "RED" or "Blue" Jet engine ?????
and now we hand over to Frank Whittle...
Trevor Best
news:86gdie$605$1...@nntp2.atl.mindspring.net...
> What can be more reliable than unbound forms and data which is updated by
> insert statements? One of them should be finished by March,
Wow, what you inserting? the Gettysburg address? I though you said it was
fast?
;-)
Manuel Lopez
went on for over an hour. Of course, it's tough to be paired up against Lincoln.
"Trevor Best" <tre...@besty.org.uk> wrote in message
news:86iki8$3mb$1...@quince.news.easynet.net...
> Arvin Meyer <a...@m.com> wrote in message
> news:86gdie$605$1...@nntp2.atl.mindspring.net...
>
> > What can be more reliable than unbound forms and data which is updated by
> > insert statements? One of them should be finished by March,
>
Joe "Nuke Me Xemu" Foster
> Thanks for the input--but the results stand! ADO twice as fast as DAO!
And in the start of the thread he wrote:
> > > rs.Open cmd, , adOpenKeyset, adLockOptimistic
I wonder if the adOpenKeyset is the, erm, key to the problem. Just as
opening DAO dynasets involve less work up-front than snapshots, perhaps
opening ADO keysets are less work than DAO dynasets, at least until you
actually start visiting the recordsets' rows. What happens if you put
in calls to MoveLast?
--
Joe Foster <mailto:jfo...@ricochet.net> Space Cooties! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
Manuel Lopez
type of query (but results don't vary for a given query). For some reason, ADO seems
to be doing twice as fast on certain joined queries, especially of queries with
queries, but I've discovered that DAO is about 60% faster than ADO on simpler
queries--I don't know how this happens, but it definitely happens. For still other
types of queries, ADO is only slightly faster than DAO. I'll have to see if there's
really a pattern here, but for now I think DAO may retain its "mostly much faster than
ADO against JET" crown.
Still, I had never heard of ADO being faster than DAO in anything before, so I think
it was worth posting, although I obviously don't have the pattern or the explanation
yet.
As far as keyset, snapshot, dynaset, that didn't make a difference. adOpenDynamic
doesn't return a dynamic recordset in Jolt because it's not supported, it sends back a
keyset here (which has quasi-dynaset behavior, such as allowing editable query result
sets, but it doesn't show additions and deletions). adOpenDynamic does, I hear, do
some optimizing with direct sql statements. At any rate, I tried adOpenDynamic and it
made no difference here. MoveLast made no difference.
"Joe "Nuke Me Xemu" Foster" <j...@bftsi0.UUCP> wrote in message
news:s8pqi9...@corp.supernews.com...
> "Manuel Lopez" <mlo...@NoJunkMail.post.harvard.edu> wrote in message
news:86i5n4$cq0$1...@bob.news.rcn.net...
>
> > Thanks for the input--but the results stand! ADO twice as fast as DAO!
>
> And in the start of the thread he wrote:
>
> > > > rs.Open cmd, , adOpenKeyset, adLockOptimistic
>
Michael (michka) Kaplan
conferences. No biggee.
--
?MichKa
(insensitive fruitarian)
random junk of dubious value, a multilingual website, the
54-language TSI Form/Report to Data Access Page Wizard,
and lots of replication "stuff" at http://www.trigeminal.com/
?
"Trevor Best" <tre...@besty.org.uk> wrote in message
news:86iki7$3mb$1...@quince.news.easynet.net...
> Mario Osorio <pro...@cantv.net> wrote in message
> news:e7iKfDnZ$GA.262@cppssbbsa04...
> > now ... what's a "RED" or "Blue" Jet engine ?????
>
Kunle Odutola
the SQL Server 7 runtime engine. Works a treat.
"Manuel Lopez" <mlo...@NoJunkMail.post.harvard.edu> wrote in message
news:86e279$3qi$1...@bob.news.rcn.net...
Michel Walsh
About automatic data conversion, with ADO, when using the Execute method (of
the connection), I found on many occasions that declaring the exact data
type solve a lot of problems:
Set adorst = cnn.Execute(..., Array(CDate(parm1), CStr(22), ... ))
is far less error prone than
Set adorst = cnn.Execute( ..., Array(parm1, parm2, ...))
About the parameters, with ADO and JET_OLEDB, the name of the parameter is
irrelevant, at least if you don't query (refresh) the parameters collection
from the connection. A typical example is to have a query with parameter
named TOTO, but you can create one named TATA and use it as TATA, named like
TATA, without any problem: the order of the created parameters will be
important, their name will be irrelevant (as can be irrelevant the name you
give to the argument of some subroutine versus the name you give to the
variable or expression you pass to that subroutine in the program calling
the subroutine). That statement holds only in the case you are using ADO
directly. See ADO 2.1 Programmer's Reference, at WROX, page 91. Note that
there are cases where the name of the parameter STAY relevant... to avoid
problem, if you don't get the parameters collection form the connection, be
sure to create it in the right order, and don't relay on the name (unless
you tested it intensively for the Provider you use).
Vanderghast, Access MVP
Manuel Lopez <mlo...@NoJunkMail.post.harvard.edu> wrote in message
news:86isjk$rn1$1...@bob.news.rcn.net...
> > "Manuel Lopez" <mlo...@NoJunkMail.post.harvard.edu> wrote in message
> news:86i5n4$cq0$1...@bob.news.rcn.net...
> >
> > > Thanks for the input--but the results stand! ADO twice as fast as DAO!
> >
> > And in the start of the thread he wrote:
> >
> > > > > rs.Open cmd, , adOpenKeyset, adLockOptimistic
> >
Mario Osorio
Manuel Lopez
"Michael (michka) Kaplan" <forme...@spamless.trigeminal.spamless.com> wrote in
message news:#C7DqrvZ$GA.272@cpmsnbbsa04...
> Just code names that Jet folks used to bandy about to impress people at
> conferences. No biggee.
>
> --
> ?MichKa
> (insensitive fruitarian)
>
> random junk of dubious value, a multilingual website, the
> 54-language TSI Form/Report to Data Access Page Wizard,
> and lots of replication "stuff" at http://www.trigeminal.com/
>
>
> ?
> "Trevor Best" <tre...@besty.org.uk> wrote in message
> news:86iki7$3mb$1...@quince.news.easynet.net...
> > Mario Osorio <pro...@cantv.net> wrote in message
> > news:e7iKfDnZ$GA.262@cppssbbsa04...
> > > now ... what's a "RED" or "Blue" Jet engine ?????
> >
Michael (michka) Kaplan
--
?MichKa
(insensitive fruitarian)
random junk of dubious value, a multilingual website, the
54-language TSI Form/Report to Data Access Page Wizard,
and lots of replication "stuff" at http://www.trigeminal.com/
?
"Mario Osorio" <pro...@cantv.net> wrote in message
news:uUfcjXzZ$GA.387@cppssbbsa05...
> ?c|
>
>
Trevor Best
people refer to War and Peace as a big book, looks like a copy of the Beano
next to the A2K Developers Hanfbook :-)
Manuel Lopez <mlo...@NoJunkMail.post.harvard.edu> wrote in message
news:86imoj$gtm$1...@bob.news.rcn.net...
> Actually the Gettysburg is quite short. I believe Edward Everett's speech
that day
> went on for over an hour. Of course, it's tough to be paired up against
Lincoln.
>
> "Trevor Best" <tre...@besty.org.uk> wrote in message
> news:86iki8$3mb$1...@quince.news.easynet.net...
> > Arvin Meyer <a...@m.com> wrote in message
> > news:86gdie$605$1...@nntp2.atl.mindspring.net...
> >
> > > What can be more reliable than unbound forms and data which is updated
by
> > > insert statements? One of them should be finished by March,
> >
Manuel Lopez
trounces getz, litwin, & gilbert.
"Trevor Best" <tre...@besty.org.uk> wrote in message
news:86madi$1hoj$3...@quince.news.easynet.net...
> OK we need to update all those sayings about what's long or big. For years
> people refer to War and Peace as a big book, looks like a copy of the Beano
> next to the A2K Developers Hanfbook :-)
>
> Manuel Lopez <mlo...@NoJunkMail.post.harvard.edu> wrote in message
> news:86imoj$gtm$1...@bob.news.rcn.net...
> > Actually the Gettysburg is quite short. I believe Edward Everett's speech
> that day
> > went on for over an hour. Of course, it's tough to be paired up against
> Lincoln.
> >
> > "Trevor Best" <tre...@besty.org.uk> wrote in message
> > news:86iki8$3mb$1...@quince.news.easynet.net...
> > > Arvin Meyer <a...@m.com> wrote in message
> > > news:86gdie$605$1...@nntp2.atl.mindspring.net...
> > >
> > > > What can be more reliable than unbound forms and data which is updated
> by
> > > > insert statements? One of them should be finished by March,
> > >
Trevor Best
(Frank Whittle)
The one he invented was probably grey or silver.
Trevor Best
Encyclopaedia Britannica.
Manuel Lopez <mlo...@NoJunkMail.post.harvard.edu> wrote in message
news:86n2uq$m3m$1...@bob.news.rcn.net...
> But the a2k book has very big type and lots of pictures. Tolstoy I'm
afraid easily
> trounces getz, litwin, & gilbert.
>
> "Trevor Best" <tre...@besty.org.uk> wrote in message
> news:86madi$1hoj$3...@quince.news.easynet.net...
> > OK we need to update all those sayings about what's long or big. For
years
> > people refer to War and Peace as a big book, looks like a copy of the
Beano
> > next to the A2K Developers Hanfbook :-)
> >
> > Manuel Lopez <mlo...@NoJunkMail.post.harvard.edu> wrote in message
> > news:86imoj$gtm$1...@bob.news.rcn.net...
> > > Actually the Gettysburg is quite short. I believe Edward Everett's
speech
> > that day
> > > went on for over an hour. Of course, it's tough to be paired up
against
> > Lincoln.
> > >
> > > "Trevor Best" <tre...@besty.org.uk> wrote in message
> > > news:86iki8$3mb$1...@quince.news.easynet.net...
> > > > Arvin Meyer <a...@m.com> wrote in message
> > > > news:86gdie$605$1...@nntp2.atl.mindspring.net...
> > > >
> > > > > What can be more reliable than unbound forms and data which is
updated
> > by
> > > > > insert statements? One of them should be finished by March,
> > > >