> 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
