Table vs. Query | Performance Battle
NAV2013 introduced the Query object. Frankly I think that this new object type deserves more attention from bloggers. We have been nagging Microsoft for years to deliver this and now it is there and what happens? Quietness.
I have at least two blogs around Queries. This one is the first. It is an important one because I want to blowup a myth even before it get’s the chance of becoming one.
Queries have two advantages when it comes to reading performance that tables don’t have. First you can limit the amount of data you retrieve from the server by defining only the specific columns you need and second you can join multiple tables into one query saving noumerous roundtrips to the server with our good old loopy-loopy code pattern.
But Queries can’t do what Tables can, they cannot write to the database.
And there is more that Queries cannot do. Let me explain with an example.
In my example I’ve created a realy simple query that contains a few fields from the Item Ledger Entry table
I use this Query in a Codeunit along with the Table variable itsself.
This is a very stupid piece of code that reads the same Item Ledger Entry four times. Twice from the table and twice with the query.
And yes, we get four messageboxes with the Document No.
So what is the clue?
Let’s look at what NAV Sends to SQL Server with Profiler…
Please note that NAV only issues three SQL Statements, not four. And I can tell you a Little secret, if you run this again, it will only issue two.
The two statements that are the same are the Query statements. True, they are more lightweight than the Table Query but unless you have a covering index it will read the entire record in SQL anyway.
Another thing that NAV2013 introduces is Service Tier caching. All the users on the same service tier share the same reading cache. So if I read customer 10000 and another user wants this data it is not read from SQL Server but from cache. NAV Cache.
Unfortunately this only works for Tables, not for Queries.
So remember this when you design your solution. If you read the same data over and over again, a Query might be overkill.
If you want my opinion you should only use queries when joining multiple tables. Never ever use queries in a single table scenario. The only thing you do is adding an extra layer of complexity to your solution and add extra objectcosts for your customer.
Thanks Waldo for the tip!