After upgrading an NAV Client from 184.108.40.206565 to 220.127.116.11113 I realized some SELECT statements have been changed.
Previously it was like:
*,DATALENGTH(“Picture”) FROM “MyDB”.“dbo”.“Contact” WHERE “Search Name”>‘XYZ’ ORDER BY “Search Name”,“No_”
Now it’s like:
“timestamp”,“No_”,“Name”,“Search Name”,“Name 2”, “Address”,“Address 2”,…,DATALENGTH(“Picture”) FROM “MyDB”.“dbo”.“Contact” WHERE “Search Name”> ‘XYZ’ ORDER BY “Search Name”,“No_”
Means: instead of the old asterisk (*) now all the fields are explicitly queried. The query above is coming from a Form …
Why was this changed? What are the “rules” for querying this or the other way (Form vs. C/AL?)? Any chance to modify this query (e.g. limit the number of fields)?
Do the fields in the query reflect the fields on the form? In other words, if you only put two fields on the form, will the query only list those two fields?
No, as far as I could see it’s all fields of that table, incl. the “Picture”. This specific test-query was taken from standard Cronus, a lookup (F5) from the standard Contact Card to the standard Contact List … (then changing the Key to “Search Name”) …
Does the order of the fields change when you change the key on the form?
I guess my question here is what is the impact of it doing its selects this way? Is it causing performance problems? using the wrong index?
From a strict SQL database perspective, would this new way actually be the preferred way? I know it doesn’t really apply to Navision here, but what if the underlying table structure changed? If select * is run, the application could potentially do the wrong thing with the resulting dataset it gets, where as with the explicit selections, it will error out, giving a clue about the underlying DB has changed when it wasn’t expected (i.e. column not present).
Does this have any effects on performance? Right now the select statement has all the fields. In code it still does select * when you do findfirst.
No, it’s always querying ALL fields, starting from “timestamp” to the laste one in order of the table’s field definition …
No, performance should not be affected - as far as I could see …
This SELECT “Field1”, “Field2”, “Fieldn” seems to come only from NAV Forms. My hope was, that it was somehow possible to limit the number of fields selected, e.g. only querying the fields which are to be displayed on the form - IMHO that would be a major improvement!
But with the current “different” SELECTs there should no difference (except that Profiler Traces get larger and are “trickier” to investigate) - Execution Plans, Reads, CPU, etc. is identical.
So my question actually is: WHY?
If there is no real advantage with the new SELECT, well, then I would prefer the old one, because it’s easier to read the statements …