Order By performance

Alex_Chow

In the SQL database, I have written in the code that finds the last item charge date. The code goes like this:

  SETCURRENTKEY("Item Charge No.","Inventory Posting Group","Item No.");
  SETRANGE("Item No.",Item."No.");
  IF FINDLAST THEN BEGIN
    IF "Item Charge No." <> '' THEN
      NewValDate := "Posting Date"
    ELSE
      EXIT(FALSE);

The SQL statement is the following:

SELECT TOP 1 * FROM "TESTDB"."dbo"."CRONUSTEST_$Value Entry"  WHERE (("Item No_"=@P1)) ORDER BY "Item Charge No_" DESC,"Inventory Posting Group" DESC,"Item No_" 
DESC,"Entry No_" DESC

I've tried different settings on the MaintainSQLIndex, MaintainSIFTIndex, and set the SQLIndex, but it's still taking a second to bring up each item.

Any suggestions?

ara3n

You could change the code a little

SETCURRENTKEY("Item Charge No.","Inventory Posting Group","Item No.");
SETRANGE("Item No.",Item."No.");
SETfilter( "Item Charge No.",''<>%1','');
IF isempty THEN
exit(false)
else begin
findlast;
NewValDate := "Posting Date"
end;


Also if you are running this on sql, you don't need to setcurrently, because last entry no will be the last item charge.

Alex_Chow

Thanks, that worked beautifully!

Based on the code change, where was the problem that was causing the slowdown? Is it the FINDLAST if there are no entries available?

cnicola

1. In SQL it does help if the fields you are filtering on are at the beginning of the key. So the way you had the code the key was not the best. By filtering on Item Charge No. you made things a bit better.
Also IsEmpty checks the table statistics to see if there are any records instead of actually reading the table so therefore it is faster for empty recordsets.

2. I was about to point out that your code and Rashed's don't do the same thing. And logically they don't: yours goes to the last record and checks whether that has an Item Charge or not. Rashed's gets the last record that has an Item Charge No. But then I realized that in practice, due to the fact that blank will be the first set of records, you will end up with the same result. Not the best coding though (I know it is always subjective but just a thought)

3. And I will point out that neither code does what you say you want: "I have written in the code that finds the last item charge date". Your code (and Rashed's) will return the last Posting Date of of the last alphabetical Item Charge Code used for that particular item which could just as well be the earliest date of them all.

So you may have wanted to use a key like "Item No., Posting Date" and use FindLast on it.

Alex_Chow

Very attentive! =D>

But I felt compelled to defend myself... Haven't forgotten what they taught us at Navision Development 101 :P

The original C/Side code in production doesn't look like this.

I was trying different coding on the SQL Server to find out exactly why the slow down occurs. The original code causes even more slowdown in SQL Server, but Rashed's code gave me some clues on what to do.