Markus,

Would it be worth the ugliness of a module-global variable to bury the
value of the row index used in the previous call to dbd_mysql.c::dbd_goto_row()
inside the MySQL driver itself, side-stepping the function signature change
for other drivers?  The benefit of avoiding the API change may not be
outweighed by the potential fragility of naively tracking the state
internally, of course, since in practice folks install and upgrade to new
versions of drivers and the libdbi core at the same time, but then again,
it may.  I'm a little nervous about the potential for problems when mixing
direct goto_row() (cursor style) and next_row() calls, but I haven't looked
at the code to see if there's actually an issue there...

--miker


On Tue, Jan 8, 2013 at 4:46 AM, Markus Hoenicka <
markus.hoeni...@mhoenicka.de> wrote:

> Olivier Doucet <webmas...@ajeux.com> was heard to say:
>
> > Hello everyone,
> >
> > I'm following a quite old topic about libdbi speed issues.
> > I was able to track the cause of these issues : The major problem is
> > how libdbi goes from one row to another.
> >
> > RRDTool (the tool that used libdbi and that I was inspecting) is using
> > dbi_result_next_row() function (as stated in libdbi documentation
> > btw).
> >
> > This function moves from one row to another with function
> > dbi_result_seek_row(), incrementing currentRow index each time. This
> > gives a call to dbd_mysql.c::dbd_goto_row() that uses
> > mysql_data_seek() each time...
> >
> > That's why for a query result of 34k rows (yes it happens. No it is
> > not a problem in the query itself), we have tens of thousands of call
> > to this function (which is very low), and this is definitely not
> > needed, because as we use fetch_row(), we automatically move from one
> > row to another. Seeking is just a useless task (as internal driver
> > does not know where we are, and needs to start from row 0 and seek to
> > the given row - where we already were).
> >
> > I'm absolutely not a libdbi user, and I don't know what could be done
> > outside libdbi to not use dbi_result_next_row() and use directly
> > RESULT->onn->driver->functions->fetch_row() directly. Is it possible ?
> >
> > And/or patching dbi_result.c :
> > just check RESULT->currowidx near line 102 before calling doing
> > goto_row() function and call it only if we are not on the good row. Am
> > I right ?
> >
>
> Hi,
>
> your analysis is pretty much correct. If you look at the comments in
> dbd_mysql.c::dbd_goto_row(), the original author of the mysql driver
> was well aware of the limitations of his implementation. The reason is
> that other database APIs, e.g. PostgreSQL, allow to fetch rows from a
> result set by index, whereas the MySQL API assumes that you step
> through the rows sequentially. The original design of libdbi appears
> to somewhat favor PostgreSQL in this respect.
>
> Anyway, without having thought about the issue in too much detail, one
> possible solution comes to mind. We could modify the driver function
> dbd_goto_row() by passing both the wanted row index rowidx and the
> current row index currowidx(which libdbi keeps track of anyway). This
> would allow drivers to decide whether they have to actually seek the
> position. pgsql doesn't have to anyway, and mysql doesn't have to if
> rowidx = currowidx+1. This API change would not mandate changes to
> existing drivers as they may ignore the additional parameter and keep
> working as before, but it may offer options to speed up queries in
> some drivers.
>
> regards,
> Markus
>
>
>
> --
> Markus Hoenicka
> http://www.mhoenicka.de
> AQ score 38
>
>
>
>
> ------------------------------------------------------------------------------
> Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS
> and more. Get SQL Server skills now (including 2012) with LearnDevNow -
> 200+ hours of step-by-step video tutorials by Microsoft MVPs and experts.
> SALE $99.99 this month only - learn more at:
> http://p.sf.net/sfu/learnmore_122512
> _______________________________________________
> libdbi-users mailing list
> libdbi-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/libdbi-users
>



-- 
Mike Rylander
 | Director of Research and Development
 | Equinox Software, Inc. / Your Library's Guide to Open Source
 | phone:  1-877-OPEN-ILS (673-6457)
 | email:  mi...@esilibrary.com
 | web:  http://www.esilibrary.com
------------------------------------------------------------------------------
Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS
and more. Get SQL Server skills now (including 2012) with LearnDevNow -
200+ hours of step-by-step video tutorials by Microsoft MVPs and experts.
SALE $99.99 this month only - learn more at:
http://p.sf.net/sfu/learnmore_122512
_______________________________________________
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users

Reply via email to