Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 17th, 2004, 04:47 PM
Registered User
 
Join Date: Jun 2004
Location: , , .
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
Reply With Quote
  #2 (permalink)  
Old June 17th, 2004, 06:35 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
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


Reply With Quote
  #3 (permalink)  
Old June 17th, 2004, 10:07 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
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
Reply With Quote
  #4 (permalink)  
Old June 18th, 2004, 02:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
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


Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 09: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 01:43 AM
Query optimization SubodhKumar SQL Language 1 October 22nd, 2003 07:35 AM



All times are GMT -4. The time now is 11:22 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.