Bug #91426 for Net-MySQL: Bad algorithmic complexity -> inacceptable performance on big selects

Sun Dec 15 06:11:38 2013 sylvain.frandaz [...] kertiostechnologies.com - Ticket created

Subject:	Bad algorithmic complexity -> inacceptable performance on big selects
Date:	Sun, 15 Dec 2013 12:11:14 +0100
To:	bug-Net-MySQL [...] rt.cpan.org
From:	Sylvain Frandaz <sylvain.frandaz [...] kertiostechnologies.com>

Hi, I'm using Net::MySQL for a big data program. I had no problems with it when selecting thousands of rows, or inserting thousands of them. However, I've noticed that the performance per result line gets worse the more lines there is to fetch. Consider the following pseudo-perl code: $dbConnection->query($mySelectQuery); $recordIterator = $dbConnection->create_record_iterator(); while (1) { $timeInMicrosecs = getMicroSeconds(); $record = $recordIterator->each(); $elapsedMicrosecs = getMicroSeconds() - $timeInMicrosecs; print STDERR "Cost to get ONE record: $elapsedMicrosecsn"; last unless (defined $record); # Here... treatment using the $record } When receiving 1000 result lines in my query, I've measured an average performance of 226 microseconds per row fetched using each(). When receiving 10000 results, it went up to 3158 microseconds. When receiving 100000 results, it went up to 40277 microseconds, per row. I'm strongly suspecting a N² (or worse) complexity algorithm making things unbearingly slow for these kind of volumes of data. I'm aware that this is not a "bug" per se, as the module is *functionally* ok (it still returns the results, but it does it VERY slowly), but I guess this performance problem will interest you, as this module intends to replace the DBI-based approach. Would you happen to know of a workaround for this problem, or should I switch to DBI for the time being, and hope for better performance ? I'd be glad to help improve Net::MySQL, and maybe even code a correction myself, but I'm currently swamped in a (already late) delivery for a customer, and can't spare the time right now. Best regards, Sylvain FRANDAZ, french Perl user. -- Sylvain Frandaz Ingénieur d'études et développement. + 33 6 32 85 15 36 sylvain.frandaz@kertiostechnologies.com www.kertios.com 23 rue Edouard Nieuport 92150 Suresnes

Download 25150a32.jpeg
image/jpeg 20.8k

Thu Dec 19 03:06:47 2013 tsucchi [...] cpan.org - Correspondence added

Sylvain, Thank you very much for reporting. DBD::mysqlPP and Net::MySQL has many bugs and poor performance as you reported. I don't want to use these modules in production system. I strongly recommend to use DBD::mysql instead. DBD::mysql is widely used and good performance. Regards, On 2013-12月-15 日 06:11:38, sylvain.frandaz@kertiostechnologies.com wrote: Show quoted text

> > > Hi, > > I'm using Net::MySQL for a big data program. I had no problems with it > when selecting thousands of rows, or inserting thousands of them. > > However, I've noticed that the performance per result line gets worse > the more lines there is to fetch. > > Consider the following pseudo-perl code: > > $dbConnection->query($mySelectQuery); > > $recordIterator = $dbConnection->create_record_iterator(); > > while (1) { > > $timeInMicrosecs = getMicroSeconds(); > > $record = $recordIterator->each(); > > $elapsedMicrosecs = getMicroSeconds() - $timeInMicrosecs; > > print STDERR "Cost to get ONE record: $elapsedMicrosecsn"; > > last unless (defined $record); > > # Here... treatment using the $record > > } > > When receiving 1000 result lines in my query, I've measured an average > performance of 226 microseconds per row fetched using each(). > > When receiving 10000 results, it went up to 3158 microseconds. > > When receiving 100000 results, it went up to 40277 microseconds, per > row. > > I'm strongly suspecting a N² (or worse) complexity algorithm making > things unbearingly slow for these kind of volumes of data. > > I'm aware that this is not a "bug" per se, as the module is > *functionally* ok (it still returns the results, but it does it VERY > slowly), but I guess this performance problem will interest you, as this > module intends to replace the DBI-based approach. > > Would you happen to know of a workaround for this problem, or should I > switch to DBI for the time being, and hope for better performance ? I'd > be glad to help improve Net::MySQL, and maybe even code a correction > myself, but I'm currently swamped in a (already late) delivery for a > customer, and can't spare the time right now. > > Best regards, > > Sylvain FRANDAZ, french Perl user. >

Thu Dec 19 03:06:47 2013 The RT System itself - Status changed from 'new' to 'open'