[RBASE-L] - Re: Selecting from a large table

[Albert Berry]

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: rbase-l@rbase.com [mailto:rbas...@rbase.com] 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