Partial Records with SetLoadFields

Fetching and updating records has historically been the greatest culprit of performance problems. The standard way that BC retrieves records is very expensive, since it will always get ALL the fields of a table (and its brethren companion tables). This post covers a (relatively) new option called SetLoadFields, which is used to specify the fields that you want to retrieve.

So What’s the Problem?

The database engine for BC is SQL Server for OnPrem and Azure SQL for SaaS; the business logic translates database operations into T-SQL statements at run time. By default, it issues a SELECT * and that means that for every standard database call, BC retrieves ALL fields from the table. Good for us developers because we never have to think about which fields to fetch. From a performance point of view though this causes a MASSIVE superfluous overhead in data traffic. Some of the most used tables in BC have bazillions of fields, and in any business logic scenario you never need more than a handful of fields.

The problem is exacerbated by the presence of table extensions. Each table extension is represented in the SQL database by a companion table that shares the primary key with the main table. Every time that you retrieve records from the main table, the system also retrieves the fields from the companion tables by issuing a JOIN on the PK fields. You can imagine a popular table like the Sales Line with a dozen table extensions; a SalesLine.FindSet command generates a SQL statement that includes a dozen JOINs.

The problem is that the number of fields that are included in SQL statements has a disproportionate effect on query performance. Read the post that I link to below for more details, but what you need to know is that the same query with all fields can take hundreds of times more than retrieving just half a dozen fields.

Only Get What you Need

To eliminate this overhead, we now have a SetLoadFields command. Basically, what you can do with this command is to define the fields that are included in the SQL statement. Instead of getting all fields and get data that you will never use, you tell BC that you only want your handful of fields.

Need an address from a Vendor? The external document number from an invoice? An Inventory Posting Group from an Item? You don’t have to read 6 million fields to do that anymore.

procedure ShowSomeCode()
var
    Vendor: Record Vendor;
begin
    Vendor.SetLoadFields(Address,"Address 2",City,"Post Code");
    Vendor.Get('10000');
    // do stuff with the address fields and ignore the rest
end;

This code sample generates a SQL statement with a SELECT on just those few fields that are defined in the SetLoadFields command, and it should ignore the companion tables altogether since these are all main table fields.

Read the documentation here and make sure you read how to use it here. For more technical in-depth information on what to do and what happens under the hood (way above my head there), read Mads’ posts here and here.

New Habits

In my day-to-day life as a BC developer I don’t normally see SetLoadFields commands. I even checked the standard objects and it’s actually quite surprising how little it is used there. In a previous life I did a LOT of performance troubleshooting, and this would have been a tremendous help in solving lots of performance problems. I know I will try to make using this command a habit.

SQL Server and Docker

Learn how to use SQL Server to access the databases in your Docker container

This post is for you if you want to be able to access the SQL Server database inside your Docker container, without having to write the query.

For one of my projects, I needed to be able to see the apps that were uninstalled but still had their schema in the tenant database. That information is in the $ndo$navappuninstalledapp table, and with SQL Server Management Studio (SSMS) it is super easy to look at table data. In my container I assumed that I would have to figure out a way to write the actual query (something I am not very good at). As it turns out, I was wrong. In this post I will explain two very easy ways to access the SQL Server database inside your Docker container.

SQL Server Management Studio

The first, most obvious, option is to do a complete install of SQL Server in whatever edition you have access to. If you want to keep things lean though, you can also install a standalone SSMS. You can download SQL Server Management Studio here.

Connecting to a Docker container could not be easier. In the connection dialog, simply enter the name of your container as the server name, and SSMS will connect to it for you. I always use NavUserPassword authentication in my containers, and by default your container password will also work as the sa password inside the container.

My container is called ‘densterdev’, and you can see the default app and tenant databases inside the container.

All I needed to do was look at some table data, and that works just fine. I did not try to do anything more advanced than that.

SQL Server Extension in VSCode

The second option is even easier than installing SSMS, because you are already using VSCode to do your AL development. Microsoft has created a ‘SQL Server’ extension, which works very similar to SSMS. In the extensions search box in VSCode, type ‘SQL’ and select the one made by Microsoft.

After installing you may need to reload VSCode to enable the extension. You will see a new tab on the left navigation pane that will show you the tooltip ‘SQL Server’ when you hover over it. When you click this tab, you will see a heading that says ‘Connections’ at the top, with a + sign next to it. Click this + and follow the prompts. Just like SSMS, you enter the container name as the server name, and it should connect to it with no problem.

