Coffeehouse Thread

17 posts

How big are your sprocs?

Back to Forum: Coffeehouse
  • User profile image
    Rossj


     I typically keep mine reasonably small and discrete - typically doing as little as possible, but I've encountered some monster sprocs in my time which seem to be all out of keeping with the actual db model and the size of the project.

    Tonight a friend told me that a team at his place had built a 250-odd line sproc for a project that I thought would be much smaller.

     So - how big are your sprocs? Do you worry about putting too much logic in your database? Why do you make them the size they are (because it is a complex project isn't a good answer)?

  • User profile image
    Sven Groot

    I typically use sprocs only to avoid putting actual SQL in my C#/VB/whatever code, and I keep them as small as possible. They tend to be single select/update/insert/delete statements, and if an insert they're usually followed by select scope_identity. But that's about all. All my logic is in other places.

    If I need more code on the database level it's usually in triggers and check constraints, not stored procedures.

  • User profile image
    Ion Todirel

    Sven Groot wrote:
    I typically use sprocs only to avoid putting actual SQL in my C#/VB/whatever code, and I keep them as small as possible. They tend to be single select/update/insert/delete statements, and if an insert they're usually followed by select scope_identity. But that's about all. All my logic is in other places.

    If I need more code on the database level it's usually in triggers and check constraints, not stored procedures.
    me too

    usually 10-15 lines

  • User profile image
    Pop Catalin Sever

    Don't know how big the sproc is but it has an 870 lines(+-) select query. It joins data from around 50 tables doing multiple agregates and uses variuouse functions. It basicaly does statistics on a conected graph with tens of thousants of lines at the largest level. It also has some comments and not all lines are sql statements. No temporary tables are used in the query.

  • User profile image
    harumscarum

    Depends on the client and their systems. The largest have been where they were pulling a lot of data from the 400. It seems that sprocs that pull data for proposals are always the largest.

    In a perfect world I would like to keep my sprocs slim and trim. I would rather a client update a rule in an IDE then a sproc.


    Do you worry about putting too much logic in your database?

    No because sometimes when pulling data from other systems you dont want to add logic to your application that does not belong (I guess this is more of a design preference) 

    Why do you make them the size they are (because it is a complex project isn't a good answer)?

    Because the calling application may not be able to manipulate the data. Also to keep bad table design join complexity in the sproc instead of branching out into the enterprise apps.

  • User profile image
    Secret​Software

    Size does not matter.

  • User profile image
    Raghavendra_​Mudugal

    Rossj wrote:
    
     So - how big are your sprocs? Do you worry about putting too much logic in your database? Why do you make them the size they are (because it is a complex project isn't a good answer)?


    Now, we have projects (two) on .net2k3 (c#, web app) with crystal reports. Here the role of the SP is to fetch the data from more than 11 tables and put it in one #table and use it in CR as reports data (huge) and no.of SP lines goes to nearly between 1000 line to 1250. We have such reports 3.nos. And other SPs are all small 50 to 150 lines.

    Well that is nothing.... In 1999, we had a project on vb6.0 and sql-6.5 (windows app, client-server), where SP lines goes to 2K to 2.5K and we had nearly some 450 procedures (only some were small).

    Because, size is not the fact to keep in mind. If 10 programmes, 10 diff ways. So the ultimate is to get the output as desired that serves our purpose. And there is no other great way other than this (as i see). I can't open some 100s of recordsets and hold the data and put into the grid in front-end Tongue Out Big Smile. SQL statements need to be very very long, with all joins and stuff (which really helps in reducing the lines in SP)

    And to all these, we need to add comments [C] to the lines so understand whats happening. So SP lines really increase more. Finally we break the SP into parts to 2 or 3 max and call them in one SP. This really helps in debugging or any later modifications.

    Phew!

  • User profile image
    LostIn​Tangent

    I definitely try to keep my sprocs simple and generally logic free. The only time I would have a sproc get over just a couple lines of code is if I had some logic that needed to be ran that would take substantially longer to run outside of a database. I really don't even like having to do that, but in the end performance beats design.

  • User profile image
    Human​Compiler

    LostInTangent wrote:
    I definitely try to keep my sprocs simple and generally logic free. The only time I would have a sproc get over just a couple lines of code is if I had some logic that needed to be ran that would take substantially longer to run outside of a database. I really don't even like having to do that, but in the end performance beats design.


    Bingo!  This is what we do too.  90% of our SPs are small and contain no logic, but sometimes the DB can do it a lot faster and we break the purity for that reason.  Also, some of our SPs that do paging with lots of weird conditional sorting get kind of long (200-300 lines), but there are only 5 to 10 of those.

  • User profile image
    irascian

    I try and keep mine small, but have seen some truly horrendous ones in my time that nobody seems to actually understand.

    But this is really a performance vs scalability question. You are pushing scalability but there are times where scalability isn't needed but performance is, and putting all the logic into a stored proc that gets called once rather than artificially using multiple stored proc invocations can make for much better performance.

  • User profile image
    Lee_Dale

    I worked on a sql server 6.5 database for a large charity donation system which had 1500 sprocs with the avergage line count being about 500 lines, some were in excess of a 1000 lines long.

    And I had to go through and convert these old procs to be sql server 2000 compliant (no compatibility mode).

    Sad

  • User profile image
    Sven Groot

    irascian wrote:
    putting all the logic into a stored proc that gets called once rather than artificially using multiple stored proc invocations can make for much better performance.

    I do try to avoid multiple sproc calls for what is logically a single operation.

    What I willt typically do is chain my stored procedures. Let's say I have a Customer table and an Order table. If I want to be able to get a single customer and all its orders (where I don't want to use a join since I don't need all the customer's information on every row), I'd have a GetOrdersForCustomer sproc, and a GetCustomer sproc that would first select the customer and then run "exec GetOrdersForCustomer @id".

    Then the DAL may figure out how to put those two results sets together. But both results are returned from a single call to the DB.

  • User profile image
    Tensor

    The current project I am working on has some monsterous sprocs. Theres one with aroudn 750 lines I never want to touch again.

    A fair amount of business logic was in them before I even began, and as time has gone on that has only got worse. They are a pain to debug and a bigger pain to alter. I would love to refactor all the logic out of them but pressure to add new functionality means there simply is not the time. Classic example of why it should be done right from the start.

  • User profile image
    odujosh

    The biggest I usuaully have is for Junction table with subtyping info: like if you  had a article with a file and you want to mark a file as the primary file for that article. (so if the file is used in more than one article for different purposes it would be repurposed by all articles)

    So I would do a ArticleFile_Save proc:

    Declare @ID  as Varchar(20)
    SELECT @ID =exec ArticleFile_ReadID @ArticleID, @FileID)
    IF(@ID is NULL)
    BEGIN
    SELECT @ID = exec ArticleFile_Insert @ArticleID, @FileID, @Primary, @Type
    END
    ELSE
    BEGIN
     exec ArticleFile_Update @ArticleID, @FileID, @Primary, @Type
    END

    SELECT @ID



    Add transaction block and the Alter proc statement  and parameter  declarations and thats as long as they get.

  • User profile image
    Dan

    An interesting question, no doubt. I blogged about this a while back as I created a simple Windows Forms application that inspects your database and enables searching the contents of stored procedures as well as summuary data for all your stored procedures.

    - 63 stored procedures
    - 2,100 lines of code
    - 33 lines of code on average

    It's not super smart in that it counts white space as I believe I still count blank lines as lines, but you get the general idea.


    Download Exe || Download Source

    Thanks,
    -Dan

  • User profile image
    phreaks

    Being in financials, our book and tax system sprocs tend to be fairly large. (about 300 - 600 lines on average).

    We keep all related logic wrapped in Sql Functions that are consumed by the sprocs.

    I guess it depends on what the application is...

    Different apps have different levels of data and procedure requirements.

    Also, consider the extra lines that OpenXML and SqlXMl require...

    Is anyone else even really using OpenXML ?

    I love it, but I don't find many resources or marketing behind it.

  • User profile image
    Ion Todirel

    Dan wrote:
    as I created a simple Windows Forms application that inspects your database and enables searching the contents of stored procedures as well as summuary data for all your stored procedures.
    nice UI Wink

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.