mibuso.com

Hi everyone, do you know why this chunk of code

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

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

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

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

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
Sometimes it's really hard to understand what piece of code/property generated a bad query.

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.

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


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

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


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.


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

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.


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.


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.