Solved

DB/VB design opinion request, be quick for the points.

Posted on 2002-06-26
13
144 Views
Last Modified: 2013-11-13
Hi,
This isn't a "how to" question; I know how to do both ways.  THis is a "which is best, and why" question:

I'm using VB client, Network based DB, max simultaneous users 20 viewing mostly different data (not transaction heavy), is it better to:

-Open a bunch of recordsets at client-startup & leave 'em open during session.

_OR_

-Open & close recordsets upon request of client each time DB access is necessary (which might be once evry 30 seconds to once every 10 minutes)

I'm currently using Access, but I'm gonna migrate to SQL Server or Oracle as soon as the LAN Admin guy gets his act together.  ADODB connection.  Sessions might last 3 to 8 hours.

Thanks in advance for sharing your experience & knowledge.

Bob
0
Comment
Question by:BobMiner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +3
13 Comments
 
LVL 11

Accepted Solution

by:
joekendall earned 300 total points
ID: 7111220
I believe it is best to open and close recordsets as they are needed. You can keep the resources in your application to a minimum. Also, it can keep you from having problems with your DB.

Another option might be to create a COM object to manage your DB connections/recordsets.

Thanks!

Joe
0
 
LVL 10

Expert Comment

by:GoodJun
ID: 7111223
For 20 concurrent users, it really doesn't matter. The speed wise is the same (you can't tell the difference).
The second method is better if you want your app be more
scalable. It has the potential to use the new technology to scale to more users.
0
 
LVL 5

Expert Comment

by:bob_online
ID: 7111232
Load them as you need them.  Your app will start up faster and require fewer resources.

Unless you're executing massive, complex queries, running them as needed shouldn't be a performance problem.  Just be sure that as they are closed, all references are set to nothing.  With ado, I've found it's best to explicitly close the recordset and then set the recordset object to nothing.
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Expert Comment

by:VBGuy91917
ID: 7111248
I would open disconnected recordsets on the client if you're not planning on having a lot of transactions on them to cut back on server traffic, unless you're on a dedicated server.  If there are other applications hitting that server, and you're connection is going to be open that whole time, and they may be hitting the server pretty hard, it could be more beneficial to have your recordsets open on the client.  That way, if someone else is hitting the server hard and causes it to crash, you still have your data.  

If there aren't any other applications hitting the server, or that aren't transaction heavy as well, then it probably wouldn't make too big of a difference.  
0
 
LVL 4

Expert Comment

by:gencross
ID: 7111416
I agree with joe and bob.  Open as you need them.
0
 
LVL 10

Expert Comment

by:GoodJun
ID: 7111486
gencross,
What's the difference between mine and theirs?
0
 
LVL 4

Expert Comment

by:gencross
ID: 7111535
GoodJun,

Joe and bob stated upfront that opening rs's as the user needs them is the better solution, not that it doesn't matter which method is used as you commented.  I did not want to state what the others already stated so I just said I agree with them.  

Not that I disagree with your entire comment.  Everything sounds good except..."For 20 concurrent users, it really doesn't matter.  The speed wise is the same (you can't tell the difference)."

There is good software design and bad software design, and in my opinion opening many rs's that may or may not be used is bad software design and poor use of resources.

That is my dissertation on why I agree with joe and bob.  I hope it was informative:)
0
 

Author Comment

by:BobMiner
ID: 7111655
I thank you all for the input especially after seeing other comments that beat you to the points.  I'll go with the consensus of "open as needed, then close".

I've asked support to award some points to GoodJun, bob online, and VBGuy91917 for your valueable opinions.

Regards,
Bob
0
 
LVL 10

Expert Comment

by:GoodJun
ID: 7111736
gencross,
>There is good software design and bad software design, and in my opinion opening many rs's that may or may not be used is bad software design and poor use of resources.

There are a lot of times open a bunch of rs at start up is the choice. (say, some library records you need often address, etc...). Design is depends on the situation. We can't say that maintain open rs is bad design. It has its places.
0
 
LVL 4

Expert Comment

by:gencross
ID: 7111900
Ok :)
0
 

Author Comment

by:BobMiner
ID: 7111901
Thanks again to all.
0
 

Author Comment

by:BobMiner
ID: 7272846
A couple of you guys have points to collect.
Thanks for the input.
Bob
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month6 days, 7 hours left to enroll

636 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