Dave,

Are you silently ignoring me?

--
Fernando Dias
[Team Elevate]

> Are you silently ignoring me?

Sorry, don't know what happened to my last response (must be a problem with the backend database

QV:

Robert, Thanks for your suggestions, i'm testing them now.  On small datasets it seems to be a little slower, but the real test will be with
representative data.

Fernando, thanks for the good idea, but alas EXCEPT is not supported in v3.30.

Dave,

> Fernando, thanks for the good idea, but alas EXCEPT is not supported in v3.30.

Sorry. I missed that detail in your first post.
--
Fernando Dias
[Team Elevate]

Dave

> Recap:
>
> I want to insert into DestTable all records in SourceTable that do NOT
exist in CompareTable (while not adding any items that are already in
> DestTable)

I guess the speed of the query is going to vary according to the size of the
3 tables and the number of duplicates.  In addition to the other
suggestions, as long as sKey is indexed in all tables and is guaranteed
unique in SourceTable,  it might be worth trying

INSERT INTO DestTable (sKey,sField1,sField2,sField3)
SELECT sKey,sField1,sField2,sField3 FROM SourceTable S2
WHERE (sKey NOT IN DestTable) AND (sKey NOT IN CompareTable)



John

"Dave" <davey.crackpot@gmail.com> wrote in message
news:42F58EA3-C1D9-4581-9DB5-7EDC7624FACC@news.elevatesoft.com...
>
> Robert, Thanks for your suggestions, i'm testing them now.  On small
> datasets it seems to be a little slower, but the real test will be with
> representative data.
>

For Dave: don't forget to add an index to the memory table. Adding indexes
in DBISAM is so fast, especially with memory tables, that it should be a
nobrainer. When in doubt, add the index.

For Tim: say you hae the following (I understand there is not reason to use
a subquery, this is just an example)

SELECT * FROM SOURCE WHERE SOURCE ID IN (SELECT ID FROM FILTERTABLE)

VS

SELECT * FROM SOURCE
JOIN FILTERTABLE ON (SOURCE.ID = FILTERTABLE.ID)

am I correct that in case 1 the subquery gets processed for each row of
source, whereas the join gets ealuated all at eh same time,before you
extract the data?

Any definitive insight on this would be appreciated.

Robert

> Fernando, thanks for the good idea, but alas EXCEPT is not supported in
> v3.30.
>

Robert,

<< For Tim: say you hae the following (I understand there is not reason to
use a subquery, this is just an example)

SELECT * FROM SOURCE WHERE SOURCE ID IN (SELECT ID FROM FILTERTABLE)

VS

SELECT * FROM SOURCE
JOIN FILTERTABLE ON (SOURCE.ID = FILTERTABLE.ID)

am I correct that in case 1 the subquery gets processed for each row of
source, whereas the join gets ealuated all at eh same time,before you
extract the data? >>

Actually, they're both about the same in terms of processing.  The sub-query
only gets executed once for an IN, and then is simply searched for each row
in the source, before the rows are generated into the result set.  This is
very similar to what the join does.  Of course, I think that the join is
just easier to read and comprehend in terms of knowing that it is/isn't
optimized, but that's my personal taste.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:0D1A10CB-F7CD-46B1-902C-AE93134CBDE2@news.elevatesoft.com...
> Robert,
>
> << For Tim: say you hae the following (I understand there is not reason to
> use a subquery, this is just an example)
>
> SELECT * FROM SOURCE WHERE SOURCE ID IN (SELECT ID FROM FILTERTABLE)
>
> VS
>
> SELECT * FROM SOURCE
> JOIN FILTERTABLE ON (SOURCE.ID = FILTERTABLE.ID)
>
> am I correct that in case 1 the subquery gets processed for each row of
> source, whereas the join gets ealuated all at eh same time,before you
> extract the data? >>
>
> Actually, they're both about the same in terms of processing.  The
> sub-query only gets executed once for an IN, and then is simply searched
> for each row in the source, before the rows are generated into the result
> set.  This is very similar to what the join does.  Of course, I think that
> the join is just easier to read and comprehend in terms of knowing that it
> is/isn't optimized, but that's my personal taste.
>

Sorry to be a pest, but this is a situation that occurs often and I want to
make sure I get it right.

My impression was that assuming that both tables are indexed on ID, the JOIN
would be able to utilize those indexes, whereas the result of the subquery
would be searched serially.

Robert

> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Robert,

<< Sorry to be a pest, but this is a situation that occurs often and I want
to make sure I get it right. >>

No problem at all.

<< My impression was that assuming that both tables are indexed on ID, the
JOIN would be able to utilize those indexes, whereas the result of the
subquery would be searched serially. >>

No, the IN search will use an index if one is available.  Actually, the
sub-query can actually do something in DBISAM that the join can't - create a
temporary index to use for the search by specifying an ORDER BY for the
sub-query.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:7F9D65EF-6A92-486D-98CC-CC1AA818DF3C@news.elevatesoft.com...

Thanks, that makes it much more clear.

Robert
>
> << Sorry to be a pest, but this is a situation that occurs often and I
> want to make sure I get it right. >>
>
> No problem at all.
>
> << My impression was that assuming that both tables are indexed on ID, the
> JOIN would be able to utilize those indexes, whereas the result of the
> subquery would be searched serially. >>
>
> No, the IN search will use an index if one is available.  Actually, the
> sub-query can actually do something in DBISAM that the join can't - create
> a temporary index to use for the search by specifying an ORDER BY for the
> sub-query.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>