Re: Do I understand form optimization correctly?

To answer your question, no, it won't matter how you execute the
queries on an Access database. Access is file based, not server based.
If you run any query on the table, the entire table is brought down to
your machine, and the query is executed. With 30,000 records this
won't be a big deal, just wanted you to know for future projects and/or
scalability.


Chris Nebinger


John Welch (remove +'s) wrote:
> I'm developing my first multi-user application, and have been reading a lot
> about the basics of multi-user optimization (ADH vol 2, this newsgroup's
> archives, etc.). I've decided to go with a file server system (mdb backend
> on network), and I want to design it so that it runs efficiently. There will
> be 5-10 concurrent users and ~30,000 records. I'm trying to optimize it by
> addressing the most important issues first. I've been focusing on the idea
> of having forms 'serve up' only a few records at a time, rather than a whole
> table's worth, and I wonder if some of you pros can help me confirm that I'm
> understanding it correctly. So here goes:
>
> 1) If I bind a form to a query that filters the records with a 'WHERE'
> clause, and the field in the 'WHERE' is indexed, the backend will send the
> whole index across the wire, but not "the whole table" as some people seem
> to say, right? Then the front end will search the index and request the
> records that match the WHERE clause, and then only those records will be
> sent. Is that right?
>
> If this is correct, then a lot less network traffic will be created, but
> there will be a lot of searching of indexes: every time the user 'navigates'
> to a new record a new query is run. So does that mean that searching large
> indices is faster than sending lots of data? Or does it depend on the
> details of the network?
>
> 2) In the WHERE clause that limits the recordsource, does using "LIKE" slow
> things down a lot? For example, to get all customers whose last names start
> with "smi". How about if I do something like "SELECT top 10 ... order by
> myIndexedField" ? Will that be a lot slower than limiting records with
> 'WHERE'?
>
> 3) For subform recordsources, I usually use saved queries with "WHERE
> myfield = forms!parentform.control_bound_to_PK_field". Is this a good way to
> go, and is it a lot better than using the "link master/child" fields
> properties, for the same reason as in (1) above?
>
> Thanks very much in advance. This newsgroup is wonderful!
> -john

.

Relevant Pages

  • Re: Non-existent A record being returned...
    ... ; Query 1 ... These DNS servers are set up to forward queries to two other DNS ... Nslookup has it's own resolver service, and doesn't rely or use Windows resolver service, or the local cache, but rather directly queries DNS, where I'm assuming you're referring to clearing the DNS server cache? ...
    (microsoft.public.windows.server.dns)
  • Re: MailMerge hangs and crashes with Access on Server
    ... All I know is that the performance of queries depends to a large extent on whether, for example, a join is performed on the client side, or on the server side, incurring no network traffic and potentially benefitting from caching on the server. ... I have mapped out the path to the final query that is used to get to the letters - and have found that it is quite tortuous indeed - utilizes 8 queries and multiple tables to get there, does about 20 calculations along the way. ... There are no dialog boxes, and actually, I have now split the database, and have kept the Word templates and the Access frontend locally on my machine, and moved the backend to the server. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Passing values
    ... Use the comma as the separator list (excerpt if you have set the Windows' ... menu for the Queries Window. ... You can also use a view and set the Server Filter but that's another story. ... > figure out how to pass a value from a form to the query. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Basic design question for a distributed application - How to access applications data
    ... The main data the application manipulates is a list of Queries. ... query is a custom object containing an SQL statement and some ... My server is where my list of queries will reside. ... My client will display that list of queries and enable the user to ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Problems encountered with mail merge
    ... When I pick my query and carry on with the next step, ... There may be several reasons why an OLEDB connection could fail, ... OLEDB does not "see" some query types (such as parameter queries) ... I have an access database with tables, ...
    (microsoft.public.word.mailmerge.fields)