Order By performance

Alex_Chow
Member Posts: 5,063
In the SQL database, I have written in the code that finds the last item charge date. The code goes like this:
The SQL statement is the following:
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?
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?
Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
0
Answers
-
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.0 -
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?Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book0 -
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.Apathy is on the rise but nobody seems to care.0 -
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.Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions