Re: Practical Cursors

Micah Yoder wrote:
> (sorry to reply to a week-old message.  need to keep up with this list more!)
>
> On Monday 17 September 2001 17:04, you wrote:
>
> > There is an obvious benefit to the use of cursors within a persistent
> > environment. In other words, if my connection to the database is live, I
> > can increase my query and display efficiency through the use of a cursor.
> >
> > However, this seems to be useless within a web based environment. If we
> > have a live connection through a C++ application, we can perform a
> > transaction and interact within the results.
>
> Yep.  That seems to be a disadvantage with ALL database systems & HTTP based
> apps.
>
> I once wrote a MySQL app (I know, but that's what the company used) to do a
> fairly complicated search on a huge database full of domain names.  The query
> was time consuming (10-30 seconds) so it obviously could not be performed for
> every prev/next page request.
>
> My first approach was to have the PHP script write the entire data resultset
> to a fixed-length file, which could be easily accessed for each request to
> the point where the user was in the file.  Only problem there was when the
> result set was large, initial query time was significantly longer.  And that
> happened a lot.
>
> I then wrote a daemon in C to do the work and store the results in RAM.  The
> PHP script connected to the daemon via a socket, and passed a request ID and
> the numbers of the records it wanted.  Sure, it was convoluted, but I
> actually got the speed up to where I was fairly happy with it.
>
> If there's a better solution than that, I'm not aware of it.
>
> But like someone else mentioned, it's not quite "practical" database usage.

    Since  search  engines and data warehousing tend to have huge
    databases with sometimes complicated,  long  running  queries
    that  produce  empty to huge result sets, it's a quite common
    problem. Thus, I would consider any solution  that  leads  to
    success at first "practical".

    PHP together with cursors might be an alternate solution. You
    open a cursor for the entire result set. You have a  function
    that  fetches  the  next n rows from the cursor and generates
    the resulting html output in a file. It returns true if  more
    rows  have  been  found.  You  call it once and if it returns
    false display "No match found" or so. If it returns true, you
    call  it  again  to create a cache file for the second result
    page, and know if there  will  be  one  (telling  you  if  to
    provide a NEXT button). You register a shutdown function that
    will call the cache file generator another m times.  Now  you
    display  the  first  cache file, leave the DB connection with
    the open transaction and cursor where they are and exit.

    The user will already see the first result  page  while  your
    server  is  still  working.  After  calling  the  cache  file
    generator function m times, the shutdown function closes  the
    cursor,   terminates   the  transaction  and  closes  the  DB
    connection.

    I think 95% of users will not hit NEXT  more  than  10  times
    before  refining  their  search, so that should be enough. If
    one really does, well, than you'd  have  to  run  the  entire
    query  again  and this time create either more cache files or
    all of them.

    Now you need some sort of vacuum cleaner for the cache  files
    and are done.

    The  drawback  for  this solution is, that you don't know how
    many pages there will be in total when you display the  first
    one.   But   the   benefits   are  that  it  fit's  into  the
    connectionless HTTP nature, has a small  resource  footprint,
    provides  first  results  early  and  does  not  require open
    transactions over user interaction.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com