Optimizing blob handling with SQLQuery

Recommended usage

When using IMAGE or TEXT fields in SQL server you should always use a master-detail relationship with two TSQLQuery's so that only blobs for one row are fetched at any time. Make sure you omit the blob field names from the SELECT list (do not use SELECT * if there are blobfields in the table) and create a second TSQLQuery that fetches the blobs. Add a where-clause to this query that uniquely identifies the table row and connect the query with the Datasource property to the master dataset's TDatasouce.

SQLQuery1.SQL.Text := 'select key1, key2, data1,data2,data3 from tablename';
SQLQuery2.SQL.Text := 'select key1,key2,blob1,blob2 from tablename where key1=:key1 and key2=:key2';

SQLQuery2.Datasource := Datasource1; // connected to SQLQuery1
SQLQuery1.Open;
SQLQuery2.Open;

Note: by including the key fields in the blobquery you can update the blobfields with TSQLUpdate or TSQLQuery.UpdateParams.

Background

When issuing a select statement that returns blob fields, SQLQuery will only retrieve the TextPointer from SQL Server upon retrieving the rows. The actual blob data is only fetched when necessary, i.e. when using the field data. DBLIB is very slow when fetching the TextPointer so to improve fetch performance you should always retrieve as few blob fields as possibly.

When inserting or modifying blobs, SQLQuery has to cache the modified blob locally as the text pointer will be invalid after the update. By using a master-detail relationship as described above the blob result set will always contain only one row and thus the memory strain will be reduced (on every reposition of the master dataset the detail dataset will be closed and reopened).

Home | News | Products | Download | Support
(C) Component Store Ltd. 2000