tomaz said:
Hello!
I have a table with some records (1,8M).
If I open a query in MSAccess and execute certain select statement it
takes
less than a second to execute. If I copy that SQL statement and execute it
via ADO in a vb application I wait 16 seconds!!!!
IS THIS POSSIBLE?
Well, native ms-access uses DAO, and, it is often a good deal faster then
ADO.
When you use ADO, it actually has to "call" the DAO object model that JET
uses.
I discovered also that this happenes only after "Compact and repair" -
meaning if I have uncompacted database it works fine (in Access and my
application) but after "Compact and repair" it works quickly only in
Access
but not in my app!
IS THIS POSSIBLE?
Yes, the above makes a lot of sense. What happens when you do a compact and
repair is that ms-access "re-sets" all of the query stats that it keeps for
sql. In effect, all of the sql and quires in ms-access become un-compiled,
or re-set. This stats information is used by the "JET" (the databae engine)
to formalize a query plan (a query plan is the method of attack that JET
will use to execute the sql given). So, when you compact and repair, you
re-set all information that JET has about the databae for the query planer.
In many cases, this re-sets is desirable, since often the query planer
information gets out of date, and JET will make poor choices. Hence, a
compact and repair will FORCE JET to re-do this work (re-build the stats).
Starting fresh query plan often helps ms-access run a lot faster. It seems
like when you take a first crack from VB...that new stats information is not
available, so, JET making bad choices to get the data.
It is surprising however that using ADO seems to get messed up here.
(forcing JET to build a new query plan is usually GOOD thing).
I suspect that when you execute the sql from ADO, JET does not build, or
save the status information for the query planner, and thus does a worse job
then does ms-access.
Then there is more: Query is slow only if I include all table columns
(either by using select * ... or if I name all the columns) I tried to
combine columns but ther's no logic... sometimes it works fast sometimes
not....
IS THIS POSSIBLE?
yes, it is. Once again, it is possible for JET to "drop", or not have to
load parts of the record. In other words, if you tell jet to restive only a
few fields, and they are in the fist frame boundary of the record, the
*additional* frames for that record need not be loaded. Jet is quite smart
that way. On the other hand, if you pull one of the 1st fields, and the
"last" field form the table..then this advantage will NOT exist...and ALL of
the frames for the record will have to be loaded into memory, despite ONLY
needing two fields.
And, there is more:
In fact, if you only ask for a single field, and it is indexed, often the
speed can be SUPER fast, as JET will actually use the values from the
indexes for the field data, and not even read or touch the main table!!! An
index is VERY fast because because each disk frame your read can have a LOT
values for that field, and there is NO other data being loaded. So, if you
ask for only a few fields, and there are indexes, often you see amazing
performance. Further, with several conditions, there is the rush-more
technology. This is when JET builds arrays of "bits", and then does bit-wise
comparisons..and again it can be very fast...
It could be something simple like a index not being used form ADO, or
perhaps rush-more is not function from ADO without stats. It hard to know,
but there certainly is a number of reasons for the above behaviors, and
given a understanding of the JET engine, what your saying makes sense...
