Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

query returning a Portion of actuall resultSet

19 posts in Product Futures Discussion Last posting was on 2002-02-11 18:55:46.0Z
Mic Posted on 2002-02-08 15:45:06.0Z
From: Mic
Date: Fri, 8 Feb 2002 10:45:06 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: query returning a Portion of actuall resultSet
Message-ID: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums>
Lines: 5
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:792
Article PK: 94321

how to return Portion(from 5'th to 10'th) of actuall resultSet with one
query ?
PostgreeSql has "select * from table limit 10 offset 5 "
which means select from 5'th till 10'th records

thanx


Roger Broadbent Posted on 2002-02-08 15:57:25.0Z
From: "Roger Broadbent" <RBroadbent@wilco-int.com>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 15:57:25 -0000
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <sypH1pLsBHA.298@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: wilcohost-180.wilco-int.com 212.36.174.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:790
Article PK: 94316

How can you have the 5th to 10th member of a set? A set has no order. Unless
there is an explicit "ORDER BY" clause, the order rows are returned in is
not guaranteed by Sybase and thus this query would make no sense.

Even with an ORDER BY, other concurrent connections might change the result
list so that the 5th item becomes the 6th or the 4th after the client
determines which rows it wants, causing double-counting or row skipping. I
would oppose such a "feature" for this kind of reason.

If what you want is the result set n rows at a time, I believe cursors can
do this.

If you really need to define precise row counts, you could emulate it with a
cursor, stepping through the rows and discarding the results you don't want,
or with client-side code doing essentially the same thing.

--
Roger Broadbent
Technical Consultant
Wilco International Ltd

<Mic> wrote in message
news:A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums...
> how to return Portion(from 5'th to 10'th) of actuall resultSet with one
> query ?
> PostgreeSql has "select * from table limit 10 offset 5 "
> which means select from 5'th till 10'th records
>
> thanx


Matt Rogish Posted on 2002-02-08 18:34:52.0Z
From: "Matt Rogish" <matt@fanhome.com>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 13:34:52 -0500
Lines: 103
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <ePjQJ#MsBHA.298@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: ip134-053-064-186.s64.muohio.edu 134.53.64.186
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:788
Article PK: 94315

Roger,

This is a major stumbling block for many of us with applications that
require queries spread out over multiple pages (e.g. web application
'pages'). I had to 'hack' in something like that with a stored procedure
but it relies upon a known sort order and an identity column.

MySQL and PostgreSQL can do it with any sort order so obviously it is not
impossible; it would help things out a TON if Sybase could work out an
efficient algorithm to do this. Both DBs have different locking schemes but
avoid any sort of concurrency issues (shared locks on each row I would
assume which would avoid rows being deleted/changed out from under you).

I assume they get away with the '5th to 10th of a generic set' by simply
applying IDENTITY-type numbers to each row returned, so that way it is
trivial to return 5 through 10. So, I think MySQL/PostgreSQL do something
like this:
SELECT *
FROM table
WHERE somecol = 10
ORDER BY name ASC
LIMIT 10 OFFSET 5

You could sort of re-write this in Sybase like:
SET ROWCOUNT 15

SELECT *
FROM table
WHERE somecol = 10
ORDER BY name ASC

-- return rows 5 through 15

So it would require ASE to 'drop' the first 5 rows and then grab the next
ten.

The way I do it in my stored procedure (again relies on the existence of
identity col):
CREATE PROCEDURE sp_get_whatever
@howmany int,
@offset int
AS
IF @offset < 1 -- we're only grabbing the first howmany records
BEGIN
SET ROWCOUNT @offset

SELECT ... FROM ... WERE...
END
ELSE

SET ROWCOUNT ( @offset )

DECLARE @min_id NUMERIC( x, 0 )

-- Here we keep pushing the identity col into a var until we hit our
offset
-- (so if we had 10 rows offset we would keep the 10th id)
SELECT @min_id = id
FROM table
WHERE ...
ORDER BY ...

SET ROWCOUNT @howmany

SELECT ..
FROM table
WHERE id >= @min_id
ORDER BY
END

--- end proc

--
Matt

