>
> I have a distributed architecture where my server handles all
> interaction with the database server. Clients (or agents) post data
> (lets call it metrics) to the server, and the server then inserts this
> data into the database. There are about three related tables, one is
> attribute data and contains a long KEY and a blob VALUE. The
> attributes table uses a blob because the attributes can be
> unpredictable and are usually serialized objects. There can be many
> agents posting a lot of data so the inserts need to be fast.
>
> One thing I discovered, which is probably obvious, is that inserting
> with blobs can be slow, maybe particularly with JDBC. One has to
> create a blob handle, stream to it, and close it. Writing my attribute
> data to a VARCHAR OCTETS field seems to be much faster, however, the
> size of this data is not consistent and could be larger than a
> VARCHAR. It is more likely that the data will fit into a VARCHAR, but
> there are a small number of cases where it might not.
>
> I've come up with a solution that I wanted to get feedback on. I've
> added an extra VARCHAR AS OCTETS field to the attributes data. When
> inserting metrics, I get the size of the serialized attributes, if
> they are larger than 1024 I write them to the BLOB field, if not I
> write the bytes to the VARCHAR field. On reads, I just check the
> VARCHAR field, if it is NULL then I know the data is in the BLOB field.
>
> This seems to have increased INSERT speed quite a bit -- probably by
> 20% or more. Note that I never use this data in QUERIES (it just comes
> back in result sets based on other QUERIES).
>
> My question is (a) does this sound like a good solution for speeding
> up my inserts and (b) are there hidden gotchas in this solution that I
> am not considering?
>
> This table doesn't really have indices besides it's primary key so
> that approach really wont impact performance, and even if it did I
> don't think the insert performance change would be as drammatic as
> this solution.
>
> TIA,
>
> Robert

you seem to be thinking that 2048 is a varchar limit? - AFAIK, you could
make the Varchar field 32000 if you really want. I never do but I don;t
think there is such a small limit. And the first paragraph is true AFAIK -
what concerns you with this?
Alan