Hi Aleksander, I've tried your patch. It worked like a charm. The performance is now comparable to PHP code. Thanks a lot for your help.
Regards, Tianhao On Sat, Nov 21, 2009 at 1:57 AM, Pawel Aleksander Fedorynski <[email protected]> wrote: > 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 > ------------------------------------------------------------------------------ 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
