none
How big should I allow my SP's to get?

    Question

  • Hi,

    I've got a stored procedure which returns entries for a calendar from all over my database by looking at lots of different tables that have date fields.

    The SP is basically a union of about 10 different selects from various places but I am also doing things like swapping that dates if the end date is before the start.

    My question is how large should I allow my SP to get before I start shifting some of the logic back to the C# code?

    Cheers,

    Paul.

    Wednesday, March 05, 2014 9:11 AM

Answers

  • always better to keep logic in SPs compared to C# for requirement like above.

    T-SQL is set based and would give you much better performance (if implemented in proper set based manner) compared to iterative processing in C#. As from your explanation, you are just doing some basic operations like swapping etc which can be best dealt at sql end.

    Also Stored procedures are pre compiled and would cache th execution plan so that subsequent executions can reuse them.

    To suggest more, we need to understand what exactly you're trying to implement, but from high level information specified I think its better to keep it in SP.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Lonny Lonsdale Wednesday, March 05, 2014 9:23 AM
    Wednesday, March 05, 2014 9:21 AM
  • There is no direct answer to that question, but maybe: it is not the right question to ask.

    As long as you are reading working with data from the tables you should probably be in SQL. Particularly if you do this in a set-based fashion. That is, if you need to swap start/end dates on 10000 rows, you can do this in a single statement in T-SQL. Not in C#.

    One situation where you should consider C# is when you want to complex logic on a scalar value, for instance regular-expression matching. Or administrative operations like working the surrounding file system. It may also be an option for data operations where there is no good set-based solution in T-SQL.

    But the sheer size has little do to with it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Lonny Lonsdale Wednesday, March 05, 2014 9:23 AM
    Wednesday, March 05, 2014 9:22 AM

All replies

  • always better to keep logic in SPs compared to C# for requirement like above.

    T-SQL is set based and would give you much better performance (if implemented in proper set based manner) compared to iterative processing in C#. As from your explanation, you are just doing some basic operations like swapping etc which can be best dealt at sql end.

    Also Stored procedures are pre compiled and would cache th execution plan so that subsequent executions can reuse them.

    To suggest more, we need to understand what exactly you're trying to implement, but from high level information specified I think its better to keep it in SP.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Lonny Lonsdale Wednesday, March 05, 2014 9:23 AM
    Wednesday, March 05, 2014 9:21 AM
  • There is no direct answer to that question, but maybe: it is not the right question to ask.

    As long as you are reading working with data from the tables you should probably be in SQL. Particularly if you do this in a set-based fashion. That is, if you need to swap start/end dates on 10000 rows, you can do this in a single statement in T-SQL. Not in C#.

    One situation where you should consider C# is when you want to complex logic on a scalar value, for instance regular-expression matching. Or administrative operations like working the surrounding file system. It may also be an option for data operations where there is no good set-based solution in T-SQL.

    But the sheer size has little do to with it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Lonny Lonsdale Wednesday, March 05, 2014 9:23 AM
    Wednesday, March 05, 2014 9:22 AM
  • 1) You meant date columns; a field is part of a temporal value (year, month, say, hour, minute and second).

    2) No, you should have a CHECK(start_date <= end_date) on the tables instead of repairing it after the fact. But more than that, you need to learn about cohesion. A module of code should have one and only one entry point, one and only one exit point, and do one and only task. This is fundamental software engineering, not just SQL! 

    3) The old rule of thumb was no more than 50 lines of T-SQL, or one statement. This came from the fact that T-SQL is not an application language. It is a simple one-pass compiler. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, March 05, 2014 2:48 PM