|
|
Posted by Neil on 10/30/05 03:03
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).
Neil
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96FF7327EABEYazorman@127.0.0.1...
> Neil (nospam@nospam.net) writes:
>> OK, point well taken. I don't see any workaround, though, since I'm
>> using ODBC linked tables. I can't use a pass-through query and pass the
>> table name as a parameter, because pass-throughs return read-only sets.
>> And a view wouldn't be able to return just the records for that
>> machine.
>
> As for how to sort out the linked tables, you will have to ask in an
> Access newsgroup.
>
>> Here's an idea. What if there were one table, but a series of views, with
>> each view returning records for a particular unique ID. When the app is
>> opened, it's assigned an available ID and its link is set to the view
>> that
>> returns records for that ID. Still kind of a kludge, but perhaps a little
>> better.
>
> A table-valued function with the machine name as parameter would be
> better.
> But without knowing Access, I'm quite sure that this can be solved by
> adding the machine name on the Access side as well. After all, that is
> also an RDBMS, and should be fitted for relational solutions.
>
>
>
> --
> 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
>
Navigation:
[Reply to this message]
|