Thanks a lot Tianhao! This was extremely helpful. Indeed mysql_data_seek() has a for loop in it and I am retarded for using it like that. (But one would think they'd mention such thing in the MySQL documentation.)
I just submitted a change to the repository which should make things faster (although I haven't confirmed that it does, yet). I switched to using mysql_row_seek() instead of mysql_data_seek(). This required prefetching all row offsets once, so if you fetch a large result set and then not iterate through it, the new code does unnecessary work. (I guess ideally one would prefetch row offsets lazily. Some day, maybe.) Would you care to try the new version out and let me know how it works for you? Thank you again for the bug report and for tracking down the problem. You are awesome. Aleksander On Fri, Nov 20, 2009 at 9:54 PM, Tianhao Qiu <[email protected]> wrote: > Hi Aleksander, > > Thanks for you reply. The columns are all strings with size less than 20 > chars. > > I tried valgrind and the function mysql_data_seek showed up as the > sole culprit. The description at > http://dev.mysql.com/doc/refman/5.0/en/mysql-data-seek.html does not > say much, but this post > (http://www.mail-archive.com/[email protected]/msg15550.html) > indicates mysql_data_seek has a complexity of O(n). SOCI seems to be > calling mysql_data_seek() on every iteration. > > Next week I will try bulk operation to see if the performance improves. > > Regards, > Tianhao > > On Fri, Nov 20, 2009 at 7:43 PM, Pawel Aleksander Fedorynski > <[email protected]> wrote: >> Hi Tianhao, >> >> Thanks so much for trying SOCI and for letting us know about this >> problem. I'll look into it this sometime next week. Can you also >> tell us something about the data you're fetching? I'm mostly asking >> about the column types and the sizes of the values. >> >> Thank you, >> >> Aleksander >> >> On Fri, Nov 20, 2009 at 12:48 PM, Tianhao Qiu <[email protected]> wrote: >>> Hi, >>> >>> I'm a new SOCI user. I like its interface and simplicity. But I'm >>> rather surprised to see that SOCI's performance is much inferior in >>> comparison to PHP when iterating a large table. I guess I could be >>> doing something wrong since the difference is huge. The C++ code took >>> one minute to iterate a table with more than 50,000 rows, while the >>> equivalent PHP code took only one second. >>> >>> My environment: >>> OS: RHEL5.2 x64 >>> DB: mysqld 5.0.45 >>> C++: gcc-4.3.1, statically linked to SOCI 3.0 >>> PHP: 5.1.6 >>> >>> C++ code: >>> >>> rowset<row> rs = (m_db_session.prepare << "SELECT firmwareversion, >>> objtype, category " >>> "FROM FCPObject WHERE accepted_flag = 1"); >>> for (rowset<row>::const_iterator it = rs.begin(); it != rs.end(); ++it) >>> { } >>> >>> PHP code: >>> >>> $query = "SELECT firmwareversion, objtype, category ". >>> "FROM FCPObject WHERE accepted_flag = 1"; >>> $result = mysql_query($query, $link); >>> while ($row = mysql_fetch_row($result)) >>> { } >>> >>> Regards, >>> >>> Tianhao >>> >>> ------------------------------------------------------------------------------ >>> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day >>> trial. Simplify your report design, integration and deployment - and focus >>> on >>> what you do best, core application coding. Discover what's new with >>> Crystal Reports now. http://p.sf.net/sfu/bobj-july >>> _______________________________________________ >>> Soci-users mailing list >>> [email protected] >>> https://lists.sourceforge.net/lists/listinfo/soci-users >>> >> >> ------------------------------------------------------------------------------ >> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day >> trial. Simplify your report design, integration and deployment - and focus on >> what you do best, core application coding. Discover what's new with >> Crystal Reports now. http://p.sf.net/sfu/bobj-july >> _______________________________________________ >> Soci-users mailing list >> [email protected] >> https://lists.sourceforge.net/lists/listinfo/soci-users >> > > ------------------------------------------------------------------------------ > Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day > trial. Simplify your report design, integration and deployment - and focus on > what you do best, core application coding. Discover what's new with > Crystal Reports now. http://p.sf.net/sfu/bobj-july > _______________________________________________ > Soci-users mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/soci-users > ------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ Soci-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/soci-users
