Also, keep an eye on index selectivity. Maybe start with updating index
statistics for the indexes you already have?

Kjell

Kjell Rilbe wrote:

>
>
> 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>
> > <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>
> > > > <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@... <mailto:kjell%40datadia.se>
> 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