In the email part of my app I store the encoded email. On display I need to check a couple of headers. Most of the time assigning the CLOB field to a stringlist and then checking the rows is fine. However, occasionally, there are emails with large attachments and these take a long time to process (eg a 14Mb email is taking c15 secs). Most of the time is taken just in reading the field. My routine only bothers to check the first 100 rows to make sure I don't waste time checking through a large email.

What I need is a FAST way to get say the first 3000 characters of a CLOB field.

I've tried both .AsString, .Value and TEDBBlobStream and

SELECT substr(_encoded,1,3000) from emails where _MsgNo = 38765

Interestingly EDBManager reports .015 secs but I have to count to 11 before the result set is shown.

Suggestions?

Roy Lambert

Its down to BlobBlockSize

I tested out the time to do this

strm:=TMemoryStream.Create;
EMails_Encoded.SaveToStream( strm);
strm.Free;

for various BlobBlockSizes


512 - c12 secs - 541Mb
1024 - c5sec - 544Mb
2048 - c4secs - 559Mb
4096 - c1.5secs - 594Mb
8172 - c1sec - 671Mb
16344 - c0.5sec - 841Mb

So in this case it looks like 4096 is the way to go.

Roy Lambert

Roy,

<< Its down to BlobBlockSize >>

Yep, that's usually the culprit.

--
Tim Young
Elevate Software
www.elevatesoft.com