|
 |
asp_databases thread: Help with code returning records from SQL. Please include sample code
Message #1 by "jeff tandy" <jtandy@f...> on Thu, 19 Jul 2001 20:04:01
|
|
I am very new to ASP(started 1 week ago) so please keep responses simple.
The following code does pull the correct records from my database but it
takes 30 seconds or more to display the results. The first part of the
code seems to cause the delay. The second 2 parts if run independently
are fast enough but the block for code for CP-H1 Server takes forever.
How can I tune up this code with out getting to complicated with the
code. Thanks in advance
Jeff
<html>
<head>
<title>CP-H1 SERVER HEALTH</title>
<link rel="stylesheet" type="text/css" href="/style.css">
</head>
<body>
<!-- ASP Code Begins -->
<!-- This is for CP-H1-SERVER (SCADAMSTR2) INSQL HEALTH -->
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="DSN=CPSQL;uid=1;pwd=1"
conn.Open
conn.DefaultDatabase="Runtime"
set rs = Server.CreateObject("ADODB.recordset")
set rs2 = Server.CreateObject("ADODB.recordset")
set rs3 = Server.CreateObject("ADODB.recordset")
set rs5 = Server.CreateObject("ADODB.recordset")
set rs6 = Server.CreateObject("ADODB.recordset")
set rs7 = Server.CreateObject("ADODB.recordset")
set rs8 = Server.CreateObject("ADODB.recordset")
set rs9 = Server.CreateObject("ADODB.recordset")
set rs10 = Server.CreateObject("ADODB.recordset")
sql = "select " &_
"value, " &_
"datetime " &_
"from " &_
"v_AnalogHistory " &_
"where " &_
"tagname='sysiodrivercps' " &_
"ORDER BY datetime desc"
rs.CursorLocation = 3
rs.Open sql,conn,3
sql2 = "select " &_
"value, " &_
"datetime " &_
"from " &_
"v_AnalogHistory " &_
"where " &_
"tagname='SysMinutesRun' " &_
"ORDER BY datetime desc"
rs2.CursorLocation = 3
rs2.Open sql2,conn,3
sql3 = "select " &_
"value, " &_
"datetime " &_
"from " &_
"v_AnalogHistory " &_
"where " &_
"tagname='SysCritErrCnt' " &_
"ORDER BY datetime desc"
rs3.CursorLocation = 3
rs3.Open sql3,conn,3
sql5 = "select " &_
"value, " &_
"datetime " &_
"from " &_
"v_AnalogHistory " &_
"where " &_
"tagname='SysActiveUsers' " &_
"ORDER BY datetime desc"
rs5.CursorLocation = 3
rs5.Open sql5,conn,3
sql6 = "select " &_
"value, " &_
"datetime " &_
"from " &_
"v_DiscreteHistory " &_
"where " &_
"tagname='SysHealth' " &_
"ORDER BY datetime desc"
rs6.CursorLocation = 3
rs6.Open sql6,conn,3
sql7 = "select " &_
"value, " &_
"datetime " &_
"from " &_
"v_DiscreteHistory " &_
"where " &_
"tagname='SysEventSystem' " &_
"ORDER BY datetime desc"
rs7.CursorLocation = 3
rs7.Open sql7,conn,3
sql8 = "select " &_
"value, " &_
"datetime " &_
"from " &_
"v_DiscreteHistory " &_
"where " &_
"tagname='SysStringStorage' " &_
"ORDER BY datetime desc"
rs8.CursorLocation = 3
rs8.Open sql8,conn,3
sql9 = "select " &_
"value, " &_
"datetime " &_
"from " &_
"v_DiscreteHistory " &_
"where " &_
"tagname='SysDatabaseServer' " &_
"ORDER BY datetime desc"
rs9.CursorLocation = 3
rs9.Open sql9,conn,3
sql10 = "select " &_
"value, " &_
"datetime " &_
"from " &_
"v_DiscreteHistory " &_
"where " &_
"tagname='SysDataReception' " &_
"ORDER BY datetime desc"
rs10.CursorLocation = 3
rs10.Open sql10,conn,3
TableTop = "<table cellspacing=1 width=725>" &_
"<col width=175>" &_
"<col width=100>" &_
"<col width=100>" &_
"<col width=100>" &_
"<col width=100>" &_
"<col width=100>" &_
"<col width=100>" &_
"<col width=100>" &_
"<col width=100>" &_
"<col width=100>"
TableHeader = "<tr>" &_
"<th>DateTime</th>" &_
"<th>Acquisition Rate<br>Char/sec</th>" &_
"<th>Minutes Run</th>" &_
"<th>Fatal Errors</th>" &_
"<th>No. Users</th>" &_
"<th>System Health</th>" &_
"<th>Event System</th>" &_
"<th>String Storage</th>" &_
"<th>Database Storage</th>" &_
"<th>Data Reception</th>" &_
"</tr>"
TableInfo = "<tr><th colspan=12 class='caption'>" &_
"SCADAMSTR2 INSQL (DATA ACQUISITION)
HEALTH" &_
"</th></tr>"
Response.Write TableTop
Response.Write TableInfo
Response.Write TableHeader
Response.Write "<tr>" &_
"<td>" & rs("datetime") & "</td>" &_
"<td>" & rs("value") & "</td>" &_
"<td>" & rs2("value") & "</td>" &_
"<td>" & rs3("value") & "</td>" &_
"<td>" & rs5("value") & "</td>" &_
"<td>" & rs6("value") & "</td>" &_
"<td>" & rs7("value") & "</td>" &_
"<td>" & rs8("value") & "</td>" &_
"<td>" & rs9("value") & "</td>" &_
"<td>" & rs10("value") & "</td>" &_
"</tr>"
Response.Write "</table><br><br>"
rs.Close
set rs = nothing
rs2.Close
set rs2 = nothing
rs3.Close
set rs3 = nothing
rs5.Close
set rs4 = nothing
rs6.Close
set rs6 = nothing
rs7.Close
set rs7 = nothing
rs8.Close
set rs8 = nothing
rs9.Close
set rs9 = nothing
rs10.Close
set rs10 = nothing
Response.Write "</table><br>"
<!-- This is for SCADAMSTR SQL HEALTH -->
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="DSN=CPSQL;uid=1;pwd=1"
conn.Open
conn.DefaultDatabase="Master"
set rs11 = Server.CreateObject("ADODB.recordset")
set rs12 = Server.CreateObject("ADODB.recordset")
set rs13 = Server.CreateObject("ADODB.recordset")
set rs14 = Server.CreateObject("ADODB.recordset")
sql = "select cntr_value " &_
"from " &_
"SysPerFinfo " &_
"where " &_
"COUNTER_NAME='User Connections' "
rs11.CursorLocation = 3
rs11.Open sql,conn,3
sql2 = "select cntr_value " &_
"from " &_
"SysPerFinfo " &_
"where " &_
"COUNTER_NAME='Number Of Deadlocks/sec' "
rs12.CursorLocation = 3
rs12.Open sql2,conn,3
sql3 = "select cntr_value " &_
"from " &_
"SysPerFinfo " &_
"where " &_
"COUNTER_NAME='Lock Waits/sec' "
rs13.CursorLocation = 3
rs13.Open sql3,conn,3
sql4 = "select cntr_value " &_
"from " &_
"SysPerFinfo " &_
"where " &_
"COUNTER_NAME='Percent Log Used' "
rs14.CursorLocation = 3
rs14.Open sql4,conn,3
TableTop1 = "<table cellspacing=1 width=725>" &_
"<col width=175>" &_
"<col width=100>" &_
"<col width=100>" &_
"<col width=100>" &_
"<col width=100>"
TableHeader1 = "<tr>" &_
"<th>No. of USERS</th>" &_
"<th>DEADLOCKS/sec</th>" &_
"<th>LOCK WAITS/sec</th>" &_
"<th>% of LOG FILE</th>" &_
"</tr>"
TableInfo1 = "<tr><th colspan=5 class='caption'>" &_
"SCADAMSTR2 SQL (DATA STORAGE AND
PROVIDER) HEALTH" &_
"</th></tr>"
Response.Write TableTop1
Response.Write TableInfo1
Response.Write TableHeader1
Response.Write "<tr>" &_
"<td>" & rs11("cntr_value") & "</td>" &_
"<td>" & rs12("cntr_value") & "</td>" &_
"<td>" & rs13("cntr_value") & "</td>" &_
"<td>" & rs14("cntr_value") & "</td>" &_
"</tr>"
Response.Write "</table><br>"
rs11.Close
set rs11 = nothing
rs12.Close
set rs12 = nothing
rs13.Close
set rs13 = nothing
rs14.Close
set rs14 = nothing
Response.Write "</table><br>"
<!-- This is for ERRORS FROM THE EVENTVIEWER -->
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="DSN=SCADAMSTR;uid=1;pwd=1"
conn.Open
conn.DefaultDatabase="EventArchiver"
set rs1 = Server.CreateObject("ADODB.recordset")
sql = "SELECT " &_
"DateAndTime, " &_
"DESCRIPTION, " &_
"SOURCE " &_
"FROM " &_
"EventLogs " &_
"where " &_
"DATEANDTIME > DATEADD(day,-5,GETDATE()) " &_
"AND " &_
"Computer = 'SCADAMSTR2' AND TypeOfEvent = 'Error'
AND SOURCE <> 'SQLServerAgent' " &_
"ORDER BY DateAndTime DESC "
rs1.CursorLocation = 3
rs1.Open sql,conn,3
TableTop2 = "<table cellspacing=1 width725>" &_
"<col width=100>" &_
"<col width=525>" &_
"<col width=100>"
TableHeader2 = "<tr>" &_
"<th>Date</th>" &_
"<th>Description</th>" &_
"<th>Source</th>" &_
"</tr>"
TableInfo2 = "<tr><th colspan=3 class='caption'>" &_
"EVENT LOG FOR LAST 5 days (all pcs)" &_
"</th></tr>"
Response.Write TableTop2
Response.Write TableInfo2
Response.Write TableHeader2
while not rs1.EOF
Response.Write "<tr>" &_
"<td>" & rs1("DateAndTime") & "</td>" &_
"<td>" & rs1("Description") & "</td>" &_
"<td>" & rs1("Source") & "</td>" &_
"</tr>"
rs1.movenext
wend
Response.Write "</table><br>"
rs1.Close
set rs1 = nothing
Response.Write "</table>"
%>
<p>
<a href="../COMMON/LocalAdministrator.htm">SERVER HEALTH LEGEND</a>
<br>
</p>
<p> </p>
</body>
Message #2 by "Peter Foti (PeterF)" <PeterF@S...> on Thu, 19 Jul 2001 17:04:15 -0400
|
|
This probably isn't the cause of your problem, but you are concatenating
a LOT of strings, and from what I've read, this is a time consuming
operation. You might try eliminating some of the unnecessary
concatenations. For example, change this:
> sql = "select " &_
> "value, " &_
> "datetime " &_
> "from " &_
> "v_AnalogHistory " &_
> "where " &_
> "tagname='sysiodrivercps' " &_
> "ORDER BY datetime desc"
to this:
sql = "select value, datetime from v_AnalogHistory where
tagname='sysiodrivercps' ORDER BY datetime desc"
Keep it all on one line and don't unnecessarily concatenate it. Yes,
it's easier to look at the way you had it, but it's less efficient. I
often will through in an extra unnecessary concatination from time to
time, just for readability. For example, I might do something like
this:
sql = "select value, datetime from v_AnalogHistory " & _
"where tagname='sysiodrivercps' ORDER BY datetime desc"
Hope this helps.
-Peter
Message #3 by Steve Carter <Steve.Carter@t...> on Fri, 20 Jul 2001 08:39:13 +0100
|
|
Two hints:
1) include the file adovbs.inc in your page and use symbolic constants
like adOpenKeySet, adCmdText instead of "magic numbers". Anyone having
to maintain or debug the code (including yourself!) will thank you for it!
2) You have more an SQL programming problem than an ASP one. If you can
return all your relevant data in a single query then you only need one
recordset
to access it. E.g. write the following
> sql = "select " &_
> "value, " &_
> "datetime " &_
> "from " &_
> "v_AnalogHistory " &_
> "where " &_
> "tagname='sysiodrivercps' " &_
> "or " &_
> "tagname='sysiodrivercps' " &_
> "or " &_
> "tagname='SysMinutesRun' " &_
> "or " &_
> "tagname='SysCritErrCnt' " &_
> "or " &_
> "tagname='SysActiveUsers' " &_
> "ORDER BY datetime desc"
> rs.CursorLocation = adSomeConstantNameHere
> rs.Open sql,conn,adSomeotherConstantNameHere
Instead of all this!
> sql = "select " &_
> "value, " &_
> "datetime " &_
> "from " &_
> "v_AnalogHistory " &_
> "where " &_
> "tagname='sysiodrivercps' " &_
> "ORDER BY datetime desc"
> rs.CursorLocation = 3
> rs.Open sql,conn,3
>
> sql2 = "select " &_
> "value, " &_
> "datetime " &_
> "from " &_
> "v_AnalogHistory " &_
> "where " &_
> "tagname='SysMinutesRun' " &_
> "ORDER BY datetime desc"
> rs2.CursorLocation = 3
> rs2.Open sql2,conn,3
>
> sql3 = "select " &_
> "value, " &_
> "datetime " &_
> "from " &_
> "v_AnalogHistory " &_
> "where " &_
> "tagname='SysCritErrCnt' " &_
> "ORDER BY datetime desc"
> rs3.CursorLocation = 3
> rs3.Open sql3,conn,3
>
> sql5 = "select " &_
> "value, " &_
> "datetime " &_
> "from " &_
> "v_AnalogHistory " &_
> "where " &_
> "tagname='SysActiveUsers' " &_
> "ORDER BY datetime desc"
> rs5.CursorLocation = 3
> rs5.Open sql5,conn,3
3) Try not to return irrelevant data. Since you are using
a client-side cursor, all the results of the SQL are being
copied to the client side (which is still only the webserver
but is still an unneccessary overhead which may be significant
for large tables.) A glance at your code suggests you only
want the latest reading from each metric. If this is the
case, look at using GROUP BY and MAX in your query to make the
DB server do the work and give the webserver a nice lean dataset.
Cheers.
Message #4 by "Tomm Matthis" <matthis@b...> on Sat, 21 Jul 2001 11:32:31 -0400
|
|
You may want to use stored procs instead of straight sql code. It would be
much faster and you can "stack" the returning recordsets.
That way you have only one call to the database.
-- Tomm
> -----Original Message-----
> From: jeff tandy [mailto:jtandy@f...]
> Sent: Thursday, July 19, 2001 8:04 PM
> To: ASP Databases
> Subject: [asp_databases] Help with code returning records from SQL.
> Please include sample code
>
>
> I am very new to ASP(started 1 week ago) so please keep responses
> simple.
> The following code does pull the correct records from my database but it
> takes 30 seconds or more to display the results. The first part of the
> code seems to cause the delay. The second 2 parts if run independently
> are fast enough but the block for code for CP-H1 Server takes forever.
> How can I tune up this code with out getting to complicated with the
> code. Thanks in advance
>
> Jeff
>
>
Message #5 by "jeff tandy" <jtandy@f...> on Tue, 24 Jul 2001 00:29:26
|
|
> Two hints:
>
> 1) include the file adovbs.inc in your page and use symbolic constants
> like adOpenKeySet, adCmdText instead of "magic numbers". Anyone having
> to maintain or debug the code (including yourself!) will thank you for
it!
>
> 2) You have more an SQL programming problem than an ASP one. If you can
> return all your relevant data in a single query then you only need one
> recordset
> to access it. E.g. write the following
>
> > sql = "select " &_
> > "value, " &_
> > "datetime " &_
> > "from " &_
> > "v_AnalogHistory " &_
> > "where " &_
> > "tagname='sysiodrivercps' " &_
> > "or " &_
> > "tagname='sysiodrivercps' " &_
> > "or " &_
> > "tagname='SysMinutesRun' " &_
> > "or " &_
> > "tagname='SysCritErrCnt' " &_
> > "or " &_
> > "tagname='SysActiveUsers' " &_
> > "ORDER BY datetime desc"
> > rs.CursorLocation = adSomeConstantNameHere
> > rs.Open sql,conn,adSomeotherConstantNameHere
>
> Instead of all this!
>
> > sql = "select " &_
> > "value, " &_
> > "datetime " &_
> > "from " &_
> > "v_AnalogHistory " &_
> > "where " &_
> > "tagname='sysiodrivercps' " &_
> > "ORDER BY datetime desc"
> > rs.CursorLocation = 3
> > rs.Open sql,conn,3
> >
> > sql2 = "select " &_
> > "value, " &_
> > "datetime " &_
> > "from " &_
> > "v_AnalogHistory " &_
> > "where " &_
> > "tagname='SysMinutesRun' " &_
> > "ORDER BY datetime desc"
> > rs2.CursorLocation = 3
> > rs2.Open sql2,conn,3
> >
> > sql3 = "select " &_
> > "value, " &_
> > "datetime " &_
> > "from " &_
> > "v_AnalogHistory " &_
> > "where " &_
> > "tagname='SysCritErrCnt' " &_
> > "ORDER BY datetime desc"
> > rs3.CursorLocation = 3
> > rs3.Open sql3,conn,3
> >
> > sql5 = "select " &_
> > "value, " &_
> > "datetime " &_
> > "from " &_
> > "v_AnalogHistory " &_
> > "where " &_
> > "tagname='SysActiveUsers' " &_
> > "ORDER BY datetime desc"
> > rs5.CursorLocation = 3
> > rs5.Open sql5,conn,3
>
>
> 3) Try not to return irrelevant data. Since you are using
> a client-side cursor, all the results of the SQL are being
> copied to the client side (which is still only the webserver
> but is still an unneccessary overhead which may be significant
> for large tables.) A glance at your code suggests you only
> want the latest reading from each metric. If this is the
> case, look at using GROUP BY and MAX in your query to make the
> DB server do the work and give the webserver a nice lean dataset.
>
> Cheers.
Thanks for the help it works good until I try to layout the results in the
table. I have tried several solution and seem to be getting nowhere. Any
help would be appreciated.
Jeff
|
|
|
View Cart
