72
M

Extend the Query object or give possibilities to use SQLCommand DotNet interoperability

Mark Boon on 6/17/2019 12:38:55 PM
In the current NAV 2018 on-premises we make use of SQL Command to produce high performing queries and more flexible queries than the current NAV Table and Query objects allow us.
Typical TSQL commands we use are
Like statement
More sophisticated OR statements
inner join
cross apply
union
Date formula's
Intermediate results in TempTables.
Join over different companies
If we want the current functionality written 100% using C/AL code, we have super bad performance, because it will end up in more and more sub queries and nested loops.
Category: Development
STATUS DETAILS
Declined
Ideas Administrator

Business Central Team (administrator)

Thank you for your feedback. We are not considering the suggestion at this time due to the following reason: 
1) for security reasons, we cannot give free access to the database. 
2) for performance and stability reasons, we need to be able to control the joins sent to the database

What we are doing instead in every release is to invest in making the Business Central data stack better and more performant.

Sincerely,

Kennie Nybo Pontoppidan

PM, Microsoft

Comments

M

M

RE: Extend the Query object or give possibilities to use SQLCommand DotNet interoperability

Ron Ketterling on 7/2/2019 1:54:10 PM

Poor performance is exacerbated in the SaaS model. Need to use SQL optimized queries wherever possible. Noticed selecting a serial number in Item Reclass Journal takes several seconds.

Category: Development