Best performance for split db

[Simon]

Hi

A few days ago I got a request to programm a database application based on
Access. The main requirements for the architecture are:
(1) The frontend must be easily updated (new version), without affecting the
backend-data
(2) Multi-user (about 2 or 3 concurrent users)

I'm thinking about splitting the app in a data-mdb and a front-end mdb where
the tables are linked. However, I've found that queries will then retrieve
the full table into the front-end and run the query there. To improve
performance, I'd like to run queries directly on the data-mdb and read just
the result rows into the front-end.

Does someone have suggestions how to access "linked queries/views"??

thanks
Simon

Posted to:
microsoft.public.access.multiuser; microsoft.public.access.setupconfig;
microsoft.public.access.queries;

 

[Rick Brandt]

Hi

A few days ago I got a request to programm a database application based on
Access. The main requirements for the architecture are:
(1) The frontend must be easily updated (new version), without affecting the
backend-data
(2) Multi-user (about 2 or 3 concurrent users)

I'm thinking about splitting the app in a data-mdb and a front-end mdb where
the tables are linked. However, I've found that queries will then retrieve
the full table into the front-end and run the query there.

The query is *always* executed on the front end unless you are using a server-based
back end (SQL Server, Oracle, etc.). However; the entire table is only brought
across the wire with a Jet back end if you have not created any indexes that the
query can utilize. With proper indexing only the index is brought across and then
the data pages with the required records. At a minimum you should have indexes for
all fields used in joins and all fields used in GroupBy, OrderBy, or WHERE clauses.



--
*******************************
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
*******************************

 

[Lance]

For a 2-3 person database use SQL Server? Overkill.

 

[GreySky]

1) It's never overkill to use SQL Server.

2) How do you propose solving his problem, aside from not
solving it at all.

Grey ~~

 

[Tony Toews]

A few days ago I got a request to programm a database application based on
Access. The main requirements for the architecture are:
(1) The frontend must be easily updated (new version), without affecting the
backend-data
(2) Multi-user (about 2 or 3 concurrent users)

I'm thinking about splitting the app in a data-mdb and a front-end mdb where
the tables are linked.

Excellent idea to reduce corruption and to easily update new versions
of the FE.

However, I've found that queries will then retrieve
the full table into the front-end and run the query there.

Why do you think this? As Rick states this isn't usually what
happens.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Lance]

Well, It really doesn't sound like he has a problem, does
it?. He was under the assumption the entire table would
be sent across the network. Now he know with proper
indexing and design this isn't so as many people here have
confirmed.

If he still may think there would be a problem *first* I
would get more information about the size of the database,
how much it is expected to grow, how it is to be used,
ect.

Generally, for a 2-3 person database SQL server is
overkill in terms of development and what added performace
it would provide. For a smaller database performance can
be *slower* using SQL server. How about licensing costs
and the cost of an additional server to run your
database. Or do you propose he use MSDE on a server
without the correct hardware spec to properly perform as a
true db server and without the proper administration
tools?

Why in fact do you think your solution is so superior?

--Lance

 

[John Vinson]

1) It's never overkill to use SQL Server.

Compare the retail cost of Access with the retail cost of (full)
SQL/Server 2000. Prepare a justification that it's never overkill to
spend the difference, one that will convince any purchasing manager.
I'm sure lots of folks will find this document useful!

 

[GreySky]

I suppose we may be arguing semantics, or just trying to
piss further... who knows.

I agree SQL Server may not be the most appropriate
solution in every circumstance. I assume he has SQL
Server, the hardware to use it, and support staff handling
various other databases housed in SQL Server. I also
assume his data is highly valuable and important to his
business.

To call using SQL Server "overkill" simply does not apply
in any circumstance (most likely due to differences in how
we interpret this word). Further, you should take the
enterprise or business unit into consideration. If all of
your databases were in SQL Server, I'd recommend not
having separate MS Access back-end databases if only to
keep support resource requirements consistent.

