Re: Distributed queries

From: Dino Chiesa [Microsoft] (dinoch_at_online.microsoft.com)
Date: 11/08/04

  • Next message: [MSFT]: "RE: Hungry Consumer" 
    Date: Mon, 8 Nov 2004 13:35:36 -0500

    If it's just data, it shouldn't matter whether the sproc retrieves it
    locally, from a remote host, or from carrier pigeon.
    I myself have no problem with the practice of a sproc getting data from
    "anywhere". It allows the data architect some additional flexibility in
    designing and deploying the database. What is stored locally today, might
    be moved to a remote DB tomorrow, and then moved to a web service the day
    after that. The DBA can make these changes without affecting any
    applications, if the sproc signature remains the same.

    beyond data retrieval,
    The question of whether "logic" should run in a business logic layer (BLL),
    independent of the db, or in a sproc or some other architectural artifact
    managed directly by the DB, should come down to practical costs and
    benefits. The BLL approach is nice, because as you say, it is obvious.
    The tools support building apps this way, debugging, deploying, or
    inspecting them, and so on.

    On the other hand one of the big lessons we are (re-)learning from large
    database deployments is that the network is not free. And, a corrolary:
    sometimes the business layer does not want the data; instead it wants an
    analysis of the data, some elaboration of the data. So, rather than
    transferring 10mb of data to a BLL, and incurring the not insignificant cost
    of transferring that data, so that the BLL itself can perform an operation
    and come up with a 1kb result... why not let the "logic" run in the
    database, closer to the actual data, where the transfer cost is optimized?
    Just send the 1kb result to the BLL.

    The problem with sprocs has been that the programming language is obscure
    and special purpose. "Non obvious" you might say. But Microsoft SQL
    Server 2005 ("Yukon"), due out next year, and IBM DB2 USB v8 ("Stinger"),
    released last month, are changing this. These db's allow you to write
    database-resident logic (stored procedures, triggers, UDFs, ) in .NET
    languages, with Visual Studio.

    The goal is to combine the perf benefits of database-resident logic with the
    ease-of-use and productivity of a general purpose language like Visual Basic
    or C#. 

    -- 
Dino Chiesa
Microsoft Developer Division
d i n o c h @  OmitThis . m i c r o s o f t . c o m
"dist_query_doubter" <dist_query_doubter@discussions.microsoft.com> wrote in 
message news:82B2EED7-B024-47D5-98BC-F1AE1EF026A2@microsoft.com...
> Some of my coworkers want to use distributed queries within stored
> procedures. For example, stored proc "A" will join with a table from 
> another
> database, possibly on another server, and return the rows. I am more
> comfortable using more "obvious" logic that resides in a business layer 
> which
> may call a different business object to retrieve values from the other
> database. The reason I call it "obvious" is because the code is visible
> within the business logic, not hidden way down in a stored procedure. I 
> guess
> the question really comes down to using stored procedures or .Net code to
> implement our business logic. Anyone?
  • Next message: [MSFT]: "RE: Hungry Consumer"

    Relevant Pages

    • Re: Timeout on populating a datagrid
      ... The sproc is reading not updating any database, ... >>Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: architecture question
      ... At worst, business logic may be placed into those methods but I cannot think of a single reason why business logic would ever have to go any "lower" than that (i.e., right in a sproc). ... with pointing out that a database probably needs to be denormalized to some extent to make it faster way before anyone should ever put business logic into a sproc. ... But I do think there are some pretty basic guiding principles that should be adhered to, or in this case principles that should be completely avoided. ...
      (microsoft.public.dotnet.languages.csharp)
    • Re: architecture question
      ... At worst, business logic may be placed into those methods but I cannot think of a single reason why business logic would ever have to go any "lower" than that (i.e., right in a sproc). ... anyhow) with pointing out that a database probably needs to be denormalized to some extent to make it faster way before anyone should ever put business logic into a sproc. ... But it's always better to denormalize a bit than to put any business logic into sprocs. ... I can see creating and sharing C# user-defined types in SQL Server, but that's about it with regards to CLR integration into SQL Server. ...
      (microsoft.public.dotnet.languages.csharp)
    • Re: architecture question
      ... And my part of the discussion was that it is an extremely rare thing to even worry about. ... At worst, business logic may be placed into those methods but I cannot think of a single reason why business logic would ever have to go any "lower" than that (i.e., right in a sproc). ... anyhow) with pointing out that a database probably needs to be denormalized to some extent to make it faster way before anyone should ever put business logic into a sproc. ... I can see creating and sharing C# user-defined types in SQL Server, but that's about it with regards to CLR integration into SQL Server. ...
      (microsoft.public.dotnet.languages.csharp)
    • Re: Cannot Undeploy Orchestration
      ... Run this script on your message box database, and it will create a sproc ... But again this pesky suspended orchestration instance is still ... >> prevent orchs from terminating. ...
      (microsoft.public.biztalk.general)
    Loading