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
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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 Search this Thread Display Modes
  #1 (permalink)  
Old April 15th, 2005, 04:34 AM
Authorized User
 
Join Date: Apr 2005
Location: Wymondham, Norfolk, United Kingdom.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default poor perfomance using Access front end to SQL

Hi,

  I've just converted an Access Database to use SQL as a front end, whilst maintaining the Access front end. Generally everythings ok with on exception. It takes an extortionate amount of time to access one particular table. A good 30 seconds or so, and in some cases locking the machine up all together. it appears more likely to lock a machine if it is running win98. Win2k and XP seem more resilient. I'm a complete SQL newcomer, but as seems fairly common, I've been given the task in a "Get it sorted by last week" senario! Can anyone help? I realise more info will be needed, but I don't know what ppl will need, so post on here and I'll endevour to get the info required!

Many thanks

Rob
Reply With Quote
  #2 (permalink)  
Old April 15th, 2005, 06:05 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Since I haven't worked with performance issues on this, I can only tell you what I've heard (and what you can read in Access help).

If you're using an MDB as your Access front end, I've heard that queries should be built as "Pass-Through". Apparently this make Jet send the query to the SQL Server where SQL Server will do all the work and send back only the necessary data. A non Pass-Through query gets all of the data and does the work on the local machine in Jet.

If you're using an APD (Access Project), you should probably build a Stored Procedure or a View to have SQL Server send you the data. This will ensure that SQL Server is doing the work.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
Reply With Quote
  #3 (permalink)  
Old April 15th, 2005, 06:13 AM
Authorized User
 
Join Date: Apr 2005
Location: Wymondham, Norfolk, United Kingdom.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply. I'm using an MDB front end (as opposed to ADP) I don't know what I need to do to convert the queries to "Pass-through". Like I said I'm a complete novice, but I need to learn rapidly!!!
Reply With Quote
  #4 (permalink)  
Old April 15th, 2005, 06:20 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

A server side stored procedure will package the data for you, but still send all the data you request. If you are requesting the entire table with your call to either the table or the stored procedure, then you are going to have the same performance hit.

What are you using to access the data in this particular table? Is this a report or a form? In either case, try limiting the amount of data you are calling by passing parameters, or only calling one record at a time.

I have a mixed dbms database I use and call both Access and SQL data, sometimes on the same form or report. I have to limit the amount of data I pull each time, and this speeds it up. In fact, Access is the slower call, as it should be.

I open my main data entry form using a combo box and button, and then call one record for the form to open. Then I use a combo box on the form to requery the recordsource and select the next record, rather than using the record selectors at the bottom of the form (gone anyway).

If you are getting this slowdown on a report, you can limit data in the same way, or just have to wait for a big call. I have one report that runs through a table with 25K+ records, and I just have to wait.



mmcdonal
Reply With Quote
  #5 (permalink)  
Old April 15th, 2005, 06:36 AM
Authorized User
 
Join Date: Apr 2005
Location: Wymondham, Norfolk, United Kingdom.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm using a form to access the data. I've found how to change all the queries to pass through queries. will this help any? I guess one of the problems I've got is that the system was created before I started at this company, and I guess it wasn't written with SQL in mind. hence the problems. what utilities can I use to see what data is being transferred etc?
Reply With Quote
  #6 (permalink)  
Old April 15th, 2005, 06:38 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Sorry for that. Pass-through can be set by opening the query in design mode. Then right click in the table area, select "SQL Specific" then Pass-Through.

Once you switch to Pass-through, you have to code the SQL yourself. You can't switch back to "Query design" view after you've change to Pass-through. So you might want to build a working query without it being Pass-Through first.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
Reply With Quote
  #7 (permalink)  
Old April 15th, 2005, 06:40 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

I think you'll find that pass-through is your answer.
Reply With Quote
  #8 (permalink)  
Old April 15th, 2005, 06:41 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What are you doing on the form? How much data do you need for each transaction?

Example: If you are pulling up employee records, you probably only need to see one employee per transaction. In that case, limit the form to opening with just one employee, either someone you select, or the first employee in the list. Then use the Look Up combo box on the form to requery the recordsource for just the employee you select.

If you need all of your records for a transaction, then you are going to have your speed issues still.

mmcdonal
Reply With Quote
  #9 (permalink)  
Old April 15th, 2005, 06:54 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

A pass-through only drops the jet engine out of the process. If you are calling huge amounts of data, you are still going to see a slow down due to volume. You need to also limit the amount of data you call. It is rarely useful to call all the data in a table, and if there are any joins, then you are calling all the data for the table, and related data in the sub-tables - for each record called.




mmcdonal
Reply With Quote
  #10 (permalink)  
Old April 15th, 2005, 07:15 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

I interpretted "get it sorted by last week" to mean there is a limit on the data being returned. Bad assumption on my part. Sorry.

Since Rob is a self-proclamined novice, it is important to point out that limiting the data is one of the first things to consider.

Certainly you can't build a pass-through query willie-nillie, joining to things that aren't pass-through. Nevertheless, if it is a pass-through query, and it's not joined with any local tables and is only joined queries that are also pass-through, won't the work be done on the SQL Server without returning more than the data requested?
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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
Multiple SQL Databases for Same Access Front End darrenb Access 0 May 27th, 2008 12:07 AM
Ms Access front End with Oracle 10g Back End rahul123 Oracle 1 July 9th, 2007 01:03 AM
Oracle back-end MS-Access 2003 client front-end Corey Access 2 February 16th, 2007 08:31 AM
Oracle Back End - MS Access Front End - Multi User ckaliveas Oracle 1 February 1st, 2007 06:00 AM
using msde and sqlserver with access front-end catkins Pro VB 6 1 April 1st, 2004 04:00 AM



All times are GMT -4. The time now is 12:51 AM.


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