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'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