Interesting... I thought the same thing until I ran these tests. I analyzed the results with ColdFusion debugging output, the Server Monitor in CF8 Ent, SeeFusion, and watched them execute through SQL Profiler, all of them showed better execution times when I removed the CFQUERYPARAMs. Now, this was on a limited subset of my queries. I would still argue that using it is far better than not. Most of the Queries I have used it in I did see a performance improvement in.
=] On Thu, Oct 30, 2008 at 9:18 AM, Jason Fisher <[EMAIL PROTECTED]> wrote: > Alan, > > SQL Server will create an execution plan for each query that gets run, > where the plan is specific to the final Query definition. From SQL Server's > perspective, these are 2 different queries, so each gets its own plan: > > Query with both names: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = @param1 > >AND Fname = @param2 > >AND Lname = @param3 > > Query with last name only: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = @param1 > >AND Lname = @param2 > > So, every query I run with only a Last Name filter will re-use that 2nd > query plan, and that increases performance. Without using CFQUERYPARAM at > all, every instance of the query is 'new' and that should (in theory) kill > your performance, not boost it. In other words, the following queries would > have the same plan with params but are each 'new' and distinct without > params: > > Query with last name only: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = '1234' > >AND Lname = 'Smith' > > Query with last name only: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = '1234' > >AND Lname = 'Johnson' > > I'd be curious to see what the Analyzer had to say on the DB server side > about the query plans for running several hundred unique queries vs several > hundred recurrences of a few plans. > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314616 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
