Question Something's causing a server lockup on my page...

[bazzanoid]

Morning all,

I've inherited a very shaky codeset from my predecessor and i'm struggling to put it all right... making good progress with bringing it up to scratch, but.....

For some time now i've been trying to resolve an issue where our SQL server suddenly eats up the CPU usage and has to be forcibly reset.

I've now successfully tracked the issue down to one particular page. If the recordsets needed are short then it doesn't have an issue, but if there's a lot (and by a lot i mean 50+, so not a stretch by any means!), it doesn't even load the page and just hangs up the server instead (mysqld.exe uses 99% of the CPU time and doesn't let go even after closing the browser window!).....

If anyone has any thoughts i'd appreciate it, it's somewhat annoying to say the least!

coding involved:

<%
Dim gobjConn, gobjRS 
Dim hobjRS 
Dim Code
					
gobjConn = Server.CreateObject("ADODB.Connection") 
			
gobjConn.Open(sConnection) 
			
gobjRS = gobjConn.Execute("SELECT * FROM oe WHERE Manufacturer = '" & trim(Request.querystring("Man")) & "' ORDER BY OE ASC")
			
if gobjrs.eof then 
%>
<div align="center">No Parts Found for this Manufacturer entered.</div>
<%
else
			
While Not gobjRS.EOF

%>
				
<table align="center">
<tr>
<td align="left" width="100">
				
<%
				
response.write("<a href='searchresultsbymanufacturer.aspx?txtManCode=" & trim(gobjRS.Fields("oe").Value) & "'>")
				
hobjRS = gobjConn.Execute("SELECT DISTINCT * FROM parts WHERE FacetPartNo LIKE '%" & gobjRS.fields("ProductCode").value & "%'")
 response.write("<b>" & gobjRS.Fields("oe").Value & "<b>") %>
</a>
</td>
<td width="125" align="center"><% 
				  
 If hobjRS.EOF OR hobjRS.BOF Then 
%>No data available </td><td width="125"> </td><%
 Else
response.write(hobjRS.Fields("Type").Value) %></td>
<td width="125" align="center"><% response.write("<a href='searchresultsbymanufacturer.aspx?txtManCode=" & trim(gobjRS.Fields("oe").Value) & "'>")%>More Info & Pricing</a></td>
<%
			
 End If
 %>

Thanks!

Barry

 

[InertiaM]

Your query suggests that your table name is OE, but you also have a field name of OE. Is this correct?

 

[bazzanoid]

Yes that's correct (the whole database doesn't conform to any sort of standardisation)...

 

[InertiaM]

Well, that shows that the database has been badly designed - which may also mean that relevant columns may not have indexes on them. This may be slowing the search down.

In addition, selection statements like "SELECT DISTINCT * " are horrible, depending on how many fields are in the table, and what is contained in them. For example, images can be stored in fields - which even though they would be returned by you query, would be completely irrelevant for your page. To explain, it appears from your page that Type is the only field that is used from the parts table, so try changing the line :-

hobjRS = gobjConn.Execute("SELECT DISTINCT * FROM parts WHERE FacetPartNo LIKE '%" & gobjRS.fields("ProductCode").value & "%'")

to

hobjRS = gobjConn.Execute("SELECT DISTINCT parts.Type FROM parts WHERE parts.FacetPartNo LIKE '%" & gobjRS.fields("ProductCode").value & "%'")

 

[bazzanoid]

changed, makes no odds at all... just checked the indexing, practically all fields are marked as standard indexed.

A lot of the trouble is that parts.facetpartno will have several records with the same entry, so it's doubling up on a lot of data - beyond the DISTINCT syntax is there any way to 'trim out the fat' so to speak when it's selecting the records.......?

 

[InertiaM]

Just realised that your page code is only processing 1 record - so only return one record.

SELECT TOP 1 parts.Type FROM parts WHERE parts.FacetPartNo LIKE '%" & gobjRS.fields("ProductCode").value & "%' GROUP BY parts.Type

Does it need to do 'like', or can it be 'equals'?

SELECT TOP 1 parts.Type FROM parts WHERE parts.FacetPartNo = '" & gobjRS.fields("ProductCode").value & "' GROUP BY parts.Type

Later on, I'd like you to consider using parameterised queries (which are much safer), but that's a topic for later ...

 

[bazzanoid]

Shouldn't be only processing one record, it's supposed to process them all and return only the unique records on FacetPartNo ..........

*checks code*

Nothing in there like SELECT TOP 1 ??

Or do you mean i should change it to TOP 1?

 

[bazzanoid]

Changed LIKE to = and it now loads the page happily without locking up the server... however the w3wp.exe processes aren't being cleared, so it sits at around 43% unless i terminate it manually...

Progress.....

 

[InertiaM]

Sorry, I didnt explain it properly. My point was that gobjRS is looping via the WHILE NOT gobjRS.EOF, but the code around hobjRS only utilises one record ie it doesnt loop. If it doesnt loop, there is no need for the query to return all the records when the page only processes one of them. In other words, yes you should try changing it to SELECT TOP 1.

As a side note, is that all the code from the page? It's just that the gobjRS.MoveNext is missing. It may just be that you mis-copied it onto here, but please check.

 

[bazzanoid]

Yes - gobjRS.movenext is a little further down. And yep, hobJRS is only used once on each pass of gobjRS, so TOP 1 would be more efficient.

Thanks!

Now trying to resolve a problem with session variables reading as DBNull when they're not....... and then not being able to convert to Decimal! All good fun on a friday..... might just call it a day soon and start over monday!

At least the server problem is fixed, HUGE thanks for that it had been doing my head in... it's not locked up in over an hour now which is bliss.