Thanks, I've converted all my queries to PT. Really appreciate your help....
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
...
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: