The problem with EXISTS() is that the behavior is vendor-dependent. The db
engine can:
- Get all the recordset and ensure count()>0.
- Get one record and exit immediately.
- Other nasty tricks.

So, I will ask in IB-Architect what's the implementation. Why do you think
that TOP_1 is expensive, Helen?

C.

> -----Original Message-----
> From: Helen Borrie [mailto:helebor@...]
> Sent: Martes 27 de Junio de 2000 12:06
> To: IBDI@egroups.com
> Subject: Re: [IBDI] Re: function TOP n FROM... was (Porting from...)
>
>
> At 02:05 PM 27-06-00 +0000, you wrote:
>
> >The reason why I asked about TOP N in the first place is that I use a
> >3rd party DLL that uses TOP 1 to check if a database table is empty.
> >
> >This vendor prefers to do a TOP 1 rather than COUNT(*) because some
> >databases do a full table scan to count the records. TOP 1
> ><i>hopefully</i> prevents this.
> >
> > -- John
>
> Oh, that is so expensive! What about IF EXISTS() ?
>
> Helen
>
>
>
>
>
> >--- In IBDI@egroups.com, Ann Harrison <harrison@n...> wrote:
> > >
> > > >
> > > >TOP isn't standard SQL and it's not a very sensible kind of thing
> >to ask of
> > > >a Client/Server database, given that the data isn't stored in any
> >physical
> > > >order. I'd be surprised if got anywhere near the top of anyone's
> >list of
> > > >"must-haves" for InterBase.
> > >
> > > There is one place where TOP<n> would be useful. Currently, an
> >order
> > > by clause causes InterBase to retrieve and sort the result set,
> >unless
> > > the order by terms match an index exactly and the query is simple
> > > (single table?). In that restricted case, InterBase retrieves the
> > > rows in index order. You probably think that index order would be
> > > faster than sorting, but you would be wrong, in general. The one
> >case
> > > where walking in index order is reliably faster is when you want
> >only
> > > the TOP<n> rows. So TOP could be a nice hint to the optimizer.
> > >
> > > Ann
> >
> >
> >------------------------------------------------------------------------
> >IT Professionals: Match your unique skills with the best IT projects at
> >http://click.egroups.com/1/3381/5/_/679568/_/962120683/
> >------------------------------------------------------------------------
> >
> >Community email addresses:
> > Post message: IBDI@onelist.com
> > Subscribe: IBDI-subscribe@onelist.com
> > Unsubscribe: IBDI-unsubscribe@onelist.com
> > List owner: IBDI-owner@onelist.com
> >
> >Shortcut URL to this page:
> > http://www.onelist.com/community/IBDI
>
> http://www.interbase2000.org
> ___________________________________________________
> "Ask not what your free, open-source database can do for you,
> but what you can do for your free, open-source database."
> (J.F.K.)
>
> ------------------------------------------------------------------------
> IT Professionals: Match your unique skills with the best IT projects at
> http://click.egroups.com/1/3381/5/_/679568/_/962129247/
> ------------------------------------------------------------------------
>
> Community email addresses:
> Post message: IBDI@onelist.com
> Subscribe: IBDI-subscribe@onelist.com
> Unsubscribe: IBDI-unsubscribe@onelist.com
> List owner: IBDI-owner@onelist.com
>
> Shortcut URL to this page:
> http://www.onelist.com/community/IBDI
>