> Hi all,
>
>
> I have a table with 4 million records. I have 7 selectables stored
> procedures that performs different grouped COUNTS on that table.
> I run a query to join all the stored procedures by a common field.
> The result is about 200 records for each stored procedure. So
> the query takes about 4 minutes to finish. Now I have just discovered
> I have to use a left outer join to get all the record I want, because
> the stored procedures are not returning the same ammount of records.
> If I run the same query using a left outer join, the query takes so long
> that I have stopped it after 6 hours.
>
> How the stored procedures are used by the query optimizer?
>
> If every stored procedure returns about 200 records I suppose the
> best way to get the results would be running every stored procedure,
> and then
> joining the 200 records, specially in the case of the left outer join,
> but I suppose the optimizer is ordering and joining the table the
> stored procedure
> is accesing (with 4 million recors), because left joining 200 records
> can't make that difference in the execution time.
>
> Is there a better way to make a left join with stored procedures?
>
> Any suggestions?

I wouldn't do it this way at all. Instead of having a live query (definitely
not using joined SPs anyway) I would create separate tables with triggered
entries to maintain the grouped totals you wish. Live queries would then be
very small and lively. You could run a housekeeping process from time to
time to re-invent these summary tables.
e.g. housekeeping clears the table and inserts one entry being the
count/total you want. Then triggered entries insert additional entries into
the table with negative/positive values for each insert/update/delete on the
master table. Your cumbersome queries would be very fast over very few
records.

Alan

>
> I'm using Firebird 1.5.2. and this is the quey I'm running.
>
>
> SELECT KI.CALLCENTER AS CALLCENTER, KI.I AS I, KIN.I_NETO AS INETO,
> KOPN.OP_NETO AS OP_NETO, KOFF.OFF AS OFF, KOFFT.OFF AS OFFT, KAC.AC AS
> AC, KACT.AC AS ACT
> FROM SP_KPI_I('2005-08-15','2005-08-21','SEGMENT') KI
> JOIN SP_KPI_I_NETO('2005-08-15','2005-08-21','SEGMENT') KIN ON
> KIN.CALLCENTER = KI.CALLCENTER
> JOIN SP_KPI_OP_NETO('2005-08-15','2005-08-21','SEGMENT') KOPN ON
> KOPN.CALLCENTER = KI.CALLCENTER
> JOIN SP_KPI_OFF('2005-08-15','2005-08-21','SEGMENT') KOFF ON
> KOFF.CALLCENTER = KI.CALLCENTER
> JOIN SP_KPI_OFF('2005-08-15','2005-08-21','SEGMENT') KOFFT ON
> KOFFT.CALLCENTER = KI.CALLCENTER
> JOIN SP_KPI_AC('2005-08-15','2005-08-21','SEGMENT') KAC ON
> KAC.CALLCENTER = KI.CALLCENTER
> JOIN SP_KPI_AC('2005-08-15','2005-08-21','SEGMENT') KACT ON
> KACT.CALLCENTER = KI.CALLCENTER
> ORDER BY KI.CALLCENTER
>
>
> Thanks
>
> Diego