G'day all...

Thanks for getting back to me, Svein!

I'm not sure that it's an indexing issue because the optimiser does
elect to use the index if I do the join on a foreign key as in the
example below (i.e. FK_MARKETING_3). What is messing with my head is
the plan clause reported by Firebird - it refers to a stored
procedure which isn't even called directly in my query. I can't begin
to tinker with the plan if the default plan doesn't make sense! I
can't use the default plan in a PLAN clause, so I'm dead in the water.

I still think the original performance problem is caused by the
ordering of stored procedure amongst the other joins because I get a
HUGE improvement by forcing the (expensive) stored procedure to be
executed first by placing it at the head of the FROM clause. Helen's
book says that inner joins will tend to be performed before left
joins, but I now need to do a left join with the results of one of
these nasty stored procedure and I've got no way of forcing the
stored procedure and its subsequent join to be executed early. I
suspect that the query is actually being re-executed unneccessarily
for each row on the right of the join.

Thanks again for your suggestions, especially those regarding date
selection - I will employ them to further improve the performance of
the query once I solve the major problem with the joins involving
stored procedures.

The simplified test query (which runs very quickly) is as follows.
Please refer to my original post (Tue Dec 11, 2007 9:02 pm (PST)) for
schema details.

/* Test Query using getroottypes stored procedure (N.B: GETROOTTYPES
calls GETALLSUBTYPES recursively )*/
select *
from getroottypes(3, NULL) rast
inner join marketing m on m.advertisingsourcemetatypeid = 3 and
m.advertisingsourcetypeid = rast.subtypeid

This is the plan the optimiser apparently came up with:
PLAN JOIN (GETALLSUBTYPES NATURAL, TYPES INDEX (FK_TYPE_1))(M INDEX
(FK_MARKETING_3))

Regards,

Alex.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> Hi Alex!
>
> > I just hope somebody here
> >(a) can be bothered looking all the way through this,
>
> Done
>
> >(b) has some idea of what I'm trying to do and
>
> Some idea sounds like a good description
>
> >(c) has some idea of how to solve my problem.
>
> Well, I don't know yet.
>
> What I do think helps bogging things down, is that neither of the
> procedures use any index. Try writing a single stored procedure
> similar to a combination of getroottypes and getallsubtypes and

write

> a simple select for this SP alone. How does this perform compared

to

> your original statement?
>
> Then, if the performance looks good, try to join try to join to the
> rest of your statement. I don't know whether your statement started
> out as you wrote it below, but normally - using LEFT JOIN to tables
> that are in the WHERE clause (or other tables that will not lead up
> to fields with no match in your output), just makes things more
> complicated for the optimizer. Also, "m.firstcontactdate
> between '2007-01-01' and '2007-12-14' and m.firstcontactdate+0
> < '2007-12-14'" gives the optimizer a chance to use an index
> that "cast(m.firstcontactdate as date) >= '2007-01-01' and cast
> (m.firstcontactdate as date) <= '2007-12-13'" doesn't (if such an
> index exists and is of use).
>
> Sorry that I cannot be of more use, I have virtually no experience
> with recursive stored procedures, and not all too much experience
> with joining stored procedures to other tables/procedures.
>
> Still, HTH,
> Set
>
> "Alex Ip" wrote:
> select re.regionname as "Region",
> cast(extract(year from m.firstcontactdate) || '-' || extract(month
> from
> m.firstcontactdate) || '-01' as date) as "Call Month",
> ast.typename as "General Advertising Source",
> adt.typename as "Detailed Advertising Source",
> ct.typename as "Call Type",
> count(m.marketingid) as "Contact Count",
> count(mo.submetatypeid) as "Mail-out Count",
> count(q.quoteid) as "Quote Count",
> count(j.jobid) as "Job Count",
> sum(q.quotevalue) as "Quoted Value",
> sum(j.jobprice) as "Work Value"
>
> from getroottypes(3, NULL) rast /* Root advertising source types */
> inner join marketing m on m.advertisingsourcemetatypeid = 3 and
> m.advertisingsourcetypeid = rast.subtypeid
> left join person p on p.marketingid = m.marketingid
> left join locality l on m.localityid = l.localityid
> left join postcode pc on l.postcodeid = pc.postcodeid
> left join territory te on pc.territoryid = te.territoryid
> left join region re on te.regionid = re.regionid
> left join interaction fi on fi.marketingid = m.marketingid
> left join interaction i on i.personid = p.personid
> left join getallsubtypes(1, 2) mo on mo.submetatypeid =
> i.actionmetatypeid
> and mo.subtypeid = i.actiontypeid /* All mail-out types - this is
> what bogs
> things down /*
> left join quote q on q.actionid = i.actionid and (bin_and
> (q.quotestatus, 1)
> = 1)
> left join job j on j.actionid = i.actionid and (bin_and

(j.jobstatus,

> 1) = 1)
> and (j.jobprice > 0)
> left join types adt on adt.metatypeid =

m.advertisingsourcemetatypeid

> and
> adt.typeid = m.advertisingsourcetypeid
> left join types ct on ct.metatypeid = fi.actionmetatypeid and
> ct.typeid =
> fi.actiontypeid
> left join types ast on ast.metatypeid = 3 and ast.typeid =
> rast.roottypeid
>
> where adt.typename is not null
> and te.RegionID = 31
> and cast(m.firstcontactdate as date) >= '2007-01-01'
> and cast(m.firstcontactdate as date) <= '2007-12-13'
>
> group by 1, 2, 3, 4, 5
> order by 1, 2, 3, 4, 5
>