Wrong key selection in report

Belias · 2012-01-07

Hi everyone, do you know why this chunk of code

SerialNoSummary.RESET;
SerialNoSummary.SETCURRENTKEY("Patient Code","Property No.","Property Type");
SerialNoSummary.SETRANGE("Patient Code",ContactOtherRC."No.")
IF SerialNoSummary.FINDSET THEN BEGIN
  REPEAT
    //do some stuff, No db reads/writes involed...
  UNTIL SerialNoSummary.NEXT = 0;
END;

generates this query?
SELECT * FROM "mydb"."dbo"."mycompany$Serial No_ Summary" WITH (READUNCOMMITTED) WHERE (("Patient Code"=@P1)) ORDER BY "Item No_","Last Source Code","Last Source Type","Code" OPTION (OPTIMIZE FOR UNKNOWN)

if i unmaintain key

Item No.,Last Source Code,Last Source Type,Code

in the table, the same code chooses the right key, which is

Patient Code,Property No.,Property Type

which is obviously enabled and maintained.

Moreover, there are no sumindexes fields that make sql choose another key.
The sqlindex property has been set on any key.

The piece of code is in a RTC report; version NAV 2009R2 build 32275

Belias · 2012-01-07

CORRECTION: after unmaintaining the sql index, the report runs actually fast (about 10x faster), but sql do the order by clause with the Item No.,Last Source Code,Last Source Type,Code key :-k

Belias · 2012-01-07

Problem solved. It turned out to be a dataitem with a sourcetableview <> from the the link with the parent dataitem.
I really miss some performance troubleshooter for RTC. Sql profiler is not enough....at least at my level of knowledge of it :whistle:
Sometimes it's really hard to understand what piece of code/property generated a bad query.

DenSter · 2012-01-07

SETCURRENTKEY should match the ORDER BY clause
SETFILTER/SETRANGE should match the WHERE clause, although there are some funky things going on there sometimes, where you see the same field filtered more than once.

If you have a SETCURRENTKEY on field 1 and 2, and you see an ORDER BY clause on two different fields, then you are not looking at the C/AL that generated the SQL.

Whether SQL Server actually uses a certain index or not doesn't affect the WHERE and ORDER BY clause.

If I remember correctly, Kriki (here on Mibuso) and Waldo (on his blog) both wrote a few detailed posts about the correlation between C/AL and the queries, see if you can find those.

Belias · 2012-01-09

DenSter wrote:

SETCURRENTKEY should match the ORDER BY clause
SETFILTER/SETRANGE should match the WHERE clause, although there are some funky things going on there sometimes, where you see the same field filtered more than once.

I noticed it as well, I'm wondering why? :roll:

DenSter wrote:

If you have a SETCURRENTKEY on field 1 and 2, and you see an ORDER BY clause on two different fields, then you are not looking at the C/AL that generated the SQL.

i don't think i completely understood this statement, sorry...if I do

SETCURRENTKEY(field1,field2);
SETRANGE(field3,'A');
FINDSET;

just becase i'm dumb or i do really need to order my table like this...then WHERE and ORDER BY "does not match"...and it's C/AL code that generated the "bad" query :-k

DenSter wrote:

Whether SQL Server actually uses a certain index or not doesn't affect the WHERE and ORDER BY clause.

But WHERE and ORDER BY (and thus SETCURRENTKEY, settableview, setrange/filter and similar) affect SQL in the index selection for the query, isn't it? this is where the problem arose in my case.

DenSter wrote:

If I remember correctly, Kriki (here on Mibuso) and Waldo (on his blog) both wrote a few detailed posts about the correlation between C/AL and the queries, see if you can find those.

And jorg as well in his book, page 101..103

DenSter · 2012-01-09

Belias wrote:

DenSter wrote:

SETCURRENTKEY should match the ORDER BY clause
SETFILTER/SETRANGE should match the WHERE clause, although there are some funky things going on there sometimes, where you see the same field filtered more than once.

I noticed it as well, I'm wondering why? :roll:

It has something to do with how the object sets filters. When the form/report has a hard coded filter on a certain field, and then the user sets another filter on that field, or the C/AL inside the report does, it will show up twice in the WHERE clause.

Belias wrote:
DenSter wrote:

If you have a SETCURRENTKEY on field 1 and 2, and you see an ORDER BY clause on two different fields, then you are not looking at the C/AL that generated the SQL.

i don't think i completely understood this statement, sorry...if I do
SETCURRENTKEY(field1,field2);
SETRANGE(field3,'A');
FINDSET;
just becase i'm dumb or i do really need to order my table like this...then WHERE and ORDER BY "does not match"...and it's C/AL code that generated the "bad" query :-k

I wouldn't say "NAV generates bad query", it generates the query that you programmed it to generate. As a rule of thumb I usually try to use a key that matches the filters that I have to set, so in your example I would use a key on Field3. Depending on the NAV version, SQL Server behaves differently as a result. In older versions the filters were more important, in newer versions the sort order is more important.

Belias wrote:

DenSter wrote:

Whether SQL Server actually uses a certain index or not doesn't affect the WHERE and ORDER BY clause.

But WHERE and ORDER BY (and thus SETCURRENTKEY, settableview, setrange/filter and similar) affect SQL in the index selection for the query, isn't it? this is where the problem arose in my case.

Sure, depending on the version that you are on, the WHERE and ORDER BY clauses do affect which index is selected. What I meant is that you cannot tell which index was used simply by looking at the query. You need to see the execution plan for that. Just because the WHERE clause says to filter on field1, or the ORDER BY clause says to sort by field2, does not mean that SQL Server will actually use an index with those fields.

Wrong key selection in report

Answers

Categories