Recordset vs XMLDOM performance

[Ben]

I am in the process of re-writing an .ASP to replace some
recordset processing with a DOM object, and performing a
transformation to present the data. My initial objective
was to clean up the code, and further separate the logic
from the presentation.

Is there also a performance benefit from moving data
manipulation away from ADO and instead traversing through
a DOM object containing the same data? Is there a
performance hit?

[Bob Barrows]

Ben wrote:

> I am in the process of re-writing an .ASP to replace some
> recordset processing with a DOM object, and performing a
> transformation to present the data. My initial objective
> was to clean up the code, and further separate the logic
> from the presentation.
>
> Is there also a performance benefit from moving data
> manipulation away from ADO and instead traversing through
> a DOM object containing the same data? Is there a
> performance hit?

It totally depends on your situation: how many fields and records you have.
FWIW, a recordset is a heavy object with a significant amount of overhead
depending on which features you enabled via the cursorlocation, cursortype
and locktype properties. So navigating through it will be many times slower
than navigating through, say, an array. An XML DOM, while still an object,
is much less heavy than a recordset. So it has that in its favor. Balancing
that however, is the fact that extracting data from it involves much string
parsing and manipulation.

Bottom line: we can't give you a definitive answer. You will have to test
and see which method servers your needs.

Bob Barrows

[Terry Murray]

I would like to know what are the default features of an ADO recordset,
particularly as they apply to Microsoft access, and how they can be
modified.

Thanks
Terry

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%2386c5g2hDHA.2400@TK2MSFTNGP11.phx.gbl...

> Ben wrote:

> > I am in the process of re-writing an .ASP to replace some
> > recordset processing with a DOM object, and performing a
> > transformation to present the data. My initial objective
> > was to clean up the code, and further separate the logic
> > from the presentation.
> >
> > Is there also a performance benefit from moving data
> > manipulation away from ADO and instead traversing through
> > a DOM object containing the same data? Is there a
> > performance hit?

>
> It totally depends on your situation: how many fields and records you

have.

> FWIW, a recordset is a heavy object with a significant amount of overhead
> depending on which features you enabled via the cursorlocation, cursortype
> and locktype properties. So navigating through it will be many times

slower

> than navigating through, say, an array. An XML DOM, while still an object,
> is much less heavy than a recordset. So it has that in its favor.

Balancing

> that however, is the fact that extracting data from it involves much

string

> parsing and manipulation.
>
> Bottom line: we can't give you a definitive answer. You will have to test
> and see which method servers your needs.
>
> Bob Barrows
>
>

[Bob Barrows]

>-----Original Message-----
>I would like to know what are the default features of an

ADO recordset,

>particularly as they apply to Microsoft access, and how

they can be

>modified.
>

You can go here to get more detail:
[url]http://msdn.microsoft.com/library/en-[/url]
us/ado270/htm/mdaobj01_19.asp

This page lists the cursor types in order from most
expensive to least expensive. You should rarely use a
keyset or dynamic cursor in an asp page.


The default cursor that you will get if you open a
recordset without changing any properties is a server-
side, forward-only cursor. The following pieces of code
are equivalent:

**************************************************
dim rs,cn
'create and open a connection using the cn variable
set rs = cn.execute("select ...",,1)

or

dim rs,cn
'create and open a connection using the cn variable
set rs = server.createobject("adodb.recordset)
rs.open "select ...", cn
**************************************************

Both examples will result in a server-side forwardonly
cursor, which is the cheapest and fastest. If you want
more control over the features (cursor-type, etc.) you
need to use the second syntax: the execute method always
returns the default cursor. To get a different server-
side cursor, set the cursortype property before opening
the recordset, or use the cursortype argument in
the .Open statement:


**************************************************
dim rs,cn
'create and open a connection using the cn variable
set rs = server.createobject("adodb.recordset)
rs.cursortype = adOpenKeyset 'requires ado constants
rs.open "select ...", cn

or

dim rs,cn
'create and open a connection using the cn variable
set rs = server.createobject("adodb.recordset)
rs.open "select ...", cn, adOpenKeyset
**************************************************

If you want a client-side cursor, set the cursorlocation
property before opening the recordset:

**************************************************
dim rs,cn
'create and open a connection using the cn variable
set rs = server.createobject("adodb.recordset)
rs.cursorlocation = adUseClient 'requires ado constants
rs.open "select ...", cn
**************************************************
Be aware that the only type of cursor that is possible
with a client-side cursor is a static cursor. So even if
you ask for a keyset or dynamic cursor, you will get a
static cursor.


There are several reasons to use a server-side instead of
client-side cursor with Access:
Performance and resources - When a client application
connects to an Access database, it loads its own copy of
the database engine into memory. When data is requested
from the database, it gets loaded into a cache, which has
its own cursor engine, in the client's memory and is then
passed to the requesting app.

With a server-side cursor, the data goes directly from
the cache to the client app.

With a client-side cursor, the data still goes into the
local cache first, but it is then passed to the ADO
Cursor Engine which builds the client-side cursor which
is passed to the requesting app. So the data winds up
being handled by two cursor engines on the client machine
instead of one.

So, should you never use a client-side cursor with an
Access database? It depends on what functionality you
need. For the most part, in asp applications, you only
need a server-side forward-only cursor, because you're
just grabbing the data, looping through it once to create
display elements and that's it.Using a more expensive
cursor than you need is the best way to impair your app's
scalability. However, if you need client-side
functionality, such as the ability to disconnect a
recordset or save it to disk, then you should not
hesitate to use a client-side cursor.

HTH,
Bob Barrows