Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Browser timeout during SQL execution

Posted on 2004-09-20
7
Medium Priority
?
584 Views
Last Modified: 2013-12-24
Coldfusion MX 6.1
SQL server 2000 sp3

I am having problems with browser timeouts during long-running SQL tasks executed through <CFQUERY>

The process runs for about 30 seconds then I get this in the browser window

The page cannot be displayed
The page you are looking for is currently unavailable. The Web site might be experiencing technical difficulties, or you may need to adjust your browser settings.

I have already added this <cfsetting requesttimeout="10000">
and I have changed the timeout settnig to 600 in the CFIDE admin tool.

I also added <cfflush> throughout.

One last thing... I have verified both through QA testing the tables and through SQL server traces that the code continues to execute AFTER the server sends the "The page cannot be diplayed to browser"...so i know that the SQL is valid...100% sure.  Are there settings on the client that cause the browser to timeout? What am I missing?

One last thing... SQL 2 and 3 (see below) take about 3 minutes to execute on average according to the trace.



code:

<!--- this procedure loads the table
etl.dbo.bldg_permit_fact
--->
<html>
<head>
<title>Process permit fact table</title>
</head>
<body>
<cfflush interval="5">
<h3> Processinig fact table updates...this may take a couple minutes</h3>
<cfflush>
<cfsetting requesttimeout="10000">


      <cfloop from="1" to="4" index="i">
            <cfquery name ="elt_fact#i#" datasource="reports">
                  [ First SQL here...]      
            </cfquery>
            
      </cfloop>
<h3>rows inserted...updates started</h3>
<cfflush>

<cfquery name="etl_fact_metrics" datasource="reports">
  [Second SQL here]
</cfquery>

<cfflush>
<h3>Almost there...one more update!</h3>

<cfquery name="etl_perm" datasource="reports">
   [Third SQL here]
</cfquery>
fact table processed successfully
</body>
</html>
0
Comment
Question by:joegensh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Expert Comment

by:speshal
ID: 12105490
Try caching your queries if you can:
<cfquery name="etl_perm" datasource="reports" CachedWithin="#CreateTimeSpan(0,1,0,0)#">
It may not work the first time due to a timeout but should on the rest


Also have a look here at a similar situation:
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21025883.html

speshal
0
 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 12109690
are thouse update,insert,delete statements?
or just select statements?

it those are select statements, i suggest indexing those tables.
this should give you faster results.

How many records are those tables anyway?
0
 

Author Comment

by:joegensh
ID: 12116667
All the SQL is DML (Update, Inserts and Delets), there are no Selects.  The tables are fairly big for the server (comaq proliant 2 processors). Both tables are 1,000,000 rows+

I feel I've tuned the SQL as good as I can.  All indexes are dropped on the columns being written too and the read columns are PKs.

I've also run SQL through the tunning wizards in SQL server.

I'll try the first suggestion....I am unsure what the cachedWith does or if it is even applicable to DML SQL.

My main question is does sql server support the timeout settings that are being passed from the cfsetting tags?
0
Optimum High-Definition Video Viewing and Control

The ATEN VM0404HA 4x4 4K HDMI Matrix Switch supports 4K resolutions of UHD (3840 x 2160) and DCI (4096 x 2160) with refresh rates of 30 Hz (4:4:4) and 60 Hz (4:2:0). It is ideal for applications where the routing of 4K digital signals is required.

 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 12119318
you might want to try moving your sql statements to storedprocedure.

You may get faster results. if it really takes that long it will time out.

IE will time out regardless of any settings. I have read that somewhere but i could not find the
link anymore. i am unsure what is the timeout limit.
0
 

Author Comment

by:joegensh
ID: 12119742
30 Seconds...

The official company policy is that storedprocs are out of favor....I like stored procs personally.

I actually backed around this issued by creating a <cfschedule> tag with an emai notification to the end user when the page runs its stuff.  Not optimal, but it works

0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 12385268
PAQed, with points refunded (125)

CetusMOD
Community Support Moderator
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
What You Need to Know when Searching for a Webhost Provider
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

715 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