[AccessD] Lookup Fields in Table Design

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