Kevin O'Shea wrote: > VFP 9 SP1... > > A colleague is looking for a way to optimize a query... a little background > - we are looking to allow users to define what records they want to see in > our lookup lists. > > The approach taken so far is to present the admin user with a list of all > the lookup records. The admin user than removes all the records they don't > want to see. We store these records that were selected NOT to see in a > table. The thinking here is that this will be less fields to store - ones > they don't want to see as opposed to ones they do want to see. > > When building the lookup lists, we looked at using a > SELECT FROM maintable WHERE field1+field2+field3 NOT IN (SELECT > field1+field2+field3 FROM filterlisttable) > > This SELECT is taking from 3 to 5 seconds (3 seconds with an index, 5 > without) on a few thousand records - not ideal. > > We looked at JOINS but there doesn't seem to be one that handles only > returning the records in my left table that aren't in my right table. > > Any thoughts on an approach to build this SELECT statement to make it > faster? Unfortunately at this time, we are also forced with the > concatenating of these fields - there is no unique field to pull from. >
I'd use the 2nd table as what the person selected...even though it might be more records. I'd use the PK (or combo key) stored as a FK in the 2nd table, then SELECT from the 2nd table, marrying in via JOIN to the first table to grab the relevant info. It may mean storing more records, but it's avoiding the NOT IN scenario, which may be worth it performance-wise! hth, --Michael _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
