I'm sure others will come up with good indexing suggestions. I don't
have time to dive into that right now. Sorry. But what I usually do is
to create multiple different one-column indexes to see if the query
optimizer chooses to use any of them and if so, does it improve
performance? If yes, then keep that index (or those indexes) and drop
the others.

Kjell

dinol.softedge wrote:

> Hi Kjell
>
> Yes that is twice as fast. It still takes 3 seconds to execute though
> for only 2500 records. It has 3.8 million non-indexed reads to the
> tbl_job_card_employee table even though I have indexed all linked
> fields on tbl_job_card_employee. Any other ideas on how to further
> increase the speed
>
> Thanks very much for your help so far
>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, Kjell Rilbe <kjell.rilbe@...>
> wrote:
> >
> > In general I've found joins to be faster than subqueries with count or
> > exists. I guess joins are easier to optimize than the other kinds. (?)
> >
> >
> > In theory, an exists subquery should be faster than a count subquery,
> > because it can stop when the first subrecord is found. With count, it
> > has to count them all.
> >
> > Anyway, doesn't this work:
> >
> > SELECT
> > [snip irrelevant part]
> > case when exists (
> > SELECT 1
> > FROM TBL_JOB_CARD_EMPLOYEE
> > WHERE TBL_JOB_CARD.JOB_CARD_ID
> > = TBL_JOB_CARD_EMPLOYEE.JOB_CARD_ID
> > ) then 'Yes' else 'No' end AS HAS_EMPLOYEES,
> > TBL_JOB_CARD.AMOUNT_DUE,
> > TBL_JOB_CARD.INVOICE_PRICE_INCL,
> > TBL_JOB_CARD.VAT
> > FROM TBL_JOB_CARD
> > LEFT OUTER JOIN TBL_CLIENT
> > ON (TBL_JOB_CARD.CLIENT_ID
> > = TBL_CLIENT.CLIENT_ID)
> > LEFT OUTER JOIN TBL_INSURER
> > ON (TBL_JOB_CARD.INSURER_ID
> > = TBL_INSURER.INSURER_ID)
> > ORDER BY JOB_CARD_ID DESC
> >
> > Kjell
> >
> > dinol.softedge wrote:
> >
> > >
> > >
> > > Hi
> > >
> > > Sorry for not being clearer. I don't need to count the employees,
> > > just find out if there is at least one per record in the tbl_job_card
> > > table
> > >
> > > --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>
> > > <mailto:firebird-support%40yahoogroups.com>, Kjell Rilbe
> <kjell.rilbe@>
> > > wrote:
> > > >
> > > > As far as I can see, you're trying to actually select an
> employee count
> > > > by using a join with a 1-M realtionship. How could that be returned
> > > with
> > > > an exists query?
> > > >
> > > > Either you're not telling us what you actually want, or I'm missing
> > > > something.
> > > >
> > > > Kjell
> > > >
> > > > dinol.softedge wrote:
> > > > >
> > > > >
> > > > > Hi
> > > > >
> > > > > I am trying to optimize the following count query by changing
> it to an
> > > > > exists query. I have tried various exists statements but I cannot
> > > > > seem to get it right. Any help would be greatly appreciated
> > > > >
> > > > > The count subquery is near the bottom
> > > > >
> > > > > SELECT
> > > > [snip irrelevant part]
> > > > > (SELECT COUNT(EMPLOYEE_ID) FROM TBL_JOB_CARD_EMPLOYEE WHERE
> > > > > TBL_JOB_CARD.JOB_CARD_ID = TBL_JOB_CARD_EMPLOYEE.JOB_CARD_ID) AS
> > > > > EMPLOYEE_COUNT,
> > > > > TBL_JOB_CARD.AMOUNT_DUE,
> > > > > TBL_JOB_CARD.INVOICE_PRICE_INCL,
> > > > > TBL_JOB_CARD.VAT
> > > > > FROM
> > > > > TBL_JOB_CARD
> > > > > LEFT OUTER JOIN TBL_CLIENT ON (TBL_JOB_CARD.CLIENT_ID =
> > > > > TBL_CLIENT.CLIENT_ID)
> > > > > LEFT OUTER JOIN TBL_INSURER ON (TBL_JOB_CARD.INSURER_ID =
> > > > > TBL_INSURER.INSURER_ID)
> > > > > ORDER BY
> > > > > JOB_CARD_ID DESC

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64