In this chapter, we have defined a query and we have also written C/AL code to get the exact same results as the query; these are two different ways of performing the exact same action. But wouldn't it be great if we could compare their execution times to know how fast queries are?

We have put together in a single codeunit both the C/AL codes defined in the section C/AL code under Where to use queries. We have created a function called QueryFunction() in which we have copy/pasted the code corresponding to the execution of the query and a function called CALFunction() in which we have copy/pasted the code corresponding to the C/AL code that emulates the behavior of the query.

Before and after the call to each function, we retrieve the current system time. We will then compare ending time versus initial time to know the execution time of every function.

The code in the OnRun() trigger of the codeunit is as follows:

CALStartingTime := CURRENTDATETIME;
CALFunction;
CALEndingTime := CURRENTDATETIME;
CALCounter := Counter;

CLEAR(Counter);
QueryStartingTime := CURRENTDATETIME;
QueryFunction;
QueryEndingTime := CURRENTDATETIME;
QueryCounter := Counter;

MESSAGE('C/AL Code was executed in %1 returning %2 records' +
        'Query was executed in %3 returning %4 records',
        CALEndingTime - CALStartingTime,CALCounter,
        QueryEndingTime - QueryStartingTime,QueryCounter);

We have used a database with only six thousand records on the Item Ledger Entry table. It is a really small database. This table has millions of records in many real databases. Even with such a small database, the difference in execution time of C/AL code and the query is huge.

More than 14 seconds for the C/AL code and just one hundred and forty one milliseconds for the query.

That really points out that queries are good data readers. That's why they are meant to be "The Dynamics NAV reading object" of the future.

One of the things that can explain that time difference is that a regular loop in C/AL retrieves all the record fields in every looped record, even if only a few fields are needed in the loop, while queries only retrieve the fields that are really needed.

In the query, three tables were used, and only a few fields for each table:

Nine different fields were retrieved and used in the query.

In C/AL code, the same tables were used, but all their fields were retrieved:

Even if only nine fields were used, 403 were retrieved in every loop.

You can try out this example in your own databases. Both the query and the codeunit to run the query and the C/AL code are available to download. Import them in to your databases and check the results yourself.