Hi Set,

You mean to say, directly constant values cannot work in exists, the way can use
with IN predicate?


Tanmoy.





________________________________
From: Svein Erling Tysvær <svein.erling.tysvaer@...>
To: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Sent: Tue, 16 November, 2010 4:04:12 PM
Subject: RE: [firebird-support] Doubts about exists predicate


Well, Tanmoy,
if I were you I would put them in a table (read about global temporary tables in
the release notes), but you can of course try to split them into several parts
like:

Field3 IN (...) or Field3 IN (...)

I don't know whether it will work since I've never tried over 1500 values within
IN. If it works, I don't know how it would affect the optimizer (will it use the
index over 1500 times or go NATURAL?). A third alternative if you use a table,
is to use JOIN:

Select t1.Field1, t1.Field2 from Table1 t1
Join Table2 T2 on t1.Field3 = t2.Field3;

In some cases (if Table1 is large, Table2 small and the result is only a small
fraction of the records from Table1), using JOIN will be considerably faster
than the other options (provided Table1.Field3 is indexed).

Set

-----Original Message-----
Tanmoy wrote:

Hi SET,

I have these constant values not in a table but in front end, from there I am
gathering these constant values and would like to include in the sql, but at the

same time I do not want to post to the table, now since the constant values are
available, how to use them in the sql, that is my problem, coz these constants
may be greater than 1500.


So according to me could you pl give me an example.


Thank You.

Tanmoy
_______________________________
Set wrote:

Hi Tanmoy!

Put the values in a table (it might be a normal or a temporary table, you know
best which will be suitable), and then use the EXISTS with subselect that I
wrote below. EXISTS are normally quick, though, of course, Field3 must be
indexed in your (temporary) table.

HTH,
Set

-----Original Message-----
Tanmoy wrote:

Hi Set,

Thank you for reply.

I have more than 1500 constant values. Here IN not accept more than 1500, then
what is the best way to write SQL to get data in less time.

I need to write SQL which should not take much time to execute.

As a beginner, If possible Please help me.

Thanks once again.

Tanmoy.

________________________________
Set wrote:

IN is good when using constants, EXISTS when involving a subselect. I.e. I would


use


Select Field1, Field2 from Table1 where Field3 IN ('Value1', 'Value2',
'Value3');

whereas I would replace

Select t1.Field1, t1.Field2 from Table1 t1 where t1.Field3 IN (select t2.Field3
from Table2 T2);

with

Select t1.Field1, t1.Field2 from Table1 t1 where exists(select * from Table2 T2
where t1.Field3 = t2.Field3);

To be precise (normally it is not a practical issue, it is only relevant when
dealing with NULL), IN (<subselect>) can always be replaced by EXISTS, whereas
NOT IN (<subselect>) isn't always identical to NOT EXISTS.

HTH,
Set





[Non-text portions of this message have been removed]