Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
karen
Starting Member
1 Post |
 Posted - 2002-01-11 : 11:32:03
|
| "Additionally, unfetched rows from a default result set can cause SQL Server to hold locks at the server, possibly preventing other users from updating" - this is quote from article Optimizing Application Performance Using Efficient Data Retrieval in sql books online.Why would SQL server holds locks for a Deafult Result set? Our understanding is that default result set is forward only , read only and full result set returned to client machine . Surely fetchs should then go against the result set on client machine and require not further call at all to the server? "A default result set always requires only one roundtrip between client and server" is another quote from same article whihc indicates no further calls needed .Perhaps i'm missing something . All info wlecome. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-11 : 12:14:00
|
| I believe what they are saying is that if the result set is too large for the client to buffer, SQL Server will hold locks on those unsent rows until the app fetches enough rows for SQL to send the entire remaining result set to the client (1 packet at a time).HTH-Chad |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-11 : 13:15:08
|
quote:
Our understanding is that default result set is forward only , read only and full result set returned to client machine. Surely fetchs should then go against the result set on client machine and require not further call at all to the server?
That depends on how you are accessing the data. ADO uses those defaults, but another data access method may not (ODBC, DAO, JDBC, etc.) You can always override these settings as well, and completely change this behavior. SQL Server only communicates with the client via the data access layer, it doesn't implement or change how the data access layer operates. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-11 : 13:28:47
|
| If you always work with disconnected record sets it ensures that you are not holding any locks due to cursor fetching.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Locks held on rows of Result set not fetched