> 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
