Posted by Neil on 10/30/05 18:59

Both table have PKs; and the join is on the PKs (the main table has a
single-field PK; and the selections table has two field -- the corresponding
ID value from the main table combined with the machine name).

The 13 second form open time (with the 50k record selections table in the
front end joined heterogeneously with the 50k record main table in the back
end) is over a WAN. The LAN users get much better performance.

I tried a solution with *no* selection table (tracking selections in code,
since they don't need to remain past the current session), and it cut it
down to about 6 seconds. But, unfortunately, I ran into another issue with
that (currently discussing that in comp.databases.ms-access, "Trapping Click
With Calculated Check Box" thread). So if I can remove the selections table
altogether, that would be better. But, if I need to keep it, as noted
elsewhere in this thread, overall I get better performance with the huge
selections table (for all users) in the back end, rather than a small
selections table (for single user) in the front end. But the load time is
longer, and the load time is the thing that users have been complaining
about the most.

Thus, it would be great if I could bring down the load time while still
having the selections table in the back end. But I don't see what else I can
do to improve performance.

Thanks,

Neil



"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
news:scb9m1dva2tpb5j3r7sb3r0n51fv8f5eed@4ax.com...
> On Sun, 30 Oct 2005 01:03:04 GMT, "Neil" <nospam@nospam.net> wrote:
>
>>Yes, it can be done on the Access side, but, as noted, things are slowed
>>down. When I had a main SQL table (50,000 records) joined to a local
>>Access
>>table (also 50,000 records, with an index and a boolean field), the form
>>took about 13 seconds to open (with the join being, of course, in the
>>front
>>end).
>
> You really need to read up on the performance issues of using Access as a
> front-end to SQL Server. There are a few basic rules to know, (such as
> making
> sure join fields are indexed, and every table has a primary key) but if
> you
> follow them all, you should not have the performance issues you are
> describing.
>
> I routinely just build plain ol' Access queries that use criteria and join
> muliple linked tables together. JET is smart enough to build prepared
> statements and process the joins at the server side, so performance is
> pretty
> good.
>

[Back to original message]