I looked past the obvious issue regarding dragging the
entire table over the network so it could do a table scan,
and focused more on his architectural design question
regarding how to create a client-server database out of MS
Access. That is why I listed his question above my answer
(essentially saying you need SQL Server if you want true
client/server capabilities).

And Larry, you'll never convince all purchasing managers
that taking a forward-looking, growth-capable approach to
IT architecture is the most approriate. I doubt you could
convince some purchasing managers that MS Access is even
required when you have MS Excel! I've been an MS Access
contractor for over 8 years (typical contract lasting six
months, then moving to the next one while still supporting
years-old customers). You can argue theory with me and
show me you're an "MVP," but I will beat you with real-
life experience and encountered pitfalls.

If your database stores real-time business-critical OLTP
data, SQL Server is the only truly viable long-term option
you have in the "real world." My experience indicates a
very strong correlation between organizations that create
backups PERIOD (whether automated or via administration
personnel) and organizations that own SQL Server.
Therefore, tell me how you can justify using a MS Access
back-end without ever making a backup of the data or
performing routine maintenance. Tell that to the CEO, and
see how fast SQL Server is approved by your reluctant
purchasing manager!

That, and have you ever heard of Small Business Server?
Jeepers, this isn't the early 1990's anymore.

I could keep going on about this with different points,
whether they be scalability, recovery to point of failure,
etc., but ...

David Atkins, MCP

 

[GreySky]

"Well, It really doesn't sound like he has a problem, does
it."

So he took the time to post his question for no reason?
And you can read his mind to know for certain that he has
no other need for client/server capabilities in a larger
context...

Man you're a ****ing genius.

David

 

[Lance]

David,

No one is arguing these points with you. This is not even
what you posted preiously. Your "It's never overkill to
use SQL Server" statment is what was the contention.

You make some valid uses for SQL server below but your
tongue in cheek insults are really not necessary. Your
endless babbling about this to your imaginary friend Larry
is getting stale.

--Lance

 

[GreySky]

1) It's never overkill to use SQL Server.

2) How do you propose solving his problem, aside from not
solving it at all.

3) I meant "John" not Larry, mea culpa.

David

 

[Rick Brandt]

1) It's never overkill to use SQL Server.

If you have it, I agree. There are certainly times when it would be overkill to
*acquire* SQL Server.

2) How do you propose solving his problem, aside from not solving it at all.

I believe that he was assuming he would have a problem based on a misunderstanding of
how Access works. Once he was relieved of this misunderstanding it is no longer
certain that he actually has a problem. If he later reports "I added indexes and
did a load of optimizing, but performance still sucks.", then a move to a
Server-based engine would again be a valid suggestion.

 

[GreySky]

I agree with the main theme of your points.

However, as we are making assumptions regarding his
situation, let's air assumptions that haven't been stated.

1) We assume we read his problem statement correctly.
Specifically, his phrase, "However, I've found that
queries will then retrieve the full table into the front-
end and run the query there" actually can be interpreted
two ways:

a) His entire table is being pulled over because MS
Access must perform a table scan. The solution to this
*typically* is to add indexes, I agree. But can you
really envision Simon viewing his query execution plan (?
in MS Access ?) saying, "This query is pulling the entire
table over the network."

b) He just realized the implications of a file-server
architecture; specifically, that data are retrieved over
the network and processed locally. In this case his use
of "full table," while meaningful to us as professionals,
would have taken on a different context to him.


2) We assume Simon made the wrong choice regarding indexes.

While I agree that he probably does need an index, I don't
know that definitively. For example, let's assume this is
an high-volume OLTP database that receives thousands of
inserts, and must be optimized for those inserts. He has
his primary key indexed and maybe a foreign key, but to
index a text field, which let's assume he's using later in
a join, simply does not make sense in this scenario. The
result is dragging the results over in order to perform a
table scan, but it isn't wise to add that index.

---

Now re-read his problem statement, and tell me if you
believe it fits better with 1a or 1b?

Assuming 1b, did I answer inappropriately?

David Atkins, MCP