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, 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>, 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
> > >
> >
> >
>
> --
> --------------------------------------
> Kjell Rilbe
> DataDIA AB
> E-post: kjell@...
> Telefon: 08-761 06 55
> Mobil: 0733-44 24 64
>