We're noisy . a brand new project, and that we are actually wondering when we should use saved methods in MySQL or otherwise.
We'd make use of the saved methods simply to place increase business design organizations. You will find several tables which represent one entity, and that we would abstract it in individuals saved methods place/update.
However, we are able to call place increase in the Model layer although not in MySQL however in PHP.
Inside your experience, The best idea option? pros and cons for both approaches. The quickest one when it comes to high end?
PS: It's is really a web project with mostly read and performance is an essential requisite.
Unlike actual programming language code, they:
- not portable (every db features its own version of PL/SQL. Sometimes different versions from the same database are incompatible - I have seen it!)
- not easily testable - you'll need a real (dev) database instance to check them and therefore unit testing their code included in a build is virtually impossible
- not easily updatable/releasable - you have to drop/create them, ie modify the development db to produce them
- don't have library support (why write code when another person has)
- aren't easily integratable along with other technologies (try calling an internet service from their store)
- are usually about as primitive as Fortran and therefore are inelegant and laborious to obtain helpful coding done
- don't offer debugging/tracing/message-logging etc (some dbs may support this - I've not seen it though)
- lack a good IDE to assist with syntax and connecting with other existing methods (eg like Eclipse does for java)
- people skilled in coding options are more rare and much more costly than application programmers
- their "high endInch is really a myth, simply because they execute around the database server they often increase the db server load, so with them will often reduce your maximum transaction throughput
- etc. For those who have a really database-specific action (eg an in-transaction action to keep db integrity), or keep the methods very atomic and straightforward, possibly you may consider them.
Caution is suggested when indicating "high endInch in advance. It frequently results in poor options at the fee for good design and it'll bite you much earlier than you believe.
Use saved methods at the own peril (from someone who's had the experience rather than wants to return). My recommendation would be to prevent them such as the plague.
Saved methods are great to make use of simply because they keep the queries organized and permit you to execute a batch at the same time. Saved methods are usually quick in execution since they're pre-put together, unlike queries which are put together on every run. It has significant impact in situations where database is on the remote server if queries have been in a PHP script, you will find multiple communication between your application and also the database server - the totally send, performed, and result tossed back. However, if using saved methods, it only have to send a little CALL statement rather than large, complicated queries.
It could take some time to adjust to programming a saved procedure simply because they get their own language and syntaxes. But when you are accustomed to it, you'll notice that your code is actually clean.
When it comes to performance, may possibly not be any significant gain if you are using saved methods or otherwise.
I would suggest that you simply avoid DB specific Saved Methods.
I have experienced lots of projects where they suddently wish to switch DB platform and also the code in the SP is generally not so portable = work and possible errors.
Saved Procedure development also necessitates the developer to possess access straight to the SQL-engine, while an ordinary connection could be transformed by anybody within the project with code-access only.
Relating to your Model/layer/tier idea: yes, stick to that.
- Website calls Business layer (BL)
- BL calls Data layer (DL)
- DL calls whatever storage (SQL, XML, Webservice, Electrical sockets, Textfiles etc.)
By doing this you are able to keep up with the logic level between tiers. IF and Only When the DL calls appears to be really slow, you can begin to fiddle around with Saved Methods, but keep up with the original none-SP code somewhere, should you suddently have to transfer the DB to another platform. With the Cloud-hosting in the industry, who knows what is the next DB platform...
I keep an eye on Amazon . com AWS of the extremely same reason.