Requesting a maximum amount of data from sql server

[petersb]

I'm working with an application that can hook out to sql server to retreive data. However there are a few constrains that I have to work within. the two main ones are: 1.Must call a stored procedure, cannot pass sql directly. 2. Can only return a single row. The second is by far the most restrictive.

So what im trying to acheive is to retreive data row by row rather than as a dataset. Is there a way to do this?

Thanks in advance

 

[jbenson001]

So what im trying to acheive is to retreive data row by row rather than as a dataset. Is there a way to do this?

What do you mean by this? You say dataset, are you referring to visual studio?

JIm

 

[gmmastros]

Who makes up the rules?

In my opinion, rule number 1 is good (must use stored procedure).

Rule 2 is not very good. When dealing with the database, think of 'round trips' as being expensive in terms of performance. My definition of a 'round trip' is... You request data (request is sent through network to database), and the db replies (again, back through network to client app).

You want to minimize the round trips. You do this by requesting all the data you need. Do NOT request more data than you need because you don't need it, and it will only slow you down. This means, filter the data with a where clause, and only return the fields you need.

I would question rule number 2. It's not practical.

Unless....
Rule number 2 applies to the ADO recordset object!?!
If you use a server side cursor (not the same thing as a SQL Server Cursor), you can set the ADO CacheSize property so that the recordset is stored on the server, and you only retrieve records from the server when you need to.

To quote Microsoft help...

CacheSize Property

Indicates the number of records from a Recordset object that are cached locally in memory.

Settings and Return Values

Sets or returns a Long value that must be greater than 0. Default is 1.

Remarks

Use the CacheSize property to control how many records the provider keeps in its buffer.....

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom