FindSet

NavStudent · 2007-09-22

Hello
In Sp1 4.0, they released findset, and this lowers the sql to nav client communication a lot. this works great if you are filtering on a set of records. However, for Get function Navision communicates with sql every time unless it's the same record.

For example.

ItemLedger.setrange("Location Code",'123'); 
if ItemLedger.findset then repeat 
  Item.get(ItemLedger."Item No."); 
until ItemLedger.next = 0;

During the loop above Navision queries sql with the item, and this slows down performance.

I would like to suggest a new function that would prefech these items in one statement. I would call the function GETRECORDSET.
All it would do is call the sql query and cache the records.
The function would take several parameters. They would be.
1. AnotherRecord (with filters),
2. "Field No." from the recordset for field relationship
3. "field No." From AnotherRecord to define the relationship.

The way it would be used is like this.

Item.GETRECORDSET(ItemLedger,ItemLedger."Item No.","No.");

So the code above would be optimized like this.
Code:

ItemLedger.setrange("Location Code",'123'); 
if ItemLedger.findset then begin 
  Item.GETRECORDSET(ItemLedger,ItemLedger."Item No.","No."); 
  repeat 
  Item.get(ItemLedger."Item No."); 
  until ItemLedger.next = 0; 
end;

If there are more than one relationship, you would add them to the function.
Code:

Salesline.setrange("Item No.",'321'); 
if Salesliner.findset then begin 
  SalesHeader.GETRECORDSET(Salesline,SalesHeader."Document    type","Document type",SalesHeader."No.","Document No."); 
  repeat 
  SalesHeader.get(Salesliner."Document type",Salesliner."Document No."); 
  until Salesliner.next = 0; 
end;

Basically the getrecordset will be a sql statement like this.
For the first example

SELECT (All fields from Item)
FROM  CRONUS5$Item INNER JOIN
        [CRONUS5$Item Ledger Entry] ON KRONUS5$Item.No_ = [CRONUS5$Item Ledger Entry].[Item No_]
WHERE ([CRONUS5$Item Ledger Entry].[Location Code] = '123')

The where clause will be based on filters on Item Ledger Entry Filters.
So the get statement will be all cached a head.
They can add additional paramaters for locking, just like findset.
What do you guys think?

cnicola · 2007-09-22

I think you know too much SQL for your own good

Seriously though:

1. I don't see much advantage to that as it creates a dependence between 2 different lines of code that in a real life example can and will be far apart (and thus making the code harder to change). C/AL code is a high level programming language and therefore, to a large extent, makes abstraction of the way the underlying layer handles it.
2. While I do not know a lot about SQL and how joins work but I would imagine that to solve the JOIN the system will still generate a read action (though I might be wrong).
3. I much rather prefer the current way of solving that by using a temporary record and loading it as I go.

kine · 2007-09-22

The GET function is the cheapest function. It is no gain if you try to optimize it. It is better to do few gets than create join. Much better optimization can be to condition the get and use it just when the Item No. is different from previous one (in case of transfers you will save 50% and more of reading). If you will sort the data by Item No., you will save much more. (of course there is additional cost for sorting, but it can be faster than the reading, depends on set size etc.)

DenSter · 2007-09-22

I agree with Kamil. You can just sort the ILE on Item number, and only GET the next Item when the number changes. You'll get much better performance.

Marije_Brummel · 2007-09-22

I agree that there is much to do especialy in having less server trafic.

You can do what you want today by building a temporary table with items first using FINDSET.

ITEM.FINDSET;
REPEAT
STUFFINTOTEMPTABLE;
UNTIL NOMOREITEMS;

ITEMLEDGERENTRY.FINDSET
READSTUFFFROMTEMP
UNTIL NEXT = OHOHNOMORE

The tempreads will be coming from your local disk.

Good luck and keep the ideas comming!!

UPDATE: Rember that when the 500 records are exeded the FINDSET wil behave like a FIND('-'). To a cursor will be created and NAV will FETCH the next records from the recordset.

cnicola · 2007-09-22

Hi Mark,

Not to be too picky but I do the temp thing a little different:

ITEMLEDGERENTRY.FINDSET
if not ReadItemFromTemp then
begin
Item.get;
StuffIntoTemp(Item);
end;
UNTIL NEXT = OHOHNOMORE

I don't know about you but my last 3 clients each has 30K to 90K items so your first loop would take a bit

And I would recommed using the temp thing unless you really need to sort by Item No.

NavStudent · 2007-09-22

ok the sql statement can be improved. Instead of doing a join you can change the statement this way.

SELECT *FROM  CRONUS5$Item 
WHERE (CRONUS5$Item.[No_] = '123') OR
(CRONUS5$Item.[No_] = '123') OR
(CRONUS5$Item.[No_] = '124') OR
(CRONUS5$Item.[No_] = '125') OR
(CRONUS5$Item.[No_] = '126') OR
......

The WHERE part will be created based on FINDSET 500 records you already have in cache in ItemLedger. It will only include the unique "Item No."

It will be just like get statement, except you are running it once.
Thus you have a lot less commucations with server.
The weight of the statement will be just like GET.
Plus no other sorting will be required.

NavStudent · 2007-09-22

Mark Brummel wrote:

UPDATE: Rember that when the 500 records are exeded the FINDSET wil behave like a FIND('-'). To a cursor will be created and NAV will FETCH the next records from the recordset.

Yes it creates a server cursor and fetches the next record(s).
I would say that saving upto 500 reads (gets) on sql server is definitely a plus.

NavStudent · 2007-09-23

cnicola wrote:

Hi Mark,

Not to be too picky but I do the temp thing a little different:

ITEMLEDGERENTRY.FINDSET
if not ReadItemFromTemp then
begin
Item.get;
StuffIntoTemp(Item);
end;
UNTIL NEXT = OHOHNOMORE

I don't know about you but my last 3 clients each has 30K to 90K items so your first loop would take a bit

And I would recommed using the temp thing unless you really need to sort by Item No.

That is exactly why we need the getrecordset function. You should not need to prefech all the items.
Plus on sql you do not need to store it in temp variable cause the client caches it. I believe there is another thread about that. Kine mentioned the performance increases after 10K records.

kine · 2007-09-23

Kine mentioned the performance increases after 10K records.

Where??? Hard to say something about it if there is no context... ;-)

And:
It is much faster and better to do the optimalization in the code, than in the technology. It took just few hours to optimize the code (or just few minutes). Optimizing on technology level is for a years... ;-)

Better than optimizing one case is to throw out the old technology and use another one, but there is no warranty that it will be better (you know, "old technology" need just 64KB in memory and 268 CPU, newer and "better" technology needs 1GB in memory and 2 core super cool CPU...) - yes, it can look nicer, but ERP is not about this (just sales are about that part... ;-))

NavStudent · 2007-09-23

I searched it and it was actually kriki.

http://www.mibuso.com/forum/viewtopic.php?t=20901

I'm not changing the technology just a new function like findset.

Here is another change to the function
Record.getrecordset(OtherRecordSet,"PK fields based on OtherRecrodSet")
e.g.

SalesHeader.GETRECORDSET(Salesline,Salesline."Document    type",Salesline."Document No.",);

DenSter · 2007-09-23

kine wrote:

It is much faster and better to do the optimalization in the code, than in the technology. It took just few hours to optimize the code (or just few minutes). Optimizing on technology level is for a years... ;-)

not to mention that none of this is anything we can do now, and we have absolutely no influence on how it will be solved.

The development team is aware of all the issues, believe me, and they want to make it work as well as possible. You have to remember that at the moment NAV also needs to work on the native database server, so they are limited to what can be done. When SQL Server is the only platform (which is not until at least one if not two versions after 5.1 comes out) you will start seeing SQL Server specific solution start to pop up.

NavStudent · 2007-09-23

Native can igonore the function, just like findset is being used just like
find('-');

PerJuhl · 2007-09-24

cnicola wrote:

I think you know too much SQL for your own good

Right, "I miss my JOIN"

Yes, it's a lot faster on SQL, as I see it you only need this in reports.

Eg. List all Item's ("No") with Stock <> 0.

Even I can do that a faster in SQL.

I think 5.1 will make you happy, with reports is build on Reporting Services and SQL statments.

With your wish, we end up in SQL and with no C/SIDE.

Every "translation" from C/SIDE to SQL and back will have a cost, so we just as well remove C/SIDE.

The good thing about C/SIDE is not thats i fast but thats it's simpel.

BR Per

cnicola · 2007-09-24

PerJuhl wrote:

Right, "I miss my JOIN"

Yes, it's a lot faster on SQL, as I see it you only need this in reports.

Eg. List all Item's ("No") with Stock <> 0.

Even I can do that a faster in SQL.

I think 5.1 will make you happy, with reports is build on Reporting Services and SQL statments.

With your wish, we end up in SQL and with no C/SIDE.

Every "translation" from C/SIDE to SQL and back will have a cost, so we just as well remove C/SIDE.

The good thing about C/SIDE is not thats i fast but thats it's simpel.

BR Per

You know it is funny. I have been with Navision since 1999 and I remember still the first wave of complaints from people coming from pure programming backgrounds: how C/AL is so simple and does not have all the power of C, C++.
I have a bachelor degree in Computer Science and when I left school I used to think that "real men" program in Visual C++ and/or C++ on Unix (hey I could have done C/AL programming with what I knew in high school).
After some time, keeping my mind open I realized that in a business environment all they care about is results and cost. I strongly believe that even today if you could write something in COBOL, ASM or BASIC that does the same things as Navision and within same cost companies would not care that it was not written in the latest or coolest programming language.
So I think the SQL wave is just another wave that will pass. Also it reminds me of last year when I went to a SQL performance class at MBS headquarters. After the training we had some presentation sessions of various big wigs at MBS. One of them was their development boss (I wish I remembered his name :oops: ) and he said that his dream is to get to a point where people will not need to know how to write SQL queries. And I think he meant it.
I mean going back to old times, I used to write Windows programs in C where you had to handle all the messages being sent to the operating system. It took you days to write a simple Notepad program. With MFC, Java classe and .NET framework I don't think any person in their right mind would want to go back to the old Windows C programming (unless you want to hack something

).
I think the reason why I love Navision is that it is simple and fast to develop and therefore it allows you to concentrate on the actual implementation and the business issues which in time I learned to love more. Oh and one more reason I like the fact that it is not all about programming: my job hasn't been outsourced to India while most of pure SQL, C++ and Java programming has or can be.

P.S. Ok and now in place of an apology for being so hard: for all you hardcore SQL people with Automation now you can send direct queries to SQL from Navision and have them executed. Obviously that cannot be usefull for write transactions since you won't have the Navision business logic validated but for read transactions should work just fine.

kriki · 2007-09-24

DenSter wrote:

kine wrote:

It is much faster and better to do the optimalization in the code, than in the technology. It took just few hours to optimize the code (or just few minutes). Optimizing on technology level is for a years... ;-)

not to mention that none of this is anything we can do now, and we have absolutely no influence on how it will be solved.

The development team is aware of all the issues, believe me, and they want to make it work as well as possible. You have to remember that at the moment NAV also needs to work on the native database server, so they are limited to what can be done. When SQL Server is the only platform (which is not until at least one if not two versions after 5.1 comes out) you will start seeing SQL Server specific solution start to pop up.

Well, this can be used on both platforms: http://www.mibuso.com/forum/viewtopic.php?t=20901

kine · 2007-09-24

I think 5.1 will make you happy, with reports is build on Reporting Services and SQL statments.

Not true. I would say "reports using part of reporting services"- nothing more, nothing less.

ara3n · 2007-09-24

NavStudent wrote:
I searched it and it was actually kriki.

http://www.mibuso.com/forum/viewtopic.php?t=20901

I'm not changing the technology just a new function like findset.

Here is another change to the function
Record.getrecordset(OtherRecordSet,"PK fields based on OtherRecrodSet")
e.g.
SalesHeader.GETRECORDSET(Salesline,Salesline."Document    type",Salesline."Document No.",);

If the compiler is smart enough and looks ahead in the loop, it could figure and issue a different statement for the first get statement.
That way nothing from navision programming perspective needs to be changed.

My 2 cents.

Marije_Brummel · 2007-09-24

But it does not read ahead. Otherwise we would not need FINDSET, FIRST etc.

You can only guess by what factor the complexity of the driver would muliply if it should read ahead...

ara3n · 2007-09-24

Agreed. If the compiler isn't smart, then adding the code and allow the user to do performance improvement will help.

Michael_Nielsen_[MSFT] · 2007-10-31

The C/AL compiler and the C/AL library are two different worlds. The compiler does all the usual compiler optimizations: Constant folding, jump optimizations, etc. However, the C/AL library functionality is a C/AL wrapper on top of a C++ library and once you make the call to it it does what is does and the optimization is then done by the SQL interface layer which then has to guess your next move.

We are working of creating more context in form of metadata to enable the SQL interface to make better desicion on what to do

kine · 2007-10-31

Good to know. I wish you success in that... 8)

FindSet

Comments

Categories