Email Transcript
From: "James Barbetti" <james_barbetti@hotmail.com> Save Address
Reply-To: "aspxml" <aspxml@ls.asplists.com>
To: "aspxml" <aspxml@ls.asplists.com> Save Address
Subject: [aspxml] Re: Performance issues of BLL versus DAL
Date: Wed, 12 Jul 2000 13:59:48 PDT
Reply Reply All Forward Delete Previous Next Close
Hi Matthew
We are developing a 3 tier web application and we have come across a
problem with the performance of one of our business functions. The
function in question returns a hierarchical list of information of the
following form:
When we first designed (and built) the system we took the Business Logic
Layer route - we had multiple selects (to our SQL Server >database) - one
for all the items related to the order, one for all the orders related to a
customer and one for all the customers related to a billing run. This
meant that building the XML string seen above was a breeze. Loop over
customers, loop over orders and loop over items building as you go. However, it was
very slow. We suspected that this was because of the multiple selects.
Oh, they had a cost, but it probably wasn't too bad.
Let me guess, you built intermediate strings at each level and
then concatenated those at the next level up? If you're using
VB's standard string concatentation that will give you a
considerable performance gain in your string processing
(which is almost certainly where most of your time is going.
How long are the strings you are building? >50K?)
(A sorting analogy; if you are building intermediate strings
you are moving away from the T(N)=O(N*N) performance of a straight
insertion sort to the (T(N)=O(N*Log(N)) performance of a
straight merge sort - but you're still doing much worse than the
T(N)=O(N) performance that should be achievable).
So we moved everything into the Data Access Layer and just used one huge
select statement with multiple joins. We didn't want to rebuild the
presentation layer, so we put logic into the DAL to create the same XML
string as above (loop through rs until item id changes, then end item tag,
loop through rs until Order id changes, then end Order tag etc etc). The
desire to keep the same XML structure meant that we couldn't simply use
ado's recordset.save (adpersistxml) method because that resulted in a
"flat" XML structure. BUT, all the processing of the rs in the DAL now
meant that the function performed SLOWER! (Although we suspect that it
will perform better under stress due to the decreased database hits).
Don't bet on it. If you really want the answer to that question,
build a "null" test case that carries out all the same operations
except building the string. Then time that. I expect you'll find
that the string nonsense is 90% or more of your processing time. Even
if you halve the cost of the database access (and perhaps you did!),
if it cost you even 10% in string performance to do it, you've
actually lost ground.
Always remember when optimizing that you should attack the slowest
thing first. The most important step when optimizing is to get
performance figures that tell you where to focus your attention.
DO NOT RELY ON YOUR INTUITION. There's little to gain in speeding
up something that accounts for 1% of your elapsed time even by a
factor of 1000. The best you'll manage is a 1% performance improvement.
Some performance stats confirmed our suspicions that the performance
problem was due to the XML construction. A previous discussion on this
list had warned me about the performance impact of hundreds of & operators,
but I don't see that we have any choice in the matter.
You do.
Okay, I'll make it easy for you - I've been trying to encourage people
to think about their string handling. The point I've been *trying*
to get across is that you should think of strings in terms of
partially-used buffers (you have to do this - if you want string
concatenation to perform - only because... VB doesn't) (C does).
But I think I'm failing, so I'll cop out and give you a fishing
rod rather than teaching you to fish with your hands. Define a
clsQuickstring class. Paste this into it.
Make Value the default property.
x--- high performance string concatenation start ---x
Option Explicit
'
'Module: clsQuickstring.cls
'Project: StringExamples.vbp
'Author: James Barbetti, 13-Jul-2000
'Description: Class that can be used to avoid the performance problems
' associated with appending large numbers of short strings
' to a single output string.
'Note: Surprisingly, it's faster *NOT* to use API calls,
' in the IDE.
'
#Const UseEvilAPIHack = 0 'set to 1 to use MemCopy calls
#if 0 < UseEvilAPIHack Then
Private Declare Sub CopyMemoryPtrs _
Lib "kernel32" Alias "RtlMoveMemory" _
(ByVal Destination As Long, ByVal Source As Long _
, ByVal Length As Long)
#end If
Private strBuffer As String
Private lngLengthUsed As Long
Private lngLengthAllocated As Long
Public Sub Append(ByRef strAppendThis As String)
Dim lngNewLength As Long
Dim lngExtrabitLength As Long
lngExtrabitLength = Len(strAppendThis)
If lngExtrabitLength = 0 Then
Exit Sub
End If
lngNewLength = lngLengthUsed + lngExtrabitLength
If lngLengthAllocated < lngNewLength Then
strBuffer = strBuffer & Space(lngNewLength)
lngLengthAllocated = lngLengthAllocated + lngNewLength
'
'The point here is that we *deliberately* allocate more room
'than we need. Lots more. Ironically this keeps memory
'use DOWN (!!!).
'
End If
Mid(strBuffer, lngLengthUsed + 1, lngExtrabitLength) = strAppendThis
'
'The key point. We don't really append (which forces a
'a buffer reallocation every time). Instead, we copy into the
'unused portion of our existing buffer.
'
'This is actually faster than doing
'a memory copy via the RtlMoveMemory API.
'I tried that too. It was 20% slower!!! See the
'#iffed out code below:
#if False Then
CopyMemoryPtrs _
StrPtr(strBuffer) + lngLengthUsed + lngLengthUsed _
, StrPtr(strAppendThis) _
, lngExtrabitLength + lngExtrabitLength
#end If
lngLengthUsed = lngLengthUsed + lngExtrabitLength
End Sub
Public Property Get Length() As Long
Length = lngLengthUsed
End Property
Public Property Get Value() As String
Value = Left(strBuffer, lngLengthUsed)
End Property
Public Property Let Value(ByRef strNewValue As String)
strBuffer = strNewValue
lngLengthAllocated = Len(strBuffer)
lngLengthUsed = Len(strBuffer)
End Property
x--- *scalable* string concatenation finish ---x
x--- example use case and performance stats start ---x
Public Sub DemonstrateStringClass()
Dim varLen As Variant
Dim dteStartTime As Date
Dim lngCount As Long
Dim strSlowString As String
Dim qstrGoodString As New clsQuickString
Debug.Print " Null VB clsQuickString"
For Each varLen In Array(1, 10, 100, 1000, 10000, 100000)
Debug.Print strSpacePad(varLen, 8); " ";
dteStartTime = Time
strSlowString = ""
For lngCount = 0 To varLen - 1
strSlowString = Chr(65 + (lngCount Mod 26))
'Null case to give cost of non-concatenation
'processing
Next lngCount
Debug.Print Format(DifferenceInSeconds( _
dteStartTime, Time), "0000.000000"); " ";
dteStartTime = Time
strSlowString = ""
For lngCount = 0 To varLen - 1
strSlowString = strSlowString & Chr(65 + (lngCount Mod 26))
Next lngCount
Debug.Print Format(DifferenceInSeconds( _
dteStartTime, Time), "0000.000000"); " ";
dteStartTime = Time
qstrGoodString = ""
For lngCount = 0 To varLen - 1
qstrGoodString.Append Chr(65 + (lngCount Mod 26))
Next lngCount
Debug.Print Format(DifferenceInSeconds( _
dteStartTime, Time), "0000.000000")
Debug.Assert qstrGoodString = strSlowString
Next
End Sub
'
'The above code yields the following statistics in the IDE
'(on my clunky old PC)
'
Null VB clsQuickString
1 0000.000028 0000.000027 0000.000098
10 0000.000036 0000.000054 0000.000103
100 0000.000169 0000.000350 0000.000560
1000 0000.001578 0000.004963 0000.005075
10000 0000.015236 0000.446889 0000.050160
100000 0000.153570 0069.832850 0000.508414
'
'And as an EXE:
'
1 0000.000035 0000.000017 0000.000225
10 0000.000023 0000.000038 0000.000053
100 0000.000123 0000.000327 0000.000316
1000 0000.001130 0000.005045 0000.002463
10000 0000.011260 0000.444496 0000.025731
100000 0000.112852 0075.082661 0000.256766
(the blip for the 1-char null case is probably initialization
of the timing routines, the blip for the 1-char class case is
probably the object creation - since the loop I've got reuses
the existing object for the subsequent tests)
As you can see, the break-even point is at about 1K total string
length (using a class has a light overhead; it's the integer
arithmetic *in* the class that slows things down) for the IDE
and 100 bytes for the EXE, but the VB string concatenation hits
a brick wall somewhere between 10K and 100K string length.
Instead of the 100 times slower I would have
predicted for 100K versus 10K, it is more like 160 times slower.
Ouch! Note too that the class gives approximately linear response.
Here are the EXE times for null and clsQuickString out to another
two orders of magnitude (I didn't want to wait a DAY for the
vanilla VB concatenation to run so I didn't try it):
Null VB clsQuickString
1000000 0001.244772 Bloody Slow 0002.540733
10000000 0011.536153 Bloody Slow 0069.970361
On my PC, clsQuickString "hits a wall" somewhere between 1Mb and 10Mb.
The hit seems to be when it's reallocating its buffer length from 8Mb
to 16Mb. At that point it breaks down. As an experiment I've also
built a string class that works on a linked-list of 2Mb buffers, and
that gives linear performance (2.5 seconds per Mb) up to 60Mb,
and okay performance (3.0 seconds per additional Mb) up to 110Mb...
But unless you've got >4Mb in your output strings, you don't need it
(Plus, I haven't unit tested it to my satisfaction).
Matthew, please try using clsQuickString.Append in your DAL.
Hopefully you're writing <4Mb at a time.
...
But make sure you're not lobbing around several clsQuickString objects.
You should need only *one*, which you append to (sort of like the
way you'd append to a text file if you were writing one - that, by the
way, is another alternative... you could use a temporary file - while
VB's built-in file writing is disturbingly slow, at least its
performance doesn't degrade anywhere nearly as badly as the
string length increases - and "no more hard disk" takes much
longer strings than "no more physical memory" and
"no more page file").
And you should *read* the Value property only once (when the
*highest* level function is setting its return value). If you have
more than one, or you read its value along the way, you're reproducing
VB's mistakes but paying the integer arithmetic overhead on
top of them....
If we use rs.save we will need to rebuild the
presentation layer so that it expects a flat XML structure, but even then
we will need to do the loop type processing up there - we'd just be moving
the processing to another layer... So, I have the following questions:
1) I'm sure that the structure above is a very common one. How have
other people done it?
To be honest, when I've had to build recordset dumps (into HTML
rather than XML, but the principle is the same), I built them in
ASP via repeated Response.Write calls. It was a little faster
than an MTS component (maybe 35%), a lot easier to read, and
there was one less point of failure. On the other hand, since
Interdev sucks, it was harder to debug (in point of fact I used VB
to develop and then ported it to ASP for the performance gain),
I know, everybody says MTS components are faster but even with
heavily hacked string handling code in my VB components,
ASP blew them into the weeds below 1K and over about the "70K
total table length" mark. And for Response.Write from
within the components it beat them from byte 1 and never
stopped beating them. It was a nasty surprise. I thought
VB/MTS would be faster. There's an example of that
"DO NOT TRUST YOUR INTUITION" point I made earlier.
But then why ignore all the work that's gone into making
SQL Server perform well? Why not just have it all in the DAL with
one big join and let SQL Server deal with performance (even though in our
case it didn't).
Did you try using SHAPE?
Anyhow, hope the string class helps.
Seeya,
James