|
ADO.net Overview by Charles Carroll
I think the ADO.net approach is mind-blowing; I initially was appalled by it
until I understood the big picture -- now I love it. Bear with me. Wrapping your mind around the order of magnitude improvement requires some serious
groking (see Stranger in a Strange Land by Henlien).
The Past: The ADO way of reading
records
ASP -> ADO -> Database for example:
Open
DO UNTIL rstemp.eof
city
rstemp.movenext
LOOP
was a series of database calls. More than most people realized. Lets break it
down assuming we are fetching 300 records with 4 columns:
- The Open is +1 call
- rstemp.eof test is +300 calls
- Each field fetch is at least 1 call (1200
fetches)
- rstemp.eof is +300 calls
That loop results in 1800 database calls over
the wire (although the cachesize option reduces those calls at the price of
server memory and an expensive cursor). Which is why I fanatically recommended
Getrows and Getstring for ADO see
http://www.learnasp.com/advice/whygetrows.asp.
Also as another note:
- statements like rstemp("city")
talked to the database over the wire and returned a variant which got converted
to an appropriate type.
- statements like rstemp("projectdate").type
talked to the database over the wire and could get one of many kinds of dates (sybase dates,
oracle dates, etc.)
Many more database conversations occurred under the covers than most people
realized, see:
http://www.learnasp.com/advice/whygetrows.asp
And since COM has to hold the data and convert it
upon demand to VB6 or C++ or other languages data types (that don't agree on
what a integer or string are) here is how a dataset is stored in memory.
| What The Programmer Thought A
Recordset Is: |
COM Reality |
c1
c2 c3
r1 integer string date
r2 integer string date
r3 integer string date
r4 integer string date
r4 integer string date |
c1
c2 c3
r1 variant variant variant
r2 variant variant variant
r3 variant variant variant
r4 variant variant variant
r5 variant variant variant |
Variants of course occupy more memory than strong types and carry a
conversion burden.
The New World Order :
The ADO.net way of reading records
ASP --> ADO+ -> DataSet
Open
.fill ' transfers to dataview in a dataset
... no loop...
the dataview has the data and the connection is closed. Any conversation is now occurring with this "in memory table" we will call
a dataview of a dataset. Since it is built from .NET glue
- rstemp("city")
does NOT talk to database over the wire, it merely checks the in memory dataview and is a strongly typed variable.
- rstemp("city").type
talked to the data and could get only a strongly typed dataset data.
- The values within are strongly typed so they are light in memory. No
conversion is needed to use the data.
In fact once the data is in the dataset, the source database is now a
dataview with none of the peculiarities of the source. Heterogenous querying, sorting, filtering even works on arrays or mixed data sources since they are all
datasets. A dataview in a datasets could have been populated by:
- An Oracle table dataset
- XML files
- Access database
- Multidimensonial array
Dataviews have conveniences arrays don't normally have
-- they can filter and sorting and SQL queries can be executed against
in-memory dataviews and are strongly typed. (ASP arrays were variants with
requisite conversion penalties)
Key Point: In ADO when you asked for a field/record data item it was a
variant. In ADO.net Datasets values are strongly typed (ints, dates, strings)
so conserve memory.
In ADO a request like "tell me the fieldname" or "tell me the
field type" i.e.
rstemp("state").type
rstemp("state").definedsize
rstemp(2).name
rstemp.recordcount
resulted in database traffic and a database call.
ADO.net just would ask the dataview, and not ask the backend database.
Eliminating
calls.
Thus GetRows and Getstring are unneeded in this
environment.
One other convenience is that multiple dataviews
can live in a dataset (this eliminates all the ADO code with many recordsets
open). Data from 8 sources for example could be placed into 8 dataviews that
live in 1 dataset. But it is only 1 dataview object from a programming
standpoint. And since the dataset data is stored internally as an XML string if
it placed in caches, session or application scope it never carries the extra
baggage of a database connection. It merely carries the data.
ADO mixes formatting and code, ADO.net does NOT!
So templates can be applied to datasets, and they can be bound:
http://www.aspng.com/learn/dbtable.aspx
http://www.aspng.com/learn/columnbinding.aspx
http://www.aspng.com/learn/listbinding.aspx
http://www.aspng.com/learn/dblist.aspx
shows some "loop free" database formatting. Those examples
specifically do not use the Dataset, opting for the DataReader since none of the
dataset's capabilities (or overhead) is needed.
http://www.aspng.com/quickstart/aspplus/doc/webdatabinding.aspx
http://www.aspng.com/quickstart/aspplus/doc/webdatalist.aspx
shows many more examples of loop free database displays bound to datasets.
Heck:
http://www.aspng.com/learn/visualinheritance.aspx
even shows how the ugliness of embedded movenexts and such is solved by
presentation and code separation.
Item #3: Putting Data Back
In ADO there were a few ways to put data into a database
- .addnew stateful operations
- INSERT INTO statements
- batch updates
A few of them required explicit server cursors, locks and ugly details.Batch updates were all or nothing. Try to move back say 50 records and one
record couldn;t be updated the whole 50 item batch was rejected.
All traffic between ADO and the backend was happening constantly and was
"deep tightly coupled" code with server cursors, lock types, etc. A
low-level probe of the underlying conversation would reveal ugly details.
They were all tightly coupled to the driver and broke with driver updates, broke every time you changed backends, etc. Recordset operations/code in
general were prone to break any time the backend or driver changed.
In ADO.net each dataset row has a "rowerror" property. Anytime 1 to
n records are submitted to the backend whichever couldn't make it result in datasets
in having THOSE ROWS ONLY get a row error property.
All traffic between
ADO.net datasets and the backend is moving as XML and penetrating firewalls and other numerous benefits/side effects arise.
RDS on IE used to be communicating over Port 1443 with SQLserver. Now ADO.net
means XML through port 80 so firewalls can't interfere!
In fact ADO+ knows how to take
INPUT DATA as parameters --> ADO+ generate XML/SQL -> gives to backend
DELETE request --> ADO+ generate XML/SQL -> gives to backend
etc.
http://www.aspng.com/quickstart/aspplus/doc/webdataaccess.aspx
shows a sample of all this.
Now these datasets actually take quite a bit of work off the server. Effectively each code has a little instance of a database engine,
relationships can be established between tables/datasets, columns can be added, tables can be added on the fly.
Basically ASP code could build up a "batch" of related data
Record A
parent of record1, record2, record3
related to t3record1
and create dozens of records in multiple tables and such with
relationships, etc. without EVER talking to the backend: Oracle, SQLserver, Access. Before if you wanted to add/delete/edit many records in ADO your
code talked to backends.
In ADO+ your code talks to this table/dataset object in memory and later after all the data is layed out the data can be moved to the backend in one
step.
Your code could for example create dozens of records in dozens of tables,
modify records, delete records and no backend database activity is
occurring. Every interrogation of the
dataview/dataset (how many records are they? how many rows are related to this other table? What is the key of the record I just added?) is not a back-end
interrogation.
This is the "tip of the iceberg" (as Scott Guthrie would say) but
this does highlight some conceptual and scalability/bandwith advantages.
 |  |  |
|
There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too.
a $5 tip buys him lunch at McDonalds,
a $20 tip buys his kid Hitoshi a new computer game,
a $39 tip buys his daughter Michiko a few nice outfits.
See our donor list.
| |
 | |  |
|
|
|
|