Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 15th, 2008, 01:01 PM
Registered User
 
Join Date: Jun 2007
Location: Satsuma, Alabama, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Page is Loading too Slow

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?
Reply With Quote
  #2 (permalink)  
Old October 15th, 2008, 03:08 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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?
Reply With Quote
  #3 (permalink)  
Old October 15th, 2008, 03:22 PM
Registered User
 
Join Date: Jun 2007
Location: Satsuma, Alabama, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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>&nbsp;&nbsp;&nbsp;&nbsp;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
%>
Reply With Quote
  #4 (permalink)  
Old October 15th, 2008, 04:17 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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?
Reply With Quote
  #5 (permalink)  
Old October 15th, 2008, 04:40 PM
Registered User
 
Join Date: Jun 2007
Location: Satsuma, Alabama, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #6 (permalink)  
Old October 15th, 2008, 04:49 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #7 (permalink)  
Old October 15th, 2008, 04:59 PM
Registered User
 
Join Date: Jun 2007
Location: Satsuma, Alabama, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'll do that tomorrow and report back.

Thankyou for your time.
Reply With Quote
  #8 (permalink)  
Old October 16th, 2008, 10:28 AM
Registered User
 
Join Date: Jun 2007
Location: Satsuma, Alabama, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Slow page Loading yadavrahul2k5 ASP.NET 2.0 Professional 5 August 13th, 2007 08:12 AM
Slow-loading form(s) dhl84 Access VBA 1 January 11th, 2007 01:16 PM
.net page loading is slow angshujit .NET Framework 1.x 1 December 26th, 2006 07:53 AM
Loading too slow chiholi Crystal Reports 0 November 16th, 2004 02:52 AM
Slow loading ASP or SSIs hcweb Classic ASP Basics 7 October 8th, 2004 05:04 PM



All times are GMT -4. The time now is 05:38 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.