[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

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

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
rsoble
Asked:
rsoble
  • 5
  • 4
1 Solution
 
rsobleAuthor Commented:
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
 
nico5038Commented:
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
 
rsobleAuthor Commented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
rsobleAuthor Commented:
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
 
nico5038Commented:
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
 
rsobleAuthor Commented:
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
 
nico5038Commented:
You're welcome.

Nic;o)
0
 
rsobleAuthor Commented:
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
 
nico5038Commented:
Glad it all worked out, great job rsoble !

Success with the application !

Nic;o)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now