Alexandre Kozlov wrote:

>Absolutely. This is quite clear.
>Suppose:
>1) you just start database (so cache doesn't work yet);
>2) your database is very large mostly due to BLOB which occupy, say, 95%
>of space on disk;
>3) Simple "SELECT COUNT(*) FROM LargeTable" will probably may take hours (of
>course, after cache stabilized it will take just about 1 minute)
>
>

This just isn't the case. Blob pages and the tail pages of large
records are marked as "large" and are not reflected in the pointer
pages. Your "select count(*)" will hit almost the same number of data
pages with an megabyte blob per record as an empty blob per record. The
megabyte blob will have a very short record containing some header bits
and the page number of the blob pointer page for the level 2 blob.

>
>
>>Do you have any evidence of that? Other than a hypothesis
>>based on a (questionable) understanding of the implementation?
>>It's been some years, but when last I compared our blob
>>performance with other systems, it was pretty good.
>>
>>
>
>Ann, this is not only a theory. I had and have such a situation. And
>simulated separation of searching fields and BLOBs (by putting BLOBs into a
>different table and then backup and restore database on empty disk). The
>result: after starting the database, searching became almost 1000 times
>faster than with fragmentation. Of course, I am talking only about
>performance "just after the database starting" - when cache is still empty -
>but it can be too long depending of database size . And I believe that even
>if not to take into account slow work at the starting of database,
>possibility to keep differently searchable data and BLOBs will give you not
>only advantage in maintenance but some improvement in performance.
>
>
>

I think you need to get a better handle of what's going on before you
propose a change. I suspect that you're measuring something else,
perhaps the flushing of the newly created blobs back to disk.


[Non-text portions of this message have been removed]