Get data from select query before completing the execution

Question

Post reply

Get data from select query before completing the execution

jitumevada

SSC Veteran

Points: 289
More actions
December 21, 2007 at 5:47 am

#182156

I have one select query. Which is returning 1000 rows.It takes 20 min. But I dont want all rows simultaneously.
Is it possible that select query return row to screen as it is fetched.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dragos_sv Default port Points: 1478 More actions · Answer 1

are you using QA ? if 'yes' pls go to options -> results and check 'scroll results as received'

toniupstny Hall of Fame Points: 3986 More actions · Answer 2

You could try the FAST n option for Select

For cursor....

SQL: sp_configure cursor threshold for SQL cursors can allow async fetch http://msdn2.microsoft.com/en-us/library/aa172570(SQL.80).aspx

EXEC sp_configure 'show advanced options',1 -- allow change of advanced options (BE CAREFUL)

RECONFIGURE WITH OVERRIDE

-- reconfigure 'cursor threshold' and force an asynch cursor

EXEC sp_configure 'cursor threshold', 100 -- Asynchronous for cursors > 100 rows

RECONFIGURE WITH OVERRIDE

-- do your async operations here

EXEC sp_configure 'cursor threshold', -1 -- Back to synchronous

RECONFIGURE WITH OVERRIDE

Toni

toniupstny Hall of Fame Points: 3986 More actions · Answer 3

You could also try to split the query into multiples so you get an interim result set. This requires ordering the results so you get consistent output (also the TOP clause requires Order by).

The example below has the first query to get the first 10 rows and the second query then goes after the rest of the data (I set the second top nn to a number higher than the number of rows in the data).

You could repeat this multiple times to get more records each time. (get the next 10 not in the top 20... then the next 10 not in the top 30... and on and on).

use northwind

go

select top 10 with ties orderid,productid

from [order details]

order by orderid

select top 10000 with ties orderid,productid

from [order details]

where orderid not in

(select top 10 with ties orderid

from [order details]

order by orderid)

order by orderid

I think this is what you are looking for?

Toni

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

jitumevada (12/21/2007)
I have one select query. Which is returning 1000 rows.It takes 20 min. But I dont want all rows simultaneously.
Is it possible that select query return row to screen as it is fetched.

The real problem is not returning rows early... the real problem is that the code takes 20 minutes to return only 1000 rows... My recommendation would be to fix the code to perform better... a lot better... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

toniupstny Hall of Fame Points: 3986 More actions · Answer 5

I agree 20 minutes for a thousand rows is a lot of time. If they would post the query, some data, schema information, etc then people could look to see if they could help speed the query itself. From what was asked for there is little to go on.

Toni