Script Timeout? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Script Timeout?

Author

Topic

MarkGG
Yak Posting Veteran

53 Posts

Posted - 2005-09-15 : 09:44:02

I have a series of nested while loops, but when I try to view the page it says the connection times out. I have made the nesting liket this because I know the depth will not excede 5 levels.

<head>
<script src="w3f.js"></script>

<style>
@import "w3f.css";
.box, .boxHover, .boxChecked, .boxCheckedHover {
  padding:0.5em 1em;
  margin:0em;
  cursor:default;
  border:1px solid #000040;
  background:#ABCDEF;
  color:#000040;
}
.boxHover {
  background:white;
}
.boxCheckedHover {
  border-color: #4040A0;
  color: #4040A0;
  background:white;
}
.boxChecked {
  background: #F8F8FC; 
  border-color: #4040A0;
  color: #4040A0;
}
.boxc {
  padding:0em 0.3em;
  text-align:center;
}
.boxc TABLE {
  margin:auto;
}
.lineright {
  border-right:1px solid #000040;
  font-size:50%;
}
.leftabove {
  border-right:1px solid #000040;
  border-top:1px solid #000040;
  font-size:50%;
}
.rightabove {
  border-top:1px solid #000040;
  font-size:50%;
}
.nolines {
  font-size:50%;
}
TD#chart {
  padding:1em;
}
DIV.message {
  color:#4040A0;
  font-weight:bold;
  background-color:#F8F8FC;
  border:1px solid #4040A0;
  margin:1em 0em;
  padding:0em 0.5em;
}
DIV.somespace {
  padding:0.2em;
  text-align:center;
}
DIV.somespace TABLE {
  margin:auto;
}
</style>

<%
ON ERROR RESUME NEXT
Dim con,sql,constring, objRS, objRS2,  objRS3, objRS4, objRS5, SQL2, SQL3, SQL4, SQL5, ParentNode, ChildNode
Set con = Server.CreateObject("ADODB.Connection")
%>

<!-- #include file="dbConnect.ssi" -->

<%
set objRS=Server.CreateObject("ADODB.recordset")
set objRS2=Server.CreateObject("ADODB.recordset")
set objRS3=Server.CreateObject("ADODB.recordset")
set objRS4=Server.CreateObject("ADODB.recordset")
set objRS5=Server.CreateObject("ADODB.recordset")
'I open my connection
con.Open dbConnect
%>

<script>
var nr=1;
var nodeBag={};
var sel=0;var attach_sel=0;
var selnode;

function Node(data,childNodes) {
  this.data=data;
  this.id=nr++;
  nodeBag[this.id]=this;
  if (childNodes) {
    this.first=childNodes[0];
    for (var i=0;i<childNodes.length;i++) {
      childNodes[i].prev=childNodes[i-1];
      childNodes[i].next=childNodes[i+1];
      childNodes[i].parent=this;
    }
  }
}
Node.prototype={
render:function(finalver) {
  var s='';var next='';var l=1;
  if (this.first) {
    l=0;var node=this.first;
    while (node) {
      next+='<TD valign="top">'+node.render(finalver)+'</td>';
      node=node.next;l++;
    }
  }
  s+='\n<table cellspacing="0" cellpadding="0" width="100%"><tr><td colspan='+l+'>';
  s+='\n<table cellspacing="0" cellpadding="0" width="100%">';
  if (this.parent) {
    s+='\n<tr><td width="50%" class="'+(!this.prev?'lineright':'leftabove')+'"> </td>';
    s+='<td width="50%" class="'+(!this.next?'nolines':'rightabove')+'"> </td></tr>';
  }
  s+='\n<tr><td colspan="2" class="boxc">\n<table cellspacing="0" cellpadding="0">';
  s+='\n<tr><td class="'+(sel==this.id&&!finalver?'boxChecked':'box')+'"'
  if (!finalver) s+=' onclick="unhover(this);choose('+this.id+');hover(this);" id="box'+this.id+'" onmouseover="hover(this)" onmouseout="unhover(this)"';
  s+='"><nobr>';
  if (finalver&&this.data.email) s+='<a href="mailto:'+this.data.email+'">';
  s+=(this.data.name||'(no name)');
  if (finalver&&this.data.email) s+='</a>';
  s+='</nobr></td></tr></table>\n</td></tr>';
  if (this.first) s+='\n<tr><td width="50%" class="lineright"> </td><td width="50%"></td></tr>';
  s+='</table></td></tr><tr>'+next+'</tr></table>';
  return s;
},
update:function() {
  var f=document.forms[0];
  for (var i=0;i<f.elements.length;i++)
    this.data[f.elements[i].name]=f.elements[i].value;
}

} //end node prototype definition

