Hi Tim,

I have many SQL scripts like the following:

----------------
select P.ID, Release, Containers, FB.Company FreightBookingCo,
CY.ContainerYard, Count(T.ID) Packed
from PackInstDetRel PIDR
left outer join ShipCo FB on (FB.ID = PIDR.FreightBookingCo)
left outer join ContainerYard CY on (CY.ID = PIDR.ContainerYardID)
left outer join PackInstDet PID on (PID.ID = PIDR.ParentID)
left outer join PackInst P on (P.ID = PID.PackInstID)
left outer join Tickets T on (T.PackInstID = PID.ID)
where (0=0)
/*1*/  and (PID.ID = '123')
/*2*/
/*3*/and (PID.StartDate > '2001-01-05')
and ((T.Nett <> null) or (T.ID is null))
Group By P.ID, Release, Containers, FB.Company , CY.ContainerYard
order by P.ID, Release
----------------

The comments /*1*/, etc are used by a procedure I've made where I can change
the text after them depending on what requirements the user specifies, or
simply leave them blank if a clause for that particular line doesn't exist.

To have this work, I've needed the first line to already have a where
condition (so each line will work with an "AND" prefix). I chose to have the
condition 0=0, to return all records to start with.  (ie, if I remove line
/*1*/ altogether, clause 3 will still work, as there will always be a
previous clause requiring the next line to have an "AND" statement)

However, recently I've made the discovery that having (0=0) as the first
clause can cause some serious performance issues. The SQL above for instance
takes 5 seconds to generate as is, and only 0.3 seconds if I remover the
(0=0), and also remove the 'and' prefix from the first line.

My questions is: Is there a clause I can have in SQL, which will allow me to
have other lines following with an 'AND' clause that will not cause any
performance loss? (I thought 0=0 might do this, but apparently not)

Thanks & Regards

Adam.

Adm

I had already with the same problem but I have changed 1=1 to True

where (1=1)
to
where (True)

It solve my problems

Eduardo

Adam,

<< My questions is: Is there a clause I can have in SQL, which will allow me
to have other lines following with an 'AND' clause that will not cause any
performance loss? (I thought 0=0 might do this, but apparently not) >>

The issue is that DBISAM still has to evaluate the actual rows to remove
deleted rows from the filter bitmap when you have a True constant expression
like.  If you use a False constant expression it doesn't.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Jose and Tim,

Fantastic! Thanks - that was just what I was looking for!  

Cheers

Adam.