[thelist] Quick combine question

[Chris Anderson]

> Hello all...
> Can anyone point me in the right direction on combining all this code
> so it
> reduces server stress by opening a single connection?
> 
> Thanks much!!!
> 

1. Do not use "SELECT *" - only select the fields you want to actually
bring back - even if you are using them all now, someone later may
change the schema to add a 4000 character string field.

2. use something like "SELECT keyword, target FROM keywords WHERE
active=yes" then in your while loop check the value of target before
adding it to the right keyword list

3. If you have a *lot* of keywords, I very much doubt the problem is
with the database access (although the above will help a little)
If you have a lot of keywords (or they are big) the problem may be due
to your constant reallocation of memory during the string concatenation.

Eg This code

	keywords = keywords & newKeyword

allocates a contiguous block of memory big enough to house the contents
of both "keywords" and "newkeyword", copies "keywords" to it, then
copies "newKeyword" to it, then points the "keywords" variable to the
new block of memory.
Doing that a few hundred times results in lots of page faults, etc and
lots of memory organisation when the strings get large.

Instead, you should try using the Mid statement (note NOT the Mid
function - they are different) and pre-allocate memory
Eg

// Pre-allocate 1kb
keywordBuffer = Space(1024)
bufferPos = 0

While Not RS.Eof
	newKeyword = RS("keyword") & ", "
	// If new keyword will overflow the buffer, increase the buffer
size
	If(bufferPos + Len(newKeyword) > Len(keywordBuffer)
		keywordBuffer = keywordBuffer + Space(1024)
	End If
	// Insert the keyword in the right place within the buffer
	// This does not allocate new memory, it copies data into
existing memory
	Mid(keywordBuffer, bufferPos, Len(newKeyword)) = newKeyword    

	// Move the insertion point
	bufferPos = bufferPos + Len(newKeyword)

	RS.Movenext
wend
//Finally trim off the last of the buffer
keywordBuffer = Left(keywordBuffer, bufferPos + 1)


(NOTE: the above was written in my email client, so please ignore syntax
and "out by 1" errors - the comments should give you the gist though)


The code still has code similar to keywords = keywords & newKeyword in
there - but it runs it far fewer times (and feel free to change the
1024, etc to optimise it to your requirements - you might even want to
consider growing the buffer by doubling the size (this will reduce the
number of allocations as the size increases - more memory used, but far
fewer expensive allocations, and so quicker)

For those .NET users - the above is how the StringBuilder works, but is
a technique I used for years in my VB6 years :-)

Chris