Table vs. Query | Performance Battle

Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
edited 2013-09-10 in NAV Tips & Tricks
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

1768.Query1.png

I use this Query in a Codeunit along with the Table variable itsself.

5516.Query2.png

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…

6278.Query3.png

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.

Caching

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!

Read the orriginal post at:

http://markbrummel.wordpress.com/2013/0 ... ce-battle/
Sign In or Register to comment.