more effecient, less bulky 1

[scottRen]

i really need to have things run more effecient. currently
i have a function that numbers cells on a page retreived by aspTear, this part of the code is fine:

<%
Function GetCell(cellnumber, extracturl)

   Const Request_POST = 1
   Const Request_GET = 2

   Set xObj = Server.CreateObject("SOFTWING.AspTear")
   strRetVal = xObj.Retrieve(extracturl,Request_GET,"","","")
   set xobj = nothing

   i = 1              ' HTML Text Location Start
   q = 1              ' Cell Number Start

   ' Loop until we have processed the cell we're looking for   
   Do until q > cellnumber
      ' Look for <TD the start of a cell
      i = InStr(i, UCase(strRetVal), "<TD")
      
      ' Find the location of the end of the <TD tag
      r = InStr(i, strRetVal, ">")          
      
      ' Let the next loop start looking after this <TD tag we found
      i = r + 1                             
      
      ' increase the count of which cell we're at
      q = q + 1                             
   Loop

   ' The start of our cell text is right after the last found tag
   StartCellText = i                    

   If (InStr(r, UCase(strRetVal), "<TABLE") > 0) AND _
         (InStr(r, UCase(strRetVal), "<TABLE") < _
              InStr(r, UCase(strRetVal), "</TD>")) then
      ThisCellText = mid(strRetVal, StartCellText, _
               InStr(r, UCase(strRetVal),"<TABLE")- StartCellText )
   Else
      ThisCellText = mid(strRetVal, StartCellText, _
               InStr(r, UCase(strRetVal), "</TD>")- StartCellText )
   End If

   GetCell = ThisCellText
End Function
%>

Then since i need the infor from may cells, and need it so that each cell info is set to a variable, i have this ugly code:

variable44 = GetCell(44, "[URL unfurl="true"]http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent")[/URL]
variable45 = GetCell(45, "[URL unfurl="true"]http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent")[/URL]
variable47 = GetCell(47, "[URL unfurl="true"]http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent")[/URL]
variable49 = GetCell(49, "[URL unfurl="true"]http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent")[/URL]

//i have a space here to indicate that the variables (44-49 for instance) is a set, i have blocked them like this for use later

variable57 = GetCell(57, "[URL unfurl="true"]http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent")[/URL]
variable58 = GetCell(58, "[URL unfurl="true"]http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent")[/URL]
variable60 = GetCell(60, "[URL unfurl="true"]http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent")[/URL]
variable62 = GetCell(62, "[URL unfurl="true"]http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent")[/URL]

.....all the way up to cell 1071......

variable1071 = GetCell(1071, "[URL unfurl="true"]http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent")[/URL]

then i need to take these variables of cell infomation and format the string so it excludes certain information and then i set the variable information into a table:

<%
strNumber = Mid(variable44, InStr(variable44, "(") + 1)
strNumber = Left(strNumber, InStrRev(strNumber, "(") - 1)
strNumber = Trim(Replace(strNumber, "(", "")) 
strNumber = Trim(Replace(strNumber, ")", "")) 
strText = Replace(variable44, "(" & strNumber & ")", "")
%> 
<table width='100%'><tr>
<td width='10%' bgcolor="#FFFFFF"><%=variable49%></td>
<td width='10%' bgcolor="#FFFFFF"><%=variable45%></td>
<td width='10%' bgcolor="#FFFFFF"><%=(CLng(strNumber))%></td>
<td width='70%' bgcolor="#FFFFFF"><a href="todaysq.asp?t=<%=table2%>&head=cn&sort=<%=sort%>"><%=strText%></a><br><%=variable47%></td>
</tr></table>


<%
strNumber1 = Mid(variable57, InStr(variable57, "(") + 1)
strNumber1 = Left(strNumber1, InStrRev(strNumber1, "(") - 1)
strNumber1 = Trim(Replace(strNumber1, "(", "")) 'in case we pick up the first "(" &
strNumber1 = Trim(Replace(strNumber1, ")", "")) 'to make sure we get rid of the ")"
strText2 = Replace(variable57, "(" & strNumber1 & ")", "")
%> 
<table width='100%'><tr>
<td width='10%' bgcolor="#FFFFFF"><%=variable62%></td>
<td width='10%' bgcolor="#FFFFFF"><%=variable58%></td>
<td width='10%' bgcolor="#FFFFFF"><%=(CLng(strNumber1))%></td>
<td width='70%' bgcolor="#FFFFFF"><a href="todaysq.asp?t=<%=table2%>&head=cn&sort=<%=sort%>"><%=strText2%></a><br><%=variable60%></td>
</tr></table>

