Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 305 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Table vs. Query | Performance Battle
Marije_Brummel
Member, Moderators Design Patterns Posts: 4,262
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.
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/
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.
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/
0