Interface unresponsive after query is run

[outlier]

Hi all,

I am executing a somewhat complex query. The query joins a local table to a third party linked VIEW table and then performs a crosstab transform to display the data in a meaningful way. I have a few other local table joins as well. The query takes about 5 seconds to run.



~20k records are displayed and the interface is COMPLETELY lagged/locked up. I click a record and access will start timing out. Scrolling down is nearly impossible... why would it still be unresponsive even after the query has finished executing?? isn't it just a result set and the interface should be fast?

[deepin125]

In my experience with query's I have seen them load as you scroll down. Are you doing any calculations that could be slowing it down? Have you tried filtering the query so that less results occur so that you can be sure that the query actually works?

[Rawb]

I don't have much (any?) experience with CrossTab views but I'd guess it's one of three things:

1. The transformation from Query Result to CrossTab view requires additional processing as you scroll around on the Form.
2. The Query Results are, in fact, loaded on-demand. This means that the first time you scroll through your Form, the Query is basically (partially) run again to load more results. This is also why the vertical scrollbar doesn't seem to know where the bottom of a Continous Form is until you're already there.
3. You have additional processing being done in your Form (such as running DMax, DAvg, etc. functions) that's slowing things down.

Try running the Query by itself. If you can view the Query Results and scroll up and down through them without trouble, then it's definitely something on the Form. If you have similar lag though, then you know it's directly related to the Query itself.

If it's Form that's causing the lag, your best bet would probably be to dump the Query results into a Temporary Table and use that for the Form. Also, try and do as much of the calculation as possible in the Query itself. That may slow the Query down, but you'd probably rather have Form that takes 8-9 seconds to load and runs fine after that than one that only takes 5 seconds to load but has lag.

If it's the Query, try filtering your results by adding a WHERE clause (or a more restrictive one) and by limiting the number of Records it returns (using SELECT TOP n).

And lastly, no matter where the problem is, try and stay away from the various "D" functions (DMax, DAvg, etc.): They will slow everything down significantly and should only be used when absolutely necessary.