Solved

subform efficiency, or is there another way?  plus executing a SQL stored procedure

Posted on 2004-04-22
9
501 Views
Last Modified: 2008-02-20
I am developing an application that uses an Access 2003 .adp GUI and a SQLServer database.  I have read elsewhere that using subforms in the GUI will cause a performance hit because of extra unnecessary database calls.  Can anyone comment on this?   I have been attempting to avoid using subforms because of this but I think I need to use one because I essentially need a multi-column listbox where each of the cells is updatable rather than just read-only.  

Specifically, my form contains a dropdown with a list of names.  When a name is selected (AfterUpdate event), I want the subform/listbox to be populated so that individual items can be edited.  I made a multi-column listbox based on a SQL query, but the data is read-only.  Is a subform the only way to accomplish this and if so, any suggestions on how to keep the performance as efficient as possible?  

Finally (and this is kinda unrelated so I will spread the points around if necessary), whichever display method I use (subform or some wonderful updatable listbox), I would like to populate it from a stored procedure with a parameter rather than a query since the query is somewhat wordy.  If someone can point me to an example of this I would appreciate it.  

Thank you.
0
Comment
Question by:rsoble
  • 5
  • 4
9 Comments
 
LVL 1

Author Comment

by:rsoble
Comment Utility
I guess it's a busy day for you Access Wizards & Gurus.  Let me summarize my questions and add a few points...

1) are there performance issues involved with using subforms with a SQL Server backend?

2) if using a subform is suboptimal, is there any other way to have a one-to-many multicolumn UPDATABLE list?

3) code example of how to populate subform or listbox using a stored procedure with a parameter.  

Thank you.
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
Comment Utility
Hi rsoble,

Without the article that poses the performance problem with subforms I'll have to guess why they do state this.
I think they are referring to so-called "linked" subforms.
Normally you use a subform in access to "follow" a relation. (E.g. OrderDetails linked to a main Order Form)
When you link such an order detail subform access will synchronize the main and the subform and thus issue a query to extract the set of detail rows for the order. Perhaps that's the mechanism the article is pointing to as being a slowing down factor.
When you fill the subform yourself I can't see any performance issue.

For the .adp and SP part I regrettably have only some theoretical knowledge so I can only give you this MS link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/IDH_acdefAccessProject.asp
hoping it's holding some more information for you.

Nic;o)

0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
The article I read that discouraged the use of subforms is here:
http://sql-server-performance.com/access.asp.  
The particular paragraph reads:
"If possible, avoid using subforms in your Access forms. Subforms require at least two queries to be issued to SQL Server and require more overhead. Instead, use a query to join the tables you need and display the results in the form. This only requires one query to be sent to SQL Server and has much less overhead. Most columns from multiple-table tables can have data inserted or updated into them, so subforms can often be avoided. [6.5, 7.0, 2000]"

I don't understand the alternative they are suggesting.  I do want to use the form as you describe:  I would have the Orders in a listbox, and then when one is selected I would populate the subform with the OrderDetails (from the event handler code of the listbox).  

Thanks for your help.
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
BTW that link was helpful about the SP question.  I followed it to here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart00/html/sa00f10.asp  which looks to be very useful.  

Interesting how they say "To get the most out of Access Data Projects, you must use stored procedures. But trying to use them for some of the most common tasks isn’t covered in the documentation."  

haha  -- ain't that the way it always is?

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Hmm, the article is from 2000/2001 and so not taking the Access Data Project in account as far as I can see.
They are right that you'll need to issue separate queries for the main/subform and indeed one query joining two tables will be faster.
Personally however I always start developing "straight forward" and with the most "heavy" queries first.
Only when performance proves to be a problem I'll start looking into alternatives as often more time is spent looking into alternatives and coding for a possible "worst case" that never is reached in the running application.

Remains ofcourse the question why to use access .adp while you could also use other languages like ASP to access the MS SQL database.

About the quality of the MS link, you found out why I often get irritated looking into their knowledge base :-)

Nic;o)
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
This previous question explains why I'm using .adp.
http://www.expertsexchange.com/Databases/Microsoft_SQL_Server/Q_20949132.html
In retrospect I realize I didn't ask my question very well since nobody mentioned .adp as a possible solution (or perhaps it was too obvious but I didn't know it existed).  I found out about that possibility on my own afterwards and immediately knew it was what I needed.

I think your idea is a good one - just use the subform and observe the performance before I go to lengths to avoid it.  Especially since this is a relatively small system (but the users have been burned by performance issues because of previous poor design so they are extra sensitive to the issue).   Also this is phase one of a long redesign process so I'll have time to see it in action while I'm still working on it.

I'll leave the question open another day or so in case any other great ideas come along.  Thanks for your help!
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
You're welcome.

Nic;o)
0
 
LVL 1

Author Comment

by:rsoble
Comment Utility
Thanks for your help Nico.  I had a little more trouble with the stored procedure stuff but finally worked it out thanks to finding this PAQ:
http://www.experts-exchange.com/Databases/MS_Access/Q_10028840.html
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Glad it all worked out, great job rsoble !

Success with the application !

Nic;o)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now