3 ms·
Rebuilding statistics will not help if the cause of the bad plan is something that the cost based optimizer is not even trying to model. Stored procedures in t
by ants_a 15d ago
Rebuilding statistics will not help if the cause of the bad plan is something that the cost based optimizer is not even trying to model.
Stored procedures in this context are just a clumsy workaround to control the planner so your comment about API layer is irrelevant. But if you like, you can write stored procedures in a ton of different languages. And if you do not have source control for your database artifacts, you are doing it wrong. A common reason for stored procedures is to not have a bunch network roundtrips in the middle of your transaction logic while you are holding onto locks / have an open conflict window.
- simondotau 14d agoSQL Server stored procedures don’t provide a separate tier of T-SQL functionality. Anything they do to the data can generally be expressed and executed as a T-SQL batch. If you think you need stored procedures to avoid network roundtrips in the middle of your transaction logic, you’d be wrong. Stored procedures are essentially a crude, database-bound API layer. For serious application development, a proper service layer provides stronger contracts, authentication, testing, versioning, observability and source control in a sane general-purpose language, ideally the same one you’re already manipulating the data with elsewhere.
- ants_a 11d agoMeh, a T-SQL batch is just an anonymous transient stored procedure. I guess we agree on them being an API that gets deployed on the database, but I disagree that it needs to be crude. It's exactly as crude as you make it. If you don't have authentication, testing, versioning, observability and source control for your database you are doing it wrong.