NAV 2013 ile yapılan en iyi 5 SQL Server İyileştirmeleri

Top 5 SQL Server Improvements in NAV 2013

by Vjekoslav Babic on June 21, 2012

Performance is one of those things you can’t get enough of and NAV is one of those systems where an extra operation per second is always welcome. Yesterday, during the Expert Panel at the NAV day of the Decisions Spring conference, there was a question: is there any improvement in how NAV 2013 works on SQL Server.
And the answer is: oh yeah!
As a matter of fact, everything is new and improved.
Jörg has already posted an overview of the news of NAV on SQL Server in his last blog post, but I still think there’s room for a couple of more words on the really amazing palette of news and improvements.

As I said, the SQL Server improvements are plenty. Here’s the list of the top 5 technical improvements that rock my boat.
1. Cursors are gone
If there was a single thing that was killing performance in NAV, that was server-side cursors. The burden on SQL Server, especially in critical multi-user environments was tremendous, and I’ve seen server monsters crawling under pressure. The cursors are replaced with MARS (Multiple Active Result Sets), which basically take the browsing through recordset chore away from the SQL and assign it to the NST.
2. Caching
Apart from MARS, another killer improvement is the caching. Most of data access operations are cached on the NST, which results in a considerable reduction in the number of SQL Server calls. Now, caching alone is a great improvement, but caching + MARS is a winner.
Try profiling a simple thing, such as this:

IF Cust.FINDSET THEN

  REPEAT

  UNTIL Cust.NEXT = 0;

Run it a couple of times in a row. Under NAV 2013, you get a single SELECT against the SQL Server, then nothing else. The iteration happens on the NST, and every consecutive call to the same stuff does everything on the NST. Try that under NAV 2009, and the profiler goes bananas.
3. SIFTs
There are several improvements in how NAV 2013 handles SIFTs. First – you don’t have to explicitly declare SIFT fields on keys. You can do CALCFIELDS and CALCSUMS on any decimal field, regardless of the structure of keys on the source table. And SQL simply calculates the value. This relieves SQL from maintaining too many indexed views. Yes, I know, it also slows the read operations slightly, but did I mention the caching? Oh, sorry, I have. There.
Another improvement is that you can include the SIFT fields into the SQL statement, and get the SIFTs with the same single SELECT statement that NST issues against SQL. You do this with the SETAUTOCALCFIELDS statement which you call on a record variable just before you FIND or FINDSET the records.
Compare these two in the profiler, and it’s clear right away:
a) with CALCFIELDS

IF Cust.FINDSET THEN

  REPEAT

    // Balance is not calculated, we have to do it manually

    Cust.CALCFIELDS(Balance);

  UNTIL Cust.NEXT = 0;

b) with SETAUTOCALCFIELDS

Cust.SETAUTOCALCFIELDS(Balance);

IF Cust.FINDSET THEN

  REPEAT

    // No need for CALCFIELDS, Balance is returned already

  UNTIL Cust.NEXT = 0;

With the option a, whenever you hit the CALCFIELDS, the NST obeys and fetches the sum. With the option b, there is a single SELECT statement, which already includes the OUTER APPLY clause, which calculates the SUM for each row retrieved.
Pretty cool stuff.
4. ADO.NET
The whole shebang is now run on ADO.NET, instead of OLEDB/ODBC that it was before. There are plenty of benefits of that, performance included.
ADO.NET streamlines deployment and administration, increases performance, reduces the number of SQL connections (Jörg has explained some drawbacks of this access, but I think generally that this is a good thing), reduces the memory consumption, and maybe a couple other things.
5. Unicode
I’ve already blogged about this, Jörg has also mentioned this, so I won’t play the same tune yet another time. NAV is now Unicode, which allows you to store characters in any language, at the same time.
Unfortunately, Unicode is not as Unicode as I’d truly love it to be, because the object captions remain tied to the chosen database collation (yes, you still need to choose this). That practically means that while you’ll be able to store characters from any alphabet, your RTC user interface will remain limited to a single character set.
Wrap up
So, to wrap it up, there is a lot of new things, bigger or smaller, that have been changed and that warrant better performance, or user experience, or both.
You may notice that I didn’t mention queries. Yes, they are a mind-boggling improvement over previous versions, but they are simply a completely new feature, not something that NAV had, and now has better than before. My list here is the list of tweaks and tune-ups that take those things that we are used to have to a new level altogether. Queries? Well, they are out of this world, but their true power is yet to come – when (I’m kind of sure it’s about “when”, not “if”) we’ll be able to use them as sources for pages or reports.

http://navigateintosuccess.com/blog/top-5-sql-server-improvements-in-nav-2013