"Roger Broadbent" <RBroadbent@wilco-int.com> wrote in message
news:sypH1pLsBHA.298@forums.sybase.com...
> How can you have the 5th to 10th member of a set? A set has no order.
Unless
> there is an explicit "ORDER BY" clause, the order rows are returned in is
> not guaranteed by Sybase and thus this query would make no sense.
>
> Even with an ORDER BY, other concurrent connections might change the
result
> list so that the 5th item becomes the 6th or the 4th after the client
> determines which rows it wants, causing double-counting or row skipping. I
> would oppose such a "feature" for this kind of reason.
>
> If what you want is the result set n rows at a time, I believe cursors can
> do this.
>
> If you really need to define precise row counts, you could emulate it with
a
> cursor, stepping through the rows and discarding the results you don't
want,
> or with client-side code doing essentially the same thing.
>
> --
> Roger Broadbent
> Technical Consultant
> Wilco International Ltd


Michael Peppler Posted on 2002-02-09 01:10:53.0Z
From: "Michael Peppler" <mpeppler@peppler.org>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 08 Feb 2002 17:10:53 -0800
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com>
User-Agent: Pan/0.9.7 (Unix)
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-No-Productlinks: Yes
Message-ID: <4Grx0dQsBHA.333@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 77
NNTP-Posting-Host: gw.peppler.org 206.55.243.57
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:778
Article PK: 94309

In article <ePjQJ#MsBHA.298@forums.sybase.com>, "Matt Rogish"

<matt@fanhome.com> wrote:

> Roger,
>
> This is a major stumbling block for many of us with applications that
> require queries spread out over multiple pages (e.g. web application
> 'pages'). I had to 'hack' in something like that with a stored
> procedure but it relies upon a known sort order and an identity column.

There are a couple of ways to do this.

One way is similar to what you've done, but with a twist. If the starting
row > 1, then you insert the primary key into a temp table with an
identity column up to start row + numrows, and then select back rows
joining on the temp table where the identity col is in the range you
want. This works fine when users usually only look at the first few
pages.

I've got code that does something like this:

create table #tmp ( id numeric(5,0) identity
, threadId numeric(9,0) )

select @num = @maxrows + @startPosition

set rowcount @num

if @direction = 0
insert #tmp(threadId)
select threadId
from eC_thread
where circleId = @circleId
and topicId = @topicId
and (@hidden = 1 or status != 0)
order by lastModDate desc
else
insert #tmp(threadId)
select threadId
from eC_thread
where circleId = @circleId
and topicId = @topicId
and (@hidden = 1 or status != 0)
order by lastModDate

and then

select ...
from eC_thread t
, eC_threadRead tr
, #tmp tmp
where t.circleId = @circleId
and t.topicId = @topicId
and t.threadId = tmp.threadId
and tr.threadId =* t.threadId
and tr.userId = @userId
and tmp.id > @startPosition
order by tmp.id

This works quite well, because most users don't go beyond the first page
:-)

The alternative is to use primary key (or any other unique key) to
navigate the data. You write your proc to return data for numrows that
are all before or after the PK that you pass in to the proc. In the web
app you record the last and first PK values that you display, and use
these as the starting point for the next/previous page queries.

It's a little messier to code, but has the advantage that it scales
really well (given proper indexes, of course).

Anyway - adding a LIMIT keyword (a la MySQL) would mean that Sybase would
internally run a query similar to the first example - this is OK when
your starting point is close to 0, but it breaks completely if your
starting point is very far from the start of the result set. (btw - do
you know if MySQL or Postgres have performance problems when you run a
query on a large table and use LIMIT to request the last page?)

Michael
--
Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler
mpeppler@peppler.org - mpeppler@mbay.net
International Sybase User Group - http://www.isug.com


Pablo Sanchez Posted on 2002-02-08 20:44:23.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 13:44:23 -0700
Lines: 30
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <g3wI5IOsBHA.298@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:786
Article PK: 94313


"Matt Rogish" <matt@fanhome.com> wrote in message
news:ePjQJ#MsBHA.298@forums.sybase.com...
> Roger,
>
> This is a major stumbling block for many of us with applications
that
> require queries spread out over multiple pages (e.g. web application
> 'pages'). I had to 'hack' in something like that with a stored
procedure
> but it relies upon a known sort order and an identity column.

