I agree completely in that the method being taken really needs to be re-evaluated. However this is not my project and I am just inquiring for someone else to see why CF would basically dedicate all resources to an SP call but put the same SQL into a CFQUERY and it no longer does. Both methods do take a long time to run but at least with the CFQUERY method other users can use the site. Had one suggestion from Isaac that maybe they try running the SP with the return of a cursor pulled out to see if perhaps the returning of that is actually what the cause is.
On 4/25/06, rhymes with 'loud' Doug Boude <[EMAIL PROTECTED]> wrote: > > Hi Aaron. > > Eegad! If an SP chokes an oracle server for 15 minutes, then it is HIGH > time to go back to the drawing board and rewrite that SP. And not rewrite it > slightly different than it currently exists, but to totally find a new > approach to solving the problem that this particular SP addressed in the > first place. Perhaps the data needs to be redistributed into a different > table structure, perhaps the SP itself should be broken up into more > discreet SPs that call each other, perhaps incorporating some temporary > holding tables in the midst of the process somewhere to speed up > joins...anything at all that would constitute a new, fresh approach to the > issue. > > Having said that, in my own experience with CF and oracle, I have found > that at times I got much better overall responses if I shared the load > between CF and Oracle. For instance, I COULD write one query that would > return my dataset just the way i needed it, but Oracle took too long to > execute it. So, I had Oracle return a couple of basic datasets to CF (after > having removed a few joins), and then had CF do a query of queries in order > to complete the transformation and joins for me. I was able to get total > page load time to less than 50% of what it had taken when Oracle was doing > all the work. > > I share that last tidbit to inspire you and your database gnomes (no > offense intended :P) to start at a high level overview of this issue and > address the real core of the challenge: the query and/or SP itself. > > Hope this helps. :) > > >We have a project here where they are using CF5/Oracle and make some SP > >calls that "run big queries" They said that when they make one of the SP > >calls the whole server will choke and stop responding to requests until > the > >SP has finished running which takes upwards of 15 minutes. If they take > the > >query out of the SP and put it into a CFQUERY then it no longer chokes, > it > >is my understanding the query still takes a long time to run this way or > >just through SQLPlus. It has been tried using ODBC and native connection > >data sources and with connection pooling enabled. Is this typical for > all > >resrouces to just get consumed for SPs and not for cfqueries or is there > >maybe something I could pass onto them to check? > > > >-- > >Aaron Rouse > >http://www.happyhacker.com/ > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238647 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
