Hello all,

I'm a new hire where I work, and while most of my experience lays with SQL Server and ASP.NET, they've asked to help out with some of their legacy apps, which are all Access97-based DBAs.
I've been asked to speed up a certain utility in any way possible. We host the database segment of the application on our servers, and the clients who use the application have limited bandwidth, and its been known for the search functionality to take 30-40 seconds to come back with all the complete records.
And so, since I'm searching (with no need to update the records) and I need to speed up Access, I thought a Pass-Through Query would be a good idea.

I've implemented the pass through query as such:
Dim searchres As QueryDef
Set searchres = CurrentDB.CreateQueryDef("Search_Results")
searchres.connect = myConnectionString
searchres.sql = myQueryString

This seems to be working, and while its hard to measure the speed up here (since we're on site with the database), it is noticable.

However.... (and here's where the problem is)
I've was also asked to recently modify the utility to search only for the highest revision number, and they way the client decided to order revision is kinda messed up. So I converted that into pass through, as well. The problem arises when they are searching with the max_revision_only search enabled. My approach has been to pass-through search for all the max revisions and save the query as "Max_Rev". I then perform their normal search, on all the records (not just the maximum revisions) and save that as a Query named "Search_Results". Then I perform an equijoin, joining the two tables, and return that as the results.

The problem is the join really seems to bog down the program. I think its actually running slower now when they search for max_revisions_only then when they use it normally. When they are not searching for max_revs, the program runs fine.
The two querydefs I create, Max_Rev and Search_Results show up with the Globe icon in Access' Query dialog box. Does this mean the querys are remote querys, or are they local querys? When this join executes, is it being done on the server, or is my computer getting all the records and all the max_revisions and joining them locally?
And lastly, what suggestions does anyone have for changing my approach in order to better speed up this application. I know there has to be a better way, but due to my nearly total ingnorance of Access until Wednesday (although I'm getting a wrox book :D) it has eluded me. Any thoughts would be appreciated.

Thank you,
Da Redrobot

Your pass-through queries are being proccessed on SQL Server. Access literally passess the query through to SQL Server without the Jet engine ever compiling the query, performing syntax checks, etc. That's why pass-through queries are written in the native SQL dialect of the server (T-SQL in the case of SQL Server), not Jet SQL. The result set sent back is handled by the Jet recordset processor as a read-only, snaphot recordset.

One of the real strengths of pass-through queries when used with Access is that they can be used to execute stored procedures on SQL server. Stored procedures are precompiled T-SQL statements and should give you better performance. Try encapsulating your query definitions in a stored procedure, then use a pass-through query to execute the stored procedure. In this case, the pass-through queries SQL statment would simply be the name of the stored procedure, or:

exec StoredProcedureName

Also be sure that all your join fields have indexes built on them.

HTH,

Bob

is the data is SQL Server? if it is not, this queries will not run faster. if the data is in SQL Server, then you need to look into your indexing. Bob is right, if the data is in sql server, use stored procedures with indexing on tables and you will gain a lot of speed.



Sal

Rereading your post I see that I didn't really answer your question regarding where the equijoin is performed (sorry, was a little pressed for time). I think I have a better answer for you.



The term "table" here was confusing. I assume you mean the two snapshot recordsets returned by the two pass-through queries, and that you are joining the two pass-through queries in a third Jet Select query (QueryDef object) which returns your final result set, i.e. using the pass-through queries as if they were attached tables.) So there are really 3 relevant queries in your database window. Right?

Lets say the SQL statement of the Jet Select query that brings together the results of the two pass-through queries into a final result set looks something like:

SELECT Max_Rev.SomeField, Search_Results.SomeField
FROM Max_Rev INNER JOIN Search_Results ON Max_Rev.JoinField = Search_Results.JoinField;

with Max_Rev and Search_Results being your pass-through query definitions.

If this is the case, the Jet engine, and not SQL Server is performing the join after scanning both recordsets cached in a local buffer.

You can see this by viewing the query execution plan Jet’s query optimizer generates when the ShowPlan function is turned on. Here’s the output after running the Jet Select query:

---------------------------------------------------------------------------------------------------------------
DATE: 06/17/04
VER: 4.00.8015

--- Query1 ---

- Inputs to Query -
Recordset
Recordset
- End inputs to Query -

01) Scan recordset
02) Scan recordset
03) Sort table '02)'
04) Inner Join table '01)' to result of '03)'
      using temporary index
      join expression "Search_Results.CustomerID=Max_Rev.CustomerID"
---------------------------------------------------------------------------------------------------------------

You can see that the two inputs to the Jet query are your snapshot recordsets cached in memory. After the recordsets are scanned, the Jet engine performs the equijoin using the join expression provided.

Again, a better way to go would be to create a single server object (like a stored procedure) that could perform all your query processing on the server, and then return a single, final result set back to Access via one pass-through query execution.

HTH,

Bob