Transcript DAT336 Connected vs Disconnected Data Access in

DAT336
Connected vs Disconnected
Data Access in ADO.NET
Pablo Castro
Program Manager – ADO.NET Team
Microsoft Corporation
Agenda
Disconnected is good
Some connected bits can help
Balancing connected/disconnected
Scrolling and streaming
Custom aggregation
Incremental load
Summary
Disconnected?
In the context of this talk:
“Disconnected” apps are online
applications that don’t keep database
connections open for long periods of time
such as the lifetime of a session
Usually 3-tier applications
We’re not going to discuss “off-line”
applications where a user can operate an
application even if the server is not
available
Disconnected is Good
Simpler to design
3-tier applications follow the
disconnected model naturally
Simpler to code once the pieces are set
i.e. data binding, marshalling in 3-tier
apps, no connection management for
long-lived business objects
Simpler to make apps scale
Middle-tier layer easy to scale-out
It’s harder to scale-up/out database servers
Disconnected ADO.NET
ADO.NET has first-class disconnected
apps support
DataSet as a relational data cache
Plays well with remoting & web services
Single implementation, no database-specific
behavior
DataAdapter/DataSet provide services for
getting, updating and merging data
Custom types for non-relational
…but too disconnected…
Handling large volumes of data
Batch processing
Reporting
Custom aggregation
Avoid buffering large
portions of data
Avoid data-shipping costs
Performance-sensitive code-paths
ASP.NET pages in apps without heavy
business logic
Gateway applications
Discrete Objects
Scenarios
This is the “easy part”
Stand-alone business objects
Business object state representation
DataSets – ADO.NET helps with retrieval,
update and change tracking
Custom objects – model your business
entity state using regular classes
Remoting or webservices enable this
scenario easily
Sessions in the Middle Tier
Scenarios
Per-session state can’t be avoided
sometimes
If needed, keep only in-memory state
No database connections if at all possible
Quite common in ASP/ASP.NET apps
“Sticky sessions” take care of scale-out
So it’s not that bad if you don’t keep
connections or other external resources
Scrolling and Streaming
Scenarios
In general, handling large results in
pieces
UI: scrolling, paging
Batch processing: chunking, scanning
large results
Custom aggregation
Options change depending on each
case
Scrolling, Paging
Goal is to fetch rows from a large
result, a few at a time
Cursors
DataAdapter.Fill method
SQL-based solutions
Scrolling, Paging: Cursors
Not available in all databases
Provide scrolling support
Sometimes even bi-directional
No need for extra logic in the application
Scalability issues
Require to maintain state
Database connections, keyset or temporary
tables in the database
Cursor escalation
Server may need to materialize some/all data
Scrolling, Paging: Cursors
Design issues
Hard to include in 3-tier applications
Need to keep connection/cursor objects
alive in middle tier
How is data propagated to the
presentation layer?
Tends to be a chatty interface
Result stability
Need to use transactions or static cursors
if stability is required
Scrolling, Paging: Fill()
ADO.NET DataAdapter.Fill() method
There’s an overload that takes first row
and number of rows
Under the covers, this method:
Skips rows as needed
Copies as many rows as requested to the
target DataTable
Scans and discards the rest of the rows
 Don’t use it for paging in large
result-sets
Scrolling, Paging: SQL
If at all possible, use SQL constructs
for paging
Stored-procedures
if you know the table schema and sort order
SQL can help in other cases
where the query is not known but constrained
Scalability/performance issues
Time taken to execute query is not
amortized across requests
Scrolling, Paging: SQL
Design issues
Fits nicely for 3-tier apps
Each page is an independent database
operation
No state held between hits
Ad-hoc queries are hard to handle
Result stability
Need to use transactions if stability is
required
Streaming
Goal is to handle very large result-sets
No buffering proportional to size of data
Concurrency issues
Interleaving
Common scenarios for streaming
Batch processing
Reporting
Custom aggregation
Streaming: DataReaders
ADO.NET providers have an streaming
interface
DataReader class exposes a row at a time
Some minor buffering might happen internally
Can scan millions of rows without taking
much resources
Scalability issues
Usually large scans happen in batch
processes
Not many at the same time
Streaming: DataReaders
Design issues
DataReaders cannot be marshaled across
tiers
Move the batch process code to the middle
tier
Send the data in chunks to the next tier (too
much overhead in most cases)
Contention can be high
Result stability
Depends on the isolation level
Custom Aggregate Logic
Similar case: scan lots of rows
But end-result is a small result-set
No need to ship the data out of the
server
Cursors can help here
Scan and aggregate inside the server
Ship only the aggregated information to
the client
Avoids moving lots of data across tiers
Incremental Load
Scenarios
Present first bit of data in UI quick
Incrementally load the rest in the
background
DataSet merge support is great here
Be aware of multi-threading issues
Incremental Load & Merge
ADO.NET DataSet can merge results
Incremental load UI
Chunking API in the middle tier
Bring down the first DataSet and display it
As more data comes, merge the DataSets
and update UI
This even preserves changes in existing data
Multi-threading issues
DataSet is not thread-safe
Same for WinForms UI controls
Summary
Disconnected is good
ADO.NET has great support for it
Good to start disconnected by default
You will need some connected pieces
ADO.NET also helps there
You can add connected parts as needed
Extremes can hurt your app
performance or scalability
Attend a free chat or web cast
http://www.microsoft.com/communities/chats/default.mspx
http://www.microsoft.com/usa/webcasts/default.asp
List of newsgroups
http://communities2.microsoft.com/
communities/newsgroups/en-us/default.aspx
MS Community Sites
http://www.microsoft.com/communities/default.mspx
Locate Local User Groups
http://www.microsoft.com/communities/usergroups/default.mspx
Community sites
http://www.microsoft.com/communities/related/default.mspx
© 2004 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.