Post reply

BLOB’s, when things will start to break

  • December 30, 2002 at 12:55 pm

    #445275

    Test both ways if you can and let us know.

    I think your approach should also depend on whether you are going to INSERT this data into DB and than SELECT only or you are planning on BLOB UPDATE operations. I would be very causious with UPDATE's.

  • January 2, 2003 at 7:57 am

    #445434

    Transaction logging of BLOBs was a problem for us, especially under SQL 6.5. On a change to the supporting database row, 6.5 appeared to log the entire BLOB (as well as the change to the row) even if the BLOB itself did not change. At one point, a problem datatabase had twice as much log space allocated to it as it had data space because of this SQL Server behavior.

    Even after upgrading to SQL 7.0 and 2000, the databases (some of them are now 35 to 70 GB in size) containing tables with large numbers of BLOBs tend to have huge logs (frequently 10 GB).

    Jon

  • January 2, 2003 at 12:32 pm

    #445464

    The issue mentioned by shew01 might be true. However, it should be relatively simple as a physical level optimization to partition the relevant table into two tables, one containing the non-BLOB data and another containing the BLOB data. This should eliminate the transaction logging problems mentioned as updates not involving the BLOB column are restricted to the primary "non-BLOB" table.

  • January 2, 2003 at 12:53 pm

    #445466

    >>However, it should be relatively simple as a physical level optimization to partition the relevant table into two tables, one containing the non-BLOB data and another containing the BLOB data.

    Actually, that is how we got around a good portion of the problem in SQL 6.5, but that workaround only works if the Developers don't mind making application changes.

    Jon

    Edited by - shew01 on 01/02/2003 12:57:26 PM

  • January 2, 2003 at 5:33 pm

    #445499

    That's the transaction overhead I was talking about. One of those "gotcha's" that you just hope your developer's know about up front, for sure. Filegroups are a more evolved way of partitioning the data nowdays, and though I am more used to the table partitioning as well, they don't require developer changes, just a maint window

  • January 13, 2003 at 3:00 pm

    #446232

    I just had an issue with blobs and replication. If writetext is used to update the blob then replication will not work and will not copy the blob. And this does not show up when you verify the subscription since blobs are not verified

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply