This post is from 2005 - but it does a pretty good job of explaining execution plans on mssql and why they are important. The comments are also pretty good.
http://www.coldfusionmuse.com/index.cfm/2005/6/28/cfqueryparam Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -----Original Message----- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2008 1:54 PM To: CF-Talk Subject: Re: (ot) URL Hack Attempt Leaves Me Scractching My Head... What Charlie says is correct. To elaborate on the performance part.. If you use proper indexes in your database it is possible to have statements that are called "covered selects". This means that every column in the select clause and where clause are all part of an index, and the records can be retrieved without every hitting the table. Example: SELECT order_id FROM orders WHERE cust_last_name = 'Wood' Let's supposed I had an index called IX_cust_name on my cust_last_name column (because I searched it often) and I added order_id in as an included column in that index. The execution plan would MOST LIKELY perform an index seek on IX_cust_name (very, very fast) and return the order_id found in stored along side it-- probably on the same page of memory. Your database never had to even hit the table. If you database is stored on a SAN with a large cache, your oft used index might even be cached in the controller memory. Now, supposed you had taken a shortcut and done the following: SELECT * FROM orders WHERE cust_last_name = 'Wood' Even though you are only using the order_id column (which no one would be able to tell easily) you execution plan now performs an index seek AND performs a bookmark lookup back to the table with the key stored in the index to fetch all the other columns in that row whether they are needed at all. Bookmark lookups can KILL you in large result sets. A lot of this stuff is so negligible you won't notice it with small database, but databases have a way of growing until performance is unbearable. Also, additional tables added to the select later will cause the amount of data being returned to blossom. Anyway, I hope that helps put some reasoning to it. ~Brad ----- Original Message ----- From: "Charlie Griefer" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Thursday, July 24, 2008 12:59 PM Subject: Re: (ot) URL Hack Attempt Leaves Me Scractching My Head... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309673 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
