Async Query

...
1) ... Need to utilize pass through queris, odbc connection, stored procedures, sql views, etc.
2) Return from the server side the smallest amount of data and records needed.
3) Make sure your queries are efficient.

1. Build and use Pass through queries
...
Thanks, I've converted all my queries to PT. Really appreciate your help.

Although I'm interested just for academic purposes and any future use, I'm interested to know how Async queries perform. I mean, they either perform like normal Access query where it pulls all data to the desktop and then run a query, or is it like PT query which sends all the Async queries to the server and extract back only results?


...
The INNER JOINS that you have shown us in that link are probably at least partly the source of your slowdown. Done that way, it appears that you have to search the whole table every time.

I built a local temporary table that had, in essence, two fields - an ID number (that was the list of prime keys from the main table) and a Yes/No flag that always initialized to YES. This table was easy to populate because it came from the main table that everything else would JOIN with.

I then built a query for the main table so that I could see all of the fields. When I ran the partial queries to eliminate things that didn't match up, it was through an INNER JOIN query that linked the temp table to the main table through the PK field. So in other words, I was looking at a layered query, never directly at the tables...

I've eliminated INNER JOINs and created a temp table as you suggested. This idea I had in my mind but copying everything against matching ID to another table would make it slow. Didn't come to an idea that I could copy only ID field and use with the main table.
Only eliminating INNER JOINs made the performance factor faster by 5-8 times per query as per execution time displayed by SQL Server profiler. I never though it would make such a big difference.

I created Clustered Index on ID in temp table, and Non-Clustered on Flag field. Then created PT queries, place condition to run only queries against active combos, update the flag to 0 and drop them before next match. This all has made quite a difference.

...but I have one question though. Why Flag is required in temporary table? I mean we could directly compare IDs and drop unmatched IDs. Why use two queries, for UPDATE and then DELETE?
Asking just for curiosity, maybe i'll pick something good.

Thank you for your help guys. I'm going to close thread as solved.

Thanks,
K
 
Last edited:
either perform like normal Access query where it pulls all data to the desktop and then run a query
this is a common misunderstanding. Access doesn't do that, the ODBC driver does - but only if it has to. The ODBC driver does what it can to convert access sql to the target native sql.

So if your query references only tables/fields on the server, all the processing is server side.

However if your query uses vba functions not known to the target native sql (such as iif, nz or a udf, etc) and cannot convert it an equivalent it needs to bring back data for these functions to operate on before completing the recordset. The same would apply if the query includes data from a local table.

There is a skill involved in optimising queries that go via ODBC. By using a passthrough query you are effectively doing the job of the ODBC driver to convert what you would have had in access sql to the db native sql - although you also have the benefit of being able to utilise native sql functionality not available in access.

As with any system that accesses data via a LAN/WAN queries should be optimised to bring through the minimum amount of data. Unfortunately the Access default position is to use a table or query as a form recordsource and then filter - which does necessitate bringing all the data across in order to filter it.
 
To add to CJ_London's good explanation, you are confusing a server side query with asynch. If properly done with ODBC it will be server side. The asynch would only allow processing to continue client side while you are waiting for the results to return. So it is more an addition to what you can do with a server side query, not a difference. You will know the processing has completed because on the client side you can trap the execution complete event.
 
@ The_Doc_Man

Maybe this experiment will convince you since you did not follow my sort or record selector logic. Make a form with two command buttons. One to load a recordset and one to bind the recordset to a control, form, or subform.

Code:
Dim rs As DAO.Recordset

Private Sub cmdLoadRS_String_Click()
  Set rs = CurrentDb.OpenRecordset("Select * from Employees order by lastname", dbOpenDynaset)
   MsgBox "Modify some records in employee table then bind the recordset to the combo and subform"
End Sub

Private Sub cmdBind_Click()
   Set Me.Employees_subform.Form.Recordset = rs
   Set Me.cmboEmployees.Recordset = rs
   MsgBox "Did any requery take place"
End Sub

Click the button to create a recordset. Now go to the original table prior to binding the recordset. Add, modify, and delete records as you wish. The more you do the more obvious. Now go back to the form and hit cmdBind. If a requery takes place it will be very obvious you will see all of your changes, if it does not it will look like the data prior to your edits (except deleted records will now say #deleted as expected).
I have found one exception. If the form or subform is in datasheet view (and only that case as far as I can tell) a requery will in fact take place, and if that subform is in datasheet view it appears that it will also cause the main form to requery.
 
I acknowledge that the situation is not as I thought it was. However, my time on the computer is limited at the moment because of a priority project. I'm fitting in this online time while my grandson isn't here (since he's out of school, he is here a lot) and when I'm not working on a current genealogy project with a deadline. And when I'm not sleeping. Please forgive me if I don't get around to that experiment right away.
 
Thanks for the helpful posts. I picked up quite of new concepts from this thread.
 

Users who are viewing this thread

Back
Top Bottom