RE: Philosophy Q: SP's or CFQUERY?
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. 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 didn't improve and actually seemed to slow down some. Here is one link http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx but there are many. -Original Message- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: Saturday, October 29, 2005 1:16 PM To: CF-Talk Subject: Philosophy Q: SP's or CFQUERY? I'm going back and tweaking a web app that I'm building and have learned a few stored procedure tricks (my SP skills were pretty basic before I started to play around). As a result, I'm replacing a number of CF queries (using cfqueryparam) with SQL Server stored procedures. Now, these are pretty much limited to the customer-facing (non-admin) pages, though I'm using SP's in the admin where I already have them written to return the rest sets that I need. Realizing that SP's will give greater performance, the question is this: Is it OK to write your most frequently run queries as SP's and leave the rest of the site as straight SQL queries? Or should I just make the entire site SP's? What I've been doing during development is writing straight SQL until I get what I need, then I rewrite as a SP. I've replaced a dozen or so queries that are all on the customer-facing end of things, but there are well over 100 queries still written as SQL on the admin side, and it would take a bit of effort to convert (or merge into existing SP's) those queries. Pete ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222703 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 inline SQL (unless you mount different DSN's). They also provide a greater control of abstraction from the underlying DB table which helps security - and does not require you to error trap and data preserve by using CFTRY/CFCATCH/CFLOCK every DB call but on the downside of that is you lose the portability which CF gives you. They are in most cases faster to process as their execution plans are 'cached' which are optimized - you do not get this kind of control with inline. They are also far easier to modify and document - which in a code world is faster. 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 ;-) until that changes The benefits could be only slightly marginal (if at all) in as far as speed is concerned - only in some cases will you find a huge performance boost in a SP; and that is likely to be with very large complex operations which would require multiple CF code-blocks to perform (and you cannot rely on QoQ in CF - well not in 6.1 you cannot). SP's simply allow you to perform decent and complex operations in the database - for some SP's are out reach as either they cannot use them based on the RDBMS or they just cant code them - and when they do they are not very efficient. The double edged sword for CF allows uses to get up and running with DB calls quickly and easily, but this has a downside in that a lot of SQL code which is produced is poor, very poor as hardly anyone would need to understand the beef of what goes on inside the SQL Engine (as in most cases, why would the...) or understand just exactly what T-SQL can do. I use SP's as its out standard and we prefer the abstraction as it allows out .NET apps etc to use them - we ery rarely use inline (we use QoQ on occasion). The case here is, if you can use SP's, then I would use them simply for the re-usability and added security, but obviously you can get just as much security from inline if coded correctly - performance at present is for the most part a redundant subject, but in some cases an SP will certainly be faster than inline if only the drivers could handle it- running a SQL Profiler will give you that info. N -Original Message- From: Greg Luce [mailto:[EMAIL PROTECTED] Sent: 31 October 2005 02:03 To: CF-Talk Subject: Re: 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 proponents for inline SQL. I've just always figured MSSQL was better able to run some pre-compiled execution plan in the case of an SP, over ad-hoc SQL queries. Neil? Dave Watts? Greg On 10/30/05, Justin D. Scott [EMAIL PROTECTED] wrote: 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 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. Perhaps ramdom wasn't the right word. The query isn't pulling random rows, but subsets based on a foreign key. The foreign key that is to be loaded is not predictable, and there are 10,000+ foreign keys that can be pulled at any time. 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 far longer than using one plan (from the SP) that was cached. Using CFQUERYPARAM on that variable may have helped, but I never tested it that way. I just went directly from dynamic query to stored procedure and 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. Even if I have some of the details wrong, that one change made things go a WHOLE lot smoother. -Justin Scott ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project
RE: Philosophy Q: SP's or CFQUERY?
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 inline SQL (unless you mount different DSN's). They also provide a greater control of abstraction from the underlying DB table which helps security - and does not require you to error trap and data preserve by using CFTRY/CFCATCH/CFLOCK every DB call but on the downside of that is you lose the portability which CF gives you. They are in most cases faster to process as their execution plans are 'cached' which are optimized - you do not get this kind of control with inline. They are also far easier to modify and document - which in a code world is faster. 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 ;-) until that changes The benefits could be only slightly marginal (if at all) in as far as speed is concerned - only in some cases will you find a huge performance boost in a SP; and that is likely to be with very large complex operations which would require multiple CF code-blocks to perform (and you cannot rely on QoQ in CF - well not in 6.1 you cannot). SP's simply allow you to perform decent and complex operations in the database - for some SP's are out reach as either they cannot use them based on the RDBMS or they just cant code them - and when they do they are not very efficient. The double edged sword for CF allows uses to get up and running with DB calls quickly and easily, but this has a downside in that a lot of SQL code which is produced is poor, very poor as hardly anyone would need to understand the beef of what goes on inside the SQL Engine (as in most cases, why would the...) or understand just exactly what T-SQL can do. I use SP's as its out standard and we prefer the abstraction as it allows out .NET apps etc to use them - we ery rarely use inline (we use QoQ on occasion). The case here is, if you can use SP's, then I would use them simply for the re-usability and added security, but obviously you can get just as much security from inline if coded correctly - performance at present is for the most part a redundant subject, but in some cases an SP will certainly be faster than inline if only the drivers could handle it- running a SQL Profiler will give you that info. N This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222706 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
| 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 day's messages for example) Is so, use SP. What is this based on? The large group of data will still have to travel from the db to cf. If it is used often it is better to cache it on the cf side. Mike -- I'd rather vote for Barney's arguments! /Hugo ### This message has been scanned by F-Secure Anti-Virus for Microsoft Exchange. For more information, connect to http://www.f-secure.com/ ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222710 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 critical, I just want to learn more about this. It doesn't really match with my experience so far. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222736 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 routine. Guarantees a positive integer value, and passes in 0 if it's a string. It's not the best way, but for small sites it's quick, easy, and pretty safe. CFQUERYPARAM would help also. 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? Unfortunately some people are still using access databases, and don't have the luxury of stored procedures. Actually, you can call Access parameter queries from CF as if they were stored procedures (which, essentially, I guess they are in the broad sense of the term.) I believe Charlie Arehart wrote an article about this. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222737 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
Hey Dave, This is through experience; try and run some CPU intensive processes and see how badly the DataDirect driver performs - it a good driver for sure, but it ain't the best, in fact I am yet to find a really good one - I haven't really tested the BD connector - maybe that it better. We found that more complex operations under highload performed better using CFQUERY on basic processes - it seemed to be that CF just seemed to queue up requests waiting on the SP to finish/return. I am also not sure if its CF or the DataDirect driver on this one but if you try and return multiple 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? 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 critical, I just want to learn more about this. It doesn't really match with my experience so far. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222738 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
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 query is even run. For example, if you have a product detail page that is expecting a product ID... cfset url.id = abs(val(trim(url.id))) cfif not url.id) cflocation url=/ /cfif Now you've guaranteed that there will be some value to pass to the query, and If someone tries to get tricky with a SQL injection attack, they get booted to the home page before the query is ever run. For most of my projects I use a combination of input scrubbing and SQL optimization (QUERYPARAM and SPs where needed). As with anything else, what you do depends on how the application will be used, what kind of traffic you're expecting, and how much time and money the client wants to throw at it. -Justin Scott ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222740 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 far longer than using one plan (from the SP) that was cached. Using CFQUERYPARAM on that variable may have helped, but I never tested it that way. I just went directly from dynamic query to stored procedure and 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. If you'd used CFQUERYPARAM, you'd have gotten the same results. When you use CFQUERYPARAM, you build a prepared statement, which will create a single execution plan that can be used by different invocations of the query with different input values. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222742 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: 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. Well, technically, if you are consistent in your use of CFQUERYPARAM, it will have the same effect. However, one common approach in security is to limit what is possible. With regard to database access, stored procedures allow you to do a better job of this, by limiting what can go wrong in your application code. Of course, if both the application code and the stored procedures are written by the same (diligent) programmer, that might not have any net effect, but in many cases that might not be true. That's one reason why I favor stored procedures. I didn't know there were so many proponents for inline SQL. I've just always figured MSSQL was better able to run some pre-compiled execution plan in the case of an SP, over ad-hoc SQL queries. Actually, most databases can perform just as well using a prepared statement created using CFQUERYPARAM as with a stored procedure. The execution plans in either case can be stored and reused. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222743 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
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 query is even run. For example, if you have a product detail page that is expecting a product ID... cfset url.id = abs(val(trim(url.id))) cfif not url.id) cflocation url=/ /cfif Now you've guaranteed that there will be some value to pass to the query, and If someone tries to get tricky with a SQL injection attack, they get booted to the home page before the query is ever run. For most of my projects I use a combination of input scrubbing and SQL optimization (QUERYPARAM and SPs where needed). As with anything else, what you do depends on how the application will be used, what kind of traffic you're expecting, and how much time and money the client wants to throw at it. I guess I don't see the time spent to write a CFQUERYPARAM tag as a significant addition of expense. Also, if you pass an invalid data value to a CFQUERYPARAM tag, CF will prevent the query from running. So, you could easily do the same sort of thing just using CFQUERYPARAM and an exception handler, which will provide the extra benefit of better performance. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222746 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
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 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 fetching those same recordsets using inline SQL performs better? Again, this has not been my experience at all (so far - perhaps I've been lucky). Also, when you say large recordsets, how much data are you talking about? Again, I'm not trying to be critical - you may very well be right about this. - it a good driver for sure, but it ain't the best, in fact I am yet to find a really good one - I haven't really tested the BD connector - maybe that it better. BlueDragon on Java uses New Atlanta's own JTurbo driver to talk to SQL Server, I think. This is supposed to be a very good driver, but I have very little experience with it so far. On .NET, of course, I suspect they simply use the native ADO.NET stuff provided by Microsoft. I am also not sure if its CF or the DataDirect driver on this one but if you try and return multiple 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. That may also be the case. I have no idea, simply because I've never had a need to have a single procedure returning that many recordsets. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222749 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 running. So, you could easily do the same sort of thing just using CFQUERYPARAM and an exception handler, which will provide the extra benefit of better performance. 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. Just a personal preference I suppose. -Justin Scott ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222760 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 can't get the exact same result using exception handling as you're doing now using conditional logic. Handling invalid data is right in line with what exceptions are all about. I'm not trying to get you to change what you're doing, which seems to work well enough, just to point out that exception handling isn't as limited as you imply. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222762 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222763 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
Hey, Well, it can vary on record size - from 100 to 1000K+ (when not paged) and it is well documented that Java/CF doesnt handle large recordsets as 'efficiently' - in as far as mem usage as CF5 does - jeez, even the MM technotes practically note: use smaller recordsets -I believe it is around 2-4x more memory with Java call than CF5 used. But when it comes to returning several recordsets (when there is absolutely no problem with it in QA) - CF can freak (or probably the driver does!); it can sometimes just bomb out with no error. There are good sides and downsides of using T-SQL SP's over inline - and I suppose we should be thankful we are not on the ASP and the go awful way it uses ADO ;-) I just prefer SP's for portability from a SQL perspective in our various languages such as .NET, I also like the way it reduces the amount of CFQueries I have to write...as like most, I am 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 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 fetching those same recordsets using inline SQL performs better? Again, this has not been my experience at all (so far - perhaps I've been lucky). Also, when you say large recordsets, how much data are you talking about? Again, I'm not trying to be critical - you may This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222766 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Philosophy Q: SP's or CFQUERY?
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 not need to be an SP, then I write it myself. The decision to do this is totally subjective though. I would be interested in seeing if there are really objective reasons to convert some 'inline' queries to SPs. For example, how does one decide if the following inline queries are to be coverted to SPs: 1. A query that returns a list of FileTypes from a table FileType that populates the cffile 'accept' attribute. 2. A query that returns a particular record to display it in the browser. So it is a simple 'SELECT * FROM SomeTable WHERE SomeTableID = 'whatever(with cfquerparam)' 3. A query that has to update a table Order_Item (say) with OrderID and ItemID, but first has to delete all the relevant ItemIDs for a particular OrderID before running the update (actually new inserts). Sorry if any of the above does not make sense. It's been a long day already. Thanks, George ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222781 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Philosophy Q: SP's or CFQUERY?
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 necessarily a bad thing, but it does split the code, which can make maintenance harder. Careful design can mitigate most of that concern, of course, but it's still added complexity that shouldn't be brought on yourself for no good reason. Single queries, in particular, ought never to be converted to SPs unless it's a security concern, if you ask me. The performance isn't going to be any greater, since you still have to pass the recordset back to CF and let it create a CF query object. cheers, barneyb On 10/29/05, Pete Ruckelshaus [EMAIL PROTECTED] wrote: I'm going back and tweaking a web app that I'm building and have learned a few stored procedure tricks (my SP skills were pretty basic before I started to play around). As a result, I'm replacing a number of CF queries (using cfqueryparam) with SQL Server stored procedures. Now, these are pretty much limited to the customer-facing (non-admin) pages, though I'm using SP's in the admin where I already have them written to return the rest sets that I need. Realizing that SP's will give greater performance, the question is this: Is it OK to write your most frequently run queries as SP's and leave the rest of the site as straight SQL queries? Or should I just make the entire site SP's? What I've been doing during development is writing straight SQL until I get what I need, then I rewrite as a SP. I've replaced a dozen or so queries that are all on the customer-facing end of things, but there are well over 100 queries still written as SQL on the admin side, and it would take a bit of effort to convert (or merge into existing SP's) those queries. Pete ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222660 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 another. I decided it was best to just leave the SQL statements in the application server and not try to create SPs. The real plan is to put most, if not all, SQL statements within CFCs thereby creating some sort of encapsulation. SPs are great in the fact that you can return multiple recordsets at once. I miss that feature, but not enough to use them again. If you desire using SPs, then I would say you are doing it the right way of creating your SQL statements then converting them to SPs once you have them tied down. M!ke -Original Message- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: Saturday, October 29, 2005 12:16 PM To: CF-Talk Subject: Philosophy Q: SP's or CFQUERY? I'm going back and tweaking a web app that I'm building and have learned a few stored procedure tricks (my SP skills were pretty basic before I started to play around). As a result, I'm replacing a number of CF queries (using cfqueryparam) with SQL Server stored procedures. Now, these are pretty much limited to the customer-facing (non-admin) pages, though I'm using SP's in the admin where I already have them written to return the rest sets that I need. Realizing that SP's will give greater performance, the question is this: Is it OK to write your most frequently run queries as SP's and leave the rest of the site as straight SQL queries? Or should I just make the entire site SP's? What I've been doing during development is writing straight SQL until I get what I need, then I rewrite as a SP. I've replaced a dozen or so queries that are all on the customer-facing end of things, but there are well over 100 queries still written as SQL on the admin side, and it would take a bit of effort to convert (or merge into existing SP's) those queries. Pete ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222668 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 didn't improve and actually seemed to slow down some. Here is one link http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx but there are many. -Original Message- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: Saturday, October 29, 2005 1:16 PM To: CF-Talk Subject: Philosophy Q: SP's or CFQUERY? I'm going back and tweaking a web app that I'm building and have learned a few stored procedure tricks (my SP skills were pretty basic before I started to play around). As a result, I'm replacing a number of CF queries (using cfqueryparam) with SQL Server stored procedures. Now, these are pretty much limited to the customer-facing (non-admin) pages, though I'm using SP's in the admin where I already have them written to return the rest sets that I need. Realizing that SP's will give greater performance, the question is this: Is it OK to write your most frequently run queries as SP's and leave the rest of the site as straight SQL queries? Or should I just make the entire site SP's? What I've been doing during development is writing straight SQL until I get what I need, then I rewrite as a SP. I've replaced a dozen or so queries that are all on the customer-facing end of things, but there are well over 100 queries still written as SQL on the admin side, and it would take a bit of effort to convert (or merge into existing SP's) those queries. Pete ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222674 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Philosophy Q: SP's or CFQUERY?
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, it all depends on the data, the DB, the available RAM on the SQL server and if I really need to have a SP for the data or not. I hate to say it's a case by case basis, but it is. There are also a few books you can read that deal with the subject really well: SQL Tuning (O'Reilly) http://www.amazon.com/exec/obidos/ASIN/0596005733/houseoffusion SQL Performance Tuning (Addison-Wesley) http://www.amazon.com/exec/obidos/ASIN/0201791692/houseoffusion SQL Server Query Performance Tuning Distilled (Apress) http://www.amazon.com/exec/obidos/ASIN/1590594215 /houseoffusion Microsoft SQL Server 2000 Performance Tuning Technical Reference (Microsoft) http://www.amazon.com/exec/obidos/ASIN/0735612706 /houseoffusion I've always been a fan of the O'Reilly books but the APress ones are looking better and better. Of course, Microsoft wrote SQL 2k, so their book might be worth looking at. :) ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222676 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Philosophy Q: SP's or CFQUERY?
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, because it only requires two network trips, rather than four, and one CF recordset creation, rather than two. This effect obviously increases with the more complex the interactions are, especially if the first recordset would have been large, and isn't needed except for use in the second query. If you just have a simple query, there's not really anything that an SP can do to speed it up that isn't going to happen anyway. And then there's the overhead of calling the SP itself, which doesn't exist in a simple query. cheers, barneyb On 10/30/05, Brian Peddle [EMAIL PROTECTED] wrote: 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 didn't improve and actually seemed to slow down some. Here is one link http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx but there are many. -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222680 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 didn't improve and actually seemed to slow down some. In my experience, it depends on the data you're querying against and how it is to be used. One example I had was a table with 1,000,000+ rows that had to have random data pulled out of it about once every 3 to 5 seconds, and more at peak hours. Even with the proper indexes and the entire table in memory, the one query being run from ColdFusion with one variable was eating up 75% of the processor on average, and closer to 90% during peak. Converting that one query to a SP and passing the variable to it knocked the CPU usage on the CF server down to about 5% on average. It really did help a LOT. If you're working with smaller tables that don't get a lot of traffic, you probably won't notice much of a difference, if any. -Justin Scott ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222681 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 or CFQUERY? 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 didn't improve and actually seemed to slow down some. In my experience, it depends on the data you're querying against and how it is to be used. One example I had was a table with 1,000,000+ rows that had to have random data pulled out of it about once every 3 to 5 seconds, and more at peak hours. Even with the proper indexes and the entire table in memory, the one query being run from ColdFusion with one variable was eating up 75% of the processor on average, and closer to 90% during peak. Converting that one query to a SP and passing the variable to it knocked the CPU usage on the CF server down to about 5% on average. It really did help a LOT. If you're working with smaller tables that don't get a lot of traffic, you probably won't notice much of a difference, if any. -Justin Scott ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222682 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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. -Justin Scott ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222683 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Philosophy Q: SP's or CFQUERY?
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 On 10/30/05, Justin D. Scott [EMAIL PROTECTED] wrote: 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 didn't improve and actually seemed to slow down some. In my experience, it depends on the data you're querying against and how it is to be used. One example I had was a table with 1,000,000+ rows that had to have random data pulled out of it about once every 3 to 5 seconds, and more at peak hours. Even with the proper indexes and the entire table in memory, the one query being run from ColdFusion with one variable was eating up 75% of the processor on average, and closer to 90% during peak. Converting that one query to a SP and passing the variable to it knocked the CPU usage on the CF server down to about 5% on average. It really did help a LOT. If you're working with smaller tables that don't get a lot of traffic, you probably won't notice much of a difference, if any. -Justin Scott -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222684 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 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. Perhaps ramdom wasn't the right word. The query isn't pulling random rows, but subsets based on a foreign key. The foreign key that is to be loaded is not predictable, and there are 10,000+ foreign keys that can be pulled at any time. 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 far longer than using one plan (from the SP) that was cached. Using CFQUERYPARAM on that variable may have helped, but I never tested it that way. I just went directly from dynamic query to stored procedure and 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. Even if I have some of the details wrong, that one change made things go a WHOLE lot smoother. -Justin Scott ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222685 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: 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 proponents for inline SQL. I've just always figured MSSQL was better able to run some pre-compiled execution plan in the case of an SP, over ad-hoc SQL queries. Neil? Dave Watts? Greg On 10/30/05, Justin D. Scott [EMAIL PROTECTED] wrote: 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 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. Perhaps ramdom wasn't the right word. The query isn't pulling random rows, but subsets based on a foreign key. The foreign key that is to be loaded is not predictable, and there are 10,000+ foreign keys that can be pulled at any time. 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 far longer than using one plan (from the SP) that was cached. Using CFQUERYPARAM on that variable may have helped, but I never tested it that way. I just went directly from dynamic query to stored procedure and 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. Even if I have some of the details wrong, that one change made things go a WHOLE lot smoother. -Justin Scott ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222686 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Philosophy Q: SP's or CFQUERY?
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 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 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 routine. Guarantees a positive integer value, and passes in 0 if it's a string. It's not the best way, but for small sites it's quick, easy, and pretty safe. CFQUERYPARAM would help also. Unfortunately some people are still using access databases, and don't have the luxury of stored procedures. I use SQL Server myself, and sp's where needed. I'm not a zealot one way or the other on what is used. Depends on the situation. -Justin Scott ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222688 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
