CF SERVER TIMEOUT PROBLEM 1

[kingjjx]

Hi, I have a problem with my CF Server, i think.
Ok .. I created a page where I query from 2 tables. It always times out. But if I just queried from 1 of the 2 tables it worked fine (i tried querying each table separately and it works fine)

What might be the problem here ?
Please help.

thanks
-jon

 

[CFProgrammer]

Not sure what could be the problem. If you can run the sql statement within the database you should be fine.


Need Cheap Hosting,

http://www.revorg.net

1 Rate, 1 Plan

 

[Guest_imported]

How long does it take to time out?
What version of ColdFusion are you using?
How complex are your queries (post your code do we can see)?
What hardware platform are you using?
What web server are you using?

When asking for help, you should always include the above information. It is impossible to even begin to solve the problem without it.

That said, have you checked the configuration of ColdFusion? What is the setting for the Timeout value? Have you tried using the RequestTimeout parameter on the URL that is your FORM's ACTION attribute? Have you turned on debugging and checked the boxes marked "Show Total Processing Time" and "Show Detailed Processing Time Breakdown" in CF administrator?

 

[kingjjx]

Ok, when I checked the CF settings, the timeout was set at 60 secs so I took that out and now whenever I query it just stalls .. i tried waiting like 2 minutes and still didnt give me the results.

I am using ColdFusion 4.5

Each table I am querying from contains about 3,000 columns making both tables 6,000 rows.

The query is not complex at all .. here's the code:

<CFQUERY NAME=&quot;Encore&quot; DATASOURCE=&quot;EncoreCompanyD&quot;>
SELECT dbo.InvMaster.StockCode, dbo.InvMaster.Description, dbo.InvMaster.ProductClass, dbo.InvPrice.PriceCode, dbo.InvPrice.SellingPrice, dbo.InvMaster.AlternateKey2
FROM dbo.InvMaster, dbo.InvPrice
WHERE (dbo.InvMaster.ProductClass = 'KITS') AND (dbo.InvPrice.PriceCode = 'R') OR (dbo.InvMaster.ProductClass = 'PCMR')
</cfquery>


ANY ADVICE PLEASE ?? THANKS

 

[Guest_imported]

OK, let's see if we can solve this. When I say &quot;test it&quot; below, I mean this: run each of your queries separately (which you know will work) and then test your regular page that queries both tables. After each test, analyze the results. What worked and didn't work. Especially note what worked differently from the previous test. Try to figure out what caused the different behavior. Keep notes of each test. Note what the configuration was, what the test was, what the processing time was, the exact error messages, etc. I know that you think that you will remember all this, but trust me, I've been there, and as the various test configurations and results mount up, you will probably have forgotten some detail that will make a difference in your analysis.

(1) Turn on debugging so that you can see variables, processing time, and SQL statements (and more if you want).

(2) When you &quot;took out&quot; the Timeout value, did you delete the number or did you uncheck the box? Be sure that you have unchecked the box, then test it. Try it with a large timeout value (and the box checked), say 300 seconds (that's 5 minutes). Be prepared to stop and restart the ColdFusion service if it hangs. Note the processing time for the separate tables and also the two-table query. Is one query taking a lot longr than the other? Is that reasonable? BTW, you can override the Timeout value by adding this parameter to your URL: RequestTimeout=n, where n is some integer.

(3) Reboot the ColdFusion host. CF 4.5 has an annoying habit of not returning memory to the OS. The only way to solve that is to reboot. If this solves the problem then you should plan on rebooting periodically, depending on how much traffic there is on your site, or upgrading to CF 5.0.

That's all for now. Write back and report. We'll go from there.

 

[jianhua]

Hey, in your query, you joined two tables together, but you didn't give the joint condition, so there would never be any results. You need code in where clause like dbo.InvMaster.fieldname = dbo.InvPrice.fieldname.

 

[Guest_imported]

Jianhua, you're brilliant! Good catch! Actually, there will be results. Without a join condition, you will get the Cartesian product of the two tables. Every row of each table will be joined with every row of the other table. That's (#rows_table1 * #rows_table2) * 2 rows returned. I should have remembered that: when the query is taking too long, it is likely to be an incorrect join. I have to confess that I didn't look at the code. When kingjjx said that he queried from two tables, I interpreted that to mean that he queried them separately. I should have thought about the possibility that he meant that he did a JOIN. Oh, well. Glad you spotted it, and I'll be kingjxx is too.

 

[kingjjx]

Yaaaay !!! Whew! Finally.! Thanks Jianhua for spotting that missing where clause and thanks a440guy for all your help.
Finally fixed it. thought it was my server. dont you hate small bugs like this.

Anyways .. thanks you guys ! this thread can finally rest in peace .. lol B-)

kingjjx