Page is Loading too Slow

kornshell · October 15th, 2008, 12:01 PM

I have a new webpage that is making an ADO connection to and querying an OSI PI database. The new webpage is basically a copy of another. The differences are 1.the new webpage is querying a new "tagname" (a Process controls term) and 2.The new page is in a different folder.

The new page's load time is drastically more than the original.

Any ideas as to why or what to check for?

Old Pedant · October 15th, 2008, 02:08 PM

Find out if the difference in time is all in the SQL Query.

Easy to do:

Code:

<%
...
startat = Timer()
Set RS = yourConnection.Execute(yourSQL)
endat = Timer()
Response.Write "Time to make query: " & ( endat - startat ) & ""
...
%>

If the time is all lost in the query (which I would suspect to be the case), then you need to figure out how to improve the query performance. Maybe you are just hitting lots more records, in which case you might be stuck. But maybe you are now NOT using an index, whereas the other page is able to take advantage of some index. So maybe you only need to add an index to the DB.

Hard to guess. What is an "OSI PI" database, by the by?

kornshell · October 15th, 2008, 02:22 PM

OSI PI is a proprietary data historian. Here's the code I'm working with like I mentioned before basically the only thing different is the strTag = "another tag"

<% Response.Buffer = true %>
<%LANGUAGE=VBSCRIPT%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="VI60_defaultClientScript" content="VBScript">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">

<title>Al. Power Usage - Last 31 Days</title>
<base target="main">
</head>
<body bgcolor="#C0C0C0" text="#2B2B80">

<p align="center"><b>    Bay
Gas Daily Usage - Last 31 Days</b></p>
<%
    Dim conn_2pi
    Dim rsList
    Dim strTag, strSQL, strDay(31)
    Dim dtDate, dtDay(31)
    Dim iDay, iDayVal(31), iTableWidth, iCol1, iCol2

    iCol1 = 20
    iCol2 = 35
    iTableWidth = iCol1 + iCol2

    'Converts a Date to a string in PI time format.
    Function GetPIDate(dtDate)

    GetPIDate = cstr(Day(dtDate) & "-" & Ucase(Left(MonthName(Month(dtDate)),3)) & _
         "-" & Right(Year(dtDate),2) & " 23:59")

    End Function

strTag = "2400_Report:Natural_Gas_Usage.Value"

   Set conn_2pi = Server.CreateObject("ADODB.Connection")
   conn_2pi.Open "PI_Web", "piadmin", ""
   Set rsList = CreateObject("ADODB.Recordset")
%>
<table border="1" width="<%=iTableWidth%>%" align="center">
  <tr>
    <td width="<%=iCol1%>%" align="center"><b>Date</b></td>
    <td width="<%=iCol2%>%" align="center" style="margin-top: 2; margin-bottom: 2">
      <p style="margin-top: 1; margin-bottom: 1"><b>MMbtu</b>
    </td>
  </tr>
<%
   Response.Flush

   For iDay = 1 to 31

      dtDay(iDay) = (Date()-1) - (31 - iDay)
      strDay(iDay) = GetPIDate(dtDay(iDay))

      strSQL = "SELECT time, status FROM picomp " & _
               "WHERE tag = '" & strTag & "' AND time = '" & strDay(iDay) & "'"

      rsList.CursorLocation = adUseClient
      rsList.Open strSQL, conn_2pi, adOpenStatic, adLockReadOnly, adCmdText

      If Not rsList.EOF Then
         iDayVal(iDay) = rsList.Fields("Status")
%>
  <tr>
    <td width="<%=iCol1%>%" align="center"><%=dtDay(iDay)%></td>
    <td width="<%=iCol2%>%" align="center"><%=iDayVal(iDay)%></td>
  </tr>
<%
      Else
%>
  <tr>
    <td width="<%=iCol1%>%" align="center"><%=dtDay(iDay)%></td>
    <td width="<%=iCol2%>%" align="center">No Data</td>
  </tr>
<%
      End If

      rsList.Close

   Next
%>
</table>
<%

   Set rsList = Nothing
   Set conn_2pi = Nothing
%>

Old Pedant · October 15th, 2008, 03:17 PM

Well, if your tag field *IS* indexed, then you don't have to do a full scan of the table to implement the WHERE clause. So then the time required would be directly related to the number of matches on the requested tag value.

But I see you are only asking for ONE RECORD. Are you sure you are only *getting* one record, for each of the different tag values??

Anyway, it's really hard for me to guess what any other possible problem is, since I have zero experience with that database.

I will say that the code strikes me as pretty bad, in general.
(1) You make one query for each day, instead of one query for the entire period.
(2) You put all those values you are using and getting into arrays, but I see no reason for that. Is there other code, after this, that then actually uses the arrays?
(3) You are using an expensive cursor (adOpenStatic) for no discernible reason.
(4) The code *feels* clumsy to me. Instead of

Code:

  
For iDay = 1 to 31
    dtDay(iDay) = (Date()-1) - (31 - iDay)

I would have done

Code:

  
startDate = Date() - 32
For iDay = 1 To 31
    dtDay(iDay) = startDate + iDay ' though why the array?

kornshell · October 15th, 2008, 03:40 PM

Well, it's a copy basically. I don't have the knowledge to discern whether it's the most efficient method.

With that said, records are inserted into the database every day with a 23:59:00 time stamp using another webpage.

This webpage queries and builds a table of records for the past 31 days.

Since I built this new webpage and put it in a new folder I thought maybe I needed to do something in IS manager or something? I've compared the properties of this folder and others and I see no differences. I'm stumped.

Old Pedant · October 15th, 2008, 03:49 PM

Again, without knowing how that DB system works, it's real hard to even guess why a change like this would matter. If you were querying against a different field in the table, the problem would make more sense. I think we could improve the general performance of the page in ways unrelated to that field, but if there's something odd about that value as presented to that DB...then I dunno.

I *would* suggest that you do as I suggested: Add that timer based code to your page and time each of the queries and report the time used for each record.

kornshell · October 15th, 2008, 03:59 PM

I'll do that tomorrow and report back.

Thankyou for your time.

kornshell · October 16th, 2008, 09:28 AM

I found my problem but, I'm not sure why yet.

In Component Services, under the COM+ Application is a list of the webpages. In the properties of each webpage is a Tab "Indentity".
The default is IWAM_WebName as the user that the webpage will run under. I changed the user name to the box administrator account and the webpages work fine at much faster speeds.

I'll need to dig around the box and sort out the various accounts that were created.

Thanks for your help.