The complete process of my app is a weekly huge insert from a file, using
external files, and inmediatly after generate a report for the data
reciently inserted (the table has a date field). Not intermediate deletes or
updates. Also weekly, I delete records older than 90 days, preventing the
table from growing indefinitely. The counts are also grouped on data that
has been reciently inserted, so I don't think the auxiliar table is a
solution.

You say "definitely not using joined SPs anyway"

why?

I tried to use also views, but I needed to pass parameters to the query, and
I read in this list selectable stored procedures are like views and you can
pass parameters to the query

Diego

----- Original Message -----
From: "Alan McDonald" <alan@...>
To: <firebird-support@yahoogroups.com>
Sent: Friday, September 30, 2005 12:49 PM
Subject: RE: [firebird-support] Stored Procedures Left Outer Join


>> 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
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>