Get data in the fastest way possible?

[JString]

Greetings.

I'm trying to figure out how to develop the fastest standard access database
possible without using any server-side apps.

Does anybody know if domain aggregate functions like DCount or DLookup are
efficient? Do they utilize indexes where available?

If not would it be more efficient to code a series of SQL statements and use
those instead?

 

[David H]

Greetings.

I'm trying to figure out how to develop the fastest standard access database
possible without using any server-side apps.

What's the bigger picture? Yes, accessing a database over a network does
introduce the network speed & traffic variable, however there are advantages
to running the database over the network that outweigh an impact on
performance - which I might add can be negligible or of such a minimal impact
that the users don't notice it.

Does anybody know if domain aggregate functions like DCount or DLookup are
efficient? Do they utilize indexes where available?

http://www.mvps.org/access/modules/mdl0012.htm
If I understand them correctly the built in domain name functions only use
indexes if the indexes are created on the underlying tables.

If not would it be more efficient to code a series of SQL statements and use
those instead?

Coded SQL Statements should be avoided as the Jet Engine is required to
optimize the query each and every time the statement is executed. Rather,
save the statement as a Query. Once Jet executes the query it will optimize
it and then (for lack of a better term) save the optimization for later us.

 

[JString]

Coded SQL Statements should be avoided as the Jet Engine is required to

optimize the query each and every time the statement is executed. Rather,
save the statement as a Query. Once Jet executes the query it will optimize
it and then (for lack of a better term) save the optimization for later us.

That is interesting... I did not know that.

 

[Rick Brandt]

That is interesting... I did not know that.

On modern hardware the difference is very seldom noticeable. If you
executed the queries multiple times in a test loop you might see a
difference.

 

[Dale Fye]

As much as I love Access as both a front and back end, is there a particular
reason you don't want to use any server-side apps? You can get SQL Server
Express for free, and it will handle all but the really large databases (I
think there is a 4GB limit to each database), and you can see huge difference
in response times with a SQL Server backend.

Just my 2 cents.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[David H]

Not to mention that size difference in the max size as well as the fact you
can upsize from SQLExpress to the full version almost as easily as Access to
SQLBig Daddy (probably easier in fact)

 

[Dymondjack]

This is an interesting subject, I've often wondered about this. Here's a
thread I just found with some very relevant points and opinions on the the
usage of coded sql vs queries and summed up a lot of my curiousity.

http://www.eggheadcafe.com/software/aspnet/31915744/qry-vs-sql-code.aspx

 

[JString]

Yes. Our IT specialist swears that our server is not equipped to run
applications.

 

[David H]

There are some possibilities here...

1) Your IT specialist is not aware that you want to use Access.

2) Your IT specialist is aware that you want to use Access, but is an idiot.
(Access is a file-based database. As such, its the application on your
machine that's doing all of the work.)

3) If you're server just isn't up to snuff, there are server hosting
companies that are available. There is one particular company that I found
with a hosting plan for as low as $100 per month plus $2 per connection.

 

[David H]

Of course I failed to mention that since its file based, you can actually
build the backend and have it on the server and your IT guy would never know.
All you would have to do then is copy the front end from machine to machine
yourself.

4) Your IT specialist is just blowing the proverbial smoke up your outhouse
hole to get you to go away. "Well the flex capacitors in our servers aren't
modulated for that, blah, blah, blah."

There are some possibilities here...

1) Your IT specialist is not aware that you want to use Access.

2) Your IT specialist is aware that you want to use Access, but is an idiot.
(Access is a file-based database. As such, its the application on your
machine that's doing all of the work.)

3) If you're server just isn't up to snuff, there are server hosting
companies that are available. There is one particular company that I found
with a hosting plan for as low as $100 per month plus $2 per connection.

Yes. Our IT specialist swears that our server is not equipped to run
applications.

[quoted text clipped - 17 lines]

If not would it be more efficient to code a series of SQL statements and use
those instead?