I believe you're using the wrong tool to solve the problem. In our
web based application, we allow end-users to page back and forth up to
200 items at a time. What we do is have the back-end send to the
front-end a few items when browsing:

1) the first pageful of items
2) a list of PK's that reference the data to page through

The front-end caches the id's and sends them to the back-end for
paging up and down the result set. The back-end, at this point is
very efficiently pulling data by id's and sending it to the front-end.
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Available for short-term and long-term contracts


Jim Egan Posted on 2002-02-09 04:02:58.0Z
From: Jim Egan <dontspam.dbaguru@eganomics.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 21:02:58 -0700
Message-ID: <MPG.16ce4d5c1a00bb3d98badd@forums.sybase.com>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 35
NNTP-Posting-Host: 12-252-108-115.client.attbi.com 12.252.108.115
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:776
Article PK: 94304


pablo@dev.null wrote...
>
> "Matt Rogish" <matt@fanhome.com> wrote in message
> news:ePjQJ#MsBHA.298@forums.sybase.com...
> > Roger,
> >
> > This is a major stumbling block for many of us with applications
> that
> > require queries spread out over multiple pages (e.g. web application
> > 'pages'). I had to 'hack' in something like that with a stored
> procedure
> > but it relies upon a known sort order and an identity column.
>
> I believe you're using the wrong tool to solve the problem. In our
> web based application, we allow end-users to page back and forth up to
> 200 items at a time. What we do is have the back-end send to the
> front-end a few items when browsing:
>
> 1) the first pageful of items
> 2) a list of PK's that reference the data to page through
>
> The front-end caches the id's and sends them to the back-end for
> paging up and down the result set. The back-end, at this point is
> very efficiently pulling data by id's and sending it to the front-end.

Very interesting solution. Depending on my understanding of your solution it seems to be
very efficient for the database server, application server and the client. But, it may
not ensure a consistent set of pages. Which may not be a big deal anyhow. Either way,
it's still innovative.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services


Pablo Sanchez Posted on 2002-02-09 04:57:47.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com> <MPG.16ce4d5c1a00bb3d98badd@forums.sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 21:57:47 -0700
Lines: 66
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <8tHdncSsBHA.304@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:774
Article PK: 94303


"Jim Egan" <dontspam.dbaguru@eganomics.com> wrote in message
news:MPG.16ce4d5c1a00bb3d98badd@forums.sybase.com...
> pablo@dev.null wrote...
> >
> > "Matt Rogish" <matt@fanhome.com> wrote in message
> > news:ePjQJ#MsBHA.298@forums.sybase.com...
> > > Roger,
> > >
> > > This is a major stumbling block for many of us with applications
> > that
> > > require queries spread out over multiple pages (e.g. web
application
> > > 'pages'). I had to 'hack' in something like that with a stored
> > procedure
> > > but it relies upon a known sort order and an identity column.
> >
> > I believe you're using the wrong tool to solve the problem. In
our
> > web based application, we allow end-users to page back and forth
up to
> > 200 items at a time. What we do is have the back-end send to the
> > front-end a few items when browsing:
> >
> > 1) the first pageful of items
> > 2) a list of PK's that reference the data to page through
> >
> > The front-end caches the id's and sends them to the back-end for
> > paging up and down the result set. The back-end, at this point is
> > very efficiently pulling data by id's and sending it to the
front-end.
>
> Very interesting solution. Depending on my understanding of your
solution it seems to be
> very efficient for the database server, application server and the
client. But, it may
> not ensure a consistent set of pages. Which may not be a big deal
anyhow. Either way,
> it's still innovative.

Thx Jim for the kind words. You're right, as presented above, it's
possible to get the data out of order. The front-end 'knows' the
order that it requests the data, therefore when the data is returned,
with PK, the front-end can sort the result accordingly. <g>

The other thing we do is we implement the caching in the database and
the front-end simply requests from the back-end: send me the
next/prev page, here's your ID back into your 'scratch' table.

The scratch table is an optimized table:

[pad]
id
scratch_1 CHAR ...

it's optimized to minimize fragmentation and maximize the number of
rows per DB page.

As a completely irrelevant side note, we did this for an Oracle
database but it'll work just as well in Sybase.
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Available for short-term and long-term contracts


Pablo Sanchez Posted on 2002-02-08 22:51:53.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 15:51:53 -0700
Lines: 30
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <1a5WJQPsBHA.333@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:781
Article PK: 94307


"Pablo Sanchez" <pablo@dev.null> wrote in message
news:g3wI5IOsBHA.298@forums.sybase.com...
>
> "Matt Rogish" <matt@fanhome.com> wrote in message
> news:ePjQJ#MsBHA.298@forums.sybase.com...
> > Roger,
> >
> > This is a major stumbling block for many of us with applications
> that
> > require queries spread out over multiple pages (e.g. web
application
> > 'pages'). I had to 'hack' in something like that with a stored
> procedure
> > but it relies upon a known sort order and an identity column.
>
> I believe you're using the wrong tool to solve the problem. In our
> web based application, we allow end-users to page back and forth up
to
> 200 items at a time. What we do is have the back-end send to the
> front-end a few items when browsing:

Oops, typo up above... we page 20 items at a time with a max of 200
total items retrieved. <g>
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Available for short-term and long-term contracts


Matt Rogish Posted on 2002-02-08 22:08:58.0Z
From: "Matt Rogish" <rogishmn@muohio.edu>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 17:08:58 -0500
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <BmOld0OsBHA.333@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: dhcp065-031-054-014.woh.rr.com 65.31.54.14
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:785
Article PK: 94312

How do you determine what a 'page' of items is? How do you generate your
list of IDs for the next/ previous pages?

--
Matt

"Pablo Sanchez" <pablo@dev.null> wrote in message
news:g3wI5IOsBHA.298@forums.sybase.com...
> I believe you're using the wrong tool to solve the problem. In our
> web based application, we allow end-users to page back and forth up to
> 200 items at a time. What we do is have the back-end send to the
> front-end a few items when browsing:
>
> 1) the first pageful of items
> 2) a list of PK's that reference the data to page through
>
> The front-end caches the id's and sends them to the back-end for
> paging up and down the result set. The back-end, at this point is
> very efficiently pulling data by id's and sending it to the front-end.
> --
> Pablo Sanchez, High-Performance Database Engineering
> www.hpdbe.com
> Available for short-term and long-term contracts


Pablo Sanchez Posted on 2002-02-08 22:50:12.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com> <BmOld0OsBHA.333@forums.sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 15:50:12 -0700
Lines: 31
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <kSu#MPPsBHA.206@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:782
Article PK: 94310


"Matt Rogish" <rogishmn@muohio.edu> wrote in message
news:BmOld0OsBHA.333@forums.sybase.com...
> How do you determine what a 'page' of items is?

The user needs to decide how many items per page are displayed. In
our application, we've standardized on 20 items per page.

> How do you generate your
> list of IDs for the next/ previous pages?

When displaying data, you'll find that there's always one table that
is a 'driver' table. You may use it to join to other tables for
additional data, but there's typically one 'driver' table. This is
the table whose ID's you collect. You collect them after the user has
entered some type of search criteria.

For example, let's say that the end-user is browsing online jobs.
They might first enter some search criteria like 'find all jobs in
Utah.' That information is passed back to the DB, it returns the
first page of data as well as all the qualifying ID's for those jobs.

I would also put an online limit for the number of jobs that can be
retrieved when it's practical. In some situations, we limit the
number of rows retrieved to 200.
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Available for short-term and long-term contracts


Matt Rogish Posted on 2002-02-09 00:25:39.0Z
From: "Matt Rogish" <rogishmn@muohio.edu>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com> <BmOld0OsBHA.333@forums.sybase.com> <kSu#MPPsBHA.206@forums.sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 19:25:39 -0500
Lines: 44
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <XSR71AQsBHA.315@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: dhcp065-031-054-014.woh.rr.com 65.31.54.14
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:779
Article PK: 94314