And i also have a "string formating script" and a "table layout" with the variable and formatted variable information in it representing for each block of code previously displayed liuke so:
variable57 = GetCell(57, "

http://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent")

 

[Tarwn]

Ok, well the biggest problem is that your doing a Request for each and every individual cell. The single biggest change you could make would be to split up the code that retrieves the content and the code that finds a particular cell in that content.
Right now your process looks something like this:

For i = 0 to 1071
   Go Get a copy of the web page
   For j = 0 to i
      get text for this cell variable
   Next
Next

That is an incredible amount of overhead. To start with you should only be pulling back tat page one time, then processing it from there:

Go get a copy of the web page
For Each Cell in Web Page
   get text for this cell variable

this will remove 1070 of your page requests and also reduce the number of instr's you have to do by about 1,941,435 (1070 + 1069 + 1068 + ... this is the number of extraneous ones your doing right now).

I would also suggest using an array if this magic 1071 upper bound is not going to get larger. It will be much eaiser to work with then 1071 indivdual variables.

-T

 

[scottRen]

Thanks Tarwn for the pointers,
i worked on it on the weekend and am took your advice plus did some reading. I think things are more effecient now, but i could be wrong. this is what i did, and now i'm not using the aspTear dll, but rather a native one.

<%
Set xml = Server.CreateObject("MSXML2.ServerXMLHTTP")
xml.Open "GET", "company.com/cgi-bin/post?action=getcurrentURL", False
xml.Send
strRetVal = xml.responseText

Function GetCell(cellnumber)
   Const Request_POST = 1
   Const Request_GET = 2

   i = 1
   q = 1

   Do until q > cellnumber

      i = InStr(i, UCase(strRetVal), "<TD")
         r = InStr(i, strRetVal, ">")          
            i = r + 1                             
      
      q = q + 1                             
   Loop


   StartCellText = i                    

   If (InStr(r, UCase(strRetVal), "<TABLE") > 0) AND _
         (InStr(r, UCase(strRetVal), "<TABLE") < _
              InStr(r, UCase(strRetVal), "</TD>")) then
      ThisCellText = mid(strRetVal, StartCellText, _
               InStr(r, UCase(strRetVal),"<TABLE")- StartCellText )
   Else
      ThisCellText = mid(strRetVal, StartCellText, _
               InStr(r, UCase(strRetVal), "</TD>")- StartCellText )
   End If

   GetCell = ThisCellText

   Set xml = Nothing
End Function

%>
<html>
<%
' i only am grabbing from 314 cells
variable44 = GetCell(44)
variable45 = GetCell(45)
variable47 = GetCell(47)
variable49 = GetCell(49)
variable44 = GetCell(57)
...and so on
variable1074 = GetCell(1074)
variable1076 = GetCell(1076)

' tried would of have use like below but i'm onlt calling 315 cells not 1071 of them
' Variable(1071)
' For i = 1 to UBound(Variable)
' Variable(i) = GetCell(i)
' Next


<html>
.....
<%
strNumber = Mid(variable44, InStr(variable44, "(") + 1)
strNumber = Left(strNumber, InStrRev(strNumber, "(") - 1)
strNumber = Trim(Replace(strNumber, "(", "")) 
strNumber = Trim(Replace(strNumber, ")", "")) 
strText = Replace(variable44, "(" & strNumber & ")", "")
%>
<table width='100%'><tr>
<td width='10%' bgcolor="#FFFFFF"><%=variable49%></td>
<td width='10%' bgcolor="#FFFFFF"><%=variable45%></td>
<td width='10%' bgcolor="#FFFFFF"><%=(CLng(strNumber))%></td>
<td width='70%' bgcolor="#FFFFFF"><a href="todaysq.asp?t=<%=table2%>&head=cn&sort=<%=sort%>"><%=strText%></a><br><%=variable47%></td>
</tr></table>

it seems to work faster, Does this seems better?
Thanks

 

[Tarwn]

You have reduced the number of times your going to get the webpage, but your GetCell function is still looping at least n times for each call (n being the cell number you pass).
You might be better off parsing the page one time into an array, then grabbing the cells out of that array that you need. Just looking at your last two GetCell() calls your going to be doing over 2000 InStr's before you get the values just for those two cells.
Additionally you might want to look into using a Regular Expression to pull out the contents of all the TD tags in your retrieved page.

Simple parse to an array example:

'grab the page
Dim xml, strRetVal
Set xml = Server.CreateObject("MSXML2.ServerXMLHTTP")
xml.Open "GET", "company.com/cgi-bin/post?action=getcurrentURL", False
xml.Send
strRetVal = xml.responseText
ucStrRetVal = UCase(strRetVal)  'did this one time instead of dynamically in the loop
Set xml = Nothing

Dim cellArray(1076)
Dim lastPos, cellStart, cellCtr

lastPos = InStr(ucStrRetVal,"<TD")
Do Until lastPos = 0 or cellCtr > 1076
   'find end of TD tag
   cellStart = InStr(lastPos,ucStrRetVal,"<")
   'advance lastPos marker
   lastPos = cellStart + 1

   'pull out contents of cell
   If (InStr(r, UCase(strRetVal), "<TABLE") > 0) AND _
         (InStr(r, UCase(strRetVal), "<TABLE") < _
              InStr(r, UCase(strRetVal), "</TD>")) then
      cellArray(cellCtr) = mid(strRetVal, StartCellText, _
               InStr(r, UCase(strRetVal),"<TABLE")- StartCellText )
   Else
      cellArray(cellCtr) = mid(strRetVal, StartCellText, _
               InStr(r, UCase(strRetVal), "</TD>")- StartCellText )
   End If

   'advance lastPos to next TD tag
   lastPos = InStr(lastPos,ucStrRetVal,"<TD")
   'increment index ctr
   cellCtr = cellCtr + 1
Loop

I assumed there would only be 1077 elements (0 - 1076) for this in the array declaration, but put an upper protection in the while loop anyways, just in case.

You may actually be better off trying to come up with a regular expression to match the contents of the two types of cells, you may even be able to force it to match only the portion of the value you want, removing the need for the extra processing afterwards.

In any case, even with a hardcoded upper bound in my array example above you should still be processing much fewer [repetitive] instructions by pushing it all into an array one time. If the cell contents are large you may run into a speed issue with the size of the array and amount of memory you need, I don't enough about your data to see if that would be a problem or not.

-T

 

[scottRen]

would i call the 44th item from the array like so?
<%=cellArray(44)%>

 

[scottRen]

Tarwn, what should i do about the "r" variable in the script:
InStr(r, UCase(strRetVal), "</TD>")- StartCellText )

