0

As mentioned in title. I wanna know that does SQL Server can partially execute a SQL like oracle do? means server wait client to process the data in last network packet and then fill next packet I noticed that when you execute a select statement in SQLServer Management studio, you can get the first rows immediately while the statement is still running. But I cannot get this in ADO component. All I can think about is use server-side cursor and use non-keyset type cursor. But ADO use sp_cursoropen and sp_cursorfetch to do this. Not using cache size like sql*plus do. It mains it is client side behavier, not like the phenomenon in SSMS. I want know why.

Improve this question
2
  • how do u "partially execute a SQL"? Commented Jun 28, 2011 at 19:18
  • Just like what Dave Costa said:"Oracle will do just enough work to fill the fetch array once and send it to the client. It will not proceed with further execution of the query until the client processes all the rows it has been sent and sends another fetch to the database" ^_^ Commented Jun 29, 2011 at 0:17

2 Answers 2

Reset to default
1

It appears that the corresponding feature in SQL Server is known as Array Fetch Size. (Discussed here, for example.)

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

The closest feature I can find controls the network packet size, but obviously that won't be an exact ratio to the number of rows returned in each packet.
I read the artical you referred days ago, and I donot think it is the same as "array size" in sql*plus. as a reference posted here ,it sames that array fetch size only affects network packet while sqlserver donot stop execute while waiting client processing last packet. Am I right?
@chenwq: Reading the reference you linked, I can't tell whether you're right or not. I don't think it says anything about whether the server continues processing once the fetch array has been filled.
That's what I'm talking about. I donot have any evidence to prove what indeed sqlserver does.
Like the questiion after I edited, if i donot use serverside cursor, Array Fetch Size, aka. Cache Size does not make ADO dataset return with first rows immediately. And sp_cursoropen/sp_cursorfetch is not what I want.
1

Are you referring to SET ARRAYSIZE, an SQL*Plus command?

If so, an analogous setting might be SET ROWCOUNT. There may be some subtleties I'm missing here though, since I don't use Oracle on a daily basis.

If I've guessed wrong, then can you expand on what "Array Size" does, since I can't find any hits that seem relevant when searching for Oracle Array Size.

Actually, re-reading stuff I can find about set arraysize in SQL*Plus, it seems to be quite different from ROWCOUNT. But the description (which seems to control how many rows are returned in each round trip, but not limiting the overall number of rows) doesn't seem to match your description "partially execute a SQL like oracle". I can't think of anything off the top of my head that would control the batching of results returned to the client.

Improve this answer

3 Comments

I don't think ROWCOUNT is analagous, as it appears to stop the query entirely after the given number of rows is fetched. The Oracle array size represents the number of rows that are fetched at one time before returning results to the client, but it does not cause the query to stop when the fetch array is full. The general purpose of arraysize is to improve network efficiency by controlling the amount of data that will be sent from the server to the client at one time.
@Dave - I was just updating my answer based on coming to the same conclusion :-|
just FYI, I think what the OP means by "partially execute" is that, if the query execution plan is of a type that can produce some result rows without having to be completely executed, then Oracle will do just enough work to fill the fetch array once and send it to the client. It will not proceed with further execution of the query until the client processes all the rows it has been sent and sends another fetch to the database. The client could choose to terminate execution of the query before fetching all the records.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.