On Wed, Apr 23, 2008 at 9:25 AM, Kevin O'Shea <[EMAIL PROTECTED]> 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. > ------------------------------------- are the keys the same between the tables? SELECT FROM maintable WHERE pKey NOT IN ( SELECT pKey FROM filterlisttable ) -- Stephen Russell Sr. Production Systems Programmer Mimeo.com Memphis TN 901.246-0159 --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ 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.
