DWUTKA at marlow.com
DWUTKA at marlow.com
Thu Mar 25 18:06:01 CST 2004
HUH? I've already listed many advantages to using a Lookup definition in a table's field. I'm not going to go over them again. Just read the last several posts. Next, saying there is a performance decrease, and SHOWING there is a performance decrease, are two totally separate things. I saw NO difference with 200,000 records. Over a million records? sure. 0*5....is....oh shoot, I used to know my math....oh ya ZERO! Now granted, if I saw no difference with 5 records, there could be a fudge factor in there. But I didn't see a difference with 200,000 records. NONE. Why wasn't there a difference? Simple, BECAUSE IT'S NOT A JOIN, IT'S NOT A LINK, IT'S A LOOKUP!!!! That means, that it is ONLY looking up the Rowsource (unless you use a callback, then it's The rowsource TYPE), when a particular field is entered. When you pull up a billion zillion records, it isn't running the lookup 'query' a billion zillion times!!!! It doesn't do that with a combo box, on a continuous form, does it? Nope, it doesn't do it with lookups either. Okay, you REALLY want to proof yourself wrong? Here's a challenge. Build a local Access table, and put a lookup field, that references your SQL server. Watch your SQL server transaction/processes. Do you see it pegging? No, because it doesn't query it for every record. It hits the lookup table to make sure it's there, and that's it! You can't PUT a Join into a Lookup field! (Yes, you can use a join in an SQL table, but you can't refer to a field in a particular record, within the Lookup field (ie, base a list of something off of a specific field within the table.).). Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Francisco H Tapia Sent: Thursday, March 25, 2004 5:00 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Lookup Fields in Table Design DWUTKA at marlow.com said the following on 3/25/2004 2:21 PM: >So what's your point? All that the Lookup field is doing, is providing easy >data entry from a datasheet view (which is a view of forms too), and a >reference for automatic/wizard stuff in Access. No effect at all. > > So where's the advantage of using the lookup? If anything if I'm customizing the lookup by form, I need to be in the datasource of the combobox anyways. >Show me a performance decrease. It's easy to SAY it decreases performance. > Joining a table adds a layer of complexity to all queries, simple joins make nearly no performance decrease, but when you begin joining tables over tables and you have a lookup associated to a table lets say... about 4 to 5 queries deep.. .well I would venture to say that it has a good possibility to add some performance issues. In a field that takes only 1-5 rows this may not be a problem but what about situations where you have litterally thousands of rows for the lookup... now you've inner joined 2 tables w/o needing to. (depending on your needs of course) a join between 200,000 may not be much, but what about situations where the total rows joined exceeds 1mill.? -- -Francisco -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com