Select * FROM and how to do it better in NAV

Some background

I met with Mark in Barcelona some time back at Tech Ed. We had both just been to a SQL (not NAV related) session, and it opened both our eyes at that moment as to why the Select * in NAV is so bad. I did my first NAV performance tuning job in 1993, (Navision on OS/2 Warp) so I am not new to performance tuning, and since most of my years in NAV have been spent recovering clients form disasters, I generally get to see the worst end of it all. Now-a-days I am seeing a few largish NAV/SQL implementations (500Gig+) and still most of the performance issues are just simple common sense stuff that is easily fixed. But this is not a thread about how WE can make NAV faster, its about how Microsoft's NAV team can make it faster.

The issue.

Everyone knows that Select * is bad, and that it needs to be fixed, but the issue has always been how to actually implement it in NAV and of course it needs to be simple. After some thinking, I feel I have something that may not be a solution, but a good starting point, so hopefully someone in Denmark might read this.

The first issue is; where is Select * a problem? Well one of the big issues is where SQL needs to scan a lot of records in a fat table (one that has a lot of large fields). A typical scenario is the Item Ledger Entry table, which can be from 1.5 to 2k in size. But in many cases, I the developer is really only interested in knowing (say) the Item No, the date and Quantity. So I only really need to tell SQL to get me those three fields, instead it gives me the full 2k, including all the Fields I filtered on (like say Location code) and thus know already. In a database with say 10 million records, SQL will often say that since you are requesting * its faster to do a clustered seek on Entry No and thus it reads everything. When I really only need maybe 20 bytes not 2,000. And of course if SQL did not have to retrieve all that junk that I don't even want, it would probably have used a better execution plan so would not have read 10,000,000 records.

The Solution

So what about a new Variable Type in NAV, basically a Record still, but with a property that lets us select which fields we want SQL to give us. For simplicity lets think of a new Variable Type and call it "SQLRecord"

Well first let's look at the issues that we need to solve:

  1. How to define the select statement from C/SIDE.
  2. How to assign SQLRecords to Records
  3. Passing SQLRecord as a parameter
  4. Using SQLRecord
  5. What about Native

1/ How to define the select statement from C/SIDE.

This would be the easy part.

Properties would show:

So now we have a variable and we should be able to use it much like a normal Item Ledger Entry Record, except that when we request this from SQL, instead of sending

"SELECT *FROM…", it would create

"SELECT [Item No_], [Posting Date], [Entry Type], Quantity, [Completely Invoiced] FROM …"

2/ How to assign SQLRecords to Records

I see two possibilities assign fields one by one, or TRANSFERFIELDS. My preference would be not to allow transfer fields. I think that field by field assignment is a better solution, such as

ItemLedgEntryWork."Item No." := ItemLedgEntry."Item No.";

ItemLedgEntryWork."Posting Date" := ItemLedgEntry."Posting Date";

I generally think this safer, since the develop clearly can see what they have done. Also it addresses the fields not in the SQLRecord such as Location Code etc, that may have been the filters on the original FINDSET. The other option would be to allow TRANSFERFIELDS, but I see this fraught with problems.

3/ Passing SQLRecord as a parameter

