Avoid requerying mysql db from Access when sorting

Question

Hi there I have an MS Access frontend and MySQL backend. Everthing runs well however I am trying to avoid the following. On an Access form, I use it to do read queries. Once the results are shown on the Access form (excel like format), I can sort A-Z, Z-A etc

The thing is that everytime I do any sorting, I can see on MySQL Administrator that a query (in fact more than one) is being performed. I need to avoid this for obvious reasons, as I user will constantly do sortings and all sort of things with the displayed data.

Since the the data is displayed already in the client side (Access form), I don't undertand the reason for MySQL to be requeried again when doing the sorting. I would expect anything you do with the results of the query will happen "locally".

Any ideas what I am doing and wrong and/or how to avoid this ?

thanks ind advance

Answer 1

That's pretty typical to make a roundtrip back to the server for sorting. I would imagine most applications do not sort from internal memory but hit the database again. It would probably be quicker to sort locally if you only had a handful of rows. But if you are dealing with millions of rows it is going to be much quicker to perform that sort on the db as that is what it is made for.

Why do you not want it to make that roundtrip for sorting?

Answer 2

thanks for answering the thing is that each query (from a table with 100,000+ records) usually results on 10-20 records being displayed only.

The user then can check which option is cheaper by sorting by price. At this stage I don't want to query back the mysql db...I want it to happen locally...as all the information is already at the client's side (like I said, only 10-20 rows are displayed)

The reason is because there can be 1000+ users at once connected and obviously I am trying to avoid as much queries to mysql as possible.

Answer 3

Is your form bound to the whole table? If so, that's a design error. It should be bound only to a limited recordset. In that case, only a small query is going to be executed server-side.

Secondly, you might want to turn on Jet SHOWPLAN (Google for it) and see what the Jet/ACE db engine is doing with regard to optimization.

Third, it may be hitting the server, but is it a significant hit?

Last of all, you may get the results you want by using disconnected recordsets assigned to your forms instead of bound recordsets. This is a lot more complicated than using the default bound forms, but can be necessary. It does make using Access a lot less pleasant as the fit and finish is not nearly as smooth as with traditional Access bound forms.