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
