On Wed, Oct 26, 2005 at 02:34:30PM -0400, Alex Vishnev wrote:
> The problem could very well be in iterator optimization. What I have done is
> bypassed sql++ integrator and used mysql_fetch_row directly on returned
> result set. After I obtain ResUse object reference, I retrieve MYSQL_RES
> with mysql_result() member call from ResUse object. That's how I save 1/2 of
> processing time. However, it is still not as good as loading data from a
> file. right now, it is double the time it takes to load data from the file.
I'm not surprised about that... loading from a file removes half (or more)
of the processing... that half is mysql. :-)
> At the same time, if I go into mysql and copy all rows from one table into
> another, it takes approx 1-2s.
> mysql> create table abc select * from oldtab;
> Query OK, 397913 rows affected (1.12 sec)
> Records: 397913 Duplicates: 0 Warnings: 0
That's not surprising either, since it is all in one SQL statement. If you
run that same statement through mysql++, no data will be transferred
between server and client, and the server does all the work, so the time
would be the same.
> So I am thinking if internally mysql takes 1-2 sec when walking thru old
> table and creating new table, I should get similar performance. Maybe I am a
> little na?ve, but.... I did not expect it to be 2 to 3 times worse than I
> have seen in db.
When transferring data from server to client in an SQL system (any SQL
database), there is processing to do:
create the SQL stmt (this can be substantial in itself
in the data-write case, not currently under discussion)
load the data from the database
format it into row-by-row chunks for socket connection and send
parse that on the client side as well
In mysql++'s case, this involves copying the data at least
2 or 3 times, not counting the C API, as I understand it.
This is probably the source of the bottleneck.
With a file, you only have:
Basically, you're doing only the server's work, and loading it straight
Unfortunately, none of this helps you unless you want to tackle the
mysql++ parse step and submit an optimizing patch. I'd like to do it,
but I have just too much other stuff that must be done.