function generate() {
  var s='';
  for (var i in nodeBag)
    if (!nodeBag[i].parent) s+=nodeBag[i].getCode()+';\n';
  document.forms[1].source.value=s;
}

function renderIt() {
  var s='';var l="0;"
  for (var i in nodeBag)
    if (!nodeBag[i].parent) {
      if (l) s+='<HR width="70%">';
      s+=nodeBag[i].render();
      l++;
    }
  document.getElementById('chart').innerHTML=s||' ';
}

function loadExample() {


<%
ParentNode = 0
ChildNode = 1

SQL = "Select * from PROJECT order by proj_parent, proj_id"
objRS.Open SQL, dbConnect
response.write("new Node({name:'" & Trim(objRS("proj_name")) & "', manager:'" & Trim(objRS("manager")) & "', purpose:'" & Trim(objRS("Purpose")) & "', description:'" & Trim(objRS("description")) & "', start:'" & Trim(objRS("start_date")) & "', completion:'" & Trim(objRS("EstComp_Date")) & "'}, [")
response.write(vbcrlf)
do until objRS.EOF


	SQL2 = "Select * from PROJECT where proj_parent='" & objRS("proj_id") & "'"
	objRS2.Open SQL2, dbConnect
	response.write("new Node({name:'" & Trim(objRS2("proj_name")) & "', manager:'" & Trim(objRS2("manager")) & "', purpose:'" & Trim(objRS2("Purpose")) & "', description:'" & Trim(objRS2("description")) & "', start:'" & Trim(objRS2("start_date")) & "', completion:'" & Trim(objRS2("EstComp_Date")) & "'}, [")
	response.write(vbcrlf)
	do until objRS.EOF

		SQL3 = "Select * from PROJECT where proj_parent='" & objRS2("proj_id") & "'"
		objRS3.Open SQL3, dbConnect
		response.write("new Node({name:'" & Trim(objRS3("proj_name")) & "', manager:'" & Trim(objRS3("manager")) & "', purpose:'" & Trim(objRS3("Purpose")) & "', description:'" & Trim(objRS3("description")) & "', start:'" & Trim(objRS3("start_date")) & "', completion:'" & Trim(objRS3("EstComp_Date")) & "'}, [")
		response.write(vbcrlf)
		do until objRS.EOF

			SQL4 = "Select * from PROJECT where proj_parent='" & objRS3("proj_id") & "'"
			objRS4.Open SQL4, dbConnect
			response.write("new Node({name:'" & Trim(objRS4("proj_name")) & "', manager:'" & Trim(objRS4("manager")) & "', purpose:'" & Trim(objRS4("Purpose")) & "', description:'" & Trim(objRS4("description")) & "', start:'" & Trim(objRS4("start_date")) & "', completion:'" & Trim(objRS4("EstComp_Date")) & "'}, [")
			response.write(vbcrlf)
			do until objRS.EOF

					SQL5 = "Select * from PROJECT where proj_parent='" & objRS4("proj_id") & "'"
					objRS5.Open SQL5, dbConnect
					do until objRS.EOF
					response.write("new Node({name:'" & Trim(objRS5("proj_name")) & "', manager:'" & Trim(objRS5("manager")) & "', purpose:'" & Trim(objRS5("Purpose")) & "', description:'" & Trim(objRS5("description")) & "', start:'" & Trim(objRS5("start_date")) & "', completion:'" & Trim(objRS5("EstComp_Date")) & "'}")
					response.write(vbcrlf)

					objRS5.movenext
					Loop
					response.write("])")
					objRS5.close

			objRS4.movenext
			Loop
			response.write("]),")
			objRS5.close

		objRS3.movenext
		Loop
		response.write("]),")
		objRS3.close

	objRS2.movenext
	Loop
	response.write("]),")
	objRS2.close


objRS.movenext
Loop
response.write("]);")
objRS.close

con.Close
set Con = nothing
%>


  new Node({name:'Boss of Org',email:'boss@example.com', purpose:'To serve and protect the W/Ww needs of the public at large'},[
    new Node({name:'One Manager',email:'man1@example.com'},[
      new Node({name:'B. Fast',email:'bfast@example.com'},[
        new Node({name:'Bea Worker',email:'beaworker@example.com'}),
        new Node({name:'D. Plebian',email:'dplebian@example.com'})
      ])
    ]),
    new Node({name:'Manny Two',email:'man2@example.com'},[
      new Node({name:'Slick Talkman',email:'stalkman@example.com'},[
        new Node({name:'Patricia Guru',email:'pguru@example.com'}),
        new Node({name:'Mark Time',email:'mtime@example.com'})
      ]),
  ]);
  renderIt();
}

</script>
</head>

<body onload="loadExample();">

<table width="100%" height="100%" cellspacing="0" cellpadding="0">
</td></tr><tr><td id="body">

<table width="100%" class="border"0 cellspacing="0" cellpadding="0" id="chartpart">
<tr><td class="header">Transportation & Works</td></tr>
<tr><td id="chart" class="border"> </td></tr>

<form onsubmit="return false">
<center>
<table class="border" cellspacing="0" cellpadding="0" style="display:none" id="props">
<tr><td class="header" colspan="2">Project Details</td></tr>
<tr><th class="props">Name:</th><td class="border"><input class="props" size="100" type="text" readonly name="name" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
<tr><th class="props">Manager:</th><td class="border"><input class="props" size="100" type="text" readonly name="manager" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
<!--<tr><th class="props">E-Mail:</th><td class="border">--> <input type=hidden class="props" size="100" readonly type="text" name="email" value="" onblur="if (selnode) selnode.update();renderIt();" />  <!-- </td></tr> --> 
<tr><th class="props">Purpose:</th><td class="border"><textarea cols=75 rows=3 class="props" size="100" readonly type="text" name="purpose" value="" onblur="if (selnode) selnode.update();renderIt();" /> </textarea></td></tr>
<tr><th class="props">Description:</th><td class="border"><textarea cols=75 rows=3 class="props" size="100" type="text" readonly name="description" value="" onblur="if (selnode) selnode.update();renderIt();" /> </textarea></td></tr>
<tr><th class="props">Start Date:</th><td class="border"><input class="props" size="100" type="text" readonly name="start" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
<tr><th class="props">Est. Completion Date:</th><td class="border"><input class="props" size="100" type="text" readonly name="completion" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
</table>
</center>
</form>

<script>initButtons()</script>
</body>
</html>

It works when I remove all of the nested queries and have just the first, but anything past that won't run properly.

Thanks,
Mark

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 10:12:45

"the connection times out"

I'm not surprised, I'm afraid!

You would need to rewrite that as a single SQL statement, possibly as a UNION ALL of each one, or as an INSERT into a temporary table which you can loop round a few times to grab all the data. I would include a "computed" column of LEVEL so that your presentation login can indent accordingly.

You ought to get shot of those SELECT * too - if you name only the columns you are using then SQL only needs to retrieve those, plus it can cache the query.

Kristen

MarkGG
Yak Posting Veteran

53 Posts

Posted - 2005-09-15 : 10:34:34

Why would a temporary table work better than what I have, what exactly is incorrect about that code? In theory it seems to make sense to me.

How would a temporary table work any better? Would I not have to query that table for the information anyway? I don't know many other ways to approach this problem

And about the * I use that because I acually use every field from the table, but would it still be better to type up each individual name?

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 11:01:25

"Why would a temporary table work better"

Sorry, I didn't explain that very well.

What you are doing is fine, up to a point. You walk through a set of "top level" records and for each one pull all of its children. Therefore you do this "L1" times (depending on the number of records at "level 1"). So ... you now have 1 + L1 trips to the server. The more levels you have, the more the number of trips to the server grows (exponentially I expect!). That's why its timing out.

However, if you make this more SET based the amount of work for SQL Server will be dramatically reduced.

What you can do with a temporary table is:


INSERT INTO TemporaryTable 
SELECT 1 AS [Level], * 
FROM PROJECT 
WHERE proj_parent='ValueOf:proj_id'

INSERT INTO TemporaryTable 
SELECT 2 AS [Level], P.* 
FROM PROJECT AS P
     JOIN TemporaryTable AS T
          ON T.proj_parent = P.proj_id  -- Have I got this the right way round??

and so on for the rest of the levels you will support (or even in a loop until there are NO further parents to satisfy!!)

Each of these will find ALL of the children at the next level, regardless of the number of parents at the previous level. So its performance is linear for the number of levels (well, sort of, obviously for bigger data sets it will slow down somewhat)

Sorry, my code is pretty rough, but hopefully you get the idea. With just one INSERT INTO ... SELECT ... per level you will have found all the results you need. Much quicker than your exponential recursive method.

Then

SELECT *
FROM TemporaryTable
ORDER BY ... some order that gets you "walking order" for your tree, and probably includes my [Level] column

You wouldn't actually use all the SELECT * in my example, of course

"would it still be better to type up each individual name"

Yup, for two reasons

1) It helps to get the query cached, which makes it faster.

2) When you stick several TEXT columns in that table in the future

