[ Date Index ][
Thread Index ]
[ <= Previous by date /
thread ]
[ Next by date /
thread => ]
On Wednesday 14 July 2004 08:48, Robin Cornelius wrote:
You need an outer join. Eg. SELECT pinksheet.requirment,contact.phone,contact.fax FROM contact LEFT JOIN pinksheet ON contact.PKey = pinksheet.contactidThat works perfectly, thanks! It seems to require a lot of horsepower
Uh-oh...
however but this should improve when I add some more where clauses in to restrict the data set a bit.
I don't suppose it will... This is all guesswork, but guesswork informed by years of making stuff work... Before you force an outer join, the SQL logic probably goes a bit like this : For each PINKSLIP that exists, fetch the CONTACT identified by PINKSLIP.CONTACT_ID. CONTACT_ID is unique in CONTACT, and is (probably) indexed, so there's not much overhead. After the outer join it goes : For each CONTACT, find all records in PINKSLIP where PINKSLIP.CONTACT_ID = CONTACT_ID. Now, PINKSLIP.CONTACT_ID isn't the primary key of PINKSLIP, so, even if it's indexed* (and you'd be amazed how many DBA's don't index stuff 'til you hit them with a clue bat) you need to scan the PINKSLIP.CONTACT_ID key to find the primary keys of the PINKSLIPs you need, and then fetch those. Even if you're only interested in a subst of the PINKSLIPs, you'll probably have to go through at least that logic to identify them. The actual data transfer tends to become insignificant. jd *Of course, if PINKSLIP.CONTACT_ID isn't indexed, then the outer join logic will have to scan the whole PINKSLIP table for each CONTACT, and that *will* use some horsepower... -- John Daragon argv[0] limited john@xxxxxxxxxx Lambs Lawn Cottage, Staple Fitzpaine, Taunton TA3 5SL, UK (house) 01460 234537 (office) 01460 234068 (mobile) 07836 576127 (fax) 01460 234069 -- The Mailing List for the Devon & Cornwall LUG Mail majordomo@xxxxxxxxxxxx with "unsubscribe list" in the message body to unsubscribe.