7 Gigabits Per Second WAN Bottlenecks (2 Viewers)

[Minty]

If the combo's are lookups and normally static values then load them into a local cache table on the FE database when you open it.
Things like employee lists, state codes, Countries, Currencies etc. that are frequently used and rarely change can and should be loaded into a local lookup table for speed.

Complicated Combo rowbox sources should also be made from views, but I suspect you are already doing that.

 

[BlueSpruce]

Complicated Combo rowbox sources should also be made from views, but I suspect you are already doing that.

The row sources are queries that bring in values which are regularly updated, so I wouldn't be able to leverage storing those values on the frontends, nor using views because the combos need all the values.

 

[Albert D. Kallal]

The row sources are queries that bring in values which are regularly updated, so I wouldn't be able to leverage storing those values on the frontends, nor using views because the combos need all the values.

Actually, I can add one more FYI to the issue of combo boxes.

Do NOT use a pass-through query to drive a bound combo box.

But, why?

This advice seems counter intuitive, since after all, a PT query is perhaps the fastest performing way to pull data from SQL server, right?

Well, there is one "dark" downside, and issue one has to be aware in the case of a PT query.

Answer:
Access from the client side cannot filter such a query!!!

So, EVEN if one where to build (modify) the PT query client side to restrict the rows for that combo box?

Still bad!!!
And the reason is as follows:

When the form loads, or you navagate to another record?
Access is displaying the combo box, and OFTEN of course we have a hidden id, and are thus displaying a 2nd column (description or whatever).
And, that means Access has to look up, and take that ONE row out of the combo box data source.

But, as I pointed out, Access can't filter a PT query down to one record, and in fact Access cannot filter the PT query in ANY WAY!!!

What this means?
Well, when you navigate to the next record, or even load the form?
Access has to hit the data source, and "scan it" for the one row that is currently selected. And since the data source can't be filtered?
Then the whole data source will be scanned.

I had a few forms - combo box based on PT query. And when I moved to next record, there was a significant delay.
I traced the issue down to that combo box + PT query.

Changing the combo box source to a view? (which the client side can then filter correctly)?
The big delay went away.

So, yes, use a PT query as you need to, but if ANY client side code, ANY where clause is to be against that PT query?
Then do not use or do this!!!
use a view.

And if the query is a single table, then of course no view is required nor will it help, and you are free to base the combo box on that SQL that points to the linked table.

However, using a PT query for that combo box, and it is a bound combo box?
Nope, Access can't filter the results (well, it can, but that's AFTER all rows are pulled from server, and THEN the filter is applied).

So, Access client will attempt a single row filter against the combo box data source - and that's not possible against a PT query.

Once again, the above narrative shows why I'm such a big fan of linked views over PT queries.

Of course, more then 1000 rows for a combo box is a bad idea anyway. But, at least with a view, then the Access client can correctly filter the one row result for display - with a PT query, Access can't filter down to that one row - Access will wind up re-pulling the data....


R
Albert

 

[NauticalGent]

Do NOT use a pass-through query to drive a bound combo box.

Found this out the hard way, using the flawed logic you mentioned...

 

[BlueSpruce]

However, using a PT query for that combo box, and it is a bound combo box?
Nope, Access can't filter the results (well, it can, but that's AFTER all rows are pulled for the filter....).

The combos need all unfiltered rows from single tables that are regularly updated, so using views provides no advantge.

 

[NauticalGent]

The combos need all unfiltered rows from single tables that are regularly updated, so using views provides no advantge.

I haven't tried views, but the fact that it is server-side filtering makes me think it would be beneficial. What I DO know is that using a client-side query was significantly better than using a PT query for my combo-boxes. I had no idea why, but like posi-traction - it just worked!

 

[Albert D. Kallal]

I haven't tried views, but the fact that it is server-side filtering makes me think it would be beneficial. What I DO know is that using a client-side query was significantly better than using a PT query for my combo-boxes. I had no idea why, but like posi-traction - it just worked!

As noted, PT queries and a combo box don't play nice here.

Access client can't filter the PT query correctly.
And for messy sql? Again, move to server, and then base the combo box on that view......

So, a PT query for a combo box simply does not work well, but building the view and using that linked view for the combo?
You be happy with the results ......

R
Albert

 

[BlueSpruce]

I haven't tried views, but the fact that it is server-side filtering makes me think it would be beneficial. What I DO know is that using a client-side query was significantly better than using a PT query for my combo-boxes. I had no idea why, but like posi-traction - it just worked!

Views are perfect for when you need filtered results, and for queries involving multiple tables. The heavy lifting is best done server side to minimise network traffic. The beauty about server side views is that Access FE's see them as linked tables. I have some combos that use self-join queries as their row sources, and those also delay form loading.

 

[Albert D. Kallal]

Views are perfect for when you need filtered results, and for queries involving multiple tables. The heavy lifting is best done server side to minimise network traffic. The beauty about server side views is that Access FE's see them as linked tables. I have some combos that use self-join queries as their row sources, and those also delay form loading.

Another trick that helps?
Just specify the view for the combo box, not a SQL query. They again will load faster. This can't always be done. But, if you have say 3-5 combo boxes on a form?
You notice a slight improvment. So, JUST the view specifed for the combo box source. Now, to be fair, views in theory don't have a valid sort, but you can put in a sort on the view (server side). The designer will then toss in a top 100 clause. As noted, a view is considered a un-sorted table.

So, if just 1-3 combo boxes? Then you can well just use SQL against the view for the combo box source.

But, I have noticed a slight improvement if you JUST specify the view name, and no SQL for the combo box.....
R
Albert

 

[BlueSpruce]

I have noticed a slight improvement if you JUST specify the view name, and no SQL for the combo box.....

Server views appear as linked tables in Access FE's so I just use a regular AccessSQL query against that logical table.