|
|
Posted by Erland Sommarskog on 10/31/05 01:01
Neil (nospam@nospam.net) writes:
> 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.
Well, I still don't know Access, so I have still problems understanding
this.
There is no last record in a view. A view, just like a table is unordered.
But if you moved to what is the last record in what Access presents to
you, it is likely that all rows in the view went into the cache on SQL
Server. (Or a cache on the Access side if there is one.) One way to
force the view into cache is to run a query like "SELECT (DISTINCT col) FROM
view", and make sure to pick a column without index. Then again, as
users accesses the data it will make into cache. Pages that are not
referenced will be removed from cache, if the cache fills up. All that is
beyond your control. (OK, so there is PINTABLE, but don't use it.)
Since I don't know your application, nor do I know Access, I cannot really
suggest alternatives to the application design, but it just does not sound
right to me to get 50000 rows, before the users can start working.
--
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]
|