List:MySQL++« Previous MessageNext Message »
From:Chris Frey Date:October 26 2005 7:43pm
Subject:Re: Retrieving 300K+ records
View as plain text  
On Wed, Oct 26, 2005 at 02:34:30PM -0400, Alex Vishnev wrote:
> Chris,
> 
> 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)
	send it
	parse it
	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:

	open file
	read file
	parse file

Basically, you're doing only the server's work, and loading it straight
into memory.

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.

- Chris

Thread
SSQLS contained within class definition compilation issueswilliam.lieberman25 Oct
  • Retrieving 300K+ recordsAlex Vishnev25 Oct
    • Re: Retrieving 300K+ recordsEarl Miles25 Oct
      • RE: Retrieving 300K+ recordsAlex Vishnev25 Oct
        • Re: Retrieving 300K+ recordsWarren Young27 Oct
      • RE: Retrieving 300K+ recordsAlex Vishnev26 Oct
        • Re: Retrieving 300K+ recordsWarren Young27 Oct
          • RE: Retrieving 300K+ recordsAlex Vishnev27 Oct
    • Re: Retrieving 300K+ recordsChris Frey26 Oct
      • Re: Retrieving 300K+ recordsEarl Miles26 Oct
        • Re: Retrieving 300K+ recordsChris Frey26 Oct
          • RE: Retrieving 300K+ recordsAlex Vishnev26 Oct
            • Re: Retrieving 300K+ recordsEarl Miles26 Oct
            • Re: Retrieving 300K+ recordsChris Frey26 Oct
  • Re: SSQLS contained within class definition compilation issuesahnkle28 Oct
    • New FeaturesFabricio Mota29 Oct
RE: SSQLS contained within class definition compilation issueswilliam.lieberman25 Oct
  • Re: SSQLS contained within class definition compilation issuesWarren Young27 Oct
Re: SSQLS contained within class definition compilation issuesWarren Young27 Oct