BLOB = [Binary] Large OBject
Well, several tables in MS Dynamics NAV use such "BLOB"-fields to store binary data like pictures. This could be quite convenient for the application, for example for creating Item catalogues displaying such pictures, etc. but from a NAV/SQL performance perspective BLOB could also be a real pain – I'd like to explain why, and what I recommend to do to avoid the problems …
The basics
A NAV "BLOB" data-type is actually the "image" data-type in SQL Server (SQL also features other LOB types as "text" or "ntext" but they aren't relevant for NAV).
[ Out of the box, NAV flags a "BLOB" field with the property "Compressed" = TRUE. If this is enabled, NAV uses a compression algorithm to save the data more compact. Have in mind that SQL Server does not recognize this "compression", so if you're using BLOB to transfer binary data from NAV to other SQL systems – which could be very smart, but that's a different story! – you should make sure to set "Compressed" to FALSE. But anyway … ]
By default, every table in NAV has a "Clustered Index" – and even though this is not a necessity for SQL Server, this is a very good idea! The general rule is: "Every table has a Clustered Index". Period.
An Index is an internal balanced-tree (B-tree) like structure, helping to retrieve records quickly. Every SQL Server table could have multiple indexes. The b-trees exist of "index pages" ("root nodes", "index nodes" and "leaf nodes") which store the indexes values. The difference between a "Clustered Index" (CI) and a "Non-Clustered Index" (NCI) is, that the "leaf node" level of a CI represents the table itself. Means: while all other index nodes only contain the indexed valued – e.g. "No." of table "Item" – the "leaf nodes" include all the other fields – e.g. "Description", "Item Category", etc.. Further, the CI defines how the data is physically stored and sorted within the table.
Hence, each table could have one CI and multiple NCI. By default, the CI is generated on basis of the "Primary Key" in NAV (which is mostly a good idea), all other "Keys" – if enabled and maintained - will be created as NCI.
As mentioned, the CI contains all fields of a table. Really all? No, not the "BLOB" fields. Fields of type "image" in SQL can store up to 2 Gigabytes of data (which requires about 256.000 "pages" x 8 KB). So if that LOB data would be included into the CI, this could dramatically increase the index size and severely degrade its performance is all aspects! That's why LOB fields are stored differently within the database, not as part of the "leaf nodes" of the CI (of course there are internal references).
The problem …
… in NAV is, that the "BLOB" fields are always read with any query on the related record. In SQL Profiler one could see those queries, for example when reading from the "Item" table:
SELECT *, DATALENGTH("Picture") FROM "Navision"."dbo"."CRONUS$Item" WHERE …
The "SELECT *" queries all fields from a table, thus, also the BLOB field(s). With "DATALENGTH" additionally the size of that "BLOB" is determined (C/SIDE uses this info to deal with the field). As the BLOB field is not part of the CI "leaf nodes", SQL Server has to perform additional internal operations to gather the LOB data. Due to the specific storage of that "image" fields, the data could be "spread" widely within the database (it cannot be granted that a LOB field is stored within one continuous array of pages), thus SQL Server has to perform a lot more "page reads" then (shown in SQL Profiler as a unusual high number of "Reads", e.g. >= 1000).
And as always: a high number of "Reads" means a high consumption of cache space and CPU and may also result in physical I/O. Too many "Reads" waste precious system resources and degrade performance!
So much about the technical side. But the question should also be: Why always reading the BLOB fields anyway?
In NAV there are a lot of important and heavily used tables which are "affected" by BLOB fields, as "Item", "Customer" or "Vendor" (and maybe "Contact" and others). For example, the "Item" table is permanently queried when looking up items, creating an order, posting a shipment, posting an invoice, posting an item journal etc.. Within all the processes and transactions NAV fires a SELECT * on the table, reading the "Picture", too.
But what is the need for a "Picture" when posting an order or processing physical inventory? There is none! Nope. Zip. Nada.Pointless.
But using "BLOB" fields in such tables could be a real problem regarding performance.
The solution
Of course, the solution cannot be to abstain from using BLOB fields. Again: using BLOBs could be very smart, not just for storing pictures, but maybe also for XML data or documents.
To avoid performance issues it is just necessary, to keep BLOBs away from business processes, and this could be done rather simple: Storing BLOBs in separate tables!
For example, when it is about the "Picture" in table "Item", just create a new NAV table e.g. called "Item Picture", existing of just two fields: "Item No." (Code20) and "Picture" (BLOB). Once this new table exists, the "Picture" could be transferred quite simple with some NAV programming – or, even faster, with some TSQL:
USE [Navision] -- change db name here
GO
INSERT INTO [dbo].[CRONUS$Item Picture] ([Item No_], [Picture])
SELECT [No_], [Picture] FROM [dbo].[CRONUS$Item] WHERE DATALENGTH([Picture]) <> 0
GO
If the data has been transferred, the "Picture" could be erased from the old "Item" table – either by some C/AL code, or this TSQL:
USE [Navision] -- change db name here
GO
UPDATE [dbo].[CRONUS$Item] SET [Picture] = NULL WHERE DATALENGTH([Picture]) <> 0
GO
(use the TSQL at own risk – no warranties or anything)
Now the "Item" table is cleaned up – de-BLOBbed so to speak - so all kinds of business processes should not encounter trouble related to BLOB Reads. Then of course it is necessary to change the application, now looking up the "Picture" from the new table "Item Picture", but actually that's a piece of cake (e.g. ItemPicture.GET(Item."No.");). Have in mind that it isNOT necessary to disable the BLOB field in the table – just not using it is all it takes.
I highly recommend to implement this wherever BLOB is used in "transactional tables"! It's just a minor effort, but could have remarkable impact on NAV/SQL performance. Further, to keep the BLOB's performance OK it is necessary to periodically maintain such tables with an ALTER INDEX REORGANIZE WITH LOB_COMPACTION (SQL 2005/2008) or DBCC INDEX_DEFRAG (SQL 2000) – see "Books Online" about details.
Great post! Thanks!
ReplyDelete