Executing direct SQL Statements

(0) Share

Report

Posted on by Community Member

Hey...

are there any reasons why I should NOT use direct SQL statements? I mean, sometimes I just want to access one simple field of a table and the "normal" statement selects every field of a table. I could reduce the network load due to returning the single field instead of all columns of a table. Or does it make no sense due to some other reasons?!

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (3)

Verified answer

Vilmos Kintera 46,149 on at

Like (1)

Report

RE: Executing direct SQL Statements

Using direct SQL statements is a horrible idea.

Your concerns on network packets for larger cursor size and roundtrips from SQL are valid, that is exactly why you would get a Best Practice deviation error if you enable the AX best practice checks for Errors and Warnings in the Compiler settings, and compile your code for a select * from table; line.

Generally, you would always want to avoid an asterisk select or full joins between tables - always use exists/notexists joins for multiple datasources for filtering purposes, and provide a field list for selects and inner joins on the data that you are actually working with.

You would only get the values from the selected fields back, nothing else.

One very important difference between a typical T-SQL statement and AX statements translated into SQL is that AX kernel makes parametrized versions of the query, meaning when you execute the same query twice, SQL Server will already have that Query Execution Plan available even if you provide different filtering criteria in the where clause, so it does not have to compute a new query plan. Parametrization of SQL statements is an excellent performance booster, which you would not typically write for a regular qucik data pull in T-SQL statements.

Was this reply helpful? Yes No
Verified answer

guk1964 10,886 on at

Like (1)

Report

RE: Executing direct SQL Statements

Generally better to query a view of the table than to query direct.

Many of the oob reports are in SSRS and the underlying queries for those can be used- you will often find that what you need is already brought into the rdp layer.

When you query a table – even WITH (NOLOCK) – you take out a schema stability lock. No one else can change that table or its indexes until your query is finished. That isn't a big deal until you need to drop an index, but it is when you can’t because people are constantly querying a table, and they think there’s no overhead as long as they use WITH (NOLOCK).

Read about– one reason why READ COMMITTED SNAPSHOT ISOLATION is the best option of Ax. . It gets you consistent data with less blocking hassles.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (1)

Report

RE: Executing direct SQL Statements

No, it will give you empty value. Easiest way to check is to run SQL server profiler and check SQL statements created by AX.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Executing direct SQL Statements

SalesLine saleLine;

select itemId, inventDimId from salesLine;

Are you sure, that the statement will just retrieve the two fields "itemID" and "inventDimId" instead of all of them?

Info(salesline.otherfield);

will probably also work right?

Was this reply helpful? Yes No
Verified answer

Mea_ 60,284 on at

Like (2)

Report
RE: Executing direct SQL Statements

Hey PeterDetzner,

That is a good list of reasons why not to do direct SQL dev.goshoom.net/.../why-not-to-touch-ax-db-directly

You can specify fields you want to select using x++ as well, just do

select itemId, inventDimId from salesLine;

instead of

select salesline;

Please note that you can have different behavior in AX related to caching, so for example, if you have EntireTable cache AX selects all the records from table to cache them irrespective of fields specified but it's done to increase performance.

Was this reply helpful? Yes No