Performance

E

Eric @ EVV

It has always been my impression that reports run quicker when they are based
on queries rather than tables.....it that a true statement ?

Thanks !
 
J

Jose Aleman

It's probably about the same. A query ask the table a question but you can
exclude certain data or ask for specific information. A query could be a
little slower since it's looking the specific data that your looking for.

Access in general is slow. I noticed that after the database reachest a
certain amount of records it slows down. Specially if you have over 200K
records.
 
A

Albert D.Kallal

Access in general is slow.
Click to expand...

I am sorry, I have to disagree with the above. When you compare it to
sql-server, or something like MySql, the JET engine runs much faster, as
much as double the speed in some cases. (we are talking about when you run
the exact same hardware here).

Remember the JET engine has little, or no overhead as compared to a "server"
based system. The JET engine is file based, and can *scrape* data direct off
the disk without having to check timestamps, or wrap things in a
transaction. As a general rule, the speed at which it grabs records is
faster then a server based product because it does so many things less.

Further, virtually any server based product you use will requite you to grab
data through a socket connection. Now, of course this is advantage if you
are using a network, but a very large portion of ms-access applications use
the JET data engine, and do NOT involve a network. So, for the majority of
ms-access applications with JET, you will get BETTER performance then sql
server on that pc, and you don't have to read data through a restrictive
socket.

At the end of the day, I am in no way suggesting, or hinting that "JET" is a
replacement for sql server. Nor does JET scale anywhere close to sql server.

By the way, for the last 3 versions of ms-access, there has been a free
edition of sql server included on the disk for use with ms-access anyway.
So, we should clarify that we are talking about JET, and not ms-access. We
don't call VB a database, nor do we call ms-access a database. These are
simply tools that let you develop applications, and you "choose" what data
engine you want.

At any rate, when talking about raw performance, then I have found jet OFTEN
outruns a server based system by a good margin (again, we are talking about
a system with no network).
 
A

Albert D.Kallal

It has always been my impression that reports run quicker when they are
based
on queries rather than tables.....it that a true statement ?
Click to expand...

As a general rule, you should not notice any difference. The time to setup
the query, or setup the table is really insignificant to the time to "load"
the data.
 
D

dbahooker

im not sure i agree

the thing about using SQL Server.. is that you can hook it up to
analysis services which is a friggin billion times faster than any mdb
app anywhere

i just am not sure im madly in love with jet.

i think that it would get better if more companies moved to gigabit
ethernet.. but it breaks my heart that i dont think that is going to
happen anytime soon
 
D

dbahooker

and i really think that we should talk about size

access works fine for a thousand records

sql works fine for a million

that has to be a piece of the argument.. you size your database based
on how many records you expect to have

but in all practicality; i would reccomend never ever ever ever ever
use any MDB in any way, shape or form
 
A

Albert D.Kallal

im not sure i agree

the thing about using SQL Server.. is that you can hook it up to
analysis services which is a friggin billion times faster than any mdb
app anywhere
Click to expand...

Without question, sql server has far better tools to deal with data.

however, JET does tend to be faster on the same hardware for most operations
on the data...
 
A

Albert D.Kallal

but in all practicality; i would reccomend never ever ever ever ever
use any MDB in any way, shape or form
Click to expand...

Well, then you would have to throw out a lot of software.

As for using JET in a commercial product, again I see no problems. The
following products for example have been based on JET:

Ms-access
Windows NT (directory services)
Microsoft Money
Internet Information Server
Index Server
Microsoft Project.
A Jet variation even serves as the message store for
Exchange Server.

Simply Accounting
CityDesk (web content manager).


Hence, the very successful (and popular) Simply Accounting package is
based on JET. I don't hear in the industry that Simply Accounting should not
be used. It is a great product. So is CityDesk..a web content management
system. My whole point here is that a lot of commercial products out
there have been based on JET...including directory services.

You are entitled to your opinion that you would no use a JET database, but
it is likely BY FAR AND AWAYS the MOST popular data engine in the
marketplace.

The installed base of the JET data engine is in the 100's of millions, and a
copy is included with windows xp.

So, while you may not like the engine....it is out there being used on
100's of millions of computers.

Many many years ago (before JET was included with windows xp), the installed
base was around 250 million....

Today, I would think that it is not far fetched to think that in excess of a
BILLION copies of jet are out there.

There is simply no data engine that comes even remotly close to the
widespread use of JET.

Based on users of this product, you stand alone, VERY alone in your opinion
to not use JET.
 
D

dbahooker

albert do you have anything other than the letters MVP to back that up?

I assume that you're talking about a single-proc server.. sql server
will use multiple procs to get queries done faster if they take a
certain amount of time; and i just dont see access ever out-performing
a 4-processor server for starters


im just saying that there is a better way-- it is called sql server

instead of having a zillion different stability problems; you can use
SQL Server-- and you'll have a lot better result.

the thing with SQL Server is that if you keep enough data in your
database; eventually you'll have a crapload of data

if you keep enough data in a mdb eventually you'll have a load of crap
 
Top