Sql-server – Updating a table which contains an Image datatype column

blobsql serversql server 2014update

I have a table which contains a few columns, one of which is an image datatype, this is used to store BLOB data, which could be anything from a jpg to a large PDF.

I have a requirement to update one of the other columns in the table, however the query is taking a ludicrously long time to complete – that's if it does complete at all.

The table is the following format:

ID (int), HexData (image), FileName(nvar), Filetype(nvar), folder(nvar), user(int)

The query used is simply –

update [database].[dbo].[myTable]
set user = 5
where id = 2

This is a test table on a test database, not the live system. There are only 5 rows, no indexes (including clustered), and no other tables in the database.

Best Answer

The table in question is a heap. This storage structure does not release pages made empty by delete operations, unless the delete occurs while an exclusive table lock is taken (and not always then).

From DELETE (Transact-SQL) in the documentation:

To delete rows in a heap and deallocate pages, use one of the following methods.
Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. This allows the pages to be deallocated.

The upshot is that your table likely contains a large number of empty pages allocated for rows the heap held in the past. You can check this using:

SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.index_type_desc,
    DDIPS.alloc_unit_type_desc,
    DDIPS.index_depth,
    DDIPS.index_level,
    DDIPS.page_count,
    DDIPS.record_count,
    DDIPS.ghost_record_count,
    DDIPS.version_ghost_record_count,
    DDIPS.forwarded_record_count
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.Heap', 'U'), -- Your table name here
        0,
        NULL,
        'DETAILED'
    ) AS DDIPS;

Without any indexes, the only way for the SQL Server to process the update query is to employ a full table scan. This will touch every data page allocated to the table, including the empty ones. If there are many pages that must be brought into memory from permanent storage, this can take a long time.

The solution is to rebuild the heap table using ALTER TABLE <table_name> REBUILD, or create a clustered index.

The image data type has been deprecated in favour of varbinary(max) for a very long time now. If you have any freedom to change this, you probably should. Even so, it is also possible that the existence of this LOB column is causing the slowness through a quite separate mechanism I describe in detail here on SQLblog.com:

Deletes that Split Pages and Forwarded Ghosts

From that article:

Summary

If you ever wonder why your deletes are so slow, it is worth checking to see if you are suffering from page splitting due to an enabled trigger and a table definition that allows for LOB allocations or ROW_OVERFLOW.

Any table with a LOB column (including the max data types) qualifies, as does one with even a surprisingly small number of variable-length columns, as shown in the examples in this post.

This is a great reason to avoid using 'max' or old-style LOB data types unnecessarily, and to be careful about the maximum length of 'normal' variable-length data types too.

Remember, it is the potential maximum row size that is important, not the actual row size.

Related Solutions

Export table without BLOB column

You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option.

Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps).

Call expdp as usual, adding:

REMAP_DATA=SCHEMA.TAB.BLOB_COLUM:SCHEMA.PACKAGE.YOUR_FUNCTION

Short example (schema: mat):

create or replace 
package remap as 
  function null_lob(col blob) return blob;
end remap;
/

create or replace
package body remap as
  function null_lob(col blob)
    return blob is
    eblob blob;
  begin
    return null;
  end null_lob;
end remap;
/

create table foo(a number, b blob);
-- insert a line with a 10M blob, commit

select a, length(b) from foo;

     A  LENGTH(B)
---------- ----------
     1   10485760

Plain export:

$ expdp mat/*** DIRECTORY=EXP_DIR TABLES=MAT.FOO 
...
Total estimation using BLOCKS method: 11.06 MB
...
. . exported "MAT"."FOO"                                 10.00 MB       1 rows
...

Dump file is ~11M.

With the data remap:

$ expdp mat/*** DIRECTORY=EXP_DIR TABLES=MAT.FOO \
                REMAP_DATA=MAT.FOO.B:MAT.remap.null_lob
...
Total estimation using BLOCKS method: 11.06 MB
...
. . exported "MAT"."FOO"                                 5.398 KB       1 rows
...

Dump file is ~100k, importing that dump does indeed import a null in the column.

Another option would be to use an external table to do the load/unload, with the ORACLE_DATAPUMP driver (I think this was introduced in 10g, see external table concepts).

For the unload, use something like:

create table foo_unload
  organization external (
    type oracle_datapump
    default directory exp_dir
    location ('foo.dmp')
  )
as select a, empty_blob() b from foo;

(You can drop the external table right after creating it, that doesn't - by default anyway - delete the dump file.)

Then move the foo.dmp file to your target server & directory, and create the external table there:

create table foo_load (a number, b blob)
  organization external
    type oracle_datapump
    default directory exp_dir
    location ('foo.dmp')
  );

And finally load your target table (CTAS or truncate ...; insert /*+ append */ ... for instance):

create table foo as select * from foo_load;

Drop the external loader table once that's done.

Sql-server – Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

Depending on your permissions, the linked server could be trying to stream all the data over locally and then doing filtering. References

You might be able to skip that pain by computing the total aggregate count first into a table on the local server and then beat against that.

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT 
    p.package_uuid
,   count(d.external_identification) AS [count]
FROM 
    ServerB.DATABASE.dbo.package p
    INNER JOIN 
        ServerB.DATABASE.dbo.doc2 d
        ON p.package_id = d.package_id
GROUP BY 
    p.package_uuid;

Try running that query locally on ServerB first to get an understanding of the theoretical throughput without factoring in your network. You can then do some quick and dirty estimates based on data sizes (500 + 8 per row in temporary table) and then it depends on your network. Hopefully this is all local network.

If the time is significantly different between the run on ServerB and pulling it back over, then you might need to use the OPENQUERY syntax to force the join on the remote server. Code approximately

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT
    OQ.package_uuid
,   OQ.[count]
FROM
    OPENQUERY(ServerB,
    N'
    SELECT 
        p.package_uuid
    ,   count(d.external_identification) AS [count]
    FROM 
        DATABASE.dbo.package p
        INNER JOIN 
            DATABASE.dbo.doc2 d
            ON p.package_id = d.package_id
    GROUP BY 
        p.package_uuid
    ) AS OQ;

Best Answer

Summary

Related Solutions

Export table without BLOB column

Sql-server – Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

Related Question