It seems to be running at about the same speed. This "INSERT INTO ttNewClients SELECT DISTINCT ClientID FROM vwBGProjectInfoTopLevel" takes a minute to execute.
-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Albert Berry Posted At: Wednesday, January 06, 2010 3:33 PM Posted To: RB7-L Conversation: [RBASE-L] - Re: Selecting from a large table Subject: [RBASE-L] - Re: Selecting from a large table Charles, Would this work well like this? It turns the NOT IN to an IN. Try it with and without the index on ttNewClients and pick whichever is faster. CREATE TEMP TABLE ttNewClients (ClientID INTEGER) INSERT INTO ttNewClients + SELECT DISTINCT ClientID FROM vwBGProjectInfoTopLevel DELETE FROM ttNewClients + WHERE ClientID IN + (SELECT ClientID FROM OldClients) SELECT COUNT(*) INTO vCount + FROM ttNewClients + WHERE LIMIT = 1 IF vCOUNT > 0 THEN CREATE INDEX ttNewClients ON ttNewClients (ClientID) INSERT INTO ttProjectInfo + SELECT * FROM vwBGProjectInfoTopLevel WHERE &fNewST AND ClientID IN (SELECT ClientID FROM ttNewClients) + AND Principal <> 'MAH' + AND (INT(ProjNum)) > 9000 ENDIF Charles Parks wrote: > The first time is to see if there are values. > > If there are values then I do an insert. > INSERT INTO ttProjectInfo + > SELECT * FROM vwBGProjectInfoTopLevel + > WHERE &fNewST + > AND ClientID NOT IN (SELECT ClientID FROM OldClients) + > AND Principal <> 'MAH' + > AND (INT(ProjNum)) > 9000 > > > I think, the problem lays with vwBGProjectInfoTopLevel. > > I tried this: > select count (*) + > into vCount + > indicator viCount + > FROM sys_tables + > WHERE sys_table_name = 'tvCurrentClients' > > if vCount > 0 then > drop view tvCurrentClients > endif > > create temp view tvCurrentClients (ClientID) + > as select distinct(ClientID) + > from vwBGProjectInfoTopLevel > > select count (*) + > into vCount + > indicator viCount + > FROM sys_tables + > WHERE sys_table_name = 'ttCurrentClients' > > if vCount > 0 then > drop table ttCurrentClients > endif > > project temp ttCurrentClients from tvCurrentClients using * > > bro * from ttCurrentClients > > It takes a minute for the "project temp" to complete. The Browse happens > right away. There are 118 Clients. > > vwBGProjectInfoTopLevel is ODBC'ed into the database. > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Albert Berry > Posted At: Wednesday, January 06, 2010 3:04 PM > Posted To: RB7-L > Conversation: [RBASE-L] - Re: Selecting from a large table > Subject: [RBASE-L] - Re: Selecting from a large table > > > Charles, are you looking for a count, or to determine that there are/is > matching value(s). If it is to know if there are values, add 'AND LIMIT = 1' > to the select count, and it will stop looking as soon as it has found a value. > Albert > > Charles Parks wrote: > >> I think, I heard somewhere that "in" is not good for speedy selects. >> >> Is there something though that is better to use for "not in"? >> >> The following select statement seems to take awhile to do a count. >> >> SELECT COUNT (*) + >> INTO vCount + >> INDICATOR viCount + >> FROM vwBGProjectInfoTopLevel + >> WHERE &fNewST + >> AND ClientID NOT IN (SELECT ClientID FROM OldClients) + AND Principal >> <> 'MAH' + AND (INT(ProjNum)) > 9000 >> >> >> >> >> > > > >
