Well the SP used in the example is poor - which is no doubt why it had bad
performance.
-Original Message-
From: Brian Peddle [mailto:[EMAIL PROTECTED]
Sent: 30 October 2005 22:31
To: CF-Talk
Subject: RE: Philosophy Q: SP's or CFQUERY?
You mention SP's will give greater performance
: Philosophy Q: SP's or CFQUERY?
And what about the security factor? I've always been under the assumption
that if your CF only had access to run SPs you were safer from SQL
injection. I'd like to hear Neil Robertson-Ravo chime in on this come 8:00
am London time.
I didn't know there were so many
Am I that predictable with SP's ;-) They are a strange beast
Stored Procedures are in general more efficient than inline SQL on a variety
of frontiers. Firstly are generally more secure than inline SQL as of
course you can adhere granular levels of security to them - you cannot do
this with
| 1. Is the query results going to be cached by CF (a list of all the
| mailing lists on a site)? If so, use standard.
I prefer caching in e.g. application scope anyways, so then it doesn't
matter.
| 2. Is the query results based on a large group of data with many
| people viewing the data (a
The problem with CF and inline SQL is that the DataDirect
driver is actually pretty good at inline SQL parsing in as
far as speed is concerned and terrible at SP execution and
return ;-)
That's the first I've heard of this - do you have any numbers to back this
up? I'm not trying to be
I would agree with that, but you can be just as safe with
inline SQL if you scrub the variables properly. I've seen
people scream in horror over a query like this...
WHERE id = #url.id#
Until I point out...
cfset url.id = abs(val(trim(url.id)))
As part of the scrubbing
recordsets (BLOB/CLOB) it will simply stop
responding - seems like on an SP with around 8-10 large recordsets returned
it will just fall over.
N
-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: 31 October 2005 16:14
To: CF-Talk
Subject: RE: Philosophy Q: SP's or CFQUERY
Since CFQUERYPARAM also generally provides a performance
benefit, why wouldn't you just use that? What do you see
as the advantage of your data scrubbing?
It depends on the project. If the variables are scrubbed from the
beginning, some basic error checking can be run that would act before
The problem with having the query come directly from ColdFusion
was that the query was different for each foreign key, which
required the SQL server to compile a new query plan (which
could be thousands of plans that would get cycled out of memory
as new ones were compiled), which took
And what about the security factor? I've always been under
the assumption that if your CF only had access to run SPs
you were safer from SQL injection.
Well, technically, if you are consistent in your use of CFQUERYPARAM, it
will have the same effect. However, one common approach in security
Since CFQUERYPARAM also generally provides a performance
benefit, why wouldn't you just use that? What do you see
as the advantage of your data scrubbing?
It depends on the project. If the variables are scrubbed from the
beginning, some basic error checking can be run that would
act
This is through experience; try and run some CPU intensive
processes and see how badly the DataDirect driver performs
I'm not sure what you mean by CPU-intensive, since most of the work is
being done by the database. Are you simply referring to large recordsets? If
so, are you also saying that
I guess I don't see the time spent to write a CFQUERYPARAM
tag as a significant addition of expense.
It's not, and at this point I do use them pretty much by default in addition
to input scrubbing.
Also, if you pass an invalid data value to a CFQUERYPARAM
tag, CF will prevent the query from
I prefer that ColdFusion not need to throw exceptions at all
if I can help it. I'd rather my sites not look like myspace.com
with a basic there was an error screen. If I can find the error
in advance through scrubbing or validation, I'd rather handle it
my own way.
There's no reason you
There's no reason you can't get the exact same result
using exception handling as you're doing now using
conditional logic.
I like to think of it as pre-exception handling :)
-Justin Scott
~|
Find out how CFTicket can
just too damn lazy to code
more than I have to ;-)
-Original Message-
From: Dave Watts
To: CF-Talk
Sent: 31/10/2005 16:50
Subject: RE: Philosophy Q: SP's or CFQUERY?
This is through experience; try and run some CPU intensive
processes and see how badly the DataDirect driver performs
I'm
I do second the too lazy to write SQL code if I don't have to. I have
recently had the luxury of having in my team a person who is more DB-aware
than I am. So now I just write library CFCs that have functions that call
the SPs. Periodically when I see that there is a one-off query that really
does
I'd use them only where there is a proven necessity. I.e. where the
performance gains (demonstrated by load testing) are worth the
trouble, or the security concerns are such that there's no other way.
SPs move your business logic out of your application and into the
database, which isn't
I went through the route of inline SQL, then to SPs, and now I'm back to
inline SQL. (When I say inline, I mean anything other than SPs.)
We use many related DBs on our SQL Server. Many queries require joins
across DBs. SPs caused us all kinds of problems when we moved DBs from
one server to
You mention SP's will give greater performance. I think that may be a myth
these days. Google around and you will find all sorts of debates on it. I
was shocked myself after spending a good bit of day taking some queries from
a CF page and dumping them into a shiny new stored proc and things
Two simple rules I use are:
1. Is the query results going to be cached by CF (a list of all the mailing
lists on a site)? If so, use standard.
2. Is the query results based on a large group of data with many people viewing
the data (a day's messages for example) Is so, use SP.
Other than that,
The performance benefits that SPs bring are based around their ability
to act on a query result without having to send the resultset back
across the network to CF. I.e. if I have to do two queries and the
second depends on the results of the first, an SP will most likely be
more performant,
You mention SP's will give greater performance. I
think that may be a myth these days. Google around
and you will find all sorts of debates on it. I was
shocked myself after spending a good bit of day
taking some queries from a CF page and dumping them
into a shiny new stored proc and
I do agree it depends on situation and I' be curious to see how it performed
in ASP or .NET or some other language as opposed to CF.
-Original Message-
From: Justin D. Scott [mailto:[EMAIL PROTECTED]
Sent: Sunday, October 30, 2005 6:48 PM
To: CF-Talk
Subject: RE: Philosophy Q: SP's
I do agree it depends on situation and I' be curious
to see how it performed in ASP or .NET or some other
language as opposed to CF.
Given how much more difficult is it to pass a query from those languages
compared to ColdFusion, I think I would be more inclined to call a SP
anyway.
What do you mean in memory? And was your DB running on the same
hardware as CF? The DB has to do the same work in either case (some
randomization of a million rows), so the overhead of the SP/query
should be completely lost in the mass of time it'd take to deal with
the data.
cheers,
barneyb
What do you mean in memory?
SQL Server will hold as much of the most accessed data pages as it can in
memory once it's loaded from previous queries. This table gets hit like
crazy, so my guess is that the entire thing is loaded in memory after a
couple of hours.
And was your DB running on the
And what about the security factor? I've always been under the assumption
that if your CF only had access to run SPs you were safer from SQL
injection. I'd like to hear Neil Robertson-Ravo chime in on this come 8:00
am London time.
I didn't know there were so many proponents for inline SQL. I've
the CF page response time went down significantly
because it no longer had to wait so long for the
SQL server to process the queries.
After re-reading this line I think it may have been confusing. What I meant
was that the response time in ms went down, so the pages got a lot faster.
And
29 matches
Mail list logo
