Twitter: raymondcamden


Address: Lafayette, LA, USA

What is the impact of enabling CLOB/BLOB for a ColdFusion DSN?

07-12-2013 5,142 views ColdFusion 5 Comments

Yesterday Adam R sent me an interesting question:

I'm hitting a wall in my google-fu regarding blob/clob settings in CF datasources. Is there any reason you know of why we shouldn't be enabling them (not suitable for production, performance, etc.?)? The byte buffer setting is just too arbitrary for our needs so it seems like we should just be clicking those check boxes. A lot of CF tuning information says disable them if you don't need them but none say why.

This was a great question, but unfortunately, I had absolutely no idea what the answer was. Luckily, I've got smart friends, so I turned to them. First up with an answer was Dave Watts:

Historically, there's been overhead required by the database connection to handle LOB fields, and if you're not using those fields for a given query you'd still be paying that overhead. I don't know if that's still actually true for modern JDBC drivers, but I wouldn't be at all surprised if it were. Back in the CF 5 days, Allaire Consulting specifically recommended at one point to create one datasource without LOB support enabled, and use that for queries that don't need to touch those fields.

LOB fields aren't stored in the normal database storage mechanism. Instead, the database table stores a pointer to another location, which is where the LOB is actually stored.

Seems logical to me. Then Charlie Arehart chimed in:

My recollection was that it caused a different kind of call from CF to the server (via JDBC), but I have not explored it.

One could determine it themselves any number of ways. For instance, if you enable the logging option (in the advanced settings of a DSN) that will createquite verbose logging of each query (like hundreds of lines per query, so don't leave it on in production).

Or, from the DB perspective, one could use a tool that tracks the details of the query as received. For instance, in SQL Server, one can use SQL Profiler to see the details of the communications.

It would be good to hear a more authoritative conclusion to why some have learned that turning it off can help.

I suspect it could be one of those things where there's not a definitive answer that always applies. For instance, I'm sure many here have heard some say that turning OFF the maintain connections can help in some situations. Yet that flies in the face of the notion that "creating connections are expensive, and each request (or query) creating a connection would add up fast". Still, some swear that using the connection pool was worse than not doing it.

Personally, I would much prefer that such recommendations come with that more detailed explanation of how they really "help". I see way too many such "tips" that "work" for one person and so get spread as "the answer" for everyone, when in fact it makes things worse for some people, all because no one really took the time to really understand/document/pass on why it was "better".

Of course, to offer that info requires writing, and reading, and few people seem to have patience for that. I suspect only very few here have even read to this final sentence. :-)

Wise points as always from Mr. Arehart. (I call him that since he is way older than I am. Honest.)

Anyway, I hope this helps others, and of course, if folks disagree or have something to add, please do so!

5 Comments

  • Roland Collins #
    Commented on 07-12-2013 at 2:27 PM
    Just out of curiosity, I did the SQL Server Profiler experiment to see what the differences would be from the database's perspective.

    There is only one minor difference on SQL Server, with BLOB/CLOB support. When they are DISABLED, the server receives the following additional command that it does not receive when the setting is enabled.

    SET TEXTSIZE 64000

    (64000 or whatever your text size is set to on the datasource)

    TEXTSIZE has a direct effect on the amount of data that you can retrieve from binary fields and varchar fields. http://msdn.microsoft.com/en-us/library/ms186238.a...

    So the setting does have a minor impact if you have lots of columns that contain over 64000 bytes of data and are constantly selecting them. But that doesn't really seem like a realistic scenario for most applications.
  • Misty #
    Commented on 07-12-2013 at 2:30 PM
    Hi ray, This is what CF Docs Say:

    CLOB

    Select to return the entire contents of any CLOB/Text columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting.

    BLOB

    Select to return the entire contents of any BLOB/ Image columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting.

    But i rarely enabled these in my Projects and did not seems to find any relevant answer for this, Above explanations by Charlie and Dave makes sense, but still not fully clear

    Thanks
  • doug #
    Commented on 07-15-2013 at 7:05 AM
    Hilarious. Seems like ever time I take a dive into something new you go off an make a blog post about it. I was just wondering the same thing myself just a month ago, and I'm glad Adam R. thought to ask you about it.

    So far in the month or so since I switched on the BLOB support I've noticed no hit to performance even though I'm transferring some large image files from the DB now. My biggest fear was that it was some kind of security hole if I unlocked it, but just as with Adam R., I couldn't find anything warning about it with my Google-fu.
  • Ben #
    Commented on 05-22-2014 at 12:16 PM
    Well, in my 10 years of modest CF development this is the first time I've run into this!
    Thanks! Would be interested in expanded discussion on the "Long Text Buffer" setting as it's related to this issue.
  • Ben #
    Commented on 05-22-2014 at 12:18 PM
    Ah, sorry, missed Misty's comment above. In any case thanks again folks!

Post Reply

Please refrain from posting large blocks of code as a comment. Use Pastebin or Gists instead. Text wrapped in asterisks (*) will be bold and text wrapped in underscores (_) will be italicized.

Leave this field empty