PDA

View Full Version : ASP to Oracle DB Connection causing IIS to hit 100% CPU usage

thiazi
08-24-2006, 05:28 PM
Hi all,

The code below is pulling information from a basic phone list database. It's restricted with javascript so you have to enter a few alpha characters, so that way you cannot pull the whole database at one time.

However, when several users start using it at once, the w3wp.exe process in IIS hits 95% and causes the CPU usage to hit 100%, making all sites on the server extremely slow.

Do you guys see anything wrong from a code standpoint?

<%@ Language=VBScript %>
<% Option Explicit %>
<% Server.ScriptTimeout = 90 %>
<!-- #include file="adovbs.inc" -->


<%

On Error Resume Next
If Err.Number <> 0 then
ReportError Err.Description
Error.Clear
End If

Sub ReportError
Response.Write("An error has occured, please notify Helpdesk. Error: " & Err.Description)
End Sub

Dim SecHeader
SecHeader = InStr(1, Request.ServerVariables("HTTP_REFERER"), "PhoneSearch.html",1)

If SecHeader = 0 or SecHeader = Null Then
Response.Write("Invalid Referrer.")
Response.Write("Invalid HTTP REFERER " & Request.ServerVariables("HTTP_REFERER"))
Response.End()
End If

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=dsbdbname;UID=usergoeshere;PWD=passgoeshere"
objConn.Open

Dim iLoca
Dim iFname
Dim iLname
Dim iDept
Dim iZone_Cube
Dim iext
iLoca = Trim(Replace(UCASE(Request("LOCA")),"'","''"))
iFName = Trim(Replace(UCASE(Request("FNAME")),"'","''"))
iLName = Trim(Replace(UCASE(Request("LNAME")),"'","''"))
iDept = Trim(Replace(UCASE(Request("DEPT")),"'","''"))
iZone_Cube = Trim(Replace(UCASE(Request("ZONE_CUBE")),"'","''"))
iext = Trim(Replace(UCASE(Request("EXT")),"'","''"))

Dim strSQL
strSQL = "SELECT * FROM AIST.V_CONTACT_DETAILS WHERE (1=1)"
If iLoca <> "" Then
strSQL = strSQL & " AND (UPPER(LOCA) LIKE '%" & iLoca & "%')"
End If
If iFname <> "" Then
strSQL = strSQL & " AND (UPPER(FNAME) LIKE '%" & iFname & "%')"
End If
If iLname <> "" Then
strSQL = strSQL & " AND (UPPER(LNAME) LIKE '%" & iLname & "%')"
End If
If iDept <> "" Then
strSQL = strSQL & " AND (UPPER(DEPT) LIKE '%" & iDept & "%')"
End If
If iZone_Cube <> "" Then
strSQL = strSQL & " AND (UPPER(ZONE_CUBE) LIKE '%" & iZone_Cube & "%')"
End If
If iext <> "" Then
strSQL = strSQL & " AND (EXT LIKE '%" & iext & "%')"
End If

strSQL = strSQL & " ORDER BY LNAME"

Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSql, objConn
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Phone List Search Results</title>
<% Response.CacheControl = "no-cache" %>
<% Response.AddHeader "Pragma", "no-cache" %>
<% Response.Expires = -1 %>

<body link="#ffffff" vlink="#ffffff" alink="#ffffff" leftmargin="0" topmargin="0" rightmargin="0" bottommargin="0" >
<div align="left">


<table width="100%">
<tr>
<td width="100%" colspan="3" align="center">
<br>
</td>
</tr>
<tr>
<td width="1%" align="center" valign="top">&nbsp;

</td>
<td width="24%" align="left" valign="top">&nbsp;

</td>
Phone List Search Results
</table>
<table border="1" align="center" style="text-align: left">
<tr bgcolor="#cccccc">
<th>Prefix</th>
<th>Phone Number</th>
<th>Extension</th>
<th>Last/Surname</th>
<th>First</th>
<th>Department</th>
<th>Title</th>
<th>Location</th>
<th>Zone/Cube</th>
<th>Fax</th>
<th>Mobile</th>
<th>Pager</th>
<th nowrap="nowrap">E-Mail</th>
</tr>
<%
Do While Not objRS.EOF
Response.Write "<tr>"
Response.Write "<td nowrap='nowrap'>" & objRS("PREFIX") & "&nbsp;</td>"
Response.Write "<td nowrap='nowrap'>" & objRS("PHONE") & "&nbsp;</td>"
Response.Write "<td nowrap='nowrap'>" & objRS("EXT") & "&nbsp;</td>"
Response.Write "<td nowrap='nowrap'>" & objRS("LNAME") & "&nbsp;</td>"
Response.Write "<td nowrap='nowrap'>" & objRS("FNAME") & "&nbsp;</td>"
Response.Write "<td>" & objRS("DEPT") & "&nbsp;</td>"
Response.Write "<td nowrap='nowrap'>" & objRS("TITLE") & "&nbsp;</td>"
Response.Write "<td width='100'>" & objRS("LOCA") & "&nbsp;</td>"
Response.Write "<td nowrap='nowrap'>" & objRS("ZONE_CUBE") & "&nbsp;</td>"
Response.Write "<td nowrap='nowrap'>" & objRS("FAX") & "&nbsp;</td>"
Response.Write "<td nowrap='nowrap'>" & objRS("MOBILE") & "&nbsp;</td>"
Response.Write "<td nowrap='nowrap'>" & objRS("PAGER") & "&nbsp;</td>"
Response.Write "<td nowrap='nowrap'><a id=""colorhyperlink"" href=mailto:" & objRS("email") & ">" & objRS("email") & "</a>&nbsp;</td>"
Response.Write "</tr>"
objRS.MoveNext
Loop
%>
</table>
<%

Response.write "<div style=""display: none"">Query string used for this output: " & strSql & "</div>"

objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing
%>
</td></tr>
</table>
</body>
</html>
thiazi
08-25-2006, 04:28 AM
I've done a few Google searches on w3wp.exe, but can't find anything that nails down specific connections into an Oracle database. Anyone have any ideas?
Spudhead
08-25-2006, 02:24 PM
Do you guys see anything wrong from a code standpoint?


Nothing particularly. You could seriously improve performance by using getRows() instead of looping through the recordset, and I don't see why you have to open the connection that early - why not do it AFTER all that string concatenation, just before you open the recordset? If you must loop through the recordset, then objRS.Fields("MOBILE") will make it do much less work to get your data for you. Also, SELECT * is never a good idea - give it a list of field names. Finally, I'm not familiar with connections to Oracle databases but have you tried a DSN-less connection?