Returning results while the query is executing

[san]

Hi,

I wanted to know if this is possible and if so, how do I
do it. Say, I have a query "SELECT * FROM Table WHERE
Column="some_value". This executes on a very large data
set and I would like to return the results as they query
executes rather than wait for the whole query to execute.
Basically, I want to get the results as they are prepared
by the database. Any way to do this?

Regards,
San

[mountain man]

"san" <sa****@hotmail.com> wrote in message
news:8e**************************@posting.google.c om...

Hi,

I wanted to know if this is possible and if so, how do I
do it. Say, I have a query "SELECT * FROM Table WHERE
Column="some_value". This executes on a very large data
set and I would like to return the results as they query
executes rather than wait for the whole query to execute.
Basically, I want to get the results as they are prepared
by the database. Any way to do this?

Regards,
San

Use a CURSOR methodology which reads each
or a group of the input rows one at a time, and if
some value is detected, writes the results to a table.

Periodically query the table for updates.
But it is slower.
Much slower.

Is this a one-off task, or is it to be queued
as a repetitive cyclic task?


--
Farmer Brown
Falls Creek
Australia
www.mountainman.com.au/software

[Erland Sommarskog]

san (sa****@hotmail.com) writes:

I wanted to know if this is possible and if so, how do I
do it. Say, I have a query "SELECT * FROM Table WHERE
Column="some_value". This executes on a very large data
set and I would like to return the results as they query
executes rather than wait for the whole query to execute.
Basically, I want to get the results as they are prepared
by the database. Any way to do this?

Since you cross-posted this to comp.databases.theory, I'm uncertain whether
you actually use MS SQL Server. In any case, this is engine dependent.

If you use MS SQL Server, you can achieve this without any special thrills.
It depends on your context, though. If you run the query from Query
Analyzer, you should have set output to text to see the rows coming in.
Results to grid and you will have to wait until all is done. If you
connect from ADO, you need to use a server-side forward-only cursor.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gert-Jan Strik]

San,

You can only get rows of the final result set. It is not possible to
peek at the intermediate results (results after each physical operator).

By default, individual rows of the result set will be sent to the client
whenever they are ready. You might be able to speed up the creation of
the first x rows by adding the hint OPTION (FAST x) where x is the
number of rows you want to have returned as fast as possible. Also,
dropping any ORDER BY clause may help.

Hope this helps,
Gert-Jan
san wrote:

Hi,

I wanted to know if this is possible and if so, how do I
do it. Say, I have a query "SELECT * FROM Table WHERE
Column="some_value". This executes on a very large data
set and I would like to return the results as they query
executes rather than wait for the whole query to execute.
Basically, I want to get the results as they are prepared
by the database. Any way to do this?

Regards,
San

[Ryan Gaffuri]

sa****@hotmail.com (san) wrote in message news:<8e**************************@posting.google. com>...

Hi,

I wanted to know if this is possible and if so, how do I
do it. Say, I have a query "SELECT * FROM Table WHERE
Column="some_value". This executes on a very large data
set and I would like to return the results as they query
executes rather than wait for the whole query to execute.
Basically, I want to get the results as they are prepared
by the database. Any way to do this?

Regards,
San

i cant speak for all implementations, but I know how to do this with
oracle. Its not 'exactly' peaking. but it has a similiar result. You
optimize to get the first few rows, then you 'page'. Its what
google.com uses when you do a search and you get that bit estimate of
hits? Downside is the query will run slower overall, so its useful
when you have alot of data and your users will look at what comes up
first, then page to the next.

I dont remember the code exactly, its on asktom.oracle.com
select /*+ FIRST_ROWS */ b.*
from ( select a.*, a.rownum
from (your query here) a
where rownum < <<pick max number of rows you want in a batch >>)
where rownum > <<will start with 1 and be max + 1 for each 'page' >>

Rownum is a pseudo-column that can be used as a counter. Its not a
real value. so you cant go:

select blah
from tab
where rownum = 200;

