3 ms·

The answer to every problem is to rebuild statistics. (And never use stored procedures. It's just a shitty API layer in the worst language imaginable, sitting o

The answer to every problem is to rebuild statistics. (And never use stored procedures. It's just a shitty API layer in the worst language imaginable, sitting outside of source control. If you need an API layer, write it in a real language, ideally the one you're already using.)

mr_toad 9d ago
In my experience with databases the answer is never never, and never always, and it’s almost always sometimes and maybe.
- simondotau 8d ago
  That’s mostly occasionally accurate.
ants_a 9d 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 8d ago
  SQL 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 5d ago
    Meh, 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.

mr_toad 9d ago

In my experience with databases the answer is never never, and never always, and it’s almost always sometimes and maybe.

simondotau 8d ago
That’s mostly occasionally accurate.

ants_a 9d 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 8d ago
SQL 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 5d ago
  Meh, 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.