SQL Server

When you just must COUNT, no matter what

  • Reading time:5 mins read

I started with blogging about COUNT = 0 situation. Then I followed it with COUNT = 1. So, let’s do a COUNT = 2 today. No, no, I am just kidding, don’t worry 🙂 But I still have to do one more COUNT post, one that will cover all other COUNT situations.

Believe it or not, sometimes you really, honestly, do need to perform an actual COUNT. You just need to know exactly how many of rows there are. It may be 0, 1 or 75 or whatever, but you need to know exactly how many. These situations are few and far between, but every once in a long while this is what you need.

So, let’s give the COUNT function one last kick from another angle.

Continue ReadingWhen you just must COUNT, no matter what

The “IF COUNT = 1” Conundrum

  • Reading time:9 mins read

If you haven’t read my yesterday’s post, then do that first.

Good. At this point you are extremely smart because you know that you should never use COUNT = 0 but should use ISEMPTY instead.

However, what if:

IF SomeTable.COUNT = 1 THEN
  DoSomething();

Well, if this is what you need, then that’s what you need. And that’s exactly what we needed in the team a few days ago.

Continue ReadingThe “IF COUNT = 1” Conundrum

Are there any records there?

  • Reading time:4 mins read

It’s been a while that I haven’t blogged, and my queue grows inversely proportional to the amount of time I have available for blogging, so let me do a short series of easy stuff, simply to take it off the list.

This is not about new features, crazy new tips and tricks or anything of the sort. It’s just a couple short lessons on performance and how to reduce your carbon footprint and make the planet last longer.

It’s about how to properly ask the database: are there any records there?

Continue ReadingAre there any records there?

NAV performance part 4: SQL Azure

  • Reading time:7 mins read

SQL Azure is a very interesting service. It’s as interesting as it is misunderstood both in terms of how exactly it works, and what it’s intended to be used for.

First of all, it’s not really the same thing as SQL Server that you install on your box, virtual or physical. It certainly provides the same functionality, and from functional perspective most of things you can do with SQL Server, you can do with SQL Azure. But it behaves in so many different ways that you can’t truly compare them side by side.

Another thing is what SQL Azure was designed for. It’s designed for massive cloud workloads where concurrency is more important than sheer speed. And in that respect it is just brilliant. However, to get most out of it, you have to write and optimize your database access code specifically to take advantage of its features and behavior, otherwise, you simply get performance that can be qualified as mediocre at best.

What happens when you put NAV on SQL Azure? Well, that’s something that you certainly can do – Microsoft does it as well. The thing is – it works. It leaves a bit to be desired if you intend to run heavy processing, but my firm conviction – having tested it and having gone medieval on it with my tests.

Let’s take a look.

Continue ReadingNAV performance part 4: SQL Azure

NAV performance part 3: Azure VM configurations

  • Reading time:7 mins read

While NAV has traditionally mostly been a domestic animal, it’s now getting pushed more and more cloudwards. A large number of partners and customers are considering Azure VMs as their platform of choice as it has many benefits over purchasing and running the whole show in your own basement.

So, I have tested several Azure VM configurations to show what A, D, and G tiers bring to the show, and how much those numbers next to tier letters really mean.

These were the contestants:

  • A2
  • A7
  • D2
  • DS13
  • GS3

I chose A7, DS13 and GS3 because they all have fairly similar resources:

image

The only significant difference here is that DS13 and GS3 use premium disks, while A7 uses standard disks, however there is also some difference in hardware configurations. G tier has much better processors than D tier, which has better processors than A tier.

The other two contestants are somewhat undersized and I used them to check if those configurations can cope with sustained pressure:

image

And, there is an unexpected guest in this roundup: the DS13 machine with SQL Azure P11. It’s an oversized and unbelievably expensive options, and the reason why I put it here is that it’s the only SQL Azure configuration that can remotely hope to compete with VM deployments of SQL.

Let’s see the results.

Continue ReadingNAV performance part 3: Azure VM configurations

NAV performance in various configurations

  • Reading time:8 mins read

Yes, I have been a lazy blogger lately. With so many people blogging about NAV nowadays, it’s really difficult to come up with original content all the time.

Let venture a little bit out of my .NET and JavaScript comfort zone, and hang around a more formidable beast: SQL. And as you know, SQL comes in many shapes. It comes in shape of your rack server, or in shape of your laptop. But it also comes in shape of clouds. Many clouds.

So, I decided to test performance of NAV under various configurations, including on-prem, Azure VM and SQL Azure, and share my findings with you. And while I am writing these lines, there are four machines sweating the crap out under some performance test code I recently wrote.

Continue ReadingNAV performance in various configurations

Benchmarking Results: NAV 2013 Outperforms All Previous Versions

  • Reading time:17 mins read

imageMarketing is nice as long as it matches the reality. With Microsoft Dynamics NAV 2013, Microsoft has promised a lot of improvements, but how well does NAV 2013 stand the reality test?

Apparently, outstandingly well.

Over the past two days, I have intensively tested NAV 2009 and NAV 2013 through a series of five different tests that measure different aspects of NAV data handling. My conclusion is clear: NAV 2013 is faster than any NAV you have ever seen, including the Classic client on the native database.

Continue reading to find out more about my findings and testing approach.

Continue ReadingBenchmarking Results: NAV 2013 Outperforms All Previous Versions

Top 5 SQL Server Improvements in NAV 2013

  • Reading time:5 mins read

imagePerformance 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.

Continue ReadingTop 5 SQL Server Improvements in NAV 2013

Was it necessary to kill the Classic client?

  • Reading time:4 mins read

dinosaurAbsolutely.

The world needs to move ahead. The technology advances. Who doesn’t want to keep up with the progress, stays behind. That’s how it has worked since the dawn of civilization.

I can’t know the exact reasons why Microsoft has decided to stop support for the Classic client with NAV “7”, but I can offer my view and position on why I believe this was absolutely necessary.

Continue ReadingWas it necessary to kill the Classic client?

Death of Classic (C/SIDE) Client

  • Reading time:3 mins read

tomb_guardYesterday, during a coffee break at the What’s New in Microsoft Dynamics NAV 2009 Technical for Application Consultants training in Vilnius, Lithuania (a fabulous place, by the way), a discussion arose around the destiny of the Classic (or C/SIDE) client in NAV. Some participants stated that “it’s never going to go away” because “Microsoft would not dare shutting it down”.

Unfortunately, it is going away, and quickly. Maybe it wasn’t too obvious at first, because Microsoft never actually said explicitly that “Classic client is going to be discontinued”, but if you read the latest Statement of Direction for Microsoft Dynamics NAV, it’s there.

Continue ReadingDeath of Classic (C/SIDE) Client