Paging recordsets in ado.net

[crbd98]

Hello All,

I am porting a ado application to ADO.NET and facing some challenges.

In particular, we have some client side views that need to display
large recordsets in a grid. The approach uses a server side cursor
combined with a predefined cache size that accomodates to allow for
pagination of the results. This was done by using the CursorLocation
and the CacheSize options on the recordset.

How can implement a similar behavior in .NET? My understanding is that
the ADO.NET DataReader is a forward only, server side cursor. The
DataSet is static, client side. I would like to have a fast forward and
server side, cursor with a CacheSize of 100, for example.

Any help or suggestion would be greatly appreciated,

Cheers
CD

 

[William \(Bill\) Vaughn]

The DataReader is not really a "cursor"--it's a data stream generated
directly from the (small) cache of rows selected for the resultset. As you
know, it's not scrollable. There is no ADO.NET equivalent of an ADO classic
server-side cursor where the keys or static rows are generated on the server
and available for scrolling. In Appendix III of my new book I show how to
create an ANSI server-side cursor which can emulate some of the traditional
server-side cursor functionality, but it's not cached--that would be up to
your code. A dated version of this appendix can be found at
http://www.betav.com/Files/Content/whitepapers.htm.

The real question is, why do you need so many rows on the client? How many
clients do you expect to support with this approach--not many I assume.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[crbd98]

Hello William,

Thank you for your reply.

The reason that I need this paged approach is to reduce network
"bursts". In my application, one of the views can display a lot of
items. In its first version, which I wrote using ado and c++, I used a
the Recordset, which by default uses a client side cursor. Due to the
size of the resultset, I would experience some network contention that
resulted in side effects in other applications running on the same
client (long story). Changing the CursorLocation to client avoided the
contention but made the operation take (even) longer because now there
was a rondtrip between the client and the server every time I performed
a "next". I found a medium ground by setting the cache size property in
my recordset to some reasonable number (e.g. 500). As a result, I would
only get a little "burst" of data for every 500 rows. This has worked
well for me.

Since then, I reworked my ui view. Instead of FULLY populating the list
control that I use in ui, I now fill it in the background so the user
can see some data (and even scroll) while the resultset is being
processed. I still use a server side cursor with a cache size set to a
multiple of my display window has proven to be really effective in
reducing the size of the network busts.

Do you have any suggestion of way to present this data?

Best regards
- CD

 

[Grant]

You might want to take a look at Infralution's Virtual Data Objects
library. This allows you to use standard ADODB recordsets and bind
standard .NET controls to them. You can use either client or server
side cursors. This allows you to browse very large recordsets without
the mermory and performance overhead involved in ADO.NET datasets.

You can get more information and download an evaluation version from:

www.infralution.com/virtualdata.html

Regards
Grant Frisken
Infralution

 

[William \(Bill\) Vaughn]

Consider that a client-side DataTable is really a cache of N rows. I suggest
you fetch enough to keep the user's interest (24-50 rows) but no more. In
the background you can execute async queries to fetch the next set of rows
to append to the local table and repeat the process as the user scrolls
forward. If they do a "search", start over I discuss these approaches in my
new book. Having the rows held in a server-side cursor impacts scalability
and performance as the rows have to be spooled out to TempDB or consume RAM
from the server-side cache.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...