Clob column data is fetched eagerly

Question

Hi,

I am trying to migrate my application from PostgreSQL to SQL Server 2014 Express edition. I have a table A which has a column foreign key referencing to table B. And table B has one of the columns as Java SQL type Clob which is mapped to varchar(max) in SQL Server. Other columns in B are primitive types. I have mapped the association to B such that B is loaded lazy. But when the database loads B, it loads all columns including the Clob column which is killing the performance of my application.

Is there a way that I can tell SQL Server to load all Clob/Blob columns to load lazy? I mean, at a column level can I mention to load only when it is accessed. I think the logic to load data could be driver specific, does SQL Server 2014 JDBC Driver 4.1 supports this property level lazy loading?

Please let me know, any help on this would be appreciated.

Santhosh

Answer 1

Do you mean in the application that you load whole columns but now the columns you need should be loaded? By the way, which application do you use and is it possible to write a query to load the required columns.

Answer 2

Riaon,

I am using Hibernate as ORM layer which loads one row as an object instance. This is for our enterprise application that we are developing. It is possible to write query and fetch only required columns, but we will have to write the piece of code outside Hibernate and set my model class properties explicitly. 

Santhosh

Answer 3

Greetings Kalyanv71,

you cant do this on SQL-Side.

The reason for this is, SQL just returns a resultset (or a few resultsets) that may include that field.
ORM's allow lazy-loading by marking specific fields.

Lazy Loading is nothing more then limiting the select and doing another (more specific) round trip to fetch the data you want.

So to implement your requirement;
Add an empty class with the properties.
Add a singleton that checks if the properties are set and fetch them if they're empty.

Sebastian

Answer 4

I am wondering why that column is mapped as varchar(max) and not varbinary(max)? What type of data do you held in that column?

Also, check this very interesting blog post by Paul White

http://sqlblog.com/blogs/paul_white/archive/2011/02/23/Advanced-TSQL-Tuning-Why-Internals-Knowledge-Matters.aspx

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 5

Sebastian,

We have implemented lazy-loading wherever possible and required. Lazy-loading as far as I understood, it is only limited to the associated relationships of a Table. Whereas, here in this case, my Clob data is a column within the table that I am loading in the first pass. If I have no other option but to externalize the Clob data into a separate table and associate with my parent table and load it lazy, I will do it. though that is my last option. I've just tried it and its working!

@Naomi,

The column is mapped to varchar(max) by Hibernate since I have used java.sql.Clob in my application. I think it would have mapped to varbinary(max) if I have used java.sql.Blob. The content that we are storing in the Clob column is huge XML content (>30MB) usually. Now, are there any advantages of using varbinary(max) instead of varchar(max) in this context apart from saving space? Please let me know for my education.

Thank you for your help!

Santhosh

Answer 6

If you only store XML data in that column (nothing else), then I think using XML type for the column may be the best choice. You would be able to use xml functions against it.

You can also keep storing it as varchar(max), in case of XML there is no point of using varbinary(max), I think.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 7

Thank you Naomi and Sebastian. I am externalizing my Clob column as a separate table and proceed. That way, it will work when I integrate my application with other databases as well.