I can see how your application would work; but I think we have two different
applications here with different requirements. Whilst you have the luxury
of saying "anything more than 200 will be lost" I can't say that! We have a
3 million row table which needs to be paginated -- should I send all three
million IDs to the client and say "There you go, ask me later what rows out
of the 3 mill you'd like" :) -- I can't!

As an example, you can go here:
http://baseball.fanhome.com/forums/forumdisplay.php?forumid=1

Page navigation is at the bottom (which uses the stored procedure I posted).

--
Matt

"Pablo Sanchez" <pablo@dev.null> wrote in message
news:kSu#MPPsBHA.206@forums.sybase.com...
> The user needs to decide how many items per page are displayed. In
> our application, we've standardized on 20 items per page.
>
> > How do you generate your
> > list of IDs for the next/ previous pages?
>
> When displaying data, you'll find that there's always one table that
> is a 'driver' table. You may use it to join to other tables for
> additional data, but there's typically one 'driver' table. This is
> the table whose ID's you collect. You collect them after the user has
> entered some type of search criteria.
>
> For example, let's say that the end-user is browsing online jobs.
> They might first enter some search criteria like 'find all jobs in
> Utah.' That information is passed back to the DB, it returns the
> first page of data as well as all the qualifying ID's for those jobs.
>
> I would also put an online limit for the number of jobs that can be
> retrieved when it's practical. In some situations, we limit the
> number of rows retrieved to 200.
> --
> Pablo Sanchez, High-Performance Database Engineering
> www.hpdbe.com
> Available for short-term and long-term contracts


Jim Egan Posted on 2002-02-09 04:11:32.0Z
From: Jim Egan <dontspam.dbaguru@eganomics.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 21:11:32 -0700
Message-ID: <MPG.16ce4f64eeb7e06c98bade@forums.sybase.com>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com> <BmOld0OsBHA.333@forums.sybase.com> <kSu#MPPsBHA.206@forums.sybase.com> <XSR71AQsBHA.315@forums.sybase.com>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 34
NNTP-Posting-Host: 12-252-108-115.client.attbi.com 12.252.108.115
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:775
Article PK: 94302


rogishmn@muohio.edu wrote...
> I can see how your application would work; but I think we have two different
> applications here with different requirements. Whilst you have the luxury
> of saying "anything more than 200 will be lost" I can't say that! We have a
> 3 million row table which needs to be paginated -- should I send all three
> million IDs to the client and say "There you go, ask me later what rows out
> of the 3 mill you'd like" :) -- I can't!

Here's how I see this being implemented:

1. User makes initial request and specifies X number of rows
2. Database returns X + 1 rows (using the SET ROWCOUNT X + 1 command)
3. Application server formats the first X rows and extracts the key from the X + 1 row
4. The X number or rows are returned as HTML with the key from X + 1 as a variable
5. User views the X rows and decides to look at the next set.
6. The browser passes back to the application server the key value from the X + 1 row
7. The application server creates a query with a WHERE clause that is something like this:
WHERE primary key >= (key value from row X + 1)

This is obviously incomplete. You'll have to include a variable to indicate the primary
key of the first row - 1 of the result set.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services


Pablo Sanchez Posted on 2002-02-09 03:44:49.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com> <BmOld0OsBHA.333@forums.sybase.com> <kSu#MPPsBHA.206@forums.sybase.com> <XSR71AQsBHA.315@forums.sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 20:44:49 -0700
Lines: 26
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <pNOl1zRsBHA.315@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:777
Article PK: 94305


"Matt Rogish" <rogishmn@muohio.edu> wrote in message
news:XSR71AQsBHA.315@forums.sybase.com...
> I can see how your application would work; but I think we have two
different
> applications here with different requirements. Whilst you have the
luxury
> of saying "anything more than 200 will be lost" I can't say that!
We have a
> 3 million row table which needs to be paginated -- should I send all
three
> million IDs to the client and say "There you go, ask me later what
rows out
> of the 3 mill you'd like" :) -- I can't!

You're missing the point of the solution. I'd love to help you but I
think I've given you enough free information to work on it on your
end.

Good luck!
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Available for short-term and long-term contracts


