home Links Articles Books Past Meetings Photos SiteMap
The MDCFUG is sponsored by TeraTech. Visit us at www.TeraTech.com

Please send
comments/questions to

michael@
teratech.com

 

Views and SPs from ColdFusion

Written from posts to CF-Community by Jim Nitterauer, Bob Silverberg, Philip Arnold and others.

Views
View are SQL servers name for Queries in Access. They are stored single SQL statements.

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....

Views enhance performance very dramatically by doing the following:

  1. SQL Server manages views as if they were actual tables therefore the results of the SQL Statement defining the view are instantly available.
  2. The Database connection - i.e. ODBC or preferably OLEDB does not have to translate a complex SQL statement, pass it off to the DB server and wait for the DB server to act on it. This lowers the overhead on the Web server in some cases quite significantly.
  3. You can define indexes for views that enhance performance significantly.

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.

Stored Procedures
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:

  1. 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.
  2. 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.
  3. 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.

I generally use <cfquery> to call my stored procedures. Others prefer <cfstoredproc>. You cannot use <cfquery> if you want to:

- capture the return code from the SP
- capture an output parameters of the SP
- return more than one result set

If you don't need to do any of the above (which is about 90% of the time for me), you could use the following code:

<cfset myNumber = 55.55>
<cfset myString = "Nice stored procedure, Buddy.">
<cfset myDate = "2001-03-30">
<cfquery name="myQuery" datasource="myDSN">
Exec MyStoredProcedure @NumberParam=#Val(myNumber)#,
@StringParam='#myString#', @DateParam='#myDate#'
</cfquery>

Then you just use the query as you would normally, e.g., <cfoutput query="myQuery> etc.

You should also use the T-SQL statements SET NOCOUNT ON before running the SELECT and SET NOCOUNT OFF after running the SELECT. You could include these inside your stored procedure, or you can include them in you call, so it would look like:

<cfquery name="myQuery" datasource="myDSN">
SET NOCOUNT ON
Exec MyStoredProcedure @NumberParam=#Val(myNumber)#,
@StringParam='#myString#', @DateParam='#myDate#'
SET NOCOUNT OFF
</cfquery>

from the SQL Server help

SET NOCOUNT (T-SQL)
Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

Syntax
SET NOCOUNT {ON | OFF}

Remarks
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft® SQL Server™ to execute queries, the results prevent “nn rows affected” from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.

For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.

The setting of SET NOCOUNT is set at execute or run time and not at parse time.

Home | Links | Articles | Past Meetings | Meeting Photos | Site Map
About MDCFUG | Join | Mailing List |Forums | Directions |Suggestions | Quotes | Newbie Tips
TOP

Copyright © 1997-2017, Maryland Cold Fusion User Group. All rights reserved.
< >