Obviously returning a query with the proper data is going to be a lot faster then returning a query, looping over the data and building a new query. This is true if you're in a cfc, udf, or a .cfm page directly.
Now to see if there is a performance problem using a cfc in your application scope. I would run 2 tests. #1 - run scenario 1, as Christian said add tickCounts around lines 23-35. This scenario uses getnumber() from the application.numberservice. #2 - run scenario 1 with 1 change. Put the getNumber() method in the testService.cfc directly. Instead of calling the application.numberservice cfc call the local copy. This will find out if the performance increase you're seeing is the call to the cfc method, or in the loop were your rebuilding the query. I'll bet the majority of the 200ms is in the query functions. Personally, instead of parsing the string to get the number for every request, I'd move this code into a scheduled tasks that pulls the data, calculates the number and then updates the db, no reason for you to manually update the dB. This way the users on your production site don't have to take the hit while you redo the same logic over and over. And you can just return the right data (which is the fastest option) Also if you want to do it at runtime, instead of denormalizing your dB, Move the code to a stored proc. If you're using MS SQL, and I believe oracle, you can create your own getNumber() UDF in the sql server and call it in your SQL and let the sql server do this, instead of CF. Hth, ----nimer -----Original Message----- From: St�phane Bisson [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 1:09 AM To: [EMAIL PROTECTED] Subject: Re: [CFCDev] ColdFusion MX visite SQL 2000 Performance Issues Thanks Nathan to point me this... I forgot to deliver the codes to prove what I saw... I worked about 1-2 hours to create this stupide example with two CFCs to show you my ColdFusion MX Scenario #1 (200-300ms) and my SQL 2000 Scenaria #2(0-20ms) ... before they return 200+ rows of results... Scenario #1: is doing a cfloop after the main SQL query and it's calling one CFC methods store in the Application scope to get the stupide number... it's creating a new qery to return also... (200-300ms) Scenario #2: is doing only a SQL and return it(0-20ms) and the SQL has this stupide number already store in the table.... Installation procedure To test Sceniaro #1 1- extract the zip to your site and put request.componentpath in application.cfm with the path of your site 2- Replace in the TestService.cfc the ?table? string with a table that you have that contains 200+ rows 3- Replace in the TestService.cfc and TestCFPerformance.cfm the ?fieldname_name_that_contains_char? string with a fieldname type=char... that you have 4- Run the cfm TestCFPerformance.cfm to see the result with Enable Debuggind to Yes.. 200 ms and more... To test Sceniaro #2, 1- extract the zip to your site and put request.componentpath in application.cfm with the path of your site 2- just read my comments in the TestService.cfc... and uncomment and comment the codes... 3- Run TestCFPerformance.cfm to see the result with Enable Debuggind to Yes.. 10 ms and more... If I remove in Scenario #1 the call to my CFC methods store in the Application scope (I hardcode the value let's say!)... it's about the samething as SQL... of course my method GetNumber is being called 200+ times, but It does not say in the debugging that it take a lot of time!... if you comment the call of my Application CFC instance... you will see a big difference... I just saw that if I call a CFC method in my cfloop... it's taking a lot more time to respond.... even if it's a stupide method... like I have in my example... Stephane ----- Original Message ----- From: "Nathan Dintenfass" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, August 12, 2003 1:18 AM Subject: RE: [CFCDev] ColdFusion MX visite SQL 2000 Performance Issues > Certainly ColdFusion should not always do what a database can do better, but > I don't think that has anything to do with how many methods you have. From > the sounds of it the overhead is in some CF looping construct that is doing > conditional logic on a string -- so, although CF might have some issue it > doesn't sound like it's a CFC issue. > > But, without any of your code posted here or on the web forums it's not > possible to give truly constructive feedback. > > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Behalf Of St�phane Bisson > > Sent: Monday, August 11, 2003 9:09 PM > > To: [EMAIL PROTECTED] > > Subject: [CFCDev] ColdFusion MX visite SQL 2000 Performance Issues > > > > > > Hi gang, I'm writting you tonight because I just thing after > > having created > > 70 CFC methods... that ColdFusion MX is really not fast enough to deliver > > the result.... no body has been taking about that I think...I was > > putting to > > much of CF codes.... now I use only SQL is the main driver because it's so > > fast!... I would like to put more CF codes in my CFC methods... but I know > > for sure, that if I put to much CF codes... it will take to much time to > > execute.... so just read my post and as usual I will be happy to see any > > comments... > > > > http://webforums.macromedia.com/coldfusion/messageview.cfm?catid=3 > > &threadid=670957#2414950 > > > > Stephane > > ---------------------------------------------------------- > You are subscribed to cfcdev. To unsubscribe, send an email > to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' > in the message of the email. > > CFCDev is run by CFCZone (www.cfczone.org) and supported > by Mindtool, Corporation (www.mindtool.com). ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com).
