Hi all,
I'm coming into this late as I just got up (it's 8:00am here). I agree with
Marc and Rick's comments that the performance gain that you'll see from SPs
is proportional to the complexity of your SQL statements. It can also be
argued (as I believe Marc pointed out) that there is some benefit to moving
all of your SQL into SPs (to separate the logic from the CFM templates).
In your specific example, I would actually use a System Stored Procedure.
Generally, when you want information about the structure of your database
you can use one of SQL Server's built in SPs, which are referred to as
System Stored Procedures. These perform very well, as they are built into
the db engine itself.
To return a list of columns in a table, you can use the sp_columns
procedure. The CF would look something like this:
<cfquery name="qryMyColumns" datasource="MyDSN">
SET NOCOUNT ON
EXEC sp_columns [your table name here]
SET NOCOUNT OFF
</cfquery>
<cflock scope="APPLICATION" type="EXCLUSIVE" timeout="10">
<cfset application.fieldlist = ValueList(qryMyColumns.column_name)>
</cflock>
Although the difference might be minimal, this would perform better than
selecting a row from the actual table and then reading the ColumnList from
the resulting query.
Books Online is an excellent resource for learning about these System Stored
Procedures (what they are, what parameters they take, and what results they
return). I use them all the time for building utilities (like code
generators).
Good luck,
Bob
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of Rick Mason
> Sent: Wednesday, 31 January 2001 7:11 a.m.
> To: [EMAIL PROTECTED]
> Subject: Re: [CFTALKTor] SQL - Returning Column Names
>
>
> > DISCLAIMER ;)
> > I'm not a SQL Server guru, soemone please correct/complete me if my
> > understanding is wrong/unclear.
>
> Neither am I but....
>
> My understanding from what we talked about last night Nolan is
> that you want
> to do as Marc suggested and separate the SQL from the CFML to further
> simplfy the CFML. This would be a reason for doing this.
>
> As for performance increases, on a query that only returns a small result
> set the performance increase would be insignificant. In my experience the
> performance differences are only noticable when doing something that
> involves a great deal of processing such as multiple inserts into mulitple
> tables.
>
>
> Rick Mason
> Pangaea NewMedia, Inc.
> http://www.pangaeanewmedia.ca
> 416-922-1600
>
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, January 30, 2002 12:43 PM
> Subject: RE: [CFTALKTor] SQL - Returning Column Names
>
>
> > > I've just started to
> > > learn about Stored Procedures, and benefits of running/referring to
> > > queries on the SQL server level (cfstoredproc) as opposed to CF level
> > > (referring to the query, not the stored proc)
> >
> > I'm not sure what you mean by "running/referring to queries on the SQL
> > server level (cfstoredproc)".
> >
> > DISCLAIMER ;)
> > I'm not a SQL Server guru, soemone please correct/complete me if my
> > understanding is wrong/unclear.
> >
> > Basically, a Stored Procedure is a piece of SQL code that resides in the
> > RDBMS and as already been checked for syntax errors and been precompiled
> > where as when you submit a query through ODBC it has to be parsed for
> syntax
> > errors, then compiled and run, thus a stored procedure would return
> results
> > faster since it is already parsed and compiled.
> >
> > > Would there be any
> > > benefit to setting the application var (myList) based on the stored
> > > procedure (the query in SQL)? Is this even possible? Would
> you notice
> > > a performance difference? Or.. would it be best to just run
> query in the
> > > application file?
> >
> > I don't really understand that statement. What do you want to
> achieve with
> > that list? Why set it as an App variable?
> >
> > From what I get, it doesn't make a huge difference in
> performance whether
> > you use a stored procedure or a CFQuery to return a one row
> recordset. It
> > might be good if you plan on putting ALL your apps SQL code in stored
> > procedures, just for the sake of separting the SQL code from
> the CFM code
> or
> > whatever logic is behind that. Some DBA's prefer it that way since they
> > don't have to bother with CFM code. For simplicity, maybe a bit of
> lazyness,
> > I would just use a CFQUERY from what I understand.
> >
> > If you could give more details maybe I, or someone else, could
> advise you
> > better..
> >
> > Marc
> >
> > -
> > You are subscribed to the CFUGToronto CFTALK ListSRV.
> > This message has been posted by: "Marc Campeau" <[EMAIL PROTECTED]>
> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
> > Manager: Kevin Towes ([EMAIL PROTECTED])
> http://www.CFUGToronto.org/
> > This System has been donated by Infopreneur, Inc.
> > (http://www.infopreneur.net)
> >
> >
>
>
> -
> You are subscribed to the CFUGToronto CFTALK ListSRV.
> This message has been posted by: "Rick Mason" <[EMAIL PROTECTED]>
> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
> Manager: Kevin Towes ([EMAIL PROTECTED])
http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "Bob Silverberg" <[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
