Posted to binaries:

http://www.elevatesoft.com/forums?action=view&category=dbisam&id=dbisam_binaries&msg=1775#1775

<< Posted to binaries: >>

DBISAM 3.x isn't optimizing the IN sub-select operator, so you'll need to
use this instead:

SELECT SendStatus FROM Send_Task INNER JOIN "Memory" ON
UPPER(Send_task.Email)  = UPPER("Memory".Email)
WHERE SendStatus=5

It's equivalent to what you're doing now, and will be optimized.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim, That does work well.


"DBISAM 3.x isn't optimizing the IN sub-select operator"

Can you please clarify your comment so I can check for similar issues.

When does an IN operator not use an index?  Is it only when we are reading from a second table, e.g.

UPDATE "SomeTable.dat" M2 set Status=6  WHERE UPPER(M2.Email) IN (SELECT UPPER(Email) FROM "History.dat")


What about explicit IN clauses such as:

UPDATE "SomeTable.dat" M2 set Status=6  WHERE M2.HistoryCode IN (1999,2000,2001,2002,2003,2007,2008,2009)

I assume they are OK (they certainly seem to be fast).

Thanks

<< Can you please clarify your comment so I can check for similar issues.

When does an IN operator not use an index?  Is it only when we are reading
from a second table, e.g. >>

Yes, sorry about that - it only applies to situations where you're using the
IN operator with a sub-query.

<< What about explicit IN clauses such as:

UPDATE "SomeTable.dat" M2 set Status=6  WHERE M2.HistoryCode IN
(1999,2000,2001,2002,2003,2007,2008,2009)

I assume they are OK (they certainly seem to be fast). >>

Yes, those can be optimized with an index in DBISAM 3.x.

--
Tim Young
Elevate Software
www.elevatesoft.com

OK, so this is fixed in v4 then?

Two links i can't find in your site map:

1. Upgrade page for v3 to v4
2. Change list for v4

Thanks

> 1. Upgrade page for v3 to v4

http://www.elevatesoft.com/products?action=order&category=dbisam&type=delphibcb

If you login you will be able to choose the product you need
as well as the adjusted prices for upgrades from version 3 to 4.


> 2. Change list for v4

http://www.elevatesoft.com/manual?action=topics&id=dbisam4&product=r&version=2007&section=before_you_begin


Fernando Dias
[Team Elevate]

Thanks Fernando, but I didn't see anything about optimization of IN sub-queries.  Is that addressed in v4 or does it require ElevateDB?

Xazzy

> Thanks Fernando, but I didn't see anything about optimization of IN
sub-queries.  Is that addressed in v4 or does it require ElevateDB?

I just ran the query against your data in v4 and on a very average PC and it
took just over 4 seconds.

John

<< Thanks Fernando, but I didn't see anything about optimization of IN
sub-queries.  Is that addressed in v4 or does it require ElevateDB? >>

Both DBISAM and ElevateDB can execute this query much faster, with ElevateDB
being the fasteest.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks John