Olaf Schmidt wrote:
> "Chris Wolf" schrieb
>
>
>> I can't resist adding my little opinion to yet another
>> "business logic in stored procs vs. app layer" holy war...
>>
>
> ... yeah, seems this thread is evolving nicely in this
> regard ...<g>
>
>
>> I usually prefer keeping the business logic in the application
>> layer and leaving the DB tier to be just a data provider.
>> In actual practice, this is not always practical.
>>
>> Let's say you implement a service where the client can
>> retrieve a set of top-level records,
>> each has an id; then for each id, you get additional
>> detail records from numerous detail/line-item tables.
>> If you implement this as a collection of fine-grained services,
>> i.e. each piece is a round trip from client, through web
>> services layer, through to db layer; and for each top-level id
>> in the result set - the performance will be abysmal.
>>
>> With Sybase stored procs, you can stack multiple result
>> sets in one call, so in the above scenario, you invoke the
>> lookup proc for each top-level id and the proc performs
>> all the secondary detail queries and stacks it all together
>> in a multiple-results result-set, such that there's only one
>> round-trip through the tiers for each top-level id in the set.
>>
>
> But that is a common problem, which can be solved
> directly in the business-layer-code at the serverside
> as well, also achieving a "stacked serialisation
> of resultsets" in one roundtrip.
> One can either code such a thing (a stacked serialization)
> "by hand" (based on XML for example, which is well-nestable) -
> or on a given platform (e.g. on Windows) one can avoid
> these hand-coded parts by making use of one of the
> already mentioned "DB-abstraction-helpers".
> E.g. ADO does support so called "shaped, hierarchical
> Recordsets" for a long time now ... over the "DataShape-
> Provider" which is part of ADO (and plays together with
> a lot of OLEDB-providers from different vendors...
> not sure if the Sybase-Provider is one of those, which is
> "Shape-Provider-capable").
> http://support.microsoft.com/kb/189657
>
> If you request the construction of such a nested
> Recordset at the serverside (over the Shape-
> Provider), then there are no network-roundtrips
> involved, in case the DB-Server and the
> AppServer do run on the same machine.
>
> But we digress ... ;-)
>
>
In your scenario, here, even though the middle-tier
("business-layer-code") is collocated with
the database, it looks to me like ADO is still a client-server
technology (I'm relatively
unfamiliar with it) you still need a "Connection" object to connect with
the database,
so I assume there's still a protocol stack, through which, the
client-based "SHAPE" mechanism must make
multiple (local) round trips. Even though the ADO Connection is not as
heavy-weight
as HTTP, or even TCP/IP (I'm assuming for local connections it may be
via named pipes)
There's still serialization/deserialization of the client-server
protocol stack.
With stored procedures, the multiple open cursors to different tables
are right there,
running in the same process/thread space of that stored proc - no
connection, no protocol
stack, so it's going to be "much" faster.
-Chris
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
