Jim Dettman
jimdettman at verizon.net
Fri Feb 20 10:18:12 CST 2009
John, <<The bottom line is that to a large extent we all do what is comfortable to us as individual programmers and to heck with ....>> So if your not comfortable with it then just ignore it? Humm. <<Well... Hmmm... your way caches the entire recordset and all its associated junk - indexes etc. My way caches exactly and only the strings for one specific language.>> No, that is not correct. A seek on an open table won't cache the entire recordset in of itself. What might be sitting in JET's cache is a few buffers worth of index pages and a couple of buffers worth of data pages, but I'd highly doubt you'd have the entire thing unless you just went through every single record or used them a lot in comparison to everything else (JET's cache is LRU based, so the pages could add up in the cache over time). But even if nothing is in the cache, a seek is based on an index and since JET indexes are B-tree based, you'd have your record within 2-3 disk hits at most, especially on a 2500 record table. Since a language table would be local to the FE, this would not be a pull over the wire and it would be quite fast. It's quite possible to that the disk drive would still have the data in its own cache, so you might not even end up doing a hard hit to the disk at all. But if the data does happen to be in the JET cache, then I doubt there would be much of a difference between the two methods. I think the class approach would still be a tad faster, but not by much. And let me be clear; it's not the class approach that I don't agree with but the point that as an application developer do I cache things in memory on my own or not? It is when considering that point that I would not choose to do an array based approach either, which would be really simple to write as I could use GetRows() to dump the set into an array. Since the array would already be sorted, I could even do a B-tree search on it rather then a sequential one. That might even beat out a class (hard to say with VBA) in terms of speed. But with the seek approach, you would not be tying up a chunk of memory to be used for only one purpose. From my viewpoint the seek approach is the best of both worlds, stuff gets cached if it is used a lot and if it is not, then it is not cached. In that case, the memory gets used for something productive rather then sitting there. And I would be really surprised if performance wasn't acceptable. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Friday, February 20, 2009 9:44 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Find First in an Array? The bottom line is that to a large extent we all do what is comfortable to us as individual programmers and to heck with .... > You mentioned that JET always goes across the wire and that's not true. If a page is in the local JET cache, then it won't. > But if every programmer were to approach application design with the "load it into memory" approach because it's fast, then very quickly you can find that the OS will start paging out to disk. So your really not in memory any more. Well... Hmmm... your way caches the entire recordset and all its associated junk - indexes etc. My way caches exactly and only the strings for one specific language. However your way (done on a form by form basis on demand) sounds like it could be flushing the jet cache as other processes run so that it may very well have to hit the disk again for the next form to load. At best it kind of sounds like "six of one, half a dozen of the other". > If I have an application that is a couple of hundred forms and user Jim D uses only one form, then that's a big waste. True, but if you cache the data as the form loads the first time then that goes away. I was proposing exactly that. Now you have the best of both worlds, you don't use time and resources until a form is actually loaded, but once it is loaded it can be loaded twice or a million times and always be fast. You should know from my JIT subforms I am all about doing stuff as / when needed. Notice that I am not caching constantly changing data, nor data that doesn't change but is rarely used, only data that rarely changes and is used constantly in the program. That "rarely changes / constantly used" is not a rare occasion in complex systems. To go to the disk dozens or hundreds or thousands of times a day to get the same data over and over IMHO is just silly, when I can just cache it and be done. It is even sillier when caching it is a trivial programming exercise. It is sillier yet when caching the data significantly speeds up the program. I learned a long time ago to organize my program into classes, each class performs a systemic task. I learned that when a set of data is in constant use in the program (and form translation fits that bill) then I would build a class system to cache it and the program always works faster. The "cache class system" looks very similar from data set to data set. It doesn't take me long to set it up because I have done it so often. So that is what I do. I do understand that it is only fast / efficient for me because I use classes all day every day, but so can any Access programmer as my lectures are intended to show. John W. Colby www.ColbyConsulting.com Jim Dettman wrote: > John, > > I posted a couple of comments yesterday to you and Drew, which still > haven't shown up on the list, but I'll answer the question you just asked > with "It depends on how big the collection is". > > What I wrote yesterday is that I would use a global recordset variable, > thus avoiding opening/closing the recordset repeatedly and I'd use seek. > > You mentioned that JET always goes across the wire and that's not true. > If a page is in the local JET cache, then it won't. > > But here that is really not an issue because a language translation table > is certainly going to be part of the front end, so the table will be local. > It's not going to change unless the app changes. > > Rocky has been talking about pulling 2500 records; if all that is pulled > into memory, that is a fair sized chunk. I'd much rather let the system use > that memory as it sees fit rather then tying it up with one specific task. > If I have an application that is a couple of hundred forms and user Jim D > uses only one form, then that's a big waste. Of course you could mitigate > that somewhat by only loading the translation when the form loads and for > the specific language as you mentioned. > > But if every programmer were to approach application design with the "load > it into memory" approach because it's fast, then very quickly you can find > that the OS will start paging out to disk. So your really not in memory any > more. > > That's why I think pulling something into memory like this is a waste. > > FWIW, > Jim. > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Thursday, February 19, 2009 8:25 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Find First in an Array? > > I was trying so hard to keep quiet. > > ;-) > > And how could seek over and over and over and over be any faster than > sucking the results out of a > collection? > > OK, I'll go back to being quiet. > > John W. Colby > www.ColbyConsulting.com > > > Drew Wutka wrote: >> But how could seek be any faster then simply running through the >> recordset? >> >> Drew >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Salakhetdinov >> Shamil >> Sent: Thursday, February 19, 2009 3:40 PM >> To: Access Developers discussion and problem solving >> Subject: Re: [AccessD] Find First in an Array? >> >> You decide, Rocky, >> >> Do you have time for that experimenting? >> >> May I warn you about what is known as: >> >> "Premature Optimization" >> http://c2.com/cgi/wiki?PrematureOptimization >> >> and >> >> "Premature Generalization" >> http://c2.com/cgi/wiki?PrematureGeneralizationIsEvil >> >> ? >> >> Have a look: >> >> "Death by premature generalization" >> http://ryanfarley.com/blog/archive/2004/04/30/570.aspx >> >> >> My name is Shamil, you know :) and I have been a premature generalizer >> in many cases I must admit - but I'm getting more and more XP/SCRUM >> agile habits these days, and I'm getting more fun from programming real >> life business tasks without my premature generalization and optimization >> not so good(?) past habits... >> >> IMO in the case of the context of this thread using .Seek is the closest >> and quickest yet good enough (and maybe the only one needed for many >> years ahead) solution/transition from your existing .FindFirst-based >> code... >> >> If that would not be good/speedy enough in your case then I'd probaably >> use static array loaded on first call/after reset using .GetRows() with >> accompanying static collection with FormName/ControlName as a key to >> keep value for static array entry index, and a function to get >> translation value using this collection/array... >> >> etc... >> >> Thank you. >> >> -- >> Shamil >> >> -----Original Message----- >> From: "Rocky Smolin at Beach Access Software" <rockysmolin at bchacc.com> >> To: "'Access Developers discussion and problem >> solving'"<accessd at databaseadvisors.com> >> Date: Thu, 19 Feb 2009 09:56:55 -0800 >> Subject: Re: [AccessD] Find First in an Array? >> >>> Dang. I think you're right. Gotta try that. You think it'll be >> faster >>> than SEEK? >>> >>> >>> Rocky Smolin >>> Beach Access Software >>> 858-259-4334 >>> www.e-z-mrp.com >>> www.bchacc.com >>> >>> >>> >>> -----Original Message----- >>> From: accessd-bounces at databaseadvisors.com >>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka >>> Sent: Thursday, February 19, 2009 9:33 AM >>> To: Access Developers discussion and problem solving >>> Subject: Re: [AccessD] Find First in an Array? >>> >>> Hmmmm, actually, the way you have this set up, you could do this just >> fine >>> without an array or collection. >>> >>> Instead of this: >>> >>> For Each ctl In frm >>> rstControls.FindFirst "fldLanguageForm = '" & argForm & "' and >>> fldLanguageControl = '" _ >>> & ctl.Name & "'" >>> >>> Do this: >>> >>> strSQL="SELECT fldLanguageControl, fldLanguage" & >> strLanguageToTranslate & " >>> FROM tblYourTableName WHERE fldLanguageForm=""" & me.Name & """" >>> set rs=New Recordset >>> rs.open strsql,currentproject.connection, adopenkeyset,adlockreadonly >> if >>> rs.eof=false then rs.movefirst do until rs.eof=true >>> me(rs.fields(0).value).Caption=rs.fields(1).value >>> Loop >>> Rs.close >>> Set rs=nothing >>> >>> That way you are pulling the recordset up....and just running through >> it >>> once...instead of trying to find every record based on the control. >>> >>> Drew >>> >>> -----Original Message----- >>> From: accessd-bounces at databaseadvisors.com >>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky >> Smolin at >>> Beach Access Software >>> Sent: Thursday, February 19, 2009 12:57 AM >>> To: 'Access Developers discussion and problem solving' >>> Subject: Re: [AccessD] Find First in an Array? >>> >>> John: >>> >>> The table layout is: >>> >>> fldLanguageID Autonumber >>> fldLanguageForm Text Name of the form that the control is on >>> fldLanguageControl Text Name of the control >>> fldLanguageControlType Text Not Used >>> fldLanguageEnglish Text >>> fldLanguageChineseComplex Text >>> fldLanguageChineseSimple Text >>> fldLanguageSpanish Text >>> fldLanguageFrench Text >>> >>> In the translate routine I use: >>> >>> Set frm = Forms(argForm) >>> For Each ctl In frm >>> rstControls.FindFirst "fldLanguageForm = '" & argForm & "' and >>> fldLanguageControl = '" _ >>> & ctl.Name & "'" >>> >>> Where argForm is the form name passed to the function. So I think I >> might >>> get a big boost in response time by indexing fldLanguageForm and >>> fldLanguageControl. >>> >>> There are only about 2500 records in the table so I'm thinking that >> the >>> whole recordset is probably in memory anyway. >>> >>> >>> Rocky Smolin >>> Beach Access Software >>> 858-259-4334 >>> www.e-z-mrp.com >>> www.bchacc.com >>> >>> >>> >>> -----Original Message----- >>> From: accessd-bounces at databaseadvisors.com >>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby >>> Sent: Wednesday, February 18, 2009 5:46 PM >>> To: Access Developers discussion and problem solving >>> Subject: Re: [AccessD] Find First in an Array? >>> >>> Does this imply a table structure of: >>> >>> TR_ID Auto >>> TR_CtlName Text >>> TR_L1 text (or memo?) for language 1 >>> TR_L2 text (or memo?) for language 2 >>> Etc >>> >>> I would suggest that you add a form (or container) field. A control >> name is >>> unique on a form, however it could be the same on different forms but >> have a >>> different language string. By having a form field you could pull >> subsets of >>> records based on the form name, then use the control name (now >> guaranteed to >>> be unique), then obtain the language string. >>> >>> It appears from your email however that you already have this stuff >> set up, >>> so you might be resistant to modifying how it works. >>> >>> If this is all just one big table with control names guaranteed to be >> unique >>> then you could just stash it in one big collection. There is a >> problem >>> however which is that classes get pretty slow as they get large. If >> you get >>> up past 10,000 strings (I don't know the exact point) it would bog >> back >>> down. If you broke it down into controls on a form, then you would >> ensure >>> that the total strings in any given collection is pretty small. >>> >>> Again I would build a class for all the code and the collection to >> load one >>> form. then a supervisor class to hold instances of this form class >> keyed on >>> form name. >>> >>> John W. Colby >>> www.ColbyConsulting.com >>> >>> >>> Rocky Smolin at Beach Access Software wrote: >>>> Max: >>>> >>>> That would work except the table driven approach is so much more >>> easier. >>>> Easy to add a language (got French and Spanish now in addition to >>>> traditional and simplified Chinese), too, or make a change to a >>> translation. >>>> To add a language I just add a column for that language to the >> Control >>>> and Messages tables and send them to the translator. Add that >>>> language to the language selector combo on the Preferences form, and >>>> walla! Another language. >>>> >>>> >>>> Rocky Smolin >>>> Beach Access Software >>>> 858-259-4334 >>>> www.e-z-mrp.com >>>> www.bchacc.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 The information contained in >> this >>> transmission is intended only for the person or entity to which it is >>> addressed and may contain II-VI Proprietary and/or II-VI Business >> Sensitive >>> material. If you are not the intended recipient, please contact the >> sender >>> immediately and destroy the material in its entirety, whether >> electronic or >>> hard copy. You are notified that any review, retransmission, copying, >>> disclosure, dissemination, or other use of, or taking of any action in >>> reliance upon this information by persons or entities other than the >>> intended recipient is prohibited. >>> >>> >>> -- >>> 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