Re: [firebird-support] Performance diff between insert...select and for select ... do?
Kjell Rilbe kjell.ri...@datadia.se [firebird-support] skrev: Hi, I'm writing a utility that will need to do two things for each record in an external table and for this purpose I use a for select ... do construct in an execute block. I do it this way because external tables can't be indexed and I will scan the entire external table anyway. The two operations are: 1. Update one existing record in the target table. 2. Insert new record in the same target table. In steady state the target table will contain about 20 million records and the external table will contain about 10 thousand records. But the first time I run this, the target table will be empty and the external table will contain about 18 million records. The update will never find a record to update during this first execution. Would I lose a lot of hours if I use the same execute block/for select construct the first time? The alternative would be to do a regular insert into target table select from externaltable the first time. As a follow-up to this question, my tests with real data showed that the execute block was *very* much slower than a simple insert from the external table for the initial import into an empty target table. I think it was something like 10 minutes vs. 10 hours, give or take... I also noted that in steady state, the Firebird solution as a whole was very slow. The thing is that for each run of this utility, I would need to visit close to 100 % of the records. I ended up tossing Firebird altogether and implemented a simple text file format instead, and a load-modify-write pattern, making good use of abundant RAM. Went from 5-10 hours to about 5 minutes. SQL databases are good for many things, but in this case, they suck (I'm assuming Firebird is not significantly worse than any other brand in this case). Regards, Kjell
Re: [firebird-support] Profiler for 2.1?
Yes, we are planning a migration to 2.5, but we have to do more than one thing at a time, we can't afford to serialise all our projects! Not least because it'll be quite a while before we get all installations upgraded and we'll need to maintain the 2.1 installations in the meantime. So does the sort of profiler I describe exist for 2.5, or is it a question of rolling one's own using the TraceAPI ... in which case I might as well roll my own using MON$CALL_STACK? On 12/04/2015 10:17, Alexey Kovyazin a...@ib-aid.com [firebird-support] wrote: Hi Tim, 2.1 series was discontinued, so nobody will invest in tools for it. Instead of this, consider migration - 2.1 is pretty close to 2.5, where TraceAPI is available. Regards, Alexey Kovyazin IBSurgeon I've just discovered MON$CALL_STACK, and it seems possible that one could use this to produce a Monte Carlo type profiler - query it once every so many seconds, and built up a tree of how often each path through nested procedures has been hit, thus pointing at which paths through the code are taking the time. Do any of the available tools have this feature? - the output I'm after is a graphical drill-down calling tree like the ones you get from profilers for conventional languages. -- Tim Ward -- Tim Ward
