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

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96FF73E7D7719Yazorman@127.0.0.1...

> You will have to bear with me, since my knowledge of Access is so poor.
>
> But if I understand this correctly, you have a local table in Access
> with selections that typically has 50000 rows. And since each user
> has his own Access instance, this means that today there are some 25-50
> instances of this table.

Yes, each on its own machine.

>
> 13 seconds to open a form is indeed a long time, and many users would
> say a too long time.
>
> But moving this data to SQL server may not a very good idea at all.
> Sending
> 50000 rows over the wire is not done snap. On a local network it may be
> decently fast, but if you have a user that works from home, it will be
> a pain.

Yes, the situation is over a T1 line. The LAN users don't have any
significant delays. But the WAN users are getting long load times.

>
> So I would suggest that you should rather look into to load fewer rows
> into the form initially, and then fetch depending on what action the
> user takes. I can't believe that the user is looking at all 50000 at
> a time.

Well, they *work* with all 50,000, even if they don't use them. They like
being able to work in datasheet view (a spreadsheet-like representation of
data) and do sorting, filtering, editing, etc. In form view (the traditional
representation of data), I could give them one record at a time; but in
datasheet view they like to have all the records there.

> If this data is only related to the user's selection, it's probably a
> good idea to keep it local anyway. The only point I can see with moving
> it to the server, is that it could permit the user to get back his
> selection if he moves to another machine.

Well, the idea was to eliminate the heterogeneous join. And, indeed, I have
seen a performance increase in other areas of the form with the trial
back-end selections table I put in place (sorting on a field, for example,
is much faster with the selections table in the back end). The problem,
though, is that in this one area, the initial opening of the form, it
actually slows things down. And that's a key area, since users need to be
able to open the form quickly when they need the data.

Neil


>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>

[Back to original message]