thanks

 

[scottRen]

OK - i got it to work but there seems to be a problem, i'm getting html to the left of the cell info:
td valign="top" ...>6K

now i want the 6k, but the html> stuff to the left need to go. I test many items in the array and they are all written like this, how can i get rid of the garbage to the left, including the ">"

thanks Tarwn, you have opened new doors in my abilities to programm asp

 

[scottRen]

i almost forgot:

xml.Send
strRetVal = xml.responseText
ucStrRetVal = UCase(strRetVal)  'did this one time instead of dynamically in the loop
Set xml = Nothing

Dim cellArray(1076)
Dim lastPos, cellStart, cellCtr
lastPos = InStr(ucStrRetVal,"<TD")

Do Until lastPos = 0 or cellCtr > 1076
   cellStart = InStr(lastPos,ucStrRetVal,"<")
   lastPos = cellStart + 1        
   
If (InStr(cellStart, UCase(strRetVal), "<TABLE") > 0) AND _
         (InStr(cellStart, UCase(strRetVal), "<TABLE") < _
              InStr(cellStart, UCase(strRetVal), "</TD>")) then
      cellArray(cellCtr) = mid(strRetVal, lastPos, _
               InStr(cellStart, UCase(strRetVal),"<TABLE")- lastPos )
   Else
      cellArray(cellCtr) = mid(strRetVal, lastPos, _
               InStr(cellStart, UCase(strRetVal), "</TD>")- lastPos )
   End If
 
  lastPos = InStr(lastPos,ucStrRetVal,"<TD")
 
  cellCtr = cellCtr + 1

Loop
%>

<%=cellArray(70)%>

 

[Tarwn]

Heh, there was a mis-type in there:

cellStart = InStr(lastPos,ucStrRetVal,"[highlight]>[/highlight]")

I was trying to re-use your existing code as much as possible and somehow must have mistyped what was supposed to be the end of that TD tag...sorry

-T