4 ms·
> The primary issue I have seen with stored procedures is how you update them. I would be curious how they manage that. 1. You put your stored procedures in gi
by taffer 6y ago
> The primary issue I have seen with stored procedures is how you update them. I would be curious how they manage that.
1. You put your stored procedures in git.
2. You write tests for your stored procedures and have them run as part of your CI.
3. You put your stored procedures in separate schema(s) and deploy them by dropping and recreating the schema(s). You never log into the server and change things by hand.
- mattmanser 6y agoWouldn't that lose all the cached query plans every deploy? Could be wrong, bit rusty on that stuff now, been a while since I worked on something where we had to worry about that.
- magicalhippo 6y agoWe have a db schema update tool. We generate an XML file representing the schema which is committed to source control. Then when new version is being deployed, the tool compares the database with the XML, and generates SQL to alter the database. For stored procs etc it compares the text of them, so those that weren't changed are ignored. It's a simple homebrew tool but it gets the job done.
- somurzakov 6y agorebuilding query plan takes less than 50ms and is routinely done by the engine itself without you ever realizing it. what you probably wanted to mention is table statistics - but they are cleared only if you truncate your table, but then again - once you populate your table - the engine will recalc statistics by itself. overall RDBMS does a lot of stuff behind the scenes for you, and you should take advantage of it, and instead think about more important atuff - business logic, data modeling, schema evolution, etc