Lots of my daily work with Dynamics NAV is tuning existing systems or architecturing the right and most efficient way of performing certain complex queries.
Tuning a query on a NAV database is extremely important, expecially if the database is big and with lots of concurrent users.
On my blog I've written in the past how you can tune up your Dynamics NAV database (SQL Server) or how you can introduce tricks in order to perform complex works in a more efficient and performant way.
Now the subject of this post is totally for the NAV Dev Team in Microsoft... 
There are two really simple tricks that I think could be really helpful for optimizing a NAV query on SQL Server, and they're based on simple concepts that are the basics for every SQL developer. Why not introduce them on NAV natively?
I'll try to explain here what I've on my mind...
1) Introducing the way to avoid "Select *" on SQL Queries:
When you write C/AL code in Dynamics NAV, your code will be translated into a SQL instruction automatically. Let's consider for example this C/AL code:
It's a simple loop on every Purchase Line with an Outstanding Quantity > 0 for retrieving the total Outstanding Amount for all the orders (it's only an example... 
).
This code will be translated by NAV into a SQL code like this (check via Client Monitor):
SELECT * FROM "EID$Purchase Line" WITH (READUNCOMMITTED) WHERE (("Outstanding Quantity">0.0)) AND "Document Type"=1 AND "Document No_"='9999.80G-53-RU0001' AND "Line No_">50000 ORDER BY "Document Type","Document No_","Line No_" OPTION (FAST 5)
NAV automatically translates every C/AL instruction into a SELECT * query. But in this sample, we want to retrieve only the "Outstanding Amount" field of the "Purchase Line" table, not all the fields.!!
The first thing that you learn when developing with databases is that SELECT * is a performance hit.
I think it could be wonderful if we can have a method called FIELDSTORETRIEVE that works like this:
This method accepts as parameters the names of the table fields we want to retrieve. The corresponding SQL query translated from NAV now will be this:
SELECT "Outstanding Amount" FROM "EID$Purchase Line" WITH (READUNCOMMITTED) WHERE (("Outstanding Quantity">0.0)) AND "Document Type"=1 AND "Document No_"='9999.80G-53-RU0001' AND "Line No_">50000 ORDER BY "Document Type","Document No_","Line No_" OPTION (FAST 5)
Now the code is really more efficient...
2) Introducing a Select Distinct method:
In a table, some of the columns may contain duplicate values and sometimes you want to list only the different (distinct) values in a table.
In SQL, the DISTINCT keyword can be used to return only distinct (different) values.
SELECT DISTINCT column_name(s)
FROM table_name
What about NAV? In NAV there's not an easy and native way to obtain a SELECT DISTINCT query and you've to work via C/AL code (with impact on performances).
Why not having a method RETRIEVEDISTINCT like this?
The code above will work like this: it filters the Customer table (I've placed dots for the filter criteria here), it retrieves the "No." and "Name" fields on the Customer table and it uses the DISTINCT keyword. The SQL code that will be generated from NAV will be something like this:
SELECT DISTINCT "No.","Name" FROM "EID$Customer" WITH (READUNCOMMITTED) WHERE .....
Wonderful and easy I think...
This is surely not the entire list of what I'd like to have on NAV as soon as possible (I'm thinking also about JOIN, .NET Code, etc) but I think that these are really simple things that could be introduced in a wonderful Service Pack.
Someone in the NAV Team can listen to me?
Technorati Tag:
Dynamics NAV