7 ms·
> but by using stored procedures in your database. This used to be the standard when clients used to connect directly to the database. Now that the world depen
by guggle 6y ago
> but by using stored procedures in your database.
This used to be the standard when clients used to connect directly to the database. Now that the world depends on web services, things are a little different and there's less incentive to maintain stored procs as an interface to your database.
It's still a good idea though, with unparalleled performance. I suppose developers don't do it for a variety of reasons: They may lack the SQL knowledge, they may not want to maintain the extra code, they may fantasize about database portability, etc.
- Philip-J-Fry 6y agoThe argument against it I usually see is "you can't version control SQL!" Where I work we have a custom system which manages releasing immutable SQL snapshots between environments and they get merged to master once in prod. The only thing needed is a process, and then version control is easy.
- xupybd 6y agoYeah you can just use a migration system and store your functions in text files checked into the version control. Nothing but the migrations are allowed in staging and production so everyone is forced to use it.
- joeyjojo 6y agoAs a front-end dev, I've been interested in learning about such a process but don't know where to look. What kind of tooling would you use to manage your migrations in a CI environment?
- smada 6y agoive used liquibase in the past (not affiliated) with great results https://www.liquibase.org https://www.liquibase.org
- funcDropShadow 6y agoFlyway or Liquibase.
- rakoo 6y agoEditing stored procedures and deploying them on a database is the same as editing code deploying it on a server. There is no difficulty in versioning it
- grep_name 6y ago> you can't version control SQL Aren't stored procedures part of the database schema? Which can be version controlled?
- JamesSwift 6y agoI'm not sure about other DBs, but SQL Server has (really good) tools for this (DACPACs). As an added bonus you can then use tSQLt to unit test the SQL.
- keithnoizu 6y agoOn high volume applications I would avoid it since it's usually easier to horizontally scale web servers than sql servers, and it makes cache strategies more difficult depending on how and what you're querying.
- taffer 6y agoIt is a common misconception that more data processing in SQL puts a higher load on the database. A typical database spends 96% of its CPU time on logging, locking, latching and marshalling [1][2] rather than processing SQL. By sending less data to the middle tier and performing fewer round trips, the use of stored procedures means that the database can actually do more real work. [1] https://dzone.com/articles/mit-prof-stonebraker-%E2%80%9C https://dzone.com/articles/mit-prof-stonebraker-%E2%80%9C [2] https://drive.google.com/file/d/0B7jyeB8kxFPjU0VySkF3UHhoVnM/edit https://drive.google.com/file/d/0B7jyeB8kxFPjU0VySkF3UHhoVnM...
- keithnoizu 6y agoIt depends. Regardless there is limited CPU, and so any scenario in which a stored procedure uses more CPU than a simple query will cause you to hit that saturation point sooner. I generally have layers of caching on top of the sql server so the majority of queries will be integer equivalency or range checks, if not get by primary key queries. So I am not generally operating in a scenario where a stored procedure would reduce record scans, etc. I generally also don't run on transactions out side of limited scenarios, since throughput is usually more important to me than data consistency.
- marcus_holmes 6y agosorry, I don't get this at all. How is using stored procs instead of an ORM going to adversely affect your cache strategy?
- keithnoizu 6y agoImagine for example that you have per record caches. You can run a query to get ids with out joining to the table and then simply fill in any gaps in your cache with a follow up query.
- zaarn 6y agoIn most cases, it is sufficient if your database runs on the DB in production and sqlite for local development. If it's too big to be able to run on the dev computer, then it should use one db across the board. Portability across databases rarely buys you anything but hard to reproduce bugs.
- bosswipe 6y agoI'm not a fan of stored procedures because 1) It's more maintainable to keep all the logic unified in the one language and system with which developers will be more familiar with 2) The programming tools are normally much better for app code 3) You can write unit tests for app code (does anybody write unit tests for sql?) 4) you can usually avoid transferring too much unnecessary data or doing too many unnecessary network requests, remember to avoid premature performance optimization
- Twisell 6y agoOn the other hand, one of the highly specialized stored procedure I wrote in PostgreSQL have being successfully and flawlessly re-used/tested on multiple out-of shelf clients app (BI related all developed in different languages) simply because they all support SQL. I agree this only apply for data-model related functions. But this has proven really low maintenance, and improvements of this "API" are automatically available in all clients. I'd bet that accessing this stored procedure through an ORM raw SQL query would provide more benefit than rewriting it from scratch in ORM's language. PS: Also yeah people do write unit test for SQL (never enough but that's another story). https://pgtap.org/ https://pgtap.org/
- HelloNurse 6y ago1) One language and one system: SQL on a database that will last much more than the applications 2) SQL has a limited scope, and so do SQL tools. You need advanced tools if you have "app code": it's a necessary evil. 3) At worst, you can write unit tests for app code that calls SQL queries and stored procedures. 4) "Usually" isn't enough, moving logic to the client requires moving data to the client.
- hackerfromthefu 6y agoWell as someone with 25 years SQL experience, I generally will still use LINQ for 90% of db access code in business style applications, for very important reasons beyond those mentioned, like - They may realise value in having a compiler provide guarantees, as a bonus immediately in a tight feedback look while editing code as well as on compilation - They have favour expressive languages with robust error handling, integrated IDE and VC etc - They may want to reduce the number of moving parts These are all along the themes of a) delivering results faster, and b) improving the maintenance lifecycle of systems - unlike those parts of the industry that don't really plan to maintain their systems and instead just wanna do rewrites in the latest hotness which is usually a hot mess from a long term maintainability perspective.
- fhars 6y agoBut then LINQ is in many ways conceptually closer to directly using SQL than to using a classical ORM.
- hackerfromthefu 6y agoSorry my wording could have been more explicit! Generally, use of LINQ in the context of DB access is done hand in hand with an ORM, such as Entity Framework, NHibernate etc, and this is how I meant it.