Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 17th, 2004, 03:47 PM
Registered User
 
Join Date: Jun 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Pass-Through Query Optimization

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
 
Old June 17th, 2004, 05:35 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default


Quote:
quote: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.
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


 
Old June 17th, 2004, 09:07 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 18th, 2004, 01:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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.

Quote:
quote:Then I perform an equijoin, joining the two tables, and return that as the results…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?


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







Similar Threads
Thread Thread Starter Forum Replies Last Post
IF statement in SQL Pass Through Query Coby Access VBA 3 February 15th, 2008 09:24 AM
SQL Optimization - Query takes a long time jlrolin SQL Language 2 March 20th, 2007 08:01 AM
pass the result of a query to a variable ... eusebio Pro VB Databases 6 March 30th, 2006 07:41 PM
Pass-Through SQL Insert Query bmwbear Access 1 October 18th, 2004 12:43 AM
Query optimization SubodhKumar SQL Language 1 October 22nd, 2003 06:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.