Q: Isn't in true that there are significant performance gains when using Views instead of Tables? What are some of the limitations of Views? For example, they're not updatable right....
We have seen dramatic improvements in performance by switching from ODBC to OLEDB and by incorporating views. Dramatic translates to 10 to 20 fold decrease in processing time.
Views are also an excellent way to enforce security -- you can exclude
certain columns from the base table(s) when creating a view, and set
permissions for retrieval of the view but not the base table(s), to ensure sensitive data isn't compromised.
Another advantage is you can create and drop views on the fly without
effecting the underlying data.
Another way in which a view could help is if SQL in the view is optimal. Perhaps a user writing the SQL from scratch would not write the "best" SQL. If you have your expert create the views then you know that your "non-expert" users won't be issuing bad join statements against the base tables.
SPs are like super Views that can contain multiple SQL statements and other programming logic like loops and if statements written in the T-SQL language. They are compiled.
Q: When would you NOT want to use stored procedures to run queries etc. on a database?
Wouldn't a stored procedure almost always run faster than one done through CF?
If you're building a completely dynamic bit of SQL, then you just can't do it in a SP
Also, SPs aren't always faster - I have some SQL where the tables are
Indexed to hell and back - the CFQuery version is faster than the SP by
about 10%, don't ask me why though
Also, also, quite often we have tons of queries in a site and it's easier to manage without hitting hundreds of SPs than allow the SQL to be updated in the templates
Also, also, also (this is just getting silly now), you just HAVE to do some things in CFQuery - like altering a table structure - it'd be silly making a SP for a one-shot SQL statement
Although I use stored procedures for about 95% of my database calls, I would agree that there are some situations in which you might choose not to use them.
You can do quite a bit of complex programming inside your SPs (e.g., using Transact-SQL in SQL Server), but you may not know that language. If you don't have the time or inclination to learn a new language, you might stick with using CF for some complex processing that could otherwise have been encapsulated within an SP.
I both agree and disagree with Philip regarding building dynamic SQL. I cannot think of many cases where you _cannot_ do it in an SP, but if you are actually building your SQL statement from within the SP, then you lose one of the performance benefits of SPs (it cannot be pre-compiled).
It is surprising just how much you can do within an SP when you start to
investigate the programming language. There are also a few tricks you can use to create somewhat dynamic SQL statements that _can_ be precompiled. I'd strongly encourage anyone who is using a database that supports SPs to learn the language of their database and challenge themselves to move as much of the processing as possible into the database. You do end up with huge performance gains in some scenarios - but with a simple SELECT statement, it's not such a big deal.
I would add another reason not to do SPs - they are harder to debug
from CF that straight SQL - when it doesn't work you don't get a good error message from CF... Of course the regular CFQUERY
error message isn't great either but at least you can cut and paste
the SQL back into your query builder to test...
I think writing SPs takes longer than inline SQL - with two enviroments
(CF and SQL ent manager) and testing. Only makes sense on speed or load critical code. Straight SQL is better for prototyping too for this reason.
But point worth noting, if you are using SQL Server you could actually
be using SP's without knowing it. There is a driver option (often on by
default) in the SQL Server Control Panel applet that instructs Sql
Server to auto-generate a SP for submitted SQL statements. These persist for the active connection, so you could be reusing SP's and getting some of the benefit of doing so already.
One big advantage to SPs that hasn't really been discussed is
encapsulation. What do I mean by that? Glad you asked:
- You can hide the complexities of the database from the web
developer.
This is great with larger development teams. In our shop, I write
most of the SPs. I then give the developer the name of the SP, the parameters that it accepts, and the result set(s) that it returns. (By the way, Fusedocs are great for this). They can then write their CF code without having to worry about the fact that there may be 10 tables being accessed behind the scenes.
- If, heaven forbid, you need to use another language (in addition to
ColdFusion) to talk to the database (like VB or ASP), you can use the
same stored procedures. If the SPs include business logic (i.e., they
don't just add and update data, but they do things like adjusting balances), you don't need to rewrite that business logic in the new language. You just call the SP and it does all of the work for you.
- If the underlying database changes, but the parameters required and
the result sets returned stay the same, you don't have to change any of
your ColdFusion code. I could go from a database with 5 tables to one with 10 tables. Sure, I'd have to rewrite all of my SPs (you'd have to
rewrite your embedded queries too), but I could leave my entire CF app untouched. Obviously this is an extreme example - you'd generally have to change some of the parameters or result sets with a major db change like that, but you get the idea.
TOP
