news:1162514842.612923.78500@h54g2000cwb.googlegroups.com...
> 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
>

> 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

> 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
> crb***@yahoo.com wrote:
>
>> 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

news:1162527226.924963.231510@b28g2000cwb.googlegroups.com...
> 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
>

> 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...
> -----------------------------------------------------------------------------------------------------------------------
>
> <crb***@yahoo.com> wrote in message
> news:1162527226.924963.231510@b28g2000cwb.googlegroups.com...
> > 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
> >

news:1162793695.411721.150280@h48g2000cwc.googlegroups.com...
> Hello William,
>
> I appreciate your suggestion. This is the direction towards which I am
> leaning. Are you suggesting to use a DataReader pumping data into my
> table as the user scrolls forward or conconcting a stored procedure
> that only returns a window of data at a time?
>>> A DataReader is simply  a mechanism to transport rowsets from the
>>> source. Yes, you can execute a stored procedure to return subsets (using
>>> RANK, or TOP or some WHERE clause strategy that just returns a few
>>> rows).

>
> Each approach has its advantages and disadvantages. The DataSet has to
> remain open until all the "snapshot" is copied into the DataTable.
>>> I think you mean the DataReader has to remain open. If you use Fill or
>>> Load, this can take a very short time to complete--given the number of
>>> rows I suggest.
The
> second approach is disconnected. I have found it easier to control the
> first approach, specially considering that data can be modified while I
> am paging through the table.
>>> I rarely endorse or suggest approaches that manipulate the rows as they
>>> arrive. I leave this to the SP returning the rowset or don't fetch the
>>> rows at all--I execute a SP to crunch the rows on the server and
>>> (perhaps) create a temporary table to store them.

>
> In practice, all such applications that I programed in the past
> required automatic refreshing of the DataTable. Therefore, I would fill
> my DataTable in the background (as you suggested) and, once all the
> data was copied, I would update my data via notifications (I put
> together a simple notification mechanism).

>
> Could you please clarify your suggestion? Also, what do you think of
> the approach I described? I shoud say thay my applications are not for
> the Web, thus my leaning towards a more "connected" approach.
>
> Thank you
> CD
> William (Bill) Vaughn wrote:
>> 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...
>> -----------------------------------------------------------------------------------------------------------------------
>>
>> <crb***@yahoo.com> wrote in message
>> news:1162527226.924963.231510@b28g2000cwb.googlegroups.com...
>> > 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
>> >
>