Dataset with large tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please someone explain this to me ! From what I understand ADO.NET is
disconnected. Ok. I have Windows app with a student table with about 15000
students. In ADO I would open a server side cursor and page thru the table in
a custom grid I built. So at most I would retrieve about 20 records or so per
page (depending on no of displayable rows). Now with ADO.NET I will have to
send the whole 15000 records
to the client in a DataSet. How can this be good ? I just don't get how
everyone claims server side cursor slow down an app. In ADO I retrieve 20
records at a time in a grid and it's instataneous. In .NET it's slowwwwwww.
This is just one example but my database is extremely large (over 2000 SQL
server tables). If I have many users opening the student module at the same
time, they have to wait and wait to get those 15000 records when I only need
an initial set of records to display the first page... I just don't
understand how real systems are done in a disconnected manner.
 
Thanks for the response. But I am already aware of the DataReader. The
DataReader can't be used to browse up and down in a data grid.
 
Ah, yes. The server-side cursor you're fond of can still be created, but
most suggest that you don't use it. What we do recommend is to use the
disconnected DataSet as a scrollable cache--not a duplicate copy of the
entire table's rows. This means a "JIT-connected" approach. So you have an
application that needs to manage 15,000 rows. Let the user query on the
rows, but return about 50 at a time--a screen-full and a buffer. If they
don't find what they want, scroll down to the buffer or rerun the query to
get another (more focused) subset. If they scroll down, fetch the next 25 or
so. This way the response time is fast and the impact on the system is low.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Finally, someone who understands ! But, how do I do this ? I have a grid with
20 displayable rows. If the user scrolls down or up by 1 or if they do page
up or page down or resize the grid - it's not obvious to me how to run
queries to get the only the records needed and in the order specified in the
ORDER BY. Do you have any suggestions ?
 
When I build data structures (products of several tables) or individual
tables that will be scrolled I include a "scrolling" index that permits easy
retrieval of the "next" or "nth" set of rows. With the scrolling index I can
query:

SELECT TOP 50 col, col1, col2 FROM myTableOrView WHERE ScrollIndex >
LastValueFetched

Ordering the rows can often be done more quickly on the client--assuming the
rows are there in the first place.

I discuss this approach in my ADO books... (at least most of them).
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
My Student table has 11 indexes which are used for various sorts which can be
selected by the user. How can I properly retrieve the next n records based on
the sort the user selects ? I can't do it client side as I would need all the
records. The scroll index you mention would let me retrieve only a certain
number of records but the scroll index would have to reflect the sort order.
I don't see how this can work.

Also, what are the titles of the books you mention ?
 
In SQL Server, the query optimizer chooses the right index based on how the
query is written. However, if you're just choosing a collation sequence
(sort order)If a student chooses a particular sort order this can be
appended to the ORDER BY clause in any combination. The trick is to design
the database and its tables, indexes and views around a scalable data access
approach from the onset. This has been done countless times. Again, once the
data has been selected, the client-side methods in ADO.NET (DataView) permit
you to sort without requerying. For example, if I'm looking for a lawyer, I
would first SELECT those lawyers that meet the criteria: Location:Local to
Redmond, Specialty:Copyright law, Type: Honest. This gives me a reasonable
number of rows to work with. After that the sort is easy and can be done on
the client in a heartbeat. However, if I pull all the lawyers in the
database to the client, we have to basically transmit the entire set of
tables to the client and create a workable subset there. While this works
for home databases, it does not work in a business environment--it simply
won't perform well enough or scale beyond a few light users.

See www.betav.com for a list of my books.

--
____________________________________
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.
__________________________________
 
First, Thanks alot for you input.

My app (which is currently VB 6.0 + ADO) has 200+ concurrent users. The
first thing most of these users do in the morning is open the Student module.
They might use a name sort, student no sort...The grid displays the students
which they can page thru and search. In ADO with server cursors, this works
extremely well. In a test version using ADO.NET and providing the same
functionality, the app slows to a crawl after about 20 users. So from what
you are saying, in ADO.NET providing the same functionality is impossible. I
just don't get how something which used to be so simple and a non-issue
before can become like this. And I haven't even got into locking issues -
there is none in ADO.NET since it's disconnected ! You can't develop real
applications with many concurrent users without locking - it's crazy.

Anyways, thanks for your help.
 
Hi,
In short, ADO.NET is designed mainly for web applications. It is not so
good for desktop applications at all. Do not expect ADO.NET will work well
with desktop applications. The huge drawback of ADO.NET can not support
scrollable server cursor because of its design.
Fortuanetly, we have ported OleDBPro into OleDBProNet, a highly reusable
module for developing high performance desktop and middle tier software on
Microsoft .NET platform version 2 using classical data accessing method
through MS OLEDB technology. It is a feature-rich, and extremely powerful
data access solution for dotNet programmers. It works very well with large
tables through all types of server cursors for all of data sources.


--
Yuancai (Charlie) Ye

Fast accessing all of remote data sources anywhere with SocketPro using
batch/queue, non-blocking and parallel computation
See 30 well-tested and real OLEDB examples
at http://www.udaparts.com
 
Back
Top