Posted by Erland Sommarskog on 10/30/05 12:25

Neil (nospam@nospam.net) writes:
> 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).
>
> With the new solution, the main SQL table still has 50,000 records, but,
> as a sample, I populated the selections table with 50,000 x 25 records,
> simulating 25 machines having records in the table. I created a view,
> joining the two tables on the back end, and linked the view (with the
> machine name as a field) to the front end. Using that 1,250,000 record
> view with a parameter to only return the 50,000 records that match the
> machine name, the form took about 17 seconds to open.
>
> One of the reasons to move the selections table to the back end was for
> speed in opening the form. But the resulting 1.25 mil record table is
> slowing things down than the smaller table in the front end did, even
> with a heterogeneous join.
>
> Thus, I'm left with no solution, unless I can use a smaller table, of if
> there's something I haven't seen re. using the view in Access (which I
> don't think there is).

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.

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.

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.

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.

--
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]