Posted by Neil on 10/30/05 22:56

Erland and everyone else who has been helping me here with this.

First, I want to thank you for steering me in the right direction. My
original idea about creating a series of tables was somewhat hair-brained;
but I was feeling desperate at the time and was willing to try anything.

Moving the table to the back end and making it a true relational table was
the right way to go. I noted that using this approach (with a resulting 1.25
mil record view) was actually slower than the original configuration with
the heterogeneous join. However, I opened the view, moved to the last
record, and now it's fast! So it seems that the indexes hadn't been filled
(or something like that). The form now opens in about 7 seconds (down from
13), which should be OK.

So I'm wondering if there is some command that one can use to accomplish
what I accomplished by moving to the last record.

Also, the other issue I recently noted regarding the HOST_NAME function is
still outstanding, and I should try to resolve it, as I've seen better
performance when using that than when passing the host name from the front
end. I started a new thread to discuss that, entitled, "Problem with
HOST_NAME Function with Linked View." If any of you would care to contribute
there, that would be great.

Thanks again to everyone for your help! It's much appreciated!!!

Neil


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96FF73E7D7719Yazorman@127.0.0.1...
> 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]