it counts the return value of the result set.

Id assume that many databases can do this. I know google uses oracle
to do this, Id assume other web vendors use other databases to do the
same thing.

[san]

"mountain man" <hobbit@southern_seaweed.com.op> wrote in message news:<iH********************@news-server.bigpond.net.au>...

"san" <sa****@hotmail.com> wrote in message
news:8e**************************@posting.google.c om...

Hi,

I wanted to know if this is possible and if so, how do I
do it. Say, I have a query "SELECT * FROM Table WHERE
Column="some_value". This executes on a very large data
set and I would like to return the results as they query
executes rather than wait for the whole query to execute.
Basically, I want to get the results as they are prepared
by the database. Any way to do this?

Regards,
San

Use a CURSOR methodology which reads each
or a group of the input rows one at a time, and if
some value is detected, writes the results to a table.

Periodically query the table for updates.
But it is slower.
Much slower.

Is this a one-off task, or is it to be queued
as a repetitive cyclic task?

Hi,

My question is: Are the results of the query returned as they are
processed? That is, as the database engine constructs the result it
returns them (without waiting for the rest of the results to be
generated)?

Regards,
San

[san]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

san (sa****@hotmail.com) writes:

I wanted to know if this is possible and if so, how do I
do it. Say, I have a query "SELECT * FROM Table WHERE
Column="some_value". This executes on a very large data
set and I would like to return the results as they query
executes rather than wait for the whole query to execute.
Basically, I want to get the results as they are prepared
by the database. Any way to do this?

Since you cross-posted this to comp.databases.theory, I'm uncertain whether
you actually use MS SQL Server. In any case, this is engine dependent.

If you use MS SQL Server, you can achieve this without any special thrills.
It depends on your context, though. If you run the query from Query
Analyzer, you should have set output to text to see the rows coming in.
Results to grid and you will have to wait until all is done. If you
connect from ADO, you need to use a server-side forward-only cursor.

Hi,

My question is: Are the results of the query returned as they are
processed? That is, as the database engine constructs the result it
returns them (without waiting for the rest of the results to be
generated)?

Regards,
San

[Erland Sommarskog]

san (sa****@hotmail.com) writes:

My question is: Are the results of the query returned as they are
processed? That is, as the database engine constructs the result it
returns them (without waiting for the rest of the results to be
generated)?

I believe so, although I have to admit that I have not conducted any
tests to verify that this is actually the case.

Notice also that it is likely to depend on the query.

If you say "SELECT * FROM big_tbl" you will probably get rows more or
less immediately. But if you say "SELECT * FROM big_tbl ORDER BY col",
you cannot get any rows before SQL Server has sorted the data.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[mountain man]

"san" <sa****@hotmail.com> wrote in message
news:8e**************************@posting.google.c om...

"mountain man" <hobbit@southern_seaweed.com.op> wrote in message

news:<iH********************@news-server.bigpond.net.au>...

"san" <sa****@hotmail.com> wrote in message
news:8e**************************@posting.google.c om...

Hi,

I wanted to know if this is possible and if so, how do I
do it. Say, I have a query "SELECT * FROM Table WHERE
Column="some_value". This executes on a very large data
set and I would like to return the results as they query
executes rather than wait for the whole query to execute.
Basically, I want to get the results as they are prepared
by the database. Any way to do this?

Regards,
San

Use a CURSOR methodology which reads each
or a group of the input rows one at a time, and if
some value is detected, writes the results to a table.

Periodically query the table for updates.
But it is slower.
Much slower.

Is this a one-off task, or is it to be queued
as a repetitive cyclic task?

Hi,

My question is: Are the results of the query returned as they are
processed? That is, as the database engine constructs the result it
returns them (without waiting for the rest of the results to be
generated)?

If you use a CURSOR, and select records in batches,
and write records in batches, how can it not? Have you
looked up 'cursor' in the query language documentation?

The results are returned in batches which correspond to your
use of the cursor. If you wish to see these results incrementally
accumulating then query the table being written.