Matt Rogish Posted on 2002-02-09 20:39:21.0Z
From: "Matt Rogish" <rogishmn@muohio.edu>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com> <BmOld0OsBHA.333@forums.sybase.com> <kSu#MPPsBHA.206@forums.sybase.com> <XSR71AQsBHA.315@forums.sybase.com> <pNOl1zRsBHA.315@forums.sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Sat, 9 Feb 2002 15:39:21 -0500
Lines: 64
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <Na11EnasBHA.304@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: dhcp065-031-054-014.woh.rr.com 65.31.54.14
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:773
Article PK: 94297

Pablo,

I think I understand what you're doing..
ex.
grab first ten rows
ID
1,2,3,4,5,6,7,8,9,10

Grab next ten, starting at ID that was last fetched (e.g. 10)
> 10: 11, 12, 14, 16, <identgap> 123, 125, 555, 1222, 2344, 123123

Grab next ten, starting at 123123 (etc.)

The problem arises when your ORDER BY is not time-based (e.g. I need to
order by username let's say, which is alphabetical and the ID series is not
monotonic).

SET ROWCOUNT 10

SELECT ID, name
FROM users
ORDER BY username ASC

123, Aaron
1244, Bob
1, Joe
...
434, ZZTop

So I guess you'd need to keep passing whatever your ORDER BY was to the
stored proc and generate them that way, e.g.
Grab next 10 users
SET ROWCOUNT 10
SELECT ..
WHERE username > 'ZZTop'
ORDER BY username ASC

The only problem is -- what if the user wishes to jump right in to a
specific 'page' -- e.g. instead of going sequentially through page 1 to page
2 etc. they say "Take me directly to page 10", or "Take me to the last
page".

I understand you do this for a living and if we're entering $$ then feel
free to reply as such, maybe just a 'You're on the right track' or 'Idiot!
Cork your pie-hole!' :D

--
Matt

"Pablo Sanchez" <pablo@dev.null> wrote in message
news:pNOl1zRsBHA.315@forums.sybase.com...
> You're missing the point of the solution. I'd love to help you but I
> think I've given you enough free information to work on it on your
> end.
>
> Good luck!
> --
> Pablo Sanchez, High-Performance Database Engineering
> www.hpdbe.com
> Available for short-term and long-term contracts


Jim Egan Posted on 2002-02-09 21:44:14.0Z
From: Jim Egan <dontspam.dbaguru@eganomics.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Sat, 9 Feb 2002 14:44:14 -0700
Message-ID: <MPG.16cf461a9474afcc98bae2@forums.sybase.com>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com> <BmOld0OsBHA.333@forums.sybase.com> <kSu#MPPsBHA.206@forums.sybase.com> <XSR71AQsBHA.315@forums.sybase.com> <pNOl1zRsBHA.315@forums.sybase.com> <Na11EnasBHA.304@forums.sybase.com>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 12
NNTP-Posting-Host: 12-252-108-115.client.attbi.com 12.252.108.115
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:772
Article PK: 94299


rogishmn@muohio.edu wrote...
> The only problem is -- what if the user wishes to jump right in to a
> specific 'page' -- e.g. instead of going sequentially through page 1 to page
> 2 etc. they say "Take me directly to page 10", or "Take me to the last
> page".

For this case, keeping a list of key values in a work table on the database server would be
most efficient.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services


Matt Rogish Posted on 2002-02-09 22:00:45.0Z
From: "Matt Rogish" <rogishmn@muohio.edu>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com> <BmOld0OsBHA.333@forums.sybase.com> <kSu#MPPsBHA.206@forums.sybase.com> <XSR71AQsBHA.315@forums.sybase.com> <pNOl1zRsBHA.315@forums.sybase.com> <Na11EnasBHA.304@forums.sybase.com> <MPG.16cf461a9474afcc98bae2@forums.sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Sat, 9 Feb 2002 17:00:45 -0500
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <PTVPkUbsBHA.333@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: dhcp065-031-054-014.woh.rr.com 65.31.54.14
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:771
Article PK: 94296

I did try that and I didn't really notice an increase in performance over
the proc we're using now. Well, maybe I'm thinking of a different method
but we'd do something like this:
(not exactly but from what I can recall)
create table cache (
someid numeric( x, 0 ) identity,
pkid numeric( x, 0 ) not null,
orderbycol varchar( 50 ) not null )

But the overhead of maintaining that table became much too high (since every
new record a user would insert to the 'base' table would have to be inserted
into the cache table).

I also tried something like this:
create table #temptable (
someid numeric( x, 0 ) identity,
(other columns in the result set)
)
set rowcount @howmany * pagenumber
insert into #temptable ( select whatever from basetable order by xyz )
select * from temptable where someid between 10 and 15

But the overhead again was much too high.

--
Matt

"Jim Egan" <dontspam.dbaguru@eganomics.com> wrote in message
news:MPG.16cf461a9474afcc98bae2@forums.sybase.com...
> For this case, keeping a list of key values in a work table on the
database server would be
> most efficient.
> --
> Jim Egan [TeamSybase]
> Senior Consultant
> Sybase Professional Services


Pablo Sanchez Posted on 2002-02-11 18:55:46.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com> <BmOld0OsBHA.333@forums.sybase.com> <kSu#MPPsBHA.206@forums.sybase.com> <XSR71AQsBHA.315@forums.sybase.com> <pNOl1zRsBHA.315@forums.sybase.com> <Na11EnasBHA.304@forums.sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Mon, 11 Feb 2002 11:55:46 -0700
Lines: 123
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <gbfhQ6ysBHA.321@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:770
Article PK: 94300

[ Sorry for the late response... decided to _not_ work on the computer
this weekend! :) ]

"Matt Rogish" <rogishmn@muohio.edu> wrote in message
news:Na11EnasBHA.304@forums.sybase.com...
> Pablo,
>
> I think I understand what you're doing..
> ex.
> grab first ten rows
> ID
> 1,2,3,4,5,6,7,8,9,10
>
> Grab next ten, starting at ID that was last fetched (e.g. 10)
> > 10: 11, 12, 14, 16, <identgap> 123, 125, 555, 1222, 2344, 123123
>
> Grab next ten, starting at 123123 (etc.)
>
> The problem arises when your ORDER BY is not time-based (e.g. I need
to
> order by username let's say, which is alphabetical and the ID series
is not
> monotonic).

Err, kinda... let me try to be more explicit ... here ya go... enjoy!

Use Case
--------
Being that our system is an online system, the most number of rows
that a user will be able to review is 500 (for this example). If we
have unlimited access to rows, we can still do some of this ... more
on this below.

Example #1 - the only one that I'll list out
--------------------------------------------
1) A user will query the DB and retrieve their data based on their
user ID and a date range on screen 'query'
2) The 'query' screen will send the data to the DB and the 'view'
screen will be used to display their results.
3) The user will be allowed to view the first 20 items of the matching
values and can page back and forth as well as 'jump' to a page. A
maximum of 500 items (25 pages) will be allowed.

Technical Specification
-----------------------
%%%%%%%%%%%%%%%%%
%%% front-end %%%
%%%%%%%%%%%%%%%%%
The front-end is only responsible for storing the 'browse' ID and it
passes this back to the back-end to retrieve a pagefull of data.
Either the next page or page I.

%%%%%%%%%%%%%%%%
%%% back-end %%%
%%%%%%%%%%%%%%%%
sproc: query_db
----------------
in: search criteria,
user_id
out: first 20 qualifying rows,
browse_id

desc: Besides retrieving the first 20 rows, a max of 500 ID's are
stored in the [browse] table. Make them comma delimited for later
fetching.

sproc: by_user_retrieve_a_page
-------------------------------
in: browse_id
page_number
items_per_page
out: the qualifying rows

desc: We'll retrieve a pagefull of data for this 'user' Use the
[browse] table to retrieve the respective rows found by 'browse_id'
and walk the list via string manipulation to fish out the ID's for a
given page. Using an EXECUTE IMMEDIATE, execute a 'SELECT ... WHERE
... IN ... <values>... ORDER BY <previous order by>

Table to stash ID's
-------------------
[browse] - table with id values
id - surrogate key
id_list CHAR(X)
id_list2 CHAR(X)
user_id - who owns this row? use for cross validation
sort_order - when multiple browse rows are required and instead
of implementing a recursive relationship
browse_id - used when spanning multiple rows - set this column
even if one row is used! :)

Sample query to retrieve all values
-----------------------------------
SELECT id_list, id_list2
FROM browse
WHERE user_id = @the_id_we_think_we_are
AND browse_id = @id_from_app
ORDER BY sort_order

Unlimited Access
----------------
If you have 'unlimited access' to data. The _best_ thing to do in my
opinion is to use the Web Server to cache this data as it's retrieved.
The front-end makes the request and the WS's cache is checked. If
it's not there, the data is retrieved from the back-end. I'd suggest
that the user only be allowed to retrieve randomly within a set of
pages: for instance, 1 .. 10, then if they're on page 12, from 12 to
22...

> I understand you do this for a living and if we're entering $$ then
feel
> free to reply as such, maybe just a 'You're on the right track' or
'Idiot!
> Cork your pie-hole!' :D

I would never say it... might think it, but not say it... no no no..
that's just a joke! :)
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Available for short-term and long-term contracts


Bret Halford Posted on 2002-02-08 22:45:55.0Z
Message-ID: <3C645523.919568EE@sybase.com>
Date: Fri, 08 Feb 2002 15:45:55 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: query returning a Portion of actuall resultSet
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com> <BmOld0OsBHA.333@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 32
NNTP-Posting-Host: 157.133.80.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:783
Article PK: 94311

That would be up to the web-page designer, who might determine that
7 or 22 or 45 rows of data looks "right" on the page. So one does a query
to ASE to get all the IDs of interest, then divide it up into sets of <n>
rows,
each corresponding to a display page.

-bret

Matt Rogish wrote:

> How do you determine what a 'page' of items is? How do you generate your
> list of IDs for the next/ previous pages?
>
> --
> Matt
>
> "Pablo Sanchez" <pablo@dev.null> wrote in message
> news:g3wI5IOsBHA.298@forums.sybase.com...
> > I believe you're using the wrong tool to solve the problem. In our
> > web based application, we allow end-users to page back and forth up to
> > 200 items at a time. What we do is have the back-end send to the
> > front-end a few items when browsing:
> >
> > 1) the first pageful of items
> > 2) a list of PK's that reference the data to page through
> >
> > The front-end caches the id's and sends them to the back-end for
> > paging up and down the result set. The back-end, at this point is
> > very efficiently pulling data by id's and sending it to the front-end.
> > --
> > Pablo Sanchez, High-Performance Database Engineering
> > www.hpdbe.com
> > Available for short-term and long-term contracts


Matt Rogish Posted on 2002-02-09 00:22:46.0Z
From: "Matt Rogish" <rogishmn@muohio.edu>
References: <A47F7B57B8D3D079005686B185256B5A.005686D285256B5A@webforums> <sypH1pLsBHA.298@forums.sybase.com> <ePjQJ#MsBHA.298@forums.sybase.com> <g3wI5IOsBHA.298@forums.sybase.com> <BmOld0OsBHA.333@forums.sybase.com> <3C645523.919568EE@sybase.com>
Subject: Re: query returning a Portion of actuall resultSet
Date: Fri, 8 Feb 2002 19:22:46 -0500
Lines: 25
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <W29AP$PsBHA.304@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: dhcp065-031-054-014.woh.rr.com 65.31.54.14
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:780
Article PK: 94306

Maybe I'm not understanding what you're saying -- but in a table I have
which is 3 million rows you say that I should sent the client app all three
million IDs and then let *it* sort out which ones to display? I hardly
think that is appropriate, nor the least bit efficient.

My method has the client tell the DB "I want rows 30 through 40" (basically
paring down the result set) and then the DB returns 10 rows. (For an
example, http://baseball.fanhome.com/forums/forumdisplay.php?forumid=1 --
the page navigation is down at the bottom).

--
Matt

"Bret Halford" <bret@sybase.com> wrote in message
news:3C645523.919568EE@sybase.com...
> That would be up to the web-page designer, who might determine that
> 7 or 22 or 45 rows of data looks "right" on the page. So one does a query
> to ASE to get all the IDs of interest, then divide it up into sets of <n>
> rows,
> each corresponding to a display page.
>
> -bret