The same app and tenant databases are shown inside VSCode

I still did not need to do anything more complex than looking at some data, so I really can’t say what features are available beyond that. My guess is that it is less capable than SSMS, so this may not be an option if you need more advanced capabilities.

SQL Setup Essentials for NAV

The way that SQL Server is set up can greatly affect the performance of your NAV database. People often ask me about how to properly set up SQL Server for NAV databases, and it is a common topic in the online forums. I’ve posted a clip on YouTube in which I cover the essential elements of how to properly set up SQL Server for NAV.

First published April 18, 2012

Performance on NAV 2013

The first performance impressions are in and it looks like NAV 2013 is the first version on SQL Server that is going to outperform the native database server.

When talking about whether to upgrade to NAV 2013, one of the things that my customers are interested in most is whether it will perform at acceptable levels. I’ve been wanting to do something elaborate and use the new testability capabilities in NAV 2013 to do a benchmark test for performance specifically. As often happens, plans don’t always come together, and by the time I started forming some ideas, other people have gone and done a much better job than I could have. So, what I thought I’d do is compile a number of links to performance related content that I’ve found so far.

First off, my fellow MVP Jörg Stryk wrote a very technical article about his impressions of NAV 2013 from a SQL Server point of view. He shares his sources, he’s done some comparisons between his own T-SQL and the queries that are generated by NAV 2013. Very interesting to read his train of thought, and, if you can keep up with him, you will learn a thing or two about SQL Server. Some of the key things that I picked up on are the modified locking mechanism, the promise of the query object, and the annoying fact that individual users cannot be identified from the SQL Profiler anymore.

Next, my coworker Faithie writes about some changes that were made to how Dimensions are handled in NAV 2013. This one has a huge potential to help improve performance. I work with customers that have millions of ledger entries, with hundreds of millions of ledger entry dimensions, and the prospect of eliminating pretty much all of those is a very compelling one.

Next up, another fellow MVP, Vjekoslav Babić, who lists his top 5 SQL Server improvements. With NAV 2013 being the first version on SQL Server only, we can finally start letting go of certain “SQL improvements” from previous versions, which were really just things to make NAV on SQL behave the same way as the native database server. If I were to list what I take away from this article, I would list all 5, so I’ll spare you and just refer you to the article itself.

Where it gets interesting is when mergetool.com‘s Per Mogensen puts together a couple of videos (here and here) to demonstrate how NAV 2013 stacks up against NAV 2009. These videos then inspired Vjeko to take it a couple of steps further, and put together his own test, and he even shares his test objects. This is turning into a real community effort!

With NAV 2013, it looks at though the potential is definitely there to get performance up to native database server expectations, which was always the argument against using SQL Server. What I am very much excited about is that finally the NAV team can let go of artificially making NAV on SQL Server behave the same way as the native database server and let SQL Server do what it is good at. I personally think that they have only just scratched the surface, and a ton of further improvements are possible. I think the query object will prove one of the most potent weapons against performance problems, and, hopefully, we’ll see a tighter integration of SQL Server capabilities from C/SIDE.

First published June 25, 2012

Essential SQL Maintenance for NAV

Lack of proper maintenance on SQL Server can lead to severe performance problems. When I am called in to troubleshoot a client’s performance issues, one of the first things that I look at is whether there are any maintenance tasks on their SQL Server for the NAV database. Many times I find that there is no maintenance at all. Putting the right maintenance in place is the easiest thing you can do to help fight performance issues. In this YouTube clip I cover the essential maintenance that should be present for every single NAV database on SQL Server.

Sorry for the bad audio, I didn’t realize that it was set to my computer’s mic instead of my headset.

First published April 18, 2012

NAV on SQL – SQL Backup and Restore

Creating a NAV database on SQL Server is very easy to do when you have a SQL Server backup, and it is much faster then restoring a NAV backup. In this YouTube clip I show you how to use SQL Server Management Studio to create a full database backup, and how to create a new NAV database with that backup file.

First published April 22, 2012

NAV on SQL – NAV Backup and Restore

It is very easy to create a new NAV database on SQL Server, using the proprietary NAV backup Mechanism. While it is preferred to use SQL Server to back up and restore your databases, the NAV backup can also be used. n additional benefit of the NAV backup is that you can back up individual companies. I’ve created a YouTube clip that shows you how to create a NAV backup and how to use that backup to create a new NAV database on SQL Server.

First published April 22, 2012