Posted by John Bell on 10/30/05 12:03

Hi

I probably know less about access than Erland, but you may want to look at
just what is happening on the SQL Server when you have everything on the
server. SQL Profiler may give you some information that we may be missing
and how each solution works. Also look at the Query Plans for the executed
SQL and check your indexes are being used and that you have up-to-date
statistics.

You may be able to simplify things with the HOST_NAME function in your view.

John

"Neil" <nospam@nospam.net> wrote in message
news:cZU8f.3368$Rl1.2750@newsread1.news.pas.earthlink.net...
> 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
>>
>
>

[Back to original message]