PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: "Jeff Greif" <jgreif at befree dot com>
Subject: Re: Moving to a specific row
Date: Aug 31 2000 7:41PM

You wind up with things like:

CREATE PROCEDURE HITS_BY_CATEGORY @unit CHAR(1),
   @INTERVAL_START DATETIME, @INTERVAL_END DATETIME,
   @pagesize INTEGER=0, @pagenumber INTEGER=0, @sortcolumn
VARCHAR(100)='CATEGORY', @sortdirection VARCHAR(4)='ASC'

Suppose the data looks like this:

CATEGORY     HITS     INTERVALSTART         INTERVALEND
aaa                3942    2000-07-23 01:00:00    2000-07-23 02:00:00

the unique id for the row is CATEGORY, INTERVALSTART, INTERVALEND.  Suppose
the user wants to see data for 1 particular day for all the categories
sorted in descending order of HITS (summed for each category over the 24
hours).

The sort of query you'll be constructing in the procedure is

   SELECT @intervalstart, @intervalend, CATEGORY, SUM(HITS) AS HITS from
HIT_HISTORY
     where INTERVALSTART>=@interval_start AND INTERVALEND <=@INTERVAL_END
     GROUP BY CATEGORY
     ORDER BY SUM(HITS) DESC [,  CATEGORY]  -- optional part needed for your
approach

and then you'll be extracting pages from the result for display.  To use the
approach you suggested, you'll have to produce a temporary table for each
user, hold onto it over multiple web page requests (and when do you know
it's safe to delete it), set up a unique index on HITS, CATEGORY and an
ordering relation like (HITS < @lasthits OR (HITS = @lasthits AND CATEGORY >
@lastcategory)), where @lasthits and @lastcategory are remembered from the
previous page displayed.  Also, this doesn't work when you tell the user
there are 20 pages of results and he wants to jump from page 3 to page 10.

In this case, an easy (but very inefficient) way to do it is, in the above
stored procedure, to execute the above query into a temporary table on every
web page access, execute DELETE FROM #temphits WHERE CATEGORY IN (SELECT TOP
@pagesize*@pagenumber CATEGORY FROM #temphits), and then SELECT TOP
@pagesize from #temphits.  I know it's gross, but it does meet the UI
requirements, including the near statelessness between pages.

Jeff

----- Original Message -----
From: "Michael Peppler" 
To: "SybPerl Discussion List" 
Sent: Thursday, August 31, 2000 11:25 AM
Subject: Re: Moving to a specific row


> ye, wei writes:
>  > Michael Peppler wrote:
>  > > The correct way to do this in a set-based envionment is to use the
>  > > unique primary key in the where clause to get the next X rows of a
>  > > request.
>  > >
>  > > So assuming you have an identity column as the primary key, you
simply
>  > > remember the last value retrieved for the current page, and when the
>  > > user hits "next" you do a request where the primary key > this value.
>  >
>  > But in web page, usually the grid need to be shown sorted by different
columns,
>  > sometimes is by the primary key, sometimes not, so where the primary
key > this
>  > value
>  > doesn't work.
>
> True. So you need to find some combination of column values that will
> be unique and ordered correctly, especially if you are getting data
> from a large table.