Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2010
    Posts
    57

    Unanswered: Stored Procedure vs. UDF vs. View vs. Computed Column

    I have two tables. The first is a table of vehicles. The second is a table of events that have occurred to those vehicles (purchased, sold, accident, destroyed, etc). The events table has columns for the event date and the type of event. Obviously one vehicle may have multiple events associated with it.

    If I look at the events there are some kinds of events that are considered to be the "beginning of life" of the vehicle (i.e. gift received, purchased) and some that are the "end of life" (i.e. sold, destroyed).

    To compute the BOL, I run a query that finds the first of the BOL events for each vehicle (group by vehicle, min on date). Similarly for the EOL (group by vehicle, max on date).

    If all I'm interested in is a simple table with the resulting vehicle, BOL, and EOL, then I just join the two preceding queries back to the vehicle table. So far piece, of cake!

    However, most of the time I don't want just a simple table. For instance in my front end I have a form to enter vehicle information, including new events. On the upper corner, I like to have a summary of the vehicle state, including BOL, EOL, days in our possession, etc.

    Right now my implementation is to add a BOL and EOL computed column to the vehicle table, which then calls a UDF. I've read, however, that it may be a bad idea to have complex joins inside UDFs (the actual queries are a bit more complex than i explained here).

    I'm looking for suggestions on how best to implement this. Is the computed column a good idea? Should I call the UDF directly from the front end? Should I use stored procedures instead?

    Thanks!
    Carlos

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is this microsoft sql server, by any chance?

    if so, this thread should probably be moved to that forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2010
    Posts
    57
    Quote Originally Posted by r937 View Post
    is this microsoft sql server, by any chance?

    if so, this thread should probably be moved to that forum
    It is on SQL Server - though I felt the question was generic to carrying out queries in SQL. I'll gladly move it, but I'm curious about what portion is SQL Server specific

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by carlosn View Post
    ... I'm curious about what portion is SQL Server specific
    computed columns, and perhaps also UDFs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2010
    Posts
    57
    I'm OK with moving - I think a moderator needs to do it, right?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by carlosn View Post
    I think a moderator needs to do it, right?
    Yep.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would make two separate hits to the DB. One for the vehicle itself, including any aggregate data that I want to group by vehicle. Then a second query to go get the event details. Actually, I probably wouldn't even bother including BOL/EOL in the first query. I'd just rifle through the results of the second query in the application and display them accordingly, assuming I don't have a performance related reason not to do so.

    I would not store the results of my aggregate values in a table as a computed column or otherwise without very good reason to do so, like performance.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can make them computed, since the computation will not occur unless the computed columns are included anywhere in a query. I would turn it into a stored procedure, but that's my personal preference.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Aug 2010
    Posts
    57
    Teddy - performance probably doesn't matter much one way or another. Vehicle numbers are in the low hundreds (and if they ever reach 1000, I will be able to afford an army of developers).

    My initial reasoning for going the computed column way was to ensure consistent results across clients. I will likely have an access client as well as a thin web client, and some executive has requested a widget like app. This way I only had to write the code once and I knew all the clients would show the same result.

  10. #10
    Join Date
    Aug 2010
    Posts
    57
    rdjabarov - can you briefly tell me why you would prefer a stored procedure?

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'd go down the .NET route if you're looking to write a bunch of different front ends from the ground up and you already settled on the Microsoft stack. Done properly you can reuse your business logic stuff across all apps. That said, you can also make a pretty good argument to keep the logic in the database if you're looking at some hugely disparate client applications.

    Stored procedures offer some benefits in the way of security, performance and abstraction. Lately I'm more on the app dev side of things and have taken to an OR/M approach. Prior to that I would have said all stored procedures, all the time.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    carlosn - consistent performance, logic is kept in 1 place, simple to maintain.

    Teddy - .NET??? OK, I'm glad you like it there Every technology has its perfect, not so perfect, and just plain wrong place to be used. Why would you write a .NET module, and potentially data layer, to perform a data manipulation that is expected to be used in, well, possibly several places, and even reports? Wouldn't it be simpler to write 1 procedure, and then call it from wherever you have to, including .NET fron-end, SSRS or Crystal reports, or even ridiculous things like pass-through queries or linked data sets in Excel?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yes it would, which is why I think it makes good sense to keep that logic in the database if you're going to have hugely disparate clients.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rdjabarov View Post
    Wouldn't it be simpler to write 1 procedure, and then call it from wherever you have to, including .NET fron-end, SSRS or Crystal reports, or even ridiculous things like pass-through queries or linked data sets in Excel?
    I've explained that reasoning to my developers until I am blue in the face.
    Their argument always passes through two phases:
    Phase1: Business logic is easier to maintain in .net or java or whatever than in SQL.
    Phase2: We don't know how to write SQL.
    And there the debate ends.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'll happily argue for business logic in the DB if you have no idea what kind of clients are going to hit it. The benefits of code reuse go out the window. I like to think in terms of "solution" as opposed to DB vs. app logic. There could be more than one possible app involved in a given solution, or not...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •