Thread

1. 2001zhaozhao · 1789596999 · focus · HN ↗

   Engineer: "HELP, our production DB is frozen on this query that worked fine before!"

   Infra: "Hmm, let's check... Well would you look at that, it seems like your LLM query planner usually works and produces fast queries, but this time when you changed a variable name to trigger query rebuild, it happened to hallucinate and miss an index, would you mind re-running the LLM a few times until you get a faster query?"

   1. reval · 1789604604 · focus · HN ↗

      I’ve seen this happen to SQL Server many times. Every time the solution is a stored proc with the recompile option enabled.

      1. simondotau · 1789615116 · focus · HN ↗

         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.)

         1. mr_toad · 1789658208 · focus · HN ↗

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

            1. simondotau · 1789744225 · focus · HN ↗

               That’s mostly occasionally accurate.
         2. ants_a · 1789721947 · focus · HN ↗

            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.

            1. simondotau · 1789745083 · focus · HN ↗

               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.

               1. ants_a · 1790010942 · focus · HN ↗

                  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.

      2. twellborn · 1790000915 · focus · HN ↗

         [dead]

Unofficial Hacker News client; not affiliated with Y Combinator.