[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Recursion hosing up CF server -  Need Help Fast!

Posted on 2005-05-17
5
Medium Priority
?
207 Views
Last Modified: 2013-12-24
Hello..

I am currently working on an app that has a custom tag that recursively calls itself to get categories and each child category of that parent. The database uses two tables to handle the categories. One is just a linking table and the other holds the categories details. This used to work fine until I made some minor changes to the tables. Initially the PK and FK of the tables where using a UUID (varchar) as the records identifier and that was used to join in the record from the categories table. This UUID is diplayed in the url of the site when referencing a category and is very ugly so I started using a string of test for the ID which is search engine friendly for example instead of using 'CE724826-3048-2AB2-244EC5979EC2E824' for the ID I am using something like 'hp-pavilion-zd7000' ... This was working just fine as I was adding new categories and sub categories building my cat structure but at a certain point it started hosing the CF server and my cpu utilization would go up to about 80% while the code that recursively gets the cats and thier children from the db. If I let it go it will timeout and release the cpu but I have been restarting the cf service to keep it from sitting there clocking the whole server. I have been struggling with this for a week now and was hoping to get a quick resolution. One work around I have in place is I added an additional field to each table called recid and use the UUID in that field to link the tables together just for this recursive processing, that fixed the problem for awhile but now it's broke again and I have not changed anything all I have done is add data to the tables.. The DB is MS SQL..

Here is what the linking table looks like..


CREATE TABLE [dbo].[SL_fusion] (
      [id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [owner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [sub_object] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [sort] [smallint] NOT NULL ,
      [state] [tinyint] NOT NULL ,
      [objGroup] [varchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [recId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


And here is the departments (categories) table

CREATE TABLE [dbo].[SL_departments] (
      [id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [title] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [short_description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [meta_description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [keywords] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [content] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [state] [tinyint] NOT NULL ,
      [image_a] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [image_b] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [image_c] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [template] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [layout] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [build_date] [smalldatetime] NOT NULL ,
      [edit_date] [smalldatetime] NOT NULL ,
      [admin_user] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [target] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pageTitle] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pageName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [deptGroup] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [recID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



and here is the custom tag code

<cfif IsDefined("request.catalog_list") Is "No">
      <cfset request.catalog_list = ArrayNew(1)>
</cfif>

<cfparam name="attributes.table" default="#request.departmentTable#">

<!--- this is the list that we're building.  Prefixing it with 'request' means that any custom tag at any level of execution has access to it.  Think of it as a temporary application variable. --->
<cfparam name="request.catalog_list" default="">

<!--- this is the value each instance of this custom tag will use to decide what elements it's looking for out of the database.  The default value is 'index' since that's your designation for the top of the catalog tree. --->
<cfparam name="attributes.owner" default="#request.indexID#">

<!--- this is the value each instance of this custom tag will preface values it adds to the listing with. Because the database only contains each item's immediate parent/child value, we have to pass this forward. --->
<cfparam name="attributes.path" default="">

<!--- go get the children for the base owner value we're supposed to be looking for --->
<cfquery datasource="#request.dsn#" username="#request.dbUserName#" password="#request.dbPassword#" name="getChildren">
      SELECT f.owner, f.sub_object, f.recid, d.id, d.title
      FROM #request.fusionTable# f, #attributes.table# d
      WHERE f.recid = d.recid
      AND f.owner = <cfqueryparam value="#attributes.owner#" cfsqltype="CF_SQL_VARCHAR">
      AND f.type = 'department'
      ORDER BY d.title, f.sub_object
</cfquery>

<!--- Loop over the children of the main owner value.  Our one and only loop. --->
<cfloop query="getChildren">

      <cfparam name="request.count" default="0">
      <cfset request.count = request.count + 1>
      
      <!--- add the path from the index to this location in the tree in front of the name of this sub-object --->
      <cfset variables.thisitempath = "#attributes.path#/#getChildren.title#">
      <cfset request.catalog_list[request.count] = StructNew()>
      <cfset request.catalog_list[request.count].tree = variables.thisitempath>
      <cfset request.catalog_list[request.count].id = getChildren.sub_object>
      
    <!--- call this file again, but this time tell it to take a look at the next level down.  if there's anything under this department, it will get added to the tree in sequence. --->
      <cf_catdump owner="#sub_object#" path="#variables.thisitempath#">

</cfloop>






0
Comment
Question by:JBeckton
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:danrosenthal
ID: 14021266
I have worked on recursive categories a few times and have accidently created endless loops also. The first thing I would do is add a counter and exit the tag if the counter gets to a maximum number, this will prevent the server from dying and will allow you to dump the information to the screen so you can more easily debug. I am assuming the problem is in your database records and this method will help to isolate the exact location.

Here is an example of an endless loop workaround:
<CFPARAM name="request.loopcounter" default = "1">
<CFSET request.loopcounter = request.loopcounter + 1>
<CFIF request.loopcounter gte 100>
    Maximum Loops Reached<CFABORT>
</CFIF>

0
 

Author Comment

by:JBeckton
ID: 14021956
danrosenthal

Thanks for the tip...

I coded in a counter and set it to a low number of loops like 5 and it's ok. I then tried changing the count to 50 and it hosed again. I think it is something to do with the DB. It did ok just returning the top level departments but it seems when it starts digging into the children and the childrens children it fails.. Could it be caused by the fields I am using to join the two tables are not the PK and the FK?
0
 
LVL 15

Assisted Solution

by:danrosenthal
danrosenthal earned 1000 total points
ID: 14022061
I would definitely make the link back to the parent a FK to enforce normalization.
Maybe your problem is that at some point all records are children and all THOSE records have all records as children so you are getting a geometrically absurd number of records to loop through. Hard to debug from here.
-Dan
0
 
LVL 10

Accepted Solution

by:
rob_lorentz earned 1000 total points
ID: 14022162

i suspect that your data might be corrupt.

parent    1     childern 2, 3, 4
parent    2     childern  5,6
parent    5     childern  1
parent    1     childern  2,3,4

before looking up a childs children you need to check that it is not one of its parents.
0
 

Author Comment

by:JBeckton
ID: 14022520

Well I just took a look at my data and I do have some departments as children of more than one department but I have been able to do this before with no problems, the difference between now and then is maybe that I have alot more departments than before. On ething I am looking for now is to see if I have a dept set as a child of a dept that has that parent as a child somewhere...

But I think i am in the right direction now.. It may be that I got some relastionships that conflict with others and it's causing the qry to run away..

I'll keep digging
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
What You Need to Know when Searching for a Webhost Provider
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month19 days, 1 hour left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question