SELECT *...

ioannidisv · 2017-08-28

Hi, I've always wondered why in SQL traces I see Dynamics NAV always shooting SELECT * FROM... commands. Is this a developer problem or a platform problem? If is the latter, can this behavior change without the creation of additional objects i.e. Views?

vaprog · 2017-08-28

In most circumstances where NAV fetches data from the database, it offers you all the data in a corresponding Record variable. There, all fields are present, therefore NAV needs to fetch all fields. The only exception beeing the FlowFields, which you (mostly) need to fetch explicitly by calling CALCFIELDS.

Query objects are NAV's way to let you (as a developer) access your data in a more efficient way.

thomasbarbut · 2017-09-04

Hello, BLOB fields need CALCFIELDS, otherwise they return NULL. Like this it is efficient.

Slawek_Guzek · 2017-09-04

The NAV as a platform does not know if you are going to need a field or not. the FIND* command is the platform command, what are you going do do with the record later is not a platform concern. In one place you may use one field down the code, in another place few other fields. How the FIND platform command is supposed to know which field are you going to use after it, later in the code ?

As for the BLOB fields - it is not efficient, but a good NAV developer knows that and does not put a BLOB into table which needs to be frequently accessed. He/she puts the BLOB into separated, integer-keyed table instead, and in frequently accessed table keeps only the reference to the blob field holding table.

See the Interaction Log Entry / Attachment table for example.

Slawek

vaprog · 2017-08-28

In most circumstances where NAV fetches data from the database, it offers you all the data in a corresponding Record variable. There, all fields are present, therefore NAV needs to fetch all fields. The only exception beeing the FlowFields, which you (mostly) need to fetch explicitly by calling CALCFIELDS.

Query objects are NAV's way to let you (as a developer) access your data in a more efficient way.

ioannidisv · 2017-09-04

Well, I wouldn't call that "efficient" but it's definitely a way. How is it efficient if for example you have blob fields in your table, that you don't need for, let's say, 80% of the times?
But anyway, thanks @vaprog for taking the time to answer this one

thomasbarbut · 2017-09-04

Hello, BLOB fields need CALCFIELDS, otherwise they return NULL. Like this it is efficient.

ioannidisv · 2017-09-04

Good to know that. Thanks @thomasbarbut !
As you have already probably understood, I'm not a Dynamics NAV developer , I'm a DBA

Slawek_Guzek · 2017-09-04

The NAV as a platform does not know if you are going to need a field or not. the FIND* command is the platform command, what are you going do do with the record later is not a platform concern. In one place you may use one field down the code, in another place few other fields. How the FIND platform command is supposed to know which field are you going to use after it, later in the code ?

As for the BLOB fields - it is not efficient, but a good NAV developer knows that and does not put a BLOB into table which needs to be frequently accessed. He/she puts the BLOB into separated, integer-keyed table instead, and in frequently accessed table keeps only the reference to the blob field holding table.

See the Interaction Log Entry / Attachment table for example.

Slawek

thomasbarbut · 2017-09-04

Ahh, i haven't noticed. It had the same difficulties like you. I started with Dynamics NAV by looking/reporting at/from SQL, but as soon as I started developing C/AL everything began to make sense. . .
Also if you want to create new Indexes,Keys for performance do it in Dynamics NAV because if Objects in Dynamics are compiled every operation made on SQL for the obejct is lost.
I had to learn it the hard way.

best regards,

ioannidisv · 2017-09-04

> @Slawek_Guzek said:
> The NAV as a platform does not know if you are going to need a field or not. the FIND* command is the platform command, what are you going do do with the record later is not a platform concern. In one place you may use one field down the code, in another place few other fields. How the FIND platform command is supposed to know which field are you going to use after it, later in the code ?
>
> As for the BLOB fields - it is not efficient, but a good NAV developer knows that and does not put a BLOB into table which needs to be frequently accessed. He/she puts the BLOB into separated, integer-keyed table instead, and in frequently accessed table keeps only the reference to the blob field holding table.
>
> See the Interaction Log Entry / Attachment table for example.
>
> Slawek

Hi Slawek,
Thanks for your reply. I understand your point, I just thought that it could be something like a SELECT statement where you can ask for the fields you need. As this seems to be predefined in NAV C/AL, there's not much anyone can do on that side.

SELECT *...

Best Answers

Answers

Categories