If you typically pull a bunch of data together, such as customer name, address, credit limit etc, then it can be less work for the db to do that in a single round trip call to a procedure than half a dozen separate calls/statements. It's really not much different to a python or shell script.Īs for performance, there's a fallacy in believing that doing more work outside the db must mean you're putting less load on the db.
Sqlite stored procedures code#
There's no barrier to putting stored procedure code under version control. Besides, this solution compiles TSQL into C code that uses the SQLite API to do the same things. You don't lose much piping data in and out of it because it's not going very far. Your application doesn't actually need to know every piece of information, so instead of piping it both ways across the network, the entire operation happens inside a stored procedure and the application just starts it.īut SQLite is a C library running in the same process. Performance: You've got a process that requires reading and writing a lot of data, but the source and destination are both database tables. Your application is probably going to be the only thing that interacts with it. It isn't going to be a big, high-performance analytics database powering your enterprise.
Sqlite stored procedures how to#
They know how to write efficient queries, so use the queries they give you because the application team is inevitably going to write queries that use full table scans and bring down production.īut SQLite is a local data store. I've heard of using stored procedures for two reasons:Įxpertise: You've got a dedicated DBA team who understands and maintains the database. SQLite is a library, not a daemon, and I can't imagine anyone running a high-volume, performance-intensive application off of it.īut the way I figure, why get in the habit of doing stored procedures with SQLite? What does it give you over just doing it in application code? Granted, many of these don't apply when using SQLite. So, the goal is to minimize the amount of work your database has to do, leaving your database to track data and relationships between entities, and moving your application logic off of it. But databases don't scale the same way (unless you relax some constraints of ACID). When you're building a service that has to handle large amount of traffic, you want to be able to spread the load between multiple servers. Performance: Parallelism is constrained by the most single-threaded part of the system. If you need to reason about the behavior of the entire system, now you have to look at the database as well. Traceability: If some of your logic is in the database instead of the application code, it's no longer all in one place. Changes to stored procedures can bypass this flow.
When application code changes, there are tools to require a formal code review process and CI/CD.
When a stored procedure changes, there's nothing in VCS to track itĪccountability: Stored procedures aren't stored under version control. Versioning: Stored procedures aren't stored under version control. In my limited development experience, I've been indoctrinated to believe that stored procedures are Of The Devil. Okay, let me re-phrase, this time with more explanation and less snarkiness:
I know I'm kind of asking a silly question, and I can probably invent a few answers to answer it if I tried, but.
0 kommentar(er)
