Monday, March 19, 2012

Getting resuls asynchronous from SQL Server

Hi,
Suppose I have following T-SQL statements :
INSERT #T SELECT ... -- takes c. 10 minutes
SELECT * FROM #T
-- Calculation in cycle over #T
-- on end of cycle will be inserted new row to #NextTable
-- end of cycle
SELECT * FROM #NextTable;
All this I run as batch.
Can I get results SQL when batch is still running but
SELECT * FROM #T
is already executed ?
And after finishing batch SQL will return only rest of results ?
Something like IIS (Internet Information Server) when returns big page to
client. It starts return page while page is still executing.
I don't want to divide this batch to multiple queries because then I would
must pass several megabytes to and from these queries multiple times. So if
it is in single batch I reduce roundtrips of data over 100 MB from SQL serev
r.Hi
If you want a paging feature then check out:
http://www.aspfaq.com/show.asp?id=2120
John
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:6411E1E1-1DD1-4110-8C60-5DE9B9C4453D@.microsoft.com...
> Hi,
> Suppose I have following T-SQL statements :
> INSERT #T SELECT ... -- takes c. 10 minutes
> SELECT * FROM #T
> -- Calculation in cycle over #T
> -- on end of cycle will be inserted new row to #NextTable
> -- end of cycle
> SELECT * FROM #NextTable;
> All this I run as batch.
> Can I get results SQL when batch is still running but
> SELECT * FROM #T
> is already executed ?
> And after finishing batch SQL will return only rest of results ?
> Something like IIS (Internet Information Server) when returns big page to
> client. It starts return page while page is still executing.
> I don't want to divide this batch to multiple queries because then I would
> must pass several megabytes to and from these queries multiple times. So
> if
> it is in single batch I reduce roundtrips of data over 100 MB from SQL
> serevr.|||I do not want to divide single select but I want to get resuls immediately
when SQL server process statement and not whole batch.
"John Bell" wrote:

> Hi
> If you want a paging feature then check out:
> http://www.aspfaq.com/show.asp?id=2120
> John
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:6411E1E1-1DD1-4110-8C60-5DE9B9C4453D@.microsoft.com...
>
>|||Using the FAST n will optimize the query to return the first n row as
quickly as it can.
John
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:7C66CCAC-5E71-45D5-8F6E-D04FDD4756F9@.microsoft.com...
>I do not want to divide single select but I want to get resuls immediately
> when SQL server process statement and not whole batch.
> "John Bell" wrote:
>|||B.J. wrote:
> I do not want to divide single select but I want to get resuls
> immediately when SQL server process statement and not whole batch.
>
Yes, you can use the FAST x query hint hto have SQL Server generate the
first X rows in the result set as quickly as possible at the expense of
full query optimization.
Select col1
From TableA OPTION (FAST 100)
But asynchronous execution is the domain of the client library and not
SQL Server. .Net does not include async calls (if I'm not mistaken). If
you're using ADO, you can issue an async execute and you should be able
to get results back faster.\
I've never tested the FAST option myself.
David Gugick
Imceda Software
www.imceda.com

No comments:

Post a Comment