your method will pull them unnecessarily - and I can tell you from experience that will a) crucify the performance in the future and b) mean that you will have to visit every single SELECT * in the code and sort it out!

Kristen

MarkGG
Yak Posting Veteran

53 Posts

Posted - 2005-09-15 : 13:50:54

Well I changed all of the wildcards (*) into the actual column names but past that I am starting to get confused.

quote:

Sorry, I didn't explain that very well.

What you are doing is fine, up to a point. You walk through a set of "top level" records and for each one pull all of its children. Therefore you do this "L1" times (depending on the number of records at "level 1"). So ... you now have 1 + L1 trips to the server. The more levels you have, the more the number of trips to the server grows (exponentially I expect!). That's why its timing out.

However, if you make this more SET based the amount of work for SQL Server will be dramatically reduced.

What you can do with a temporary table is:

INSERT INTO TemporaryTable
SELECT 1 AS [Level], *
FROM PROJECT
WHERE proj_parent='ValueOf:proj_id'

INSERT INTO TemporaryTable
SELECT 2 AS [Level], P.*
FROM PROJECT AS P
JOIN TemporaryTable AS T
ON T.proj_parent = P.proj_id -- Have I got this the right way round??

and so on for the rest of the levels you will support (or even in a loop until there are NO further parents to satisfy!!)

