wiki.dynamicsbook: Select * in Navision on SQL

[Blog bot]

Summary:

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.

== See Also ==
*[[SQL memory]]
*[[C/SIDE database vs SQL]]
*[[Converting NAV C/SIDE database to SQL]]
*[[NAV SQL Performance]]
*[[NAV SQL]]
*[[Planning a move from NAV native to SQL]]
*[[SQL]]
*[[SQL database]]


== External Links ==
*[http://dynamicsuser.net/blogs/single...r-in-nav.aspx]

[[Category:SQL]]
[[Category:Performance]]
[[Category:NAV]]
[[Categoryatabase]]
[[Category:Server]]

Источник http://wiki.dynamicsbook.com/index.p...avision_on_SQL