Best data transfer method

[Dennis]

We are building a standard 3-tier web app. We are trying to determine the
best way to transfer data from the middle tier components to the UI. We
have been testing to compare disconnected ADO recordsets, XML, and variant
arrays. In our testing with the components on a separate server from the
UI, arrays blow recordsets and XML out of the water. A call to return an
array runs at about 18-19 milliseconds. Calls for recordsets and XML run
about 32-34 ms.

However, according to this article in MSDN -
http://msdn.microsoft.com/library/te...lus_client.htm - it recommends
against using arrays, and wo of the issues it says are:

- Inefficient across the wire, sometimes taking 50 percent more packets than
XML strings, and many more packets than a marshalled-by-value Recordset
object.

- Costly to obtain from a recordset-a GetRows call can take considerably
more time than obtaining an XML string via an ADODB.Stream object, depending
on the recordset characteristics.

We are using GetRows to get the array from the RS and using the stream
object to get the XML, and our testing completely contradicts these two
points. Obviously the down side to using arrays is there is no metadata, so
we need to have very good documentation to make sure we know what data is
coming back. But we aren't updating anything with this data, it's just for
display purposes, so we don't have to worry about sending back updates.

What have other people done in this situation? Are we the only ones to find
this big of a discrepancy?

Dennis

(darin) Sounds like MS is right on the money again<g>
I'd tend to believe your tests over MS published tests. They never seem to
bear themselves out.

At any rate, if you really don't need to ever update the data your pulling,
by all means, use the array approach if it's faster.

One thing that kills me is that people will go on and on about how insane it
is to use Recordsets to marshal data, then proceed to describe all the code
they had to write to handle collections of objects, filtering, tracking
which objects need updating, and all the updating and deleting code. I just
marshal the changed recordset back and be done with it.

Is it slower? Yes. by very much? Well, that all depends. You were getting
~18ms call speeds. For how much data? One record? 100? 1000? There's a lot
involved in marshalling all the meta data that goes along with a RS, but
that cost might be minimal compared to the time to marshall all the actual
data.

If you're showing a difference of ~18ms between RS and array marshalling,
and that's for 1 record being passed, and it's passed as a result of a
client request (bound by UI actions), that's a completely livable situation
(unless your serving up webpages to 1000s and 1000s of people)

Sounds like your on the right track to me...

"Dennis" <dbronstein@brainranger.com> wrote in message
news:3a6ca520$1@news.devx.com...
> We are building a standard 3-tier web app. We are trying to determine the
> best way to transfer data from the middle tier components to the UI. We
> have been testing to compare disconnected ADO recordsets, XML, and variant
> arrays. In our testing with the components on a separate server from the
> UI, arrays blow recordsets and XML out of the water. A call to return an
> array runs at about 18-19 milliseconds. Calls for recordsets and XML run
> about 32-34 ms.
>
> However, according to this article in MSDN -
> http://msdn.microsoft.com/library/te...lus_client.htm - it
recommends
> against using arrays, and wo of the issues it says are:
>
> - Inefficient across the wire, sometimes taking 50 percent more packets
than
> XML strings, and many more packets than a marshalled-by-value Recordset
> object.
>
> - Costly to obtain from a recordset-a GetRows call can take considerably
> more time than obtaining an XML string via an ADODB.Stream object,
depending
> on the recordset characteristics.
>
> We are using GetRows to get the array from the RS and using the stream
> object to get the XML, and our testing completely contradicts these two
> points. Obviously the down side to using arrays is there is no metadata,
so
> we need to have very good documentation to make sure we know what data is
> coming back. But we aren't updating anything with this data, it's just
for
> display purposes, so we don't have to worry about sending back updates.
>
> What have other people done in this situation? Are we the only ones to
find
> this big of a discrepancy?
>
> Dennis
>
>

[Dennis]

<Darin> wrote in message news:3a6d1acf$1@news.devx.com...
> (darin) Sounds like MS is right on the money again<g>
> I'd tend to believe your tests over MS published tests. They never seem to
> bear themselves out.
>
> At any rate, if you really don't need to ever update the data your
pulling,
> by all means, use the array approach if it's faster.
>
> One thing that kills me is that people will go on and on about how insane
it
> is to use Recordsets to marshal data, then proceed to describe all the
code
> they had to write to handle collections of objects, filtering, tracking
> which objects need updating, and all the updating and deleting code. I
just
> marshal the changed recordset back and be done with it.

I agree. If we were updating this data and sending it back, it would be a
no-brainer to use recordsets.


> Is it slower? Yes. by very much? Well, that all depends. You were getting
> ~18ms call speeds. For how much data? One record? 100? 1000? There's a lot
> involved in marshalling all the meta data that goes along with a RS, but
> that cost might be minimal compared to the time to marshall all the actual
> data.

The data we tested had 3 records in it with about 15 fields. The actual
time isn't important, it's the relative time that matters and the array is
almost twice as fast. We will try it with a larger data set to see if that
makes a difference.

> If you're showing a difference of ~18ms between RS and array marshalling,
> and that's for 1 record being passed, and it's passed as a result of a
> client request (bound by UI actions), that's a completely livable
situation
> (unless your serving up webpages to 1000s and 1000s of people)
>
> Sounds like your on the right track to me...

The only thing we're concerned about is that we want to expose a public API,
and a recordset would work better for that since it has all the metadata
with it. So it's something we need to decide if it's worth the performance
loss just to have the metadata. It also makes maintenance a little harder
with the arrays.

Dennis

[Thomas Eyde]

Why not provide both and let the client developer decide?
/Thomas

"Dennis" <dbronstein@brainranger.com> wrote in message
news:3a6da264@news.devx.com...

> The only thing we're concerned about is that we want to expose a public
API,
> and a recordset would work better for that since it has all the metadata
> with it. So it's something we need to decide if it's worth the
performance
> loss just to have the metadata. It also makes maintenance a little harder
> with the arrays.

[Dennis]

Because that's extra work and we're on a tight schedule.

"Thomas Eyde" <thomas.eyde@bdc.no> wrote in message
news:3a6f1dfd@news.devx.com...
> Why not provide both and let the client developer decide?
> /Thomas
>
> "Dennis" <dbronstein@brainranger.com> wrote in message
> news:3a6da264@news.devx.com...
>
> > The only thing we're concerned about is that we want to expose a public
> API,
> > and a recordset would work better for that since it has all the metadata
> > with it. So it's something we need to decide if it's worth the
> performance
> > loss just to have the metadata. It also makes maintenance a little
harder
> > with the arrays.
>
>
>

[Thomas Eyde]

I don't understand how it is extra work. You do have a recordset to extract
the array from? Just expose that from another method.
/Thomas

"Dennis" <dbronstein@brainranger.com> wrote in message
news:3a6f35b2$1@news.devx.com...
> Because that's extra work and we're on a tight schedule.
>
> "Thomas Eyde" <thomas.eyde@bdc.no> wrote in message
> news:3a6f1dfd@news.devx.com...
> > Why not provide both and let the client developer decide?
> > /Thomas

[Richard Dalton .]

"Thomas Eyde" <thomas.eyde@bdc.no> wrote:
>Why not provide both and let the client developer decide?


Call Cheek.Insert(Tongue)

Good gracious man are you MAD!!!!
Let the client developers decide.
There would be anarchy!!!!

Seriously though, it raises an interesting point.
Sometimes the internal representation of data in an object
is much richer than the interface of the object itself.
But the principle of data hiding says:

"Thou shalt not show thy clients, thy inner workings"

It can be very tempting sometimes to just give direct access
to the recordset, collection, array, linked list, or whatever.
I for one would be reluctant. I think the object should
have an interface which is divorced from the inner data.

-Richard

[Thomas Eyde]

With "client developer" I meant the developer who will use your object/component,
not necessarily the guy with the Excel VBA Macro...

If a collection is what the developer needs, then give it to him. That applies
to arrays, recordsets and whatever as well.

OO does not deny you to returns objects? A recordset is an object, why is
returning it a bad thing? You are hiding the generating process. But you
have to protect your data from invalid updates, of course.

/Thomas

"Richard Dalton" . wrote:
>It can be very tempting sometimes to just give direct access
>to the recordset, collection, array, linked list, or whatever.
>I for one would be reluctant. I think the object should
>have an interface which is divorced from the inner data.

[Richard Dalton .]

"Thomas Eyde" <thomas.eyde@eunet.no> wrote:
>OO does not deny you to returns objects? A recordset is an >object, why
is returning it a bad thing? You are hiding the >generating process.


It's not that there are hard and fast rules. Go with
what works. But personally I'd prefer if client applications
did not have access to recordsets. A recordset is like a
direct line to the database. I won't get into philosophical
debates about connected and disconnected recordsets.

Surely a great part of the reasoning behind a tiered
architecture (i.e. Abstraction) goes out the window if you
allow client developers to use recordsets directly.

It's like having a very rigid reporting structure in a
company but allowing anybody to talk directly to the
CEO whenever they feel like it. It might be a good idea in
specific instances, it might get things done quicker.
But what's the point in having the reporting structure? And
who gets do decide when bypassing the reporting structure
is justified.

-Richard

[Dennis]

We'd have to create all those methods and test them. That takes time.

"Thomas Eyde" <thomas.eyde@eunet.no> wrote in message
news:3a6f559d$1@news.devx.com...
> I don't understand how it is extra work. You do have a recordset to
extract
> the array from? Just expose that from another method.
> /Thomas
>
> "Dennis" <dbronstein@brainranger.com> wrote in message
> news:3a6f35b2$1@news.devx.com...
> > Because that's extra work and we're on a tight schedule.
> >
> > "Thomas Eyde" <thomas.eyde@bdc.no> wrote in message
> > news:3a6f1dfd@news.devx.com...
> > > Why not provide both and let the client developer decide?
> > > /Thomas
>
>
>

[Thomas Eyde]

I get this feeling we are talking about different things here.

If I needed, let's say some customer data I would have code to generate my
recordset. This code I could put in a private method:
Private Function GetCustomerRs() As ADODB.Recordset

I would then expose my array as:
Public Function GetCustomersArray() As Variant
GetCustomersArray = GetCustomerRs.GetRows()
End Function

The GetCustomersArray is already there, this one I have to test. But it relies
on a private method which I assume will work if GetCustomersArray works.
So what more do I have to test if I decide to expose GetCustomerRs as public?

"Dennis" <dbronstein@brainranger.com> wrote:
>We'd have to create all those methods and test them. That takes time.

> It can be very tempting sometimes to just give direct access
> to the recordset, collection, array, linked list, or whatever.
> I for one would be reluctant. I think the object should
> have an interface which is divorced from the inner data.

true enough, but then sometimes to process of divorcing the two costs too
much performance. I don't know whether this is one of those cases, but ya
gotta look at all the sides...

[Richard Dalton .]

<Darin> wrote:
>true enough, but then sometimes to process of divorcing the two >costs too
much performance. I don't know whether this is one of >those cases, but ya
gotta look at all the sides...

And so we find ourselves back at square one.

Actually in this specific case I don't think performance
would be as serious a problem as it sometimes can be.
I think I'd still err on the side of purity and hide the
recordset. But there definitely seems to be a
Purity Vs Performance issue.


-Richard

> Actually in this specific case I don't think performance
> would be as serious a problem as it sometimes can be.
> I think I'd still err on the side of purity and hide the
> recordset. But there definitely seems to be a
> Purity Vs Performance issue.

I've noticed a lot of discussion about purity in OO designs. It kind of
strikes me like guys I know that choke when they see a DB that's not third
normal form.

I see a bit of difference between hiding and showing the recordset but not
much. Here's why.

If you HIDE the recordset....
You persist state to some +thing+ and pass the thing, unpersist it on the
other side and use the data.
If you're returning a single record, all's well, but if it's multiple
records, you want to sort, search, filter, add, and remove records locally,
then return the modifications to the server for update. That's an awfull lot
of code to write for the client side, but it can be done.
If you want to change the underlying DB, no problem, because the persisted
data is explicitly persisted. It's pure.
But what if you want to add a field to the DB and you want the client to see
it. All **** breaks loose. You've got interfaces that have to be extended
and or broken etc.

You pass a disconnected recordset....
Yes, the client side sees field names straight out of the DB, but in my book
that makes debugging easier.
The client can filter, sort, and generally manipulate that data all day long
without going back to the server. Recordsets are as fast as any VB code
could be for performing the kind of general manipulations that they do.
You need to add a field to the table. No problem. If the recordset is
created via a SELECT *, then it gets included from then on out.
If you delete a field or rename a field? Well, that kind of gets back to
the whole +design first+ philosophy. I don't believe you can anticipate
everything though. Hence the benefit of the flexibility with recordsets.


In the end, I don't look at it as "passing recordsets to the client", I look
at it as, "passing a persisted block of data to the client, and using a
sophisticated library at the client to manipulate that data".

I'd rather not write that library if I don't have to<g>

[Kevin Downs]

<Darin> wrote in message news:3a776b61@news.devx.com...

> In the end, I don't look at it as "passing recordsets to the client", I look
> at it as, "passing a persisted block of data to the client, and using a
> sophisticated library at the client to manipulate that data".
>
> I'd rather not write that library if I don't have to<g>
>


I couldn't agree more.

--
Regards,
Kevin