Each of these will find ALL of the children at the next level, regardless of the number of parents at the previous level. So its performance is linear for the number of levels (well, sort of, obviously for bigger data sets it will slow down somewhat)

Sorry, my code is pretty rough, but hopefully you get the idea. With just one INSERT INTO ... SELECT ... per level you will have found all the results you need. Much quicker than your exponential recursive method.

Ok this part confuses me now. Should I have the temporary table pre-made or should I create/drop it every time the page is loaded?

About the statements you made, what is select 1 and select 2? That appears to be "T-SQL"? and I not to familiar with that. P represents project? The query part I believe is right. You need to select the project id's where the parent is equal to project_id of the above query.

Thanks for the help so far

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 14:42:28

You can do

SELECT MyColumn1, MyColumn2
FROM MyTable

Then you can do

SELECT 'SomeStuff' AS MyNewColumn, MyColumn1, MyColumn2
FROM MyTable

and your resultset will have an extra column called "MyNewColumn"

So I was doing

SELECT 1 AS [Level], ...

to get an extra column called "Level"

Later on you need to join the PROJECT table to the TemporaryTable.

At that point there are column name conflicts between the two tables
(well, it depends what you call the column names in the TempoaryTable, but I assumed they might be the same).
So you can "alias" the tables and explicitly name the columns unambiguously by using the Alias names.

So:

SELECT P.ColumnFromPROJECT, T.ColumnFromTempTable
FROM PROJECT AS P
JOIN TemporaryTable AS T

"Should I have the temporary table pre-made"

If you have one pre-made then you have to worry about two people making a query at the same time, and sharing that table. Better to have a real temporary one I reckon.

You can CREATE a #TemporaryTable name, and SQL will create it in a special TEMPDB database, and it will automatically be dropped when it goes out of scope (and given a unique name that won't clash with another user), or you can DECLARE an @TemporaryTable which will be created in memory (and not clash, and also be dropped when it goes out of scope).

I think the "@" one would probably best best, unless there are going to be an huge number of rows stored. Just use a DECLARE statement to create it just before you first use it.

Books Online (BoL) will have more details of the syntax etc.

Hope that helps

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources