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

[Charles Parks]

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
>
>
>
>