If that's the hold up, I might try
-- There will be duplicates, but it is a straight run down the file.
-- R:Base does not have to take this into memory and sort it
INSERT INTO ttNewClients SELECT ClientID from vwBGProjectInfoTopLevel
-- from here on in, an index will help the speed
create index ttNewClients on ttNewClients (ClientID)
del fro ttNewClients where clientid in (select clientid from oldclients)
dele dup from ttNewClients
-- now you have your new client list, indexed, and can use an IN clause.
Albert
PS Leaving the office
Al
Charles Parks wrote:
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