I see that this is one of the bigger issues with SQL Record, since in NAV if you passed this as VAR with all the filters sorting etc. The called function needs to be fully aware of the SQL Fields that are viable in this Variable. If its is passed not as VAR, then what are we passing. The key issue here is that allowing any passing through a function assumes that the developer is fully aware what they are doing. Much as you can pass an Option Variable or field to a function, but really since its actually an integer, there is no real connection at the other end. E.g. you can have a function that requires passing SalesLine.Type, but there is no problem passing SalesLine."Document Type" instead. So the question is if passing a SQLRecord would force type matching, i.e. that the "SQL Fields" Properties are exactly the same for the Called Function and the Caller. Since we must assume a developer knows what they are doing, (and they can just as easily mess up by manually assigning the wrong fields to a normal record and passing that, that it should not be mandatory that the SQL Fields are identical, though obviously it must be from the same Table.

4/ Using SQLRecord

In principle this should be no different to using a normal record variable. INSERT and DELETE should not be possible, but the normal FINDFIRST, FINDSET, Get etc should remain the same.

When you press F5, you would only see the fields that are available in the SQLRecord's "SQL Fields" property, so it will be easy to se what fields are available, and trying to assign a field that is not defined should give a compiler error.

What also this record type will do, is make developers think more about what data they really need, instead of just assuming that they can get everything they want.

The property should also be available in report designer, so that reports that need just a handful of fields from rather fat tables, don't have to get so much data when that data will never be used.

5/ What about Native

In Native there should be no special handling, obviously there will be no performance improvement (since there are no plans to modify native Server), but the code should work without problems.

 

In Summary

Well it's just an idea, and yes I know that there are many other ways to speed up NAV, but this is just one more of them. And I know that there are a lot more issues to resolve than just those I mentioned above, but I don't see this as insurmountable. Of course I am by no means a SQL expert, so maybe I am overlooking something obvious, and any feedback would be much appreciated.

Lets hope someone in Denmark is reading this blog. (Or maybe I will just forward someone a link).

 

david

Posted: 2009-3-14 16:00 by David Singleton | with 5 comment(s) |
Bookmark and Share

Comments

# re: Select * FROM and how to do it better in NAV

I hope they allows us to simply create datatype where we can build sql query.  

That way we can join mulitple tables. This will solve a lot of looping we have to do in order to get what we want.

Friday, March 14, 2008 4:26 PM by Rashed Amini

# re: Select * FROM and how to do it better in NAV

Great idea David. Keeping it simple is key. Thats the approach you have taken. Minimal but with great impact.

Rashed's approach is great too "allows us to simply create datatype where we can build sql query". This has so much potential but could introduce headaches if not done right. This is probably more for a specific requirements.

Saturday, March 15, 2008 3:41 AM by munib

# re: Select * FROM and how to do it better in NAV

Hi David, I really like your idea. I worked with XAL (the forgotten ERP that Microsoft bought along with Navision and Axapta) for a long time and they had something called a #SQLFieldList macro that you put in before the use of a table. This just said to the compiler "If you are on a SQL database then only include the listed fields in the select statement". It worked fine and yes it dramitcally improved performance.

I think your idea is much better - the ability to configure the fields in a standard NAV way using a data entry form fits really well with the way the program works.

However, why can't the system just figure out what fields are needed? Let's face it, when you write your code, you will use some of the fields on a table and not some of the others. The compilation process could identify which fields are actually needed and generate the most efficient SQL rather than being lazy and pulling everything back every time.

Sunday, March 16, 2008 10:39 AM by Gaspode

# re: Select * FROM and how to do it better in NAV

Thanks for the positive feedback.

Rashed, I think that a user defined datatype is something to expect one day, but its something totally unrelated to what I am suggesting here. In fact you can already do this by generating your own T-SQL, but this is not something a "typical" C/SIDE developer should be doing.

munib00, yes, what I am proposing here is doing it The Navision Way. Nothing complex, and something that existing NAV developers can start using tomorrow.

Gaspose, no I thought about that, and without a total redesign of the C/SIDE compiler, I can not see that working. Each object is compiled seperately, so how would it know if a record used in one function accessing 3 fields is then not used in another function where it needs 2 more fields.

I see the issue of passing SQLRecords to other functions as one of the more complex ones for Microsoft to resolve, since there is always that danger that a developer passes a record that is different to what the next developer expects. Which is why I suggest that you can only pass a SQLRecord to a SQLRecord, that way if someone is using that record, its up to them to go back to the source function and make sure that they fields they need are being called.

Sunday, March 16, 2008 4:43 PM by David Singleton

# Select * FROM and how to do it better in NAV

Some background I met with Mark in Barcelona some time back at Tech Ed. We had both just been to a SQL

Tuesday, October 28, 2008 3:19 PM by David Singleton's Navision Blog