Once this new table exists, the "Picture" could be transferred quite simple with some NAV programming – or, even faster, with some TSQL: called " Item Picture", existing of just two fields: "Item No." (Code20) and "Picture" (BLOB). 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!įor example, when it is about the "Picture" in table "Item", just create a new NAV table e.g. Again: using BLOBs could be very smart, not just for storing pictures, but maybe also for XML data or documents. Of course, the solution cannot be to abstain from using BLOB fields. Pointless.īut using "BLOB" fields in such tables could be a real problem regarding performance. Within all the processes and transactions NAV fires a SELECT * on the table, reading the " Picture", too.īut what is the need for a "Picture" when posting an order or processing physical inventory? There is none! Nope. 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. 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). But the question should also be: Why always reading the BLOB fields anyway? Too many "Reads" waste precious system resources and degrade performance! >= 1000).Īnd as always: a high number of "Reads" means a high consumption of cache space and CPU and may also result in physical I/O. 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. 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. With " DATALENGTH" additionally the size of that "BLOB" is determined (C/SIDE uses this info to deal with the field). The " SELECT *" queries all fields from a table, thus, also the BLOB field(s). SELECT *, DATALENGTH("Picture") FROM "Navision"."dbo"."CRONUS$Item" WHERE … In SQL Profiler one could see those queries, for example when reading from the "Item" table: … in NAV is, that the "BLOB" fields are always read with any query on the related record. 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). Fields of type " image" in SQL can store up to 2 Gigabytes of data (which requires about 256.000 "pages" x 8 KB). 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.Īs mentioned, the CI contains all fields of a table. Hence, each table could have one CI and multiple NCI.
Further, the CI defines how the data is physically stored and sorted within the table. "No." of table "Item" – the "leaf nodes" include all the other fields – e.g. Means: while all other index nodes only contain the indexed valued – e.g. 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. The b-trees exist of " index pages" ("root nodes", "index nodes" and "leaf nodes") which store the indexes values. Every SQL Server table could have multiple indexes. Period.Īn Index is an internal balanced-tree ( B-tree) like structure, helping to retrieve records quickly. 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 …Ī 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).īy 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". This could be quite convenient for the application, for example for creating Item catalogues displaying such pictures, etc. Well, several tables in MS Dynamics NAV use such "BLOB"-fields to store binary data like pictures.
0 kommentar(er)
