4 ms·

It’s Time to Get over That Stored Procedure Aversion You Have

12 points by fastbmk_com 7y ago

purple_ducks 7y ago
2015...
- fastbmk_com 7y ago
  Yeah, so? Things have significantly changed since then?
  - grzm 7y ago
    It's common on HN to include the year in the submission title if it's over a year old. As only the submitter or a mod can edit the title, commenters often prompt an edit as your parent did. It's not a comment on the worthiness of the submission: it's an aid to the readers.
    - fastbmk_com 7y ago
      Looks like the 'edit' link has disappeared for an unknown reason.
      - monkeydreams 7y ago
        It disappears very quickly after you post. You only have a short time (minutes?) before your post belongs to the ages.
        fastbmk_com 7y ago
        What a cruel world!
alunchbox 7y ago
Erm. No. There's plenty of reasons why "rock star developers" and plenty of blogs discuss this. ORM usage is only as good as the stored procedures that are written. If devs don't understand an ORM they're bound it abuse it, because it's easy to do so. My biggest issue with stored procedures is change management. I've seen plenty of crazy custom tools that try to use Git, SVN, and just file system copies of Stores procedures. All of em have been 'lovely' to work with to say the least. Having your core business logic on the server is amazing due to version control. If it's performance that's a bottle neck I would say 95% (my POV) it's a developer that doesn't understand what the ORM is doing. The beauty of using EF or another micro ORM (dapper) is that SQL performance is optimized from caching the SQL hash. And the option is still there to execute a stored procedure. Theres absolutely times for using stored procedures, but I'd sacrifice a little bit of performance for maintainability.
- fastbmk_com 7y ago
  What's the problem with maintainability of stored procedures? For example, one can keep them in 'stored-procedures.sql' under Git version control and deploy them via one command, like `psql ...`. Won't it work that way?
  - alunchbox 7y ago
    Maintainability is more then just version control. It's about being able to discover complex business logic, refactoring, and finding patterns to reduce code use. There's only one useful tool I found to quickly manage thousands of stored procedures and it was datagrip unfortunately the company I was working at would not allow devs to use anything but SSMS. Have you tried filtering thousands of stored procedures that don't follow naming convention and are riddled with bugs?! (Even with datagrip it still bad, not horrible but still bad) Tooling is a devs best friend. Using an IDE or vim/emacs with the correct extensions allows developers to easily see where a class is being used, allowing quick inspection. I do believe stored procedures have a place but they've been abused too much.
    - fastbmk_com 7y ago

12 points by fastbmk_com 7y ago

purple_ducks 7y ago

2015...

fastbmk_com 7y ago
Yeah, so? Things have significantly changed since then?
- grzm 7y ago
  It's common on HN to include the year in the submission title if it's over a year old. As only the submitter or a mod can edit the title, commenters often prompt an edit as your parent did. It's not a comment on the worthiness of the submission: it's an aid to the readers.
  - fastbmk_com 7y ago
    Looks like the 'edit' link has disappeared for an unknown reason.
    - monkeydreams 7y ago
      It disappears very quickly after you post. You only have a short time (minutes?) before your post belongs to the ages.
      - fastbmk_com 7y ago
        What a cruel world!

alunchbox 7y ago

Erm. No. There's plenty of reasons why "rock star developers" and plenty of blogs discuss this. ORM usage is only as good as the stored procedures that are written. If devs don't understand an ORM they're bound it abuse it, because it's easy to do so. My biggest issue with stored procedures is change management. I've seen plenty of crazy custom tools that try to use Git, SVN, and just file system copies of Stores procedures. All of em have been 'lovely' to work with to say the least. Having your core business logic on the server is amazing due to version control. If it's performance that's a bottle neck I would say 95% (my POV) it's a developer that doesn't understand what the ORM is doing. The beauty of using EF or another micro ORM (dapper) is that SQL performance is optimized from caching the SQL hash. And the option is still there to execute a stored procedure. Theres absolutely times for using stored procedures, but I'd sacrifice a little bit of performance for maintainability.

fastbmk_com 7y ago
What's the problem with maintainability of stored procedures? For example, one can keep them in 'stored-procedures.sql' under Git version control and deploy them via one command, like `psql ...`. Won't it work that way?
- alunchbox 7y ago
  Maintainability is more then just version control. It's about being able to discover complex business logic, refactoring, and finding patterns to reduce code use. There's only one useful tool I found to quickly manage thousands of stored procedures and it was datagrip unfortunately the company I was working at would not allow devs to use anything but SSMS. Have you tried filtering thousands of stored procedures that don't follow naming convention and are riddled with bugs?! (Even with datagrip it still bad, not horrible but still bad) Tooling is a devs best friend. Using an IDE or vim/emacs with the correct extensions allows developers to easily see where a class is being used, allowing quick inspection. I do believe stored procedures have a place but they've been abused too much.
  - fastbmk_com 7y ago