Optimise Classic ASP

Need to find bottleneck

[Krispy2]

I have a Classic ASP application which merges data from a SQL database with Templates - sort of like mail-merge.

It uses a Find & Replace approach to find the {MARKERS} in the template.

This has been working fine for years on a variety of sites.

But just now I have a particular page which is taking 60 or more seconds to render.

I'm sure its the complexity of the Templates that are being used (I've profiled the SQL queries and they are only responsible for 100-200ms, but are returning quite a lot of data - 100 rows or less, but some columns are quite "bulky").

So I think I need to look at discovering where in the ASP the bottleneck is.

(Sadly I can't easily cache the content is it is customised for each visitor)

Any ideas on how to profile which parts of the ASP code are causing the bottle-neck would be much appreciated.

[Krispy2]

P.S. This does not appear to be a client-side rendering issue; I have some "phone-home" JS in the page that logs when the client-side rendering is complete, and that logs only a second, or so, after the ASP said it was done.

[mrMister]

What are you using to "find & replace"? Are you using regular Expressions? Why don't you paste some of your code.

[Krispy2]

Generally I use RegEx for the Replace for "MailMerge", but in this particular case I am using VBScript REPLACE()

Where I use RegEx to find all MailMerge Tags (I use curly brackets, as in "{MyTag}") the code looks like this:

strTokenSearch = "(\{)([^\};:]+)(\})" 
Set objRegEx = New RegExp 
objRegEx.Global = True 
objRegEx.Pattern = strTokenSearch 
set colMatch = objRegEx.execute(strTemplate) 

which gets an Array of matches, and then I substitute from the Database query ResultSet like this:

For each strMatch in colMatch 
strReplace = fnValueGet(strMatch) ' Function Gets from "ResultSet" 
strSrc = objRegEx.replace(strSrc, strReplace) 
Next 
Set objRegEx = nothing 

However, the MailMerge stuff for the page which I'm having trouble with is working on a different basis:

Get ResultSet from database
For Each Row:
For Each Column:
Perform any specified formatting/manipulation "Hints" to the Column Value
Apply "Output method"

There are a number of different Output Methods, one of which is a MailMerge Template (i.e. the one used in this scenario), and that uses VBScript REPLACE() to replace the {MyColumnName} tag in the Template with the Column Value.

So each Find & Replace is done column-by-column, rather than more en-masse as with the RegEx code.

and the code for that is:

strMergeOutput = replace(strMergeOutput, "{"& strColName & "}", strColValue, 1, 9999, VBTextCompare) 

and I suspect that this is where the problem is!

In case relevant:

This code has grown/evolved over the years :( so the "chunk" that does the processing of a ResultSet and massages the data and does the Replace is 73K (that's the raw code, no comments/whitespace), so it is quite complicated/sophisticated.

I can re-hack it how I see fit, but I'd prefer to start out down the most profitable route.

I have a couple of thoughts:

1. Make sure that all {MyColumnTags} are correct-case, and use a Case-SENSITIVE replace

2. I could create a new ASP file that ONLY supports this MailMerge-to-template method, and was built to be lean-and-mean.

3. I could split the template into an HTML-snippet + Column definition array (and Cache it in that format).

Example:

"<tr><td>" {MyColumn1}
"</td><td>" {MyColumn2}
"</td></tr>" NULL

This would mean that I could walk this array, grab the matching Column from the ResultSet and concatenate the output. (Using JOIN() may be the fastest way of achieving that?). That would remove the need for any poor-performant Find & Replace.

4. If I do (3) I would quite like to make a change to accommodate AJAX at the same time. So I would like to be able to partially render the output, and send an HTML snippet to the Client. So, for example, user is shown a Grid of 100 records. The click on EDIT on row 11. After submitting a Maintenance Form an AJAX request is made to re-render row 11. The ASP stuff walks the resultset rows until it gets to the one matching Row 11, Renders that, and then sends that Snippet to the user. (And some Client-side-stuff does an InnerHTML or somesuch to refresh the page). [And maybe, even, the SQL Query could be altered to ONLY query for "Row 11"]

Thanks for your help, I hope this is not information-overload!

[Krispy2]

P.S. I stuck some Debug code in. There are 101 rows in the ResultSet and that particular REPLACE() is called 4,141 times in total :(

[Krispy2]

OK, possibly Panic Over ...

... the code was doing:

strTemplate = fnGetTemplate() 
strMergeOutput = "" 
For Each Row: 
strMergeOutput = strMergeOutput + strTemplate 
... 
For each Column: 
strMergeOutput = replace(strMergeOutput, "{"& strColName & "}", strColValue, 1, 9999, VBTextCompare) 

I have now changed:

strMergeOutput = strMergeOutput + strTemplate 

to become:

strMergeOutputTotal = strMergeOutputTotal + strMergeOutput 
strMergeOutput = strTemplate 

to prevent strMergeOutput becoming a bigger & bigger target for the REPLACE as each row is processed. This was obviously exponentially bad as the number of rows in the resulset got bigger.

Process time has dropped from 16.6 seconds to 2.2.

I would welcome anyone's comments on the other Proposed Improvements listed above.

Thanks.

[mattur]

Hi Krispy2,

You can use JS asp code to compute time in milliseconds, which can be useful to do rough 'n ready profiling of your code to identify bottlenecks. You can use an include file (see below), and then call DisplayTimeElapsed after each major subroutine to identify how long each bit takes. You can remove the timer code prior to going live.

<%
'-----------------------------------------------------------------------------
' Name: timer.asp
' Purpose: include this file at start of page to start timer, 
' then call DisplayTimeElapsed to display processing time 
' at relevant points in your code
'-----------------------------------------------------------------------------
Dim dtmStart
dtmStart = getTimeInMilliseconds()
'-----------------------------------------------------------------------------
' display time elasped since dtmStart
'-----------------------------------------------------------------------------
Sub DisplayTimeElapsed()
Dim dtmEnd
dtmEnd = getTimeInMilliseconds()
Response.write("<p><i>Processing Time: " & dtmEnd - dtmStart & "ms</i></p>")
End Sub
'-----------------------------------------------------------------------------
' use server side javascript to get time in milliseconds
'-----------------------------------------------------------------------------
%>
<script language="JScript" runat="Server">
function getTimeInMilliseconds() {
var d = new Date();
return d.getTime()
}
</script>

[Krispy2]

That's very useful. Thanks mattur

[bmcgee]

Why use JS when you can use the ASP function Timer()

[mattur]

Thanks for the info, bmcgee. Timer() was added to VBScript in version 5. The code I posted is just something I've used since the early days when JScript was the only way to do this - it's quite old :)

[Krispy2]

I thought that Timer() only had one-second accuracy, but reading the DOCs I'm wrong - or it has changed since I first read them many years ago :(

"The Timer function returns the number of seconds that have passed since midnight (12:00:00 AM).

Timer keeps track of the seconds to at least seven decimal places of accuracy"

I reckon "seven decimal places" shold do the trick for profiling :)

Thanks