Fetching results across page loads

[google]

I would like to find a way to execute a query against an MS SQL server
and return a small number of rows each page load. However, I cannot
read all the results in during the first page load due to size
contraints.

Here are the details. A user submits a request. I construct a SQL query
and execute on the MS SQL server. The query results in a million rows.
The initial page load shows the first 100 rows. Each successive page
load shows the next 100 rows. Because our web server has limited disk
space, I do not want to return the entire result set now, but only 100
rows at a time. Also, for performance reasons, I do not want to rerun
the query each page load.

So, I need a "persistent result" set that can be fetched from across
page loads. I have tried to store the result set in a session variable
using serialize and unserialize, but to no avail. It seems that across
page loads the result set loses its way, even with a persistent
database connection.

Is this kind of peristent result set possible?

Thanks.

[Geoff Berrow]

I noticed that Message-ID:
<11*********************@f14g2000cwb.googlegroups. com> from
go****@macrotex.net contained the following:

Here are the details. A user submits a request. I construct a SQL query
and execute on the MS SQL server. The query results in a million rows.
The initial page load shows the first 100 rows. Each successive page
load shows the next 100 rows. Because our web server has limited disk
space, I do not want to return the entire result set now, but only 100
rows at a time. Also, for performance reasons, I do not want to rerun
the query each page load.

Why not? That's what a dbms is for.

Use LIMIT

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Chung Leong]

"Geoff Berrow" <bl******@ckdog.co.uk> wrote in message
news:10********************************@4ax.com...

I noticed that Message-ID:
<11*********************@f14g2000cwb.googlegroups. com> from
go****@macrotex.net contained the following:

Here are the details. A user submits a request. I construct a SQL query
and execute on the MS SQL server. The query results in a million rows.
The initial page load shows the first 100 rows. Each successive page
load shows the next 100 rows. Because our web server has limited disk
space, I do not want to return the entire result set now, but only 100
rows at a time. Also, for performance reasons, I do not want to rerun
the query each page load.

Why not? That's what a dbms is for.

Use LIMIT

AFAIK, MS SQL doesn't support the "LIMIT rowcount, offset" construct. The
recommended method, I think, is to place the recordset into a temporary
table with an identity column and retrieve the subset from there. I usually
use mssql_data_seek(), although I think the function moves forward by
pulling all the prior rows through the pipe.

[Geoff Berrow]

I noticed that Message-ID: <U7********************@comcast.com> from
Chung Leong contained the following:

Use LIMIT

AFAIK, MS SQL doesn't support the "LIMIT rowcount, offset" construct.

Must get some reading glasses. I read that as MySql.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Colin McKinnon]

Geoff Berrow wrote:

I noticed that Message-ID: <U7********************@comcast.com> from
Chung Leong contained the following:

Use LIMIT

AFAIK, MS SQL doesn't support the "LIMIT rowcount, offset" construct.

Must get some reading glasses. I read that as MySql.

MS SQL server does support a ROWCOUNT directive which combined with sorting
and remembering state can be used to simulate the behaviour.

C.

[Chung Leong]

"Colin McKinnon" <co**************@andthis.mms3.com> wrote in message
news:d3*******************@news.demon.co.uk...

MS SQL server does support a ROWCOUNT directive which combined with sorting and remembering state can be used to simulate the behaviour.

That sub-query technique is a pain to implement though, since you have to
invert the order by clause. I've found that is acceptable performance-wise
to retrieve the primary key in one query and use them in the where clause in
a second query. Skipping through a few thousands integers isn't as bad as
skipping through thousands of rows with actual data.

[Greyhawkes]

Chung Leong wrote:

MS SQL server does support a ROWCOUNT directive which combined with
sorting and remembering state can be used to simulate the behaviour.

That sub-query technique is a pain to implement though, since you have to
invert the order by clause. I've found that is acceptable performance-wise
to retrieve the primary key in one query and use them in the where clause in
a second query. Skipping through a few thousands integers isn't as bad as
skipping through thousands of rows with actual data.

Can you give a brief example of how you would implement this?

Be well,
Steve