SQL Server – Vjeko.com

When you just must COUNT, no matter what

Post published:March 9, 2017
Post comments:3 Comments
Reading time:6 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.

(more…)

The “IF COUNT = 1” Conundrum

Post published:March 8, 2017
Post comments:19 Comments
Reading time:10 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.

(more…)

Are there any records there?

Post published:March 7, 2017
Post comments:11 Comments
Reading time:5 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?

(more…)

NAV performance part 4: SQL Azure

Post published:May 7, 2016
Post comments:1 Comment
Reading time:9 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.

(more…)

NAV performance part 3: Azure VM configurations

Post published:May 7, 2016
Post comments:7 Comments
Reading time:10 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:

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:

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.

(more…)