DWUTKA at marlow.com
DWUTKA at marlow.com
Fri Mar 26 16:13:34 CST 2004
ROTFLMAOTHMHOTDIUD (That's 'rolling on the floor laughing my a$$ off, then hitting my head on the desk in utter dispair'). Okay, let's see, you have just proven to yourself that there is no performance degradation...what you had before was a fluke. Yes, you and Charlotte are correct that some indexing is added to speed up the lookup process, which does NOT affect querrying the actual data in the main table. (Going to get to why in a minute) It's not a complex and undocumented mechanism. It's actually VERY simple, and it is in the help files (just looked). It's a property of the field in the tabledefs. (Look up DisplayControl Property in the help files). That defines what the DEFAULT CONTROL is for the table. By default, it's 109, which is a textbox. However, change that to a Listbox or Combobox, and there are deeper properties which tell it how to build the 'default control', when you go to build a form. Nothing mysterious so far. It also explains why setting the limit to list property DOESN'T affect data entry of that field when you use code, or you use something OTHER then the default control (or either change the type, or change the properties of that control on a form.). It doesn't affect that, BECAUSE it's a property that is only looked at by the Wizards, NOT by Jet, when retrieving the data. Now, when it builds the indexes, JET does look at it, to determine if it needs to add indexes to help the listbox. This is a guess, but I would be willing to bet, that what it is adding, is a quick index reference to show what the 'displayed' data for that field is. Which is no more indexing, then if you just went and created the relationship in the first place! Let's drill a bit deeper, cause I'm on a roll. All actual data operations are completely ignorant of the 'default' control. It's used by the wizards, and it's also used by the datasheet view, but ONLY for looking at the data. You're premise that it degrades performance is off, because you are assuming that since it runs a query, that for some VERY weird reason, that it would run that query for every record, instead of running it whenever a field is entered into. Do combo boxes on a continuous form run their rowsource for every record displayed on their form? NO, in fact, that can be a problem with continuous forms, if you need to have a different source based on a value within that record. (Which was a thread from not too long ago). This of course doesn't mean that continuous forms are useless, what it does mean, is that if you look at the behavior and results between a table's datasheet view, and a form's continuous form view, you'll see that they act the same way. You are only getting the 'lookup' properties when the data is DISPLAYED (and in combo/listbox, they are only run when that field is ENTERED). Why? Another simple answer. Because even though a table is just data, the Datasheet view of a table is a little bit MORE then just the raw data, in fact, it's a simple BOUND form. The Caption field displays on the first row. Does the Datasheet view 'reference' the Caption property for every record it displays? No, of course not, it looks at the table definition, get's the properties, and creates the matrix necessary to display the data. SAME THING with the DefaultControl property. When it builds the matrix, just like with a continuous form, it creates a column of comboboxes, which all share the same source. Not only the same source, but the EXACT same results. And now a little deeper. Is the only option for a lookup field a combo or listbox? Nope, in fact, Textbox is an option. It's the default, so we are just calling a field a Lookup field if it has a combo or a listbox set. But in reality, it's a property of ALL fields, whether you use them or not. But you can also have a checkbox as the default. It of course is the default for a yes/no field. But if you want the Yes/No field to display as 'Yes', 'No', instead of a box with a check in it, then you go to the Lookup tab, and switch the default control to TEXTBOX. You can ALSO select ComboBox for Yes/No fields. (Male/Female, Child/Adult, Moron/Genius). I wouldn't be at all surprised, if you could fanangle a toggle switch in there. LOL. So we aren't looking at some 'mysterious' mechanism, that no one but the almighty Microsoft knows about. We are talking about easily accessible FIELD PROPERTIES! In reality, you aren't avoiding their use, you use them whether you like it or not, you just aren't changing their value. As for your 'Data Modeling books' comment, you should really ask how many I have read, and then ask how many complaints I've had on performance of my databases. The answer would be zip and (almost) zip (not perfect! LOL). I am not hacking the books, they put out good information. The 'database' community is made up of a lot of smart people. But this is just one of those things where the mob mentality took something to heart, which was done over preference, NOT fact. Took an MIS course at a local university, and missed a question on a test, when it asked what a relational database developer calls a record. It was multiple choice and it didn't have 'record' as an answer. Because, AS a relational database developer, I call my records, records If you really want to have your educational foundation shaken a bit (to realize that not everything written down is law), find someone taking courses like that (like MIS). Now, is it me, or is there something wrong, when I get tested to know that a File is a table, a tuple is a record, but when I went to do my 'ACCESS' homework, my spine shivered, because they required that the field names, and the db object names have SPACES in them. That they didn't normalize their data, and they absolutely went the long way around doing things? Does that make any sense? Unfortunately it does. Understanding how something works, is completely different from memorizing definitions, and/or instructions. It is far more difficult to teach concepts, and actual understanding, then it is to give out definitions and step by step instructions. As for your reductionist view, wouldn't that make you an unbounder? LOL. Sorry, had to add that. I don't go in and add things willy nilly. I do set the values (of properties that are there whether you want them or not), to a value that I find accomodating for my needs. Same with my forms. If I look at what I need the form to do, and say, well, that certainly fits within the bound form guides, I use a bound form. If I say, 'Up, nope, can't do that with a bound form', then I go with an unbound form. Boy, I'm really stoking the fire right now. Finally, how can you say there's uncertainty? It's a property! It's not smoke and mirrors! Arg...... Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Ken Ismert Sent: Friday, March 26, 2004 2:37 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Lookup Fields in Table Design Drew, Charlotte, all: To be entirely fair, I looked again at my test db, and found that a form based on the reference (non-lookup) table, using a lookup combo, produced roughly the same results as the form based on the table with lookup fields defined. So, I think we are laboring under different ideas of performance. Charlotte and I are correct in the sense that you carry the table lookup baggage in every query and form based on that table, whether you want it or not, unless you take steps to turn it off. This, I think, complicates the straightforward construction of queries, forms and reports. Drew is correct that there exists in Access a very complex and undocumented mechanism that will turn Off and On the table lookup feature, depending on its deduced context, which produces basically the same timed results in casual tests. This mechanism goes so far as to extend its reach from back to frontend. AFAIK, it is unique to Access. I think I have clearly demonstrated cases, both evidential and anecdotal, that show that this mechanism is not entirely trustworthy. I guess I am a reductionist in this regard. Take something, like a table, and strip it to its bare essentials. After all, parts you don't include will never fail or need repair. Then, where appropriate, add those parts in their proper context. Contrast this to the constructionist approach, which would add everything you could possibly need or want to the table, then turn around and strip out those parts where you don't need them, all the while trusting that this truly black box mechanism will magically provide you with the best performance. If you've done EVERYTHING correctly, you will probably get away with table lookup fields. This includes: using small, indexed lookup tables that don't grow; and appropriate indexes on all main tables. But without question, you've introduced needless fragility into your table design. Later on, if you change an index while tuning your table structures, you may find that the performance of some unrelated queries goes to crap, and I bet that the table lookup won't be the first thing that leaps to mind when trying to find the cause. Tell me, who in the general database community thinks this is a good idea? Please, show me the data modeling book that has a step that says "embed client-side UI elements in the table design". Looking beyond the uncertainty that table lookup fields introduce to the reliability of the backend, I honestly can't see how you are going to get ahead in the long run with it. It fills no functional niche that isn't covered by more standard, broadly accepted techniques. Finally, it is the little hassles that this feature consistently gives that have caused me to largely abandon it. -Ken -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com