Sorry for top-posting, I'm not currently at a real computer. I guess you'Re right in that the CTE is called once for each iteration. Since you're joining on output and not input fields, that probably would not be necessary, but maybe Firebird doesn't optimizer such - somewhat unusual - cases.
7000 cases is peanuts, and even 2-5 minutes seems too long. Typically, I would probably use something like IIF (<SomeDateField> between :PeriodFrom, :PeriodTo), costs, 0) and modify your SP to return an additional field, but how to best solve this requires knowledge of your SP. So, could you show it to us or is it too complex or secret? Set ---- Hi to all, I am in the process of writing a list of entries with running costs in a quarter and all costs that have arsien since. So, I wrote a stored procedure with the mathematical logic for calculating the respective sums with different rates of exchange and some other rules. I made this procedure flexible so that it either returns all costs for a given period (e.g. quarter) or all costs until a certain point of time. The resulting structure for the lists basically looks like this: with costs_in_period as (select cp1.file_id, cp1.costs as costsinquarter from cost_procedure (periodfrom,periodto) cp1) select cp2.file_id, cip.costsinquarter, cp2.costs as costssofar from cost_procedure (null,periodto) cp2 left join costs_in_period cip on cip.file_id = cp2.file_id I now have about 7000 cases and both queries incividually take about 2-5 minutes to query. But when I now use the above given CTE structure I killed the running transaction after 5 hours running time. I thought that firebird does nothing else as individually running the two queries and then joining them. But it seems to me that it queries the CTE for each entry in the main query. Am I making something wrong and/or is there a better way of doing that? Kind regards and thanks for an answer in advance Christian ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
