| Author |
Topic |
|
dubem1
Starting Member
4 Posts |
 Posted - 2002-07-11 : 11:41:57
|
| We are designing a new software and we are debating if we place all our queries in stored procedures or if we simply place them directly in the software code.what's your opinion on this topic??thank you |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-11 : 11:45:26
|
| I'm curious, what reasons have you come up with for each side of the debate so far?<O> |
 |
|
|
dubem1
Starting Member
4 Posts |
Posted - 2002-07-11 : 12:04:30
|
| Well there is a lotBut the main reason to place all queries in stored procs in a performance issue.and the main reason to the DB team to not have stores proc is that they have to maintain stored procs in sql and also in oracle (because we have both SGDB)And we don't really know about maintenance. It is easier to maintain queries in the software or in stored proc. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-07-11 : 12:11:29
|
| If you use built in queries in your software: The biggest disadvantage of this is that everytime you have a bug or need to make an update to your database schema you will have to re-deploy your software. If you use stored procedures: Assuming that your software code is good and you need to change the database schema you can do so in a single batch script and update all the stored procedures so they handle the new schema. Of course you will still have to redeploy if you add/remove parameters. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-11 : 12:19:29
|
| Well, I can tell you this. Maintaining Oracle and SQL Server versions of the queries or procedures is equally difficult with embedded SQL, because each product has subtle syntax differences. Trying to find a least-common denominator of code for the two will only make both of them perform poorly. Each product should have the code specifically written for it.Stored procedures are my recommendation. Maintenance becomes easier because you are more or less forced to spell out the steps of a procedure and break them up into smaller units. You may end up with more procedures, but they become much easier to fix because they are smaller.Even more importantly, if you design your stored procedures correctly, they will appear and function the same way to the application that calls them, regardless of whether it's an Oracle or SQL Server database. In essence, your procedure call looks like this:connObj.Execute "EXECUTE myProcedure @parameter1=15, @account='ABC Industries'"Your SQL Server and Oracle procedure simply accept those parameters and do whatever needs to be done to process them correctly. Except for minor syntax changes, you can use the above snippet of code as a template for all of applications that call those databases. You cannot believe how quickly you can put together an application when all you have to do is copy and paste existing procedure calls with little or no modification.Lastly, should you need to change the stored procedures or modify a table used by them, you can do it without having to modify the application code. If you used embedded SQL in multiple application you'd have to change ALL of them, hunting and pecking through the code. |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2002-07-11 : 12:20:25
|
| In my experience, I can't remember a single case where it was better to have SQL embedded in an application instead of using stored procs.If you're careful about using ANSI-compliant SQL, you should be able to copy and paste lots of stored proc code between SQLServer and Oracle. The create statements will be different, but the meat of the proc will be the same.-- monkey |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-07-11 : 16:31:31
|
| And one more reason to use stored procedures: REUSABILITY. If you need the same info from another section of your code, you don't have to repeat the entire query... And this gets back to maintainability. |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-07-12 : 02:02:08
|
| What robvolk was saying ties in with the whole idea of having a data access layer as well, so you have the data layer with the tables etc and the stored procedures, the data access layer that deals with all the 'objCmd.Execute' stuff, and the next layer up only has to call a function like 'getLatestSales()' from the data access layer and doesn't have to worry about anything else at all. Sorted. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-12 : 08:11:32
|
| To say the same thing, a bit more abstractly....The role of a database professional is to take a logical model, given by a data modeler, and implement a physical model in a DBMS. Then, provide in interface back to the logical model to the applications developer.<O> |
|
|
|
|
use stored Procs or not