Re: Paging recordsets in ado.net

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?

Each approach has its advantages and disadvantages. The DataSet has to
remain open until all the "snapshot" is copied into the DataTable. 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.

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...
-----------------------------------------------------------------------------------------------------------------------

<crbd98@xxxxxxxxx> wrote in message
news:1162527226.924963.231510@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Cannot connect to Server
    ... Test 1 - the clients can ping one anotehr ... gpupdate/force - reboot both client and server - ping to server from client ... Suggestion 2 - ping to server works when running safe mode with networking ...
    (microsoft.public.windows.server.sbs)
  • RE: Mail for one particular recipient keeps getting stuck in the outgoing queue
    ... Suggestion 1: Checking Recipient Policies ... and then click Recipient Policies. ... Click the E-Mail Addresses tab. ... Servers\ServerName\Protocols\SMTP\Default SMTP virtual server. ...
    (microsoft.public.exchange.admin)
  • Re: Run-time error 9 while processing winsock received data
    ... otherwise the third suggestion is to dump VB at least ... > server uses when sending dynamically generated content. ... > communcation error truncating the data stream. ... > that Winsock cannot detect to raise an error event. ...
    (microsoft.public.win32.programmer.networks)
  • Re: Mailing problem - update what?
    ... Turned out to be your suggestion 6 that seemed to do the ... the SMTP server that I'm specifying is the company Exchange ... On the Access tab, click the Relay ...
    (microsoft.public.dotnet.framework.windowsforms)
  • RE: Internet timeouts on workstations
    ... The client systems in question are all brand new installs of Windows XP ... > Suggestion - Clean Settings in Internet Explorer: ... On the SBS Server and one of the problematic client ... Ethernet adapter Server Local Area Connection: ...
    (microsoft.public.windows.server.sbs)
Loading