Re: Do You Use blobspaces?

natebsi@gmail.com wrote: >> It sounds like that you have a very large column in a table and you want to store it as a blob in an effort to shrink the table size. > > Yes, that part of it. But according to the doc, there are benefits to > storing a text/byte column in a blobspace as opposed to a normal > dbspace such as not having the data pass through the logical logs or > shared memory. This would seem to suggest that that blobspaces, from a > performance standpoint, are always better. Is that untrue? > >> Is this column ever used in a query's condition statement? > > No. What is the page size of the blobspace ? Cheers Paul

On Jun 14, 12:55 pm, "Paul Watson (Oninit)" <p...@oninit.com> wrote: > nate...@gmail.com wrote: > >> It sounds like that you have a very large column in a table and you want to store it as a blob in an effort to shrink the table size. > > > Yes, that part of it. But according to the doc, there are benefits to > > storing a text/byte column in a blobspace as opposed to a normal > > dbspace such as not having the data pass through the logical logs or > > shared memory. This would seem to suggest that that blobspaces, from a > > performance standpoint, are always better. Is that untrue? Blobspaces are not always better and certainly not always noticeably better. We have certain fields that we store as blobs because users could enter large amounts of formatted text. In actual fact, most users enter no more than a sentence. Since we are well below the 4KB minimum page size, and since you can only store one blob per blobpage, it is far more efficient for us to store in row. This is based on our testing, your mileage may differ, standard disclaimers, etc... If you find yourself in a similar situation, you might consider using lvarchars, as we should have done. Save the large-object types for true large objects. Sincerely, Christopher Coleman

Thanks for the responses! In our case, we have quite a few tables with text/byte columns, but there is only one where we have begun putting it in a blobspace. The column question usually fairly large, containing jpg and pdf files, around a minimum of 30KB or so and as large as a couple megs. It is not uncommon for this table to be larger (from a size perspective only, not number of rows) then the rest of the database. Does this sounds like a reasonable candidate? It for this size of a byte column, where I made the assumption that a blobspace would be a no-brainer. Hope I'm not wrong! :) On Jun 16, 1:35 pm, Christopher <christoph...@gmail.com> wrote: > On Jun 14, 12:55 pm, "Paul Watson (Oninit)" <p...@oninit.com> wrote: > > > nate...@gmail.com wrote: > > >> It sounds like that you have a very large column in a table and you want to store it as a blob in an effort to shrink the table size. > > > > Yes, that part of it. But according to the doc, there are benefits to > > > storing a text/byte column in a blobspace as opposed to a normal > > > dbspace such as not having the data pass through the logical logs or > > > shared memory. This would seem to suggest that that blobspaces, from a > > > performance standpoint, are always better. Is that untrue? > > Blobspaces are not always better and certainly not always noticeably > better.  We have certain fields that we store as blobs because users > could enter large amounts of formatted text.  In actual fact, most > users enter no more than a sentence.  Since we are well below the 4KB > minimum page size, and since you can only store one blob per blobpage, > it is far more efficient for us to store in row. > > This is based on our testing, your mileage may differ, standard > disclaimers, etc... > > If you find yourself in a similar situation, you might consider using > lvarchars, as we should have done.  Save the large-object types for > true large objects. > > Sincerely, > Christopher Coleman

We have experimented with various sizes, but lately we have been making them the default size simply as it makes the onstat -d update much easier to look at. Not a real great reason, I know. > > What is the page size of the blobspace ? > > Cheers > Paul