Performance hit with CLOB/TEXT?

Question

We have a database with a generous use of the CLOB/TEXT datatype all over the schema. After running lots of queries yesterday on multiple tables having a CLOB type, I came to the conclusion that as soon as there is a CLOB in the SELECT clause (even if the value if NULL/empty), the query takes a huge performance hit.

We have a client-server application (not a web-based) one that is deployed at the customer's site. Also, we support several databases at the backend, so it is not feasible for us to do database-specific optimizations in the code.

Going away from the CLOBs is not really an option.

I understand most of the optimization needed to be done for a CLOB/TEXT datatype would be on the database server. What kind of recommendations should I pass along to the customers for this?

What could I do as a developer in the code to help speed up the queries?

Answer 1

The extent of the performance issue will be database specific. But, in general you would expect most implementations to store the CLOB data in a physically separate place on the disk from the fixed-width fields.

CLOBs should be used sparingly, only as necessary, for just this reason. If you do have to use one, make sure you don't need to bring back a lot of them at the same time. They are best when you only need to retrieve one at a time, such as when you view the full record in your client.

Answer 2

A proper answer will need more information. In particular, could you post examples of "slow" queries, along with the structure of the tables they access?

That said, in general CLOBs should not automatically cause performance problems.

Queries using conditions on them will be slow, as CLOBs typically cannot be indexed. Retrieving them will have an impact if the total amount of data to be transferred becomes large. But apart from these points, CLOBs should not significantly impact performance (i.e. just having a CLOB in a table should not hurt).

These are just general observations. Details will of course depend on the DBMS you use, and its configuration.