>If you want to query (management asks for) the yearly turnover of the company >from the start of the year to yesterday >and you have 1000000+ invoices ... It is time and resource consuming, even >with stored procedures. If I have monthly >archives (tables) then I have to query each of the tables.
Turnover so far this year can be solved by having summary table(s) that you fill through triggers (inserting a positive value ON INSERT, negative value ON DELETE and both or either ON UPDATE), an occasionally summarize. Turnover the last year (i.e. the last 365 days) cannot as easily be solved this way since the starting point is not fixed (though I guess you could have monthly summaries and then separately add the month(s) that you partially want covered). The queries would be more complex than a simple select, but it should be faster and could well be feasible. I disagree with Karol in that queries running minutes necessarily is wrong (I do have one program running 5-6 hours and I think one of the queries takes over 30 minutes. This program, however, is run between once and three times yearly, and I have done my best to make it fast enough). I do agree with Karol that frequently executed tasks taking a long time (“long time” being relative, it could be seconds, it could be hours) should be examined to see if the query or database design ought to be rewritten or if the original question could be reformulated (e.g. sometimes people asking “how many” really only wonder “are there any”). Sometimes optimization is possible, other times there are good reasons for things being time consuming. Unfortunately, I don’t know which of them are applicable to your situation, Tiberiu. One question: How often are your cumulative queries run and could several of them be run simultaneously? At my workplace, it is unusual to notice any Firebird slowdown when one person starts a time consuming query. However, if several persons simultaneously executes heavy queries, things can come to a halt. This is despite us using SuperServer. I think we had more of a problem with this when we used Firebird 1.5, than we’ve had after switching to 2.5. Set
