Drew Wutka
DWUTKA at Marlow.com
Tue Feb 6 14:20:26 CST 2007
Ok, process records, let me see if I can take a stab at that. Let's say you have an insurance case. It gets assigned a case number, and you are allowing people to edit the details, as new things happen. This kind of concept should be redesigned so that each individual entry is a new record. It's a simple design, the case number and one time details would be in one table, then a separate table would hold additional entries, each with the foreign key from the original case number. This way, you can view all 'work' done on the case, but each 'change' is a new record, which is just added to the db, instead of tying up the entire record during editing. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters Sent: Tuesday, February 06, 2007 1:55 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Connections and Performance Yes - lot's of editing. These are mostly process records where a single record can stay available for editing for days or weeks before the record is finally closed. I do have a Vacation Management module which is mostly adding records though. But, due to screen graphics redisplay, it's the slowest of the bunch! I'm going to try to work through this one to see if I can speed it up. Dan Waters -----Original Message----- Subject: Re: [AccessD] Connections and Performance Do you have a lot of people editing data? That is a design issue that will cut the performance. Instead of editing, you should add. I know it won't work for everything, but it is feasible in many situations. Drew -----Original Message----- Subject: Re: [AccessD] Connections and Performance Thanks Mark! That sounds quite similar to what I do now. Lots of input/output and FE/BE. I'll try the continuous connection technique to see how that works. I'll have to wait till the next time I'm on site though. Dan Waters -----Original Message----- Subject: Re: [AccessD] Connections and Performance Dan, My experience with this is around is about 6 years ago and with A97. Most of my decisions then were based on this group's input. At that time the average feedback I received with Access as the FE and BE...was 40 users... after that corruption...so I limited them to 40 users. There were a number of things I did to increase performance. I had a seperate computer that housed the BE. Nothing else was done with this machine(just an extra desktop we had). FEs on each users machine. This app was a complaint management system...so continuous lookup/edit existing/adding new. I had an unbound searchscreen...it had a couple of bound subforms to show each user the stats of their cases...and the search results subform was unbound until you hit search. The form to display/edit the case record did not use the filter property...the 'record source' SQL used the searchform as criteria. Hope this helps...let me know if you have any specific questions. Thanks, Mark A. Matte >From: "Dan Waters" <dwaters at usinternet.com> >Reply-To: Access Developers discussion and problem >solving<accessd at databaseadvisors.com> >To: "'Access Developers discussion and problem >solving'"<accessd at databaseadvisors.com> >Subject: Re: [AccessD] Connections and Performance >Date: Tue, 6 Feb 2007 08:57:15 -0600 > >Hi Drew, > >Do you know how many FE clients could maintain that type of connection to a >BE at the same and still have reasonable performance? > >Does anyone have experience with this configuration? > >Dan Waters > >-----Original Message----- >Subject: Re: [AccessD] Connections and Performance > >I believe there is only one connection between a front end and back end. >In fact, many databases have better performance when the connection is >maintained through a 'linked table'. Create a dummy table (one field, >no records), called tblLink. Create a form bound to that table, and >open that form, hidden, on startup. That keeps the 'connection' live >while the user is in the database. > >I know in Access 97 that process was recommended in the help files under >'splitting a database'. > >Drew > >-----Original Message----- >Subject: Re: [AccessD] Connections and Performance > >Thanks Drew, > >I have asked everyone at my customer's site to close the screens they're >not >using (these are bound), but told them they can stay logged in because >the >main screen is not bound. We'll see if that helps. If not then I will >need >to start timing out the bound forms. > >I still don't know what is the definition of one connection. Is one >bound >form a connection? Is a FE with any number of bound forms a connection? >Or >is a connection defined by the periods of time when data transfer is >taking >place between a FE and BE? Or . . . ? > > >Thanks for teaching me! >Dan Waters > > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka >Sent: Monday, February 05, 2007 4:06 PM >To: Access Developers discussion and problem solving >Subject: Re: [AccessD] Connections and Performance > >Most 'articles' that 'document' Access performance are doing so based >upon poorly designed systems. You have to design your system based on >system requirements. If you are going to have 1 to 5 users, you can >keep everything bound, and be reasonably assured that things will work >well. If you are talking 5 to 30 users, now it's time to pick up some >of the slack. Timeout forms and reports that are going to tie up db >connections. 30 to 100 users, you better start thinking unbound. 100+ >users, better start thinking about something other then Access as a >front end (I would recommend ASP). > >The point is that JET, as a db system, can handle a lot, but Access, as >a Front End provides more bells and whistles that tax the backend more. >Case in point, we have our production database in Oracle, on it's own >honking box. Huge server, loads of memory and disk space, but when >certain things are run, even that system gets taken to It's knees. > >Drew > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters >Sent: Friday, February 02, 2007 10:13 PM >To: 'Access Developers discussion and problem solving' >Subject: Re: [AccessD] Connections and Performance > >Drew, > >I read this somewhere - either in MS documentation or in a book where I >believe the author. > >Here's the reason I was asking: > >At a certain customer site, they experienced a dramatic performance >slowdown >at a time when about 10 people were logged on concurrently. Each client >PC >has it's own FE. I know that they open the system and leave the process >screens open, all of which are bound. I want to suggest that they >remember >to close the process screens so that just the main screen is open, which >is >not bound. This way a fewer number of connections are being used at any >one >time and performance would probably be acceptable. This particular >customer, I believe, does not have a very good network, so that is part >of >the problem. But that's unlikely to get improved, so I wanted to >provide at >least a partial solution, hence my question. > >I've also heard that a connection is made not based on whether a form is >open and bound, but on whether or not there is data traffic between to >and >from a table, which only takes a small part of a second. > >So what causes the performance slowdown when only 10 client PC's are >logged >on, and what could be done to improve this? > >Dan Waters > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka >Sent: Friday, February 02, 2007 9:26 AM >To: Access Developers discussion and problem solving >Subject: Re: [AccessD] Connections and Performance > >Where are you getting these numbers. Access can have up to 255 >connections. > >Drew > >-----Original Message----- >From: Dan Waters [mailto:dwaters at usinternet.com] >Sent: Friday, February 02, 2007 8:45 AM >To: 'Access Developers discussion and problem solving' >Subject: Re: [AccessD] Connections and Performance > >Access has the potential for 8 simultaneous connections, but 3 of them >are >reserved for the system's use. That leaves five for users. > >Dan Waters > > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby >Sent: Friday, February 02, 2007 8:02 AM >To: 'Access Developers discussion and problem solving' >Subject: Re: [AccessD] Connections and Performance > > >But, the connection limit for one BE is 5 FE's. > >What does this mean? I have never heard of any such thing. > > >John W. Colby >Colby Consulting >www.ColbyConsulting.com > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters >Sent: Friday, February 02, 2007 8:58 AM >To: 'Access Developers discussion and problem solving' >Subject: [AccessD] Connections and Performance > >I've read several times that maintaining a connection between a FE and a >BE >will increase the performance of the FE because it doesn't need to >reconnect >before transferring data. The connection here would be a bound form >connected by a table link to a table in the BE. > >But, the connection limit for one BE is 5 FE's. So, will maintaining >connections on more than 5 FE's reduce performance? Seems logical, but >I >was wondering if this is correct or is there more to it? > >Thanks! > >Dan Waters > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com _________________________________________________________________ Laugh, share and connect with Windows Live Messenger http://clk.atdmt.com/MSN/go/msnnkwme0020000001msn/direct/01/?href=http:/ /ima gine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=hmtagli ne -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com