2 ms·
"Performance" Performance is my primary reason for using stored procedures. The thing that almost every high-performance app eventually runs up against is shee
by jerf 4y ago
"Performance"
Performance is my primary reason for using stored procedures. The thing that almost every high-performance app eventually runs up against is sheer query quantity. No matter how good you are with doing set operations rather than operating on one value at a time, the time inevitably comes when you need to do a query from your app, do something to the result, and generate the next query, and repeat this for an arbitrary number of times. If you can move that process to the database, you can get performance wins so massive your peers won't believe such wins were even possible.
"Perhaps the biggest argument against it is the fact that most SQL-based languages are firmly stuck in the 80s."
Postgres is chipping away at this at least. It's still a mess, but every time I leave for a couple of years and come back, the game has been upped. I don't know exactly how long it's been in there, but as a for-instance, Postgres stored procedures have print debugging now, which I described in a Go context here but should apply to any other language that has a good Postgres-specific driver: https://www.reddit.com/r/golang/comments/12k6c6m/persistent_data_with_golang/jg3tf0c/?context=3 https://www.reddit.com/r/golang/comments/12k6c6m/persistent_... That's just a night & day change. I can live with print-debugging even if I don't get a true debugger, but working without even print debugging is a little too 1960s for my tastes. There's also been a lot of improvement in being able to intermix arrays, tables, and other such things with each other, which used to be a major hassle, though it's still pretty chaotic in terms of naming schemes and such.
- Capricorn2481 4y agoCan you test any of this logic?
- jerf 4y agoTesting code is exactly where I'm doing the print debugging from. I'm sure not doing it on production unless I have no choice! I don't take a dogmatic view of testing. There is no requirement that test code in X must be run in a test harness in X. There is no problem testing my database procedures with code being driven from my primary app language. What matters is that cases are covered, and that the desired outcomes are verified. My database code is perfectly well covered with testing. If yours isn't, that's a process problem on your end and is your problem, not mine.
- Capricorn2481 4y agoI wasn't accusing you of anything, I was genuinely asking. Do you mean that your testing logic is done in the code? I don't really know what that has to do with print debugging. Are you reading output from the database and running assertions on that?
- branko_d 4y agoFor what it’s worth, we implement tests in the client language (C# in our case) and just call the stored procedures/views (in SQL Server) as any client would. Most tests are written so that they rollback after execution, and to tolerate any residual data that might have been left after the tests that must commit. A very small number of tests require a clean database, which we accomplish by traversing INFORMATION_SCHEMA and just emptying all the tables (we also temporarily disable constraints and triggers). These tests are just normal “unit” tests from the perspective of Visual Studio, which brings one enormous benefit: you can put a breakpoint in T-SQL code and just right-click on the test and choose Debug, and the breakpoint will be hit! That’s possible because Visual Studio debugger can switch from C# to T-SQL seamlessly. This is a fantastic way to debug in exactly the right test case which exposes the bug or behavior that you want to test.
- Capricorn2481 4y agoVery cool, thanks for sharing
- wswope 4y ago> I can live with print-debugging even if I don't get a true debugger There’s been a plpgsql debugger built into pgadmin for about a decade, FYI.