Views or Stored Procedures to view data
-
Another question!
The database I am building will be accessed via a PHP frontend.
As I have normalised the tables many joins are required to retrieve data about one contact.
I had thought of using views to view the data, but was wondering whether it would be better to use Stored Procedures? Sorry if this is a basic question.
-
The anwer is as always: It depends.
I personnaly prefer to use stored procedures for the following reasons:
- Better encapsulation of the business logic (you can have several select update insert delete within one SP)
- Better security (no access to any objects within the database expect SP, you are not allowed to inser, update only via SP where all the parameters can be/must be checked before inserting the values into the table)
- Fewer characters to send over the network ("exec MyStoredProc 'kkjhgb', 15, 'jj'" has less chars then "select * from MyView where column1 = 'kkjhgb' and coluns2 = 15 and coloun3 = 'jj'"
- You can hide your schema definition by using SPs
- SP is faster (but here the difference is minimal compared to a view). The SP is precompiled and the where clause won't change, just the values
Bye
Gabor
Bye
Gabor -
One of the main advantages of PHP is platform independence. If you start using non standard SQL features such as MSSQL stored procedures, your application will become less portable and future proof.
I'd advise against using anything *except* straightforward queries for PHP development. That way you can be sure that you can migrate to MySQL, Interbase etc at a later date.
quote:
Another question!The database I am building will be accessed via a PHP frontend.
As I have normalised the tables many joins are required to retrieve data about one contact.
I had thought of using views to view the data, but was wondering whether it would be better to use Stored Procedures? Sorry if this is a basic question.
-
JeffDyer's point does not rule out using views, only SPs.
Some queries may require stored procs, of course, e.g. crosstabs, returning lists of values in a single field etc., so with this in mind you might want to use SPs for all data access for consistency's sake, if you're not worried about migration issues.
I have also found that SQL often peforms better when queries are broken down into steps using temp tables (Really annoying this - shows what a load of rubbish SQL's query optimiser is) - so in that case you might need SPs too.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
One more advantage that stored procedures may have above views is that their execution plans can checked.
He who knows others is learned but the wise one is one who knows himself.
He who knows others is learned but the wise one is one who knows himself. -
jeffdyer,
I have to point out: there is no standard SQL!
Every RDBMS implement his own SQL dialect.
Of cours if you want to have a very simple select, update insert delete that's fine. But as long you will start with some tricky queries you will have to use the RDBMS extension. There is no true SQL2 or SQL3 implementation.
Just a comparison: Lets compare SQL Server, DB2, Oracle, MySql, PostgreSQL... for the following simple query:
select top 10 * from authors.
Any standard answer valide for the above RDBMS?
Bye
Gabor
Bye
Gabor -
You all seem dead set against views so let me put a point in their favour.
1. You can update data if its in a view - this could be a security problem or a read benefit.
2. When you come back to make a mod to your application in 3 months time, its much easier interpreting a view using designer than pure SQL
P
-
Gabor, You are of course correct.
However, one important rule in programming is "KISS" (Keep It Simple Stupid). Using straightforward queries and avoiding proprietary functions *whenever possible* is always a good plan if you have no compelling reason to do otherwise.
Of course, if you have to do a Top 10 query, you will have to refer to your manual anyway!
Regards.
quote:
jeffdyer,I have to point out: there is no standard SQL!
Every RDBMS implement his own SQL dialect.
Of cours if you want to have a very simple select, update insert delete that's fine. But as long you will start with some tricky queries you will have to use the RDBMS extension. There is no true SQL2 or SQL3 implementation.
Just a comparison: Lets compare SQL Server, DB2, Oracle, MySql, PostgreSQL... for the following simple query:
select top 10 * from authors.
Any standard answer valide for the above RDBMS?
Bye
Gabor
-
quote:
However, one important rule in programming is "KISS" (Keep It Simple Stupid). Using straightforward queries and avoiding proprietary functions *whenever possible* is always a good plan if you have no compelling reason to do otherwise.
well, actually there is, was, should be some kind of standard ANSI SQL.
But I don't know how far you will get with this when you develop solely against one specific RDBMS. In this case those proprietary functions add certainly value.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You are right, Frank. I'm a "general purpose" developer working for my own small software house - I use MySQL, Access, SQL Server, Interbase, Paradox on a regular basis, depending upon the size and type of the job.
One intranet application was written in Delphi for MS SQL Server, then ported to MySQL and Interbase for different customers - in circumstances like these, I'd certainly appreciate a new ANSI SQL.
The original poster said she was developing using PHP for the web, so I'd thinkkeeping your back end database options open to be important here.
Best regards,
Jeff Dyer
http://www.logicsoftware.co.uk
quote:
However, one important rule in programming is "KISS" (Keep It Simple Stupid). Using straightforward queries and avoiding proprietary functions *whenever possible* is always a good plan if you have no compelling reason to do otherwise.
well, actually there is, was, should be some kind of standard ANSI SQL.
But I don't know how far you will get with this when you develop solely against one specific RDBMS. In this case those proprietary functions add certainly value.
Frank
[/quote]
-
quote:
You are right, Frank. I'm a "general purpose" developer working for my own small software house - I use MySQL, Access, SQL Server, Interbase, Paradox on a regular basis, depending upon the size and type of the job.
I was once involved in developing an application that was supposed to be database independent. We used a smalltalk (anyone knows this language?) framework that should do the whole database stuff for us.
Well, it did. We could use SQL Server, DB2, Oracle. So far, so good. The downside was that everything was loaded into memory. So as the database size grew, performance was hurt that much, that we decided to go down to database level and do some programming there and get away from that framework.
Database independence was not given anymore.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply