Subject Re: [firebird-support] Re: RE: At my wits end
Author Eddie Bush
Ed,

As high of an opinion as I have of Perl (and it is high), I don't think it's a language to be considered when you get down to things like this where performance counts. Perl is awesome, but it simply cannot compete with the performance you get in compiled applications.

If your issue is bandwidth you're probably not going to increase performance much more. If, however, you're running into some sort of speed of execution thing you might consider building a multi-threaded import utility. The reason I suggest this approach is that I was once tasked with some heavy-duty data maintenance and typical scripts just weren't sufficient for the amount of data and frequency I had to load data (we were testing an application and we routinely reloaded a ton of data). This was under Informix, but there was this multi-threaded import app that would really suck the data in rapidly. You could specify some number of threads to execute concurrently and, as I recall, about four threads seemed to be a *very* happy place. If you're going to build such a thing you might as well make it configurable though, so you can play around with what your ideal number of threads is.

Good Luck!

Eddie Bush
----- Original Message -----
From: Steve Staneff
To: firebird-support@yahoogroups.com
Sent: Friday, March 12, 2004 11:07 PM
Subject: [firebird-support] Re: RE: At my wits end


Ed,

Mileage does indeed vary. As a quick and possibly irrelevant test, I just copied 1,665,884 records from one table to another, and it took less than 3 minutes (I left the room and it was done when I came back). That's better than 9250 reads & inserts per second. This was using WinXP, FB 1.5rc7, and IBConsole on a 2.8ghz pentium.

That said, you mentioned that you're bringing in records from CSV-formatted files on a routine basis. This is the kind of thing that scripting languages were made for. I'd suggest looking into either Perl or Python. Personally, I use Python for just this sort of thing, and the CSV and Kinterbasdb modules mask the complexity on both ends. I'm not a programmer, but I've employed them, and I much prefer writing a script myself whenever possible (no offense intended to the programmers out there...).

Steve

Epstein, Ed wrote:
> 1) Use the API directly - Don't have the skill
> 2) Use an external program written to use the API - SQL scripts, import
> engines like FBExport, etc. I really needed a way to do it internally and
> they did not even reach an accetable speed.
> 3) Use external tables. This does not work since I would have to come up
> with a method of delivering them to the server. This has proved to be quite
> impossible due to circumstances that I cannot control.
>
> I took the advice of this forum and paid to have a component written to
> execute the SQL statements for me and use only the API to do this. No ADO
> layers, etc. Just pure looped API statements.
>
> This does EVEN worse than ADO and OLEDB. I received multiple testing
> programs from potential coders and none of them could even break 100 tps,
> let alone the 1500 tps i need.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.620 / Virus Database: 399 - Release Date: 3/11/2004

[Non-text portions of this message have been removed]