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