So you go into Visual Studio Code, fire up the terminal, run npm start and suddenly you see these ugly warning all over the place, when no code was changed and it had worked before. WTH?!

  And of course, the first thing you do is google the error "there are multiple modules with names that only differ in casing" and click on the first StackOverflow link found and you find the exact problem that you have. But the leading answer was completely misleading for me.

  Here is my scenario: I have a folder that is a clone of a Git repo. I had added another folder with a completely new React application to the same repo, meaning I have to open Visual Studio Code in the main folder, but then change directory in the terminal before I can run commands like npm start. And what I did was do a simple cd myappfolder, like I would normally do and noticed in passing and immediately dismissed that the path in the terminal is now shown as MainFolder/myappfolder and not MainFolder/MyAppFolder as it is on the disk. And that was the exact issue! All I had to do was cd ../MyAppFolder and the annoying warning disappeared.

  To be fair, that's actually the second answer for the SO question, but it did make me waste a few minutes looking at import statements. Lesson learned: when you change directory from the Code terminal, use the Tab autocomplete feature to get disk paths with their actual capitalization!

  And it's clear that this ridiculous issue has caused others a lot of grief, too, as the same answer was rewarded with +50 SO points by someone. Me, being a cheap bastard, I am not doing that.

So you are working on a React app and, to test it, you repeatedly run npm start in the Powershell terminal window of Visual Studio Code. Only you have a browser open on the application and don't want to open another one whenever you restart the server. Or perhaps you have a default browser that is not optimal.

The solution is to set the environment variable BROWSER to either "none" or "chrome" or whatever browser you need. In order to set it in the same Powershell terminal, use $Env:BROWSER="none". Now running npm start will not open a new browser window/tab.

More details here: Advanced Configuration

  So you started working on a React application with Typescript and, after running npx create-react-app someapplicationname --template typescript you executed npm run start and Internet Explorer 11 opened up with a blank page and an unexpected colon issue in the console. Heh, that's not a pun! You click on the error and you see that somehow the rendered website is using Javascript modules which, of course, are not supported by Internet Explorer.

  Why, oh why, would the default browser be set to Internet Explorer? Because you might be working for a company that is high in paranoia and low in technical competence and the only way its house of cards security would work is using obsolete bug ridden hackable systems based on old technology. This post is not about solving corporate culture, though.

  Don't overthink it! Just follow these steps in the Visual Studio Code terminal or an admin command prompt in the folder of your application and never think about it again:

  • make sure you kill the server if it is running (Ctrl-C, then Y in the terminal where you ran npm run start)
  • run npm i fast-text-encoding in the terminal
  • edit index.tsx and add these lines at the very beginning:
    import 'react-app-polyfill/ie11';
    import 'react-app-polyfill/stable';
    import 'fast-text-encoding/text';
  • edit package.json and add the value "ie 11" in the browserlist/development array (or you might want to replace the entire browserlist value with a simple array containing ["ie 11"])
  • from the left side expand the node_modules folder and delete the .cache folder (Shift-Right click to avoid deleting to Recycle bin)
  • run npm run start in the terminal again
  • make sure that the page in Internet Explorer 11 is also completely refreshed by pressing Ctrl-F5

  Now the page should work.

  More on this on the React issue page IE11 support doesn't work in dev mode, even after adding all polyfills and enabling ie11 support #8197

  Many people, including myself, automatically think of "the client is always right" when talking capitalism, but that's not correct. In fact, capitalism is defined as "an economic and political system in which a country's trade and industry are controlled by private owners for profit, rather than by the state"; profit is the only goal or driver of the system, even for different systems, which might be controlled by the state, for example. Every social or legislative characteristic of a particular political system is just a patch that tries to fix from outside what is obviously a ridiculously simplistic concept.

  Even so, we still cling to the idea that we are "served" by companies, like we are some sort of aristocrats being catered for by legions of servants. There is a logic to that, as when a company stops catering to their clientele, they are supposed to lose it. But that's just an illusion. In fact, this only happens if some very specific conditions are met:

  1. the client is aware of the bad service, meaning:
    • they know what they're supposed to get
    • they know what they are getting
  2. there is an alternative to the service, meaning:
    • there is at least another company able to meet the requirements
    • the company is accessible to the client
      • the cost of access is reasonable
      • the client is allowed to access the competitor
    • the client knows the competitor exists
  3. the client wants to get better service, meaning:
    • there isn't too small of a difference between various services (subjective perception that all companies are the same)
    • the effort of changing the service is not too large (subjective perception of effort)
    • the emotions of the client do not bind them to the company (subjective perception of the company)

  Now, all of the points above require not only effort, but persistent effort. One needs to get the necessary knowledge and then keeping up to date, avoiding misdirection and obfuscation, then make decisions and then take action. But even so...

  A company is usually a client of other companies. In my job I am usually hired by companies who do management of people for other companies that need software, with any number of intermediaries and internal chains of command inside each. One might think that because the end client is paying for the entire chain, we should all care about what they want. But we do not! And here are a few of the reasons:

  1. the client does NOT know what they want, therefore they will never be aware of what they're supposed to get
  2. the client has spent considerable effort in creating a relationship between them and the company that provides them with the software, therefore the effort of changing to another provider is quite forbidding
  3. the client has spent considerable effort in creating a relationship with just one company, because they don't want to handle ANY of the responsibilities that company is supposed to handle, therefore they are not in contact with competitors
  4. the chain of command inside the client is made up of people who have personal connections with the company in question, so changing to another provider would be in their detriment, therefore the client is not allowed to change to another provider
  5. the company itself is not providing a better service because it doesn't have to, for the same profit; their competitors think alike, so there is absolutely no reason to change
  6. there is an intermediary system to measure productivity: instead of getting paid for value, the company gets paid for hours worked, for example
  7. a smaller nimbler company might rise to provide the same service for less money or a better service for the same amount, but there is point 4. as well as the perception that smaller companies are riskier
  8. a company might want to change, but be unable to because it depends on other companies or it lacks the necessary competency
  9. one might be competent inside of a company, but if the company is big enough, they get promoted to other posts until they reach a position were they are not competent enough to be promoted
  10. clients themselves prefer to cut costs than get better service

  This happens at every level from the top - where you feel you might be, to the bottom - where you actually are. And guess what? When you realize the same effort and care that you expect from others should be coming from you, you quickly find reasons not to provide any of them:

  1. my job is boring, why should I do better?
  2. my boss is an ass, why should I do better?
  3. my clients are idiots, why should I do better?
  4. I never meet my clients, that someone else's job, why should I do better?
  5. whenever I tried to change something, someone shut me down because they are better positioned than me, why should I do better?
  6. I am getting paid by the hour, so working faster means less money, why should I do better?
  7. most of the money from the client remains with the entire chain of people over me and I get the scraps, why should I do better?
  8. my job is not important for me, it's just a means to support my actual life, why should I do better?

  This may appear as a pyramid of interests where you are just a cog in the machine or whatever, but it is not, it's a full circle. You get what you give. The client and the company and the least competent employee is always you. There are no other species of creatures that fill any of these positions. Your boss is human, your employee is human, your client is human. In Romania there is the saying that you're stealing your own hat.

  But it's OK. You can't do any better, so why should you do better? You are only human, so why be more human? You are a tiny cog in the machine so why grow larger? Do whatever you want, I don't care.

A few years ago I wrote an article about using RealProxy to intercept methods and properties calls in order to log them. It was only for .NET Framework and suggested you inherit all intercepted classes from MarshalByRefObject. This one is a companion piece that shows how interception can be done in a more general way and without the need for MarshalByRefObject.

To do that I am going to give you two versions of the same class, one for .NET Framework and one for .NET Core which can be used like this:

//Initial code:
IInterface obj = new Implementation();

//Interceptor added:
IInterface obj = new Implementation();
var interceptor = new MyInterceptor<IInterface>();
obj = interceptor.Decorate(obj);

//Interceptor class (every method there is optional):
public class MyInterceptor<T> : ClassInterceptor<T>
{
    protected override void OnInvoked(MethodInfo methodInfo, object[] args, object result)
    {
        // do something when the method or property call ended succesfully
    }

    protected override void OnInvoking(MethodInfo methodInfo, object[] args)
    {
        // do something before the method or property call is invoked
    }

    protected override void OnException(MethodInfo methodInfo, object[] args, Exception exception)
    {
        // do something when a method or property call throws an exception
    }
}

This code would be the same for .NET Framework or Core. The difference is in the ClassInterceptor code and the only restriction is that your class has to implement an interface for the methods and properties intercepted.

Here is the .NET Framework code:

public abstract class ClassInterceptor<TInterface> : RealProxy
{
    private object _decorated;

    public ClassInterceptor()
        : base(typeof(TInterface))
    {
    }

    public TInterface Decorate<TImplementation>(TImplementation decorated)
        where TImplementation:TInterface
    {
        _decorated = decorated;
        return (TInterface)GetTransparentProxy();
    }

    public override IMessage Invoke(IMessage msg)
    {
        var methodCall = msg as IMethodCallMessage;
        var methodInfo = methodCall.MethodBase as MethodInfo;
        OnInvoking(methodInfo,methodCall.Args);
        object result;
        try
        {
            result = methodInfo.Invoke(_decorated, methodCall.InArgs);
        } catch(Exception ex)
        {
            OnException(methodInfo, methodCall.Args, ex);
            throw;
        }
        OnInvoked(methodInfo, methodCall.Args, result);
        return new ReturnMessage(result, null, 0, methodCall.LogicalCallContext, methodCall);
    }

    protected virtual void OnException(MethodInfo methodInfo, object[] args, Exception exception) { }
    protected virtual void OnInvoked(MethodInfo methodInfo, object[] args, object result) { }
    protected virtual void OnInvoking(MethodInfo methodInfo, object[] args) { }
}

In it, we use the power of RealProxy to create a transparent proxy. For Core we use DispatchProxy, which is the .NET Core replacement from Microsoft. Here is the code:

public abstract class ClassInterceptor<TInterface> : DispatchProxy
{
    private object _decorated;

    public ClassInterceptor() : base()
    {
    }

    public TInterface Decorate<TImplementation>(TImplementation decorated)
        where TImplementation : TInterface
    {
        var proxy = typeof(DispatchProxy)
                    .GetMethod("Create")
                    .MakeGenericMethod(typeof(TInterface),GetType())
                    .Invoke(null,Array.Empty<object>())
            as ClassInterceptor<TInterface>;

        proxy._decorated = decorated;

        return (TInterface)(object)proxy;
    }

    protected override object Invoke(MethodInfo targetMethod, object[] args)
    {
        OnInvoking(targetMethod,args);
        try
        {
            var result = targetMethod.Invoke(_decorated, args);
            OnInvoked(targetMethod, args,result);
            return result;
        }
        catch (TargetInvocationException exc)
        {
            OnException(targetMethod, args, exc);
            throw exc.InnerException;
        }
    }


    protected virtual void OnException(MethodInfo methodInfo, object[] args, Exception exception) { }
    protected virtual void OnInvoked(MethodInfo methodInfo, object[] args, object result) { }
    protected virtual void OnInvoking(MethodInfo methodInfo, object[] args) { }
}

DispatchProxy is a weird little class. Look how it generates an object which can be cast simultaneously to T or Class<T>!

There are many other things one can do to improve this class:

  • the base class could make the distinction between a method call and a property call. In the latter case the MethodInfo object will have IsSpecialName true and start with set_ or get_
  • for async/await scenarios and not only, the result of a method would be a Task<T> and if you want to log the result you should check for that, await the task, get the result, then log it. So this class could make this functionality available out of the box
  • support for Dependency Injection scenarios could also be added as the perfect place to use interception is when you register an interface-implementation pair. An extension method like container.RegisterSingletonWithLogging could be used instead of container.RegisterSingleton, by registering a factory which replaces the implementation with a logging proxy

I hope this helps!

P.S. Here is an article helping to migrate from RealProxy to DispatchProxy: Migrating RealProxy Usage to DispatchProxy

Definition

So, the task at hand is the subject of a common interview question: Implement an algorithm to get all valid (opened and closed) combinations of n pairs of parentheses. This means that for n=1 there is only one solution: "()". "((" or "))" are not valid, for 2 you will have "(())" and "()()" and so on. The question is trying to test how the interviewee handles recursion and what is commonly called backtracking. But as usual, there's more than one way to skin a cat, although for the life of me I can't see why you would want to do that.

The solutions here will be in C# and the expected result is an enumeration of strings containing open and closed parentheses. The code can be easily translated into other languages, including Javascript (ECMAScript 2015 introduced iterators and generator functions), but that's left to the reader. Let's begin.

Analysis

Before we solve any problem we need to analyse it and see what are the constraints and the expected results. In this case there are several observations that can be made:

  • the resulting strings will be of length n*2 (n pairs)
  • they will contain n '(' characters and n ')' characters
  • they cannot start with a ')' or end in a '('
  • in order to generate such a string, we can start with a smaller string to which we add '(' or ')'
  • we cannot add a ')' if there isn't at least one corresponding unclosed '(' 
  • if we add a '(' we need to have enough characters left to close the parenthesis, so the number of unclosed parentheses cannot exceed the characters left to fill
  • we could count the open and closed parentheses, but we only care about the number of unclosed ones, so instead of "closed" and "open" values, we can only use "open" to represent unclosed parentheses

Let's go for some variables and calculations:

  • n = number of pairs
  • open = number of unclosed parentheses in a string
  • open cannot be negative
  • one cannot add ')' if open = 0
  • one cannot add '(' if open >= n*2 - substring.length

Recursive solution

A simple implementation of these requirements can done with recursion:

public IEnumerable<string> GenerateRecursive(int n, string root = "", int open = 0)
{
    // substring is long enough, return it and exit
    if (root.Length == n * 2)
    {
        yield return root;
        yield break;
    }
    // if we can add '(' to existing substring, continue the process with the result
    if (open < n * 2 - root.Length)
    {
        // if only C# could just 'yield IteratorFunction()' this would look sleeker
        foreach (var s in GenerateRecursive(n, root + "(", open + 1))
        {
            yield return s;
        }
    }
    // if we can add ')' to existing substring, continue the process with the result
    if (open > 0)
    {
        foreach (var s in GenerateRecursive(n, root + ")", open - 1))
        {
            yield return s;
        }
    }
}

However, every time you see recursion you have to ask yourself: could n be large enough to cause a stack overflow? For example this fails for n=3000. The nice thing about this method, though, is that it can be limited to the number of items you want to see. For example var firstTen = GenerateRecursive(1000).Take(10) is very fast, as the generation is depth first and only computes the first ten values and exits.

So, can we replace the recursion with iteration?

Iterative solution

In order to do thing iteratively, we need to store the results of the previous step and use them in the current step. This means breadth first generation, which has its own problems. Let's see some code:

public IEnumerable<string> GenerateIteration(int n)
{
    // using named tuples to store the unclosed parentheses count with the substring
    var results = new List<(string Value,int Open)>() { ("",0) };
    for (int i = 0; i < n*2; i++)
    {
        // each step we compute the list of new strings from the list in the previous step
        var newResults = new List<(string Value, int Open)>();
        foreach (var (Value, Open) in results)
        {
            if (Open < n * 2 - Value.Length)
            {
                newResults.Add((Value + "(", Open + 1));
            }
            if (Open > 0)
            {
                newResults.Add((Value + ")", Open - 1));
            }
        }
        results = newResults;
    }
    return results.Select(r=>r.Value);
}

It's pretty sleek, but if you try something like var firstTen = GenerateRecursive(1000).Take(10) now it will take forever since all combinations of 1000 parentheses need to be computed and stored before taking the first 10! BTW, we can write this much nicer with LINQ, but be careful at the gotcha in the comment:

public IEnumerable<string> GenerateLinq(int n)
{
    // looks much nicer with LINQ
    IEnumerable<(string Value, int Open)> results = new[] { ("", 0) };
    for (var i = 0; i < n * 2; i++)
    {
        results =
            results
                .Where(r => r.Open < n * 2 - r.Value.Length)
                .Select(r => (Value: r.Value + "(", Open: r.Open + 1))
            .Concat(results
                .Where(r => r.Open > 0)
                .Select(r => (Value: r.Value + ")", Open: r.Open - 1))
            );  // but if you do not end this with a .ToList()
                // it will generate a huge expression that then will be evaluated at runtime! Oops!
    }
    return results.Select(r => r.Value);
}

But can't we do better? One is going to stack overflow, the other memory overflow and the last one kind of does both.

Incremental solution

They did say this requires an incremental solution, right? So why don't we take this literally? '(' and ')' are like 0 and 1, as ')' must always follow a '('. If you view a parenthesis string as a binary number, then all possible combinations can be encoded as numbers. This means that we could conceivably write a very fast function that would compute all possible combinations using bit operations, maybe even special processor instructions that count bits and so on. However, this would work only for n<=32 or 64 depending on the processor architecture and we don't want to get into that. But we can still use the concept!

If a string represents a fictional number, then you can start with the smallest one, increment it and check for validity. If you combine the incremental operation with the validity check you don't need to go through 2n operations to get the result. It doesn't use any memory except the current string and it is depth first generation. The best of both worlds! Let's see some code:

public IEnumerable<string> GenerateIncrement(int n)
{
    // the starting point is n open parentheses and n closing ones
    // we use the same array of characters to generate the strings we display
    var arr = (new string('(', n) + new string(')', n)).ToCharArray();
    // iteration will stop when incrementation reaches the "maximum" valid combination
    var success = true;
    while (success)
    {
        yield return new string(arr);
        success = Increment(arr, n);
    }
}

private bool Increment(char[] arr, int n)
{
    // we begin with a valid string, which means there are no unclosed parentheses
    var open = 0;
    // we start from the end of the string
    for (var i = arr.Length - 1; i >= 0; i--)
    {
        // ')' is equivalent to a 1. To "increment" this string we need to go to the previous position
        // incrementing 01 in binary results in 10
        if (arr[i] == ')')
        {
            open++;
            continue;
        }

        // '(' is equivalent to a 0. We will turn it into a ')' to increment it,
        // but only if there are unclosed parentheses to close
        open--;
        if (open == 0) continue;

        // we 'increment' the value
        arr[i] = ')';
        // now we need to reset the rest of the array
        var k = n - (open + i) / 2;
        // as many opening parenthesis as possible
        for (var j = i + 1; j < i + 1 + k; j++)
        {
            arr[j] = '(';
        }
        // the rest are closing parentheses
        for (var j = i + 1 + k; j < n * 2; j++)
        {
            arr[j] = ')';
        }
        return true;
    }
    // if we reached this point it means we got to a maximum
    return false;
}

Now doing GenerateIncrement(1000000).Take(10) took more to display the results than to actually compute them.

More solutions

As this is a classic interview question, there are a billion solutions to it at LeetCode. Yet the purpose of interview questions is to find out how one thinks, not what the solution of the problem actually is. I hope this helps.

Intro

  When talking Dependency Injection, if a class implementing Interface1 needs an implementation of Interface2 in its constructor and the implementation for Interface2 needs an implementation of Interface1 you get a circular dependency error. This could be fixed, though, by providing lazy proxy implementations, which would also fix issues with resources getting allocated too early and other similar issues.  Now, theoretically this is horrible. Yet in practice one meets this situation a lot. This post will attempt to clarify why this happens and how practice may be different from theory.

Problem definition

  Let's start with defining what an interface is. Wikipedia says it's a shared boundary between components. In the context of dependency injection you often hear about the Single Responsibility Principle, which stipulates that a class (and by extension an interface) should only do one thing. Yet even in this case, the implementation for any of the Facade, Bridge, Decorator, Proxy and Adapter software patterns would do only one thing: to proxy, merge or split the functionality of other components, regardless of how many and how complex they are. Going to the other extreme, one could create an interface for every conceivable method, thus eliminating the need for circular dependencies and also loading code that is not yet needed. And then there are the humans writing the code. When you need a service to provide the physical location of the application you would call it ILocationService and when you want to compute the distance between two places you would use the same, because it's about locations, right? Having an ILocationProviderService and an ILocationDistanceCalculator feels like overkill. Imagine trying to determine if a functionality regarding locations is already implemented and going through all the ILocation... interfaces to find out, then having to create a new interface when you write the code for it and spending sleepless nights wondering if you named things right (and if you need to invalidate their cache).

  In other words, depending on context, an interface can be anything, as arbitrarily complex as the components it separates. They could contain methods that are required by other components together with methods that require other components. If you have more such interfaces, you might end up with a circular dependency in the instantiation phase even if the execution flow would not have this problem. Let's take a silly example.

  We have a LocationService and a TimeService. One handles points in space the other moments in time. And let's say we have the entire history of someone's movements. You could get a location based on the time provided (GetLocation) or get the time based on a provided location (GetTime). Now, the input from the user is text, so we need the LocationService and the TimeService to translate that text into actual points in space and moments in time, so GetLocation would use an ITimeService, while GetTime would use an ILocationService. You start the program and you get the circular dependency error. I told you it would be silly. Anyway, you can split any of the services into ITimeParser and ITimeManager or whatever, you can create a new interface called ITextParser, there are a myriad refactoring solutions. But what if you don't have the luxury to refactor and why do you even need to do anything? Surely if you call GetLocation you only need to parse the time, you never call GetTime, and the other way around.

Solution?

  A possible solution is to only actually get the dependency implementation when you use it. Instead of providing the actual implementation for the interface you need, you provide a lazy proxy. Here is an example of a generic (and lazy one liner) LazyProxy implementation:

public class LazyProxy<TInterface>:Lazy<TInterface>
{
    public LazyProxy(IServiceProvider serviceProvider) : base(() => serviceProvider.GetService<TInterface>()) { }
}

  Problem solved, right? LocationService would ask for a LazyProxy<ITimeService> implementation, GetLocation would do _lazyTimeService.Value.ParseTime(input) which would instantiate a TimeService for the first time, which would ask for a LazyProxy<ILocationService> and in GetTime it would use _lazyLocationService.Value.ParseLocation(input) which would get the existing instance of LocationService (if it's registered as Singleton). Imagine either of these services would have needed a lot of other dependencies.

  Now, that's what called a "leaky abstraction". You are hiding the complexity of instantiating and caching a service (and all of its dependencies) until you actually use it. Then you might get an error, when the actual shit hits the actual fan. I do believe that the term "leaky" might have originated from the aforementioned idiom. Yuck, right? It's when the abstraction leaked the complexity that lies beneath.

  There are a number of reasons why you shouldn't do it. Let's get through them.

Criticism

  The most obvious one is that you could do better. The design in the simple and at the same time contrived example above is flawed because each of the services are doing two very separate things: providing a value based on a parameter and interpreting text input. If parsing is a necessary functionality of your application, then why not design an ITextParser interface that both services would use? And if your case is that sometimes you instantiate a class to use one set of functions and sometimes to use another set of functions, maybe you should split that up into two. However, in real life situations you might not have full control over the code, you might not have the resources to refactor the code. Hell, you might be neck deep in spaghetti code! Have you ever worked in one of those house of cards companies where you are not allowed to touch any piece of code for fear it would all crash?

  The next issue is that you would push the detection for a possible bug to a particular point of the execution of your code. You would generate a Heisenbug, a bug that gets reproduced inconsistently. How appropriate this would have been if an IMomentumService were used as well. Developers love Heisenbugs, as the time for their resolution can vary wildly and they would be forced to actually use what they code. Oh, the humanity! Yet, the only problem you would detect early is the cycle in the dependency graph, which is more of a design issue anyway. A bug in the implementation would still be detected when you try to use it. 

  One other issue that this pattern would solve should not be there in the first place: heavy resource use in constructors. Constructors should only construct, obviously, leaving other mechanisms to handle the use of external resources. But here is the snag: if you buy into this requirement for constructors you already use leaky abstractions. And again, you might not be able to change the constructors.

  Consider, though, the way this pattern works. It is based on the fact that no matter when you request the instantiation of a class, you would have a ready implementation of IServiceProvider. The fact that the service locator mechanism exists is already lazy instantiation on the GetService method. In fact, this lazy injection pattern is itself a constructor dependency injection abstraction of the service provider pattern. You could just as well do var timeService = _serviceProvider.GetService<ITimeService>() inside your GetLocation method and it would do the exact same thing. So this is another reason why you should not do it: mixing the metaphors. But hey! If you have read this far, you know that I love mixing those suckers up!

Conclusion

  In conclusion, I cannot recommend this solution if you have others like refactoring available. But in a pinch it might work. Let me know what you think!

  BTW, this issue has been also discussed on Stack Overflow, where there are some interesting answers. 

  MultiSelect is a Kendo UI control that transforms a select element into a nice dropdown with text filtering which allows the selection of multiple items. This is how you use the same control to write values directly in the list, something akin to the Outlook address bar functionality.

  Long story short: the control exposes some events like: 'filtering','open','close' and 'change'. In the filtering event, which is fired by someone writing or pasting text in order to filter the list of items, we dynamically create a list item that holds that value, so that the user can just press Enter and enter the value in the list. The code also allows for a custom transformation function, so for example someone could enter "1,2,3" and it would be translated into three values 1, 2 and 3 instead of an item with the value "1,2,3". On the close and change events we clear the items in the list that have not been selected. This means you cannot use this code as is to show an autocomplete list and also add dynamic values, but it is easy to tweak for that purpose.

  In order to use it, instead of doing $(selector).kendoMultiSelect(options), just use $(selector).kendoDynamicMultiSelect(options). Here is the code:

$.fn.kendoDynamicMultiSelect = function (options) {
  var multiSelect = $(this).kendoMultiSelect(options).getKendoMultiSelect();

  multiSelect.bind('filtering', function (ev) {
    var val = ev.filter && ev.filter.value;
    if (!val) return;
    
    var dataSource = ev.sender.dataSource;
    var items = dataSource.data();
    
    // if there is an existing item in the list, don't create a new one
    var existingItem = items.filter(function (i) {
      return i.value == val;
    })[0];
    if (existingItem) return;

    // find or create the item that will hold the current filter value
    var inputItem = items.filter(function (i) {
      return i.isInput;
    })[0];
    if (!inputItem) {
      inputItem = dataSource.insert(0, { isInput: true });
      // when inserting a value the input gets cleared in some situations
      // so set it back 
      ev.sender.input.val(ev.filter.value);
    }
    inputItem.value = val;
  });

  // cleans input items and also applies an optional value transformation function
  var updateValues = function (ev) {
    var values = ev.sender.value();
    if (typeof options.valueTransformationFunction === 'function') {
      // for example split comma separated values
      values = options.valueTransformationFunction(values);
    }

    var dataSource = ev.sender.dataSource;
    var items = dataSource.data();
    for (var i = 0; i < items.length; i++) {
      var item = items[i];
      item.shouldBeKept = false;
    }

    // add items for existing values
    for (var i = 0; i < values.length; i++) {
      var value = values[i];
	    
      var item = items.filter(function (i) { return i.value == value; })[0];
      if (!item) {
        item = dataSource.add({ value: value });
      }
      item.isInput = false;
      item.shouldBeKept = true;
    }

    ev.sender.value(values);

    // delete all others
    for (var i = 0; i < items.length; i++) {
      var item = items[i];
      if (!item.shouldBeKept) {
        dataSource.remove(item);
      }
    }
  };

  multiSelect.bind('change', updateValues);
  multiSelect.bind('close', updateValues);
};

I kind of copied this code by hand and tried it on another computer. If you find any bugs, let me know. Also, I know this is old time tech, but they use it in my company and I couldn't find this functionality by googling it, so here it is.

I hope it helps.

OK, so I played a little with SQL and I found an interesting flow for analysing queries. It uses the SET STATISTICS PROFILE functionality, but the results of this are usually hard to read and handle in any meaningful way. There are applications that help out with this, but this blog post is trying to show you a method that doesn't need any extra software (for when you are working for a paranoid company that doesn't allow you to install what you need to do your work, for example).

This works in the query itself, so no need of any extra tool except SQL Server Management Studio and Excel:

  1. Add SET STATISTICS PROFILE OFF at the start of the query (because you don’t need to profile the setup)
  2. Add SET STATISTICS PROFILE ON just before the SELECT that you want to optimize
  3. Clear cache and stats - this is optional, but good practice. There are multiple ways of doing this and it depends on your environment and preferences, so I am not covering this here.
  4. Execute the query -> In the query results you will get the results of the query, but also the profiling statistics of the query execution, also in table form
  5. Copy the entire statistics table with headers and insert it into a new Excel sheet
  6. Add a new column right after Parent, call it IsLeaf
  7. Fill the IsLeaf column with a formula to see if the value in NodeId exists in the Parent column
    1. Write "=COUNTIF($F$2:$F$10000,E2)=0" as the first value of the column
    2. Keep pressing Shift, then press End and Down arrow (and release Shift) – you should have the entire column selected
    3. Press Ctrl-D
  8. Select the header row of the table
  9. Click on "Sort and Filter"
  10. Select "Filter"
  11. Click on a random cell, click on "Sort and Filter" again
  12. Click on "Custom sort"
  13. Select TotalTreeSubcost and "From largest to smallest"
  14. Now click on the filter on the IsLeaf column and filter on value TRUE (only the leaves)

You should now have the rows of the final tree branch nodes, ordered descending by the cost to the query.

Here you can look at the IO cost, CPU cost and Rows columns to find the places you need to work on. These values need to be as small as possible.

I hope this helps.

  Update: more analysis shows that the change was not because of the OR clauses, but because of some debug OPTIONs that I had used. This post is thus wrong.

Original post:

  So I had this stored procedure that would calculate counts from a table, based on a specific column which was also indexed. Something like this:

SELECT Code, COUNT(*) as Nr 
FROM MyTable

  The code would take the result of this query and only use the counts for some of the codes, let's say 'A', 'B' and 'C'. There was also a large number of instructions that had a NULL Code. So the obvious optimizations was:

SELECT Code, COUNT(*) as Nr 
FROM MyTable
WHERE Code IN ('A','B','C')

  And it worked, however I was getting this annoying warning in the execution plan: "Operator used tempdb to spill data during execution". What the hell was that?

  Long story short, I found a very nice SO answer that explains it: SQL Server cardinality estimation can use two types of statistics to guess how many rows will get through a predicate filter:

  • about the column on average using the density vector
  • about particular values for that column using the histogram

When a literal is used, the cardinality estimator can search for that literal in the histogram. When a parameter is used, its value is not evaluated until after cardinality estimation, so the CE has to use column averages in the density vector.

  Probably, behind the scenes, ('A','B','C') is treated as a variable, so it only uses the density vector. Also, because how the IN operator is implemented, what happens to the query next is very different than replacing it with a bunch of ORs:

SELECT Code, COUNT(*) as Nr 
FROM MyTable
WHERE (Code='A' OR Code='B' OR Code='C')

  Not only the warning disappeared, but the execution time was greatly reduced!  

  You see, the query here is simplified a lot, but in real life it was part of a larger one, including complicated joins and multiple conditions. With an IN clause, the execution plan would only show me one query, containing multiple joins and covering all of the rows returned. By using OR clauses, the execution plan would show me three different queries, one for each code.

  This means that in certain situations, this strategy might not work, especially if the conditions are not disjunct and have rows that meet multiple ones. I am also astonished that for such a simple IN clause, the engine did not know to translate it automatically into a series of ORs! My intent as a developer is clear and the implementation should just take that and turn it into the most effective query possible.

  I usually tell people to avoid using OR clauses (and instead try to use ANDs) or query on values that are different (try for equality instead) or using NOT IN. And the reason is again the execution plan and how you cannot prove a general negative claim. Even so, I've always assumed that IN will work as a series or ORs. My reasoning was that, in case of an OR, the engine would have to do something like an expensive DISTINCT operation, something like this: 

SELECT *
FROM MyTable
WHERE (Code='A' OR Code='B')

-- the above should equate to
SELECT *
FROM MyTable
WHERE Code='A'
UNION -- not a disjunct union so engine would have to eliminate duplicates
SELECT *
FROM MyTable
WHERE Code='B'

-- therefore an optimal query is
SELECT *
FROM MyTable
WHERE Code='A'
UNION ALL - disjunct union
SELECT *
FROM MyTable
WHERE Code='B'
-- assuming that there are no rows that meet both conditions (code A and code B)

  In this case, however, SQL did manage to understand that the conditions were disjunct so it split the work into three, correctly using the index and each of them being quite efficient.

  I learned something today!

  I was working on a grid display and I had to properly sort date columns. The value provided was not a datetime, but instead a string like "20 Jan 2017" or "01 Feb 2020". Obviously sorting them alphabetically would not be very useful. So what I did was implement a custom sorting function that first parsed the strings as dates, then compared them. Easy enough, particularly since the Date object in Javascript has a Parse function that understands this format.

  The problem came with a string with the value "01 Jan 0001" which appeared randomly among the existing values. I first thought it was an error being thrown somewhere, or that it would not parse this string or even that it would be an overflow. It was none of that. Instead, it was about handling the year part.

  A little context first:

Date.parse('01 Jan 0001') //978300000000
new Date(0) //Thu Jan 01 1970 00:00:00

Date.parse('01 Jan 1950') //-631159200000
new Date(Date.parse('01 Jan 1950')) //Sun Jan 01 1950 00:00:00

Date.parse('31 Dec 49 23:59:59.999') //2524600799999
Date.parse('1 Jan 50 00:00:00.000') //-631159200000

new Date(Date.parse('01 Jan 0001')) //Mon Jan 01 2001 00:00:00

  The first two lines almost had me convinced Javascript does not handle dates lower than 1970. The next two lines disproved that and made me think it was a case of numerical overflow. The next two demonstrated it was not so. Now look closely at the last line. What? 2001?

  The problem was with the handling of years that are numerically smaller than 50. The parser assumes we used a two digit year and translates it into Date.parse('01 Jan 01') which would be 2001. We get a glimpse into how it works, too, because everything between 50 and 99 would be translated into 19xx and everything between 00 and 49 is considered 20xx.

  Note that .NET does not have this problem, correctly making the difference between a 2 digit and 4 digit year.

  Hope it helps people.

Update: due to popular demand, I've added Tyrion as a Github repo.

Intro

  Discord is something I have only vaguely heard about and when a friend told me he used it for chat with friends, I installed it, too. I was pleasantly surprised to see it is a very usable and free chat application, which combines feature of IRC, other messenger applications and a bit of Slack. You can create servers and add channels to them, for example, where you can determine the rights of people and so on. What sets Discord apart from anything, perhaps other than Slack, is the level of "integration", the ability to programatically interact with it. So I create a "bot", a program which stays active and responds to user chat messages and can take action. This post is about how to do that.

  Before you implement a bot you obviously need:

  All of this has been done to death and you can follow the links above to learn how to do it. Before we continue, a little something that might not be obvious: you can edit a Discord chat invite so that it never expires, as it is the one on this blog now.

Writing code

One can write a bot in a multitude of programming languages, but I am a .NET dev, so Discord.NET it is. Note that this is an "unofficial" library, so it may not (and it is not) completely in sync with all the options that the Discord API provides. One such feature, for example, is multiple attachments to a message. But I digress.

Since my blog is also written in ASP.NET Core, it made sense to add the bot code to that. Also, in order to make it all clean code, I will use dependency injection as much as possible and use the built-in system for commands, even if it is quite rudimentary.

Step 1 - making dependencies available

We are going to need these dependencies:

  • DiscordSocketClient - the client to connect to Discord
  • CommandService - the service managing commands
  • BotSettings - a class used to hold settings and configuration
  • BotService - the bot itself, which we are going to make implement IHostedService so we can add it as a hosted service in ASP.Net

In order to keep things separated, I will not add all of this in Startup, instead encapsulating them into a Bootstrap class:

public static class Bootstrap
{
    public static IWebHostBuilder UseDiscordBot(this IWebHostBuilder builder)
    {
        return builder.ConfigureServices(services =>
        {
            services
                .AddSingleton<BotSettings>()
                .AddSingleton<DiscordSocketClient>()
                .AddSingleton<CommandService>()
                .AddHostedService<BotService>();
        });
    }
}

This allows me to add the bot simply in CreateWebHostBuilder as: 

WebHost.CreateDefaultBuilder(args)
   .UseStartup<Startup>()
   .UseKestrel(a => a.AddServerHeader = false)
   .UseDiscordBot();

Step 2 - the settings

The BotSettings class will be used not only to hold information, but also communicate it between classes. Each Discord chat bot needs an access token to connect and we can add that as a configuration value in appsettings.config:

{
  ...
  "DiscordBot": {
	"Token":"<the token value>"
  },
  ...
}
public class BotSettings
{
    public BotSettings(IConfiguration config, IHostingEnvironment hostingEnvironment)
    {
        Token = config.GetValue<string>("DiscordBot:Token");
        RootPath = hostingEnvironment.WebRootPath;
        BotEnabled = true;
    }

    public string Token { get; }
    public string RootPath { get; }
    public bool BotEnabled { get; set; }
}

As you can see, no fancy class for getting the config, nor do we use IOptions or anything like that. We only need to get the token value once, let's keep it simple. I've added the RootPath because you might want to use it to access files on the local file system. The other property is a setting for enabling or disabling the functionality of the bot.

Step 3 - the bot skeleton

Here is the skeleton for a bot. It doesn't change much outside the MessageReceived and CommandReceived code.

public class BotService : IHostedService, IDisposable
{
    private readonly DiscordSocketClient _client;
    private readonly CommandService _commandService;
    private readonly IServiceProvider _services;
    private readonly BotSettings _settings;

    public BotService(DiscordSocketClient client,
        CommandService commandService,
        IServiceProvider services,
        BotSettings settings)
    {
        _client = client;
        _commandService = commandService;
        _services = services;
        _settings = settings;
    }

    // The hosted service has started
    public async Task StartAsync(CancellationToken cancellationToken)
    {
        _client.Ready += Ready;
        _client.MessageReceived += MessageReceived;
        _commandService.CommandExecuted += CommandExecuted;
        _client.Log += Log;
        _commandService.Log += Log;
        // look for classes implementing ModuleBase to load commands from
        await _commandService.AddModulesAsync(Assembly.GetEntryAssembly(), _services);
        // log in to Discord, using the provided token
        await _client.LoginAsync(TokenType.Bot, _settings.Token);
        // start bot
        await _client.StartAsync();
    }

    // logging
    private async Task Log(LogMessage arg)
    {
        // do some logging
    }

    // bot has connected and it's ready to work
    private async Task Ready()
    {
        // some random stuff you can do once the bot is online: 

        // set status to online
        await _client.SetStatusAsync(UserStatus.Online);
        // Discord started as a game chat service, so it has the option to show what games you are playing
        // Here the bot will display "Playing dead" while listening
        await _client.SetGameAsync("dead", "https://siderite.dev", ActivityType.Listening);
    }
    private async Task MessageReceived(SocketMessage msg)
    {
        // message retrieved
    }
    private async Task CommandExecuted(Optional<CommandInfo> command, ICommandContext context, IResult result)
    {
        // a command execution was attempted
    }

    // the hosted service is stopping
    public async Task StopAsync(CancellationToken cancellationToken)
    {
        await _client.SetGameAsync(null);
        await _client.SetStatusAsync(UserStatus.Offline);
        await _client.StopAsync();
        _client.Log -= Log;
        _client.Ready -= Ready;
        _client.MessageReceived -= MessageReceived;
        _commandService.Log -= Log;
        _commandService.CommandExecuted -= CommandExecuted;
    }


    public void Dispose()
    {
        _client?.Dispose();
    }
}

Step 4 - adding commands

In order to add commands to the bot, you must do the following:

  • create a class to inherit from ModuleBase
  • add public methods that are decorated with the CommandAttribute
  • don't forget to call commandService.AddModuleAsync like above

Here is an example of an enable/disable command class:

public class BotCommands:ModuleBase
{
    private readonly BotSettings _settings;

    public BotCommands(BotSettings settings)
    {
        _settings = settings;
    }

    [Command("bot")]
    public async Task Bot([Remainder]string rest)
    {
        if (string.Equals(rest, "enable",StringComparison.OrdinalIgnoreCase))
        {
            _settings.BotEnabled = true;
        }
        if (string.Equals(rest, "disable", StringComparison.OrdinalIgnoreCase))
        {
            _settings.BotEnabled = false;
        }
        await this.Context.Channel.SendMessageAsync("Bot is "
            + (_settings.BotEnabled ? "enabled" : "disabled"));
    }
}

When the bot command will be issued, then the state of the bot will be sent as a message to the chat. If the parameter of the command is enable or disable, the state will also be changed accordingly.

Yet, in order for this command to work, we need to add code to the bot MessageReceived method: 

private async Task MessageReceived(SocketMessage msg)
{
    // do not process bot messages or system messages
    if (msg.Author.IsBot || msg.Source != MessageSource.User) return;
    // only process this type of message
    var message = msg as SocketUserMessage;
    if (message == null) return;
    // match the message if it starts with R2D2
    var match = Regex.Match(message.Content, @"^\s*R2D2\s+", RegexOptions.IgnoreCase);
    int? pos = null;
    if (match.Success)
    {
        // this is an R2D2 command, everything after the match is the command text
        pos = match.Length;
    }
    else if (message.Channel is IPrivateChannel)
    {
        // this is a command sent directly to the private channel of the bot, 
        // don't expect to start with R2D2 at all, just execute it
        pos = 0;
    }
    if (pos.HasValue)
    {
        // this is a command, execute it
        var context = new SocketCommandContext(_client, message);
        await _commandService.ExecuteAsync(context, message.Content.Substring(pos.Value), _services);
    }
    else
    {
        // processing of messages that are not commands
        if (_settings.BotEnabled)
        {
            // if the bot is enabled and people are talking about it, show an image and say "beep beep"
            if (message.Content.Contains("R2D2",StringComparison.OrdinalIgnoreCase))
            {
                await message.Channel.SendFileAsync(_settings.RootPath + "/img/R2D2.gif", "Beep beep!", true);
            }
        }
    }
}

This code will forward commands to the command service if message starts with R2D2, else, if bot is enabled, will send replies with the R2D2 picture and saying beep beep to messages that contain R2D2.

Step 5 - handling command results

Command execution may end in one of three states:

  • command is not recognized
  • command has failed
  • command has succeeded

Here is a CommandExecuted event handler that takes these into account:

private async Task CommandExecuted(Optional<CommandInfo> command, ICommandContext context, IResult result)
{
    // if a command isn't found
    if (!command.IsSpecified)
    {
        await context.Message.AddReactionAsync(new Emoji("🤨")); // eyebrow raised emoji
        return;
    }

    // log failure to the console 
    if (!result.IsSuccess)
    {
        await Log(new LogMessage(LogSeverity.Error, nameof(CommandExecuted), $"Error: {result.ErrorReason}"));
        return;
    }
    // react to message
    await context.Message.AddReactionAsync(new Emoji("🤖")); // robot emoji
}

Note that the command info object does not expose a result value, other than success and failure.

Conclusion

This post has shown you how to create a Discord chat bot in .NET and add it to an ASP.Net Core web site as a hosted service. You may see the result by joining this blog's chat and giving commands to Tyr, the chat's bot:

  • play
  • fart
  • use metric or imperial units in messages
  • use Yahoo Messenger emoticons in messages
  • whatever else I will add in it when I get in the mood :)

  For a more in depth exploration of the concept, read Towards generic high performance sorting algorithms

Sorting

  Consider QuickSort, an algorithm that uses a divide and conquer strategy to sort efficiently and the favourite in computer implementations.

  It consists of three steps, applied recursively:

  1. find a pivot value
  2. reorder the input array so that all values smaller than the pivot are followed by values larger or equal to it (this is called Partitioning)
  3. apply the algorithm to each part of the array, before and after the pivot

  QuickSort is considered generic, meaning it can sort any type of item, assuming the user provides a comparison function between any two items. A comparison function has the same specific format: compare(item1,item2) returning -1, 0 or 1 depending on whether item1 is smaller, equal or larger than item2, respectively. This formalization of the function lends more credence to the idea that QuickSort is a generic sorting algorithm.

  Multiple optimizations have been proposed for this algorithm, including using insertion sort for small enough array segments, different ways of choosing the pivot, etc., yet the biggest problem was always the optimal way in which to partition the data. The original algorithm chose the pivot as the last value in the input array and the average complexity was O(n log n), but worse case scenario was O(n^2), when the array was already sorted and the pivot was the largest value. Without extra information you can never find the optimal partitioning schema (which would be to choose the median value of all items in the array segment you are sorting).

  But what if we turn QuickSort on its head? Instead of providing a formalized comparison function and fumbling to get the best partition, why not provide a partitioning function (from which a comparison function is trivial to obtain)? This would allow us to use the so called distribution based sorting algorithms (as opposed to comparison based ones) like Radix, BurstSort, etc, which have a complexity of O(n) in a generic way!

  My proposal for a formal signature of a partitioning function is partitionKey(item,level) returning a byte (0-255) and the sorting algorithm would receive this function and a maximum level value as parameters.

  Let's see a trivial example: an array of values [23,1,31,0,5,26,15] using a partition function that would return digits of the numbers. You would use it like sort(arr,partFunc,2) because the values are two digits numbers. Let's explore a naive Radix sorting:

  • assign 256 buckets for each possible value of the partition function result and start at the maximum (least significant) level
  • put each item in its bucket for the current level
  • concatenate the buckets
  • decrease the level and repeat the process

Concretely:

  • level 1: 23 -> bucket 3, 1 -> 1, 31 -> 1, 0 -> 0, 5 -> 5, 26 -> 6, 15 -> 5 results in [0,1,31,5,15,6]
  • level 0: 0 -> 0, 1 -> 0, 31 -> 3, 5 -> 0, 15 -> 1, 6 -> 0 results in [0,1,5,6,15,31]

Array sorted. Complexity is O(n * k) where k is 2 in this case and depends on the type of values we have, not on the number of items to be sorted!

  More complex distribution sorting algorithms, like BurstSort, optimize their function by using a normal QuickSort in small enough buckets. But QuickSort still requires an item comparison function. Well, it is easy to infer: if partFunc(item1,0) is smaller or larger than partFunc(item2,0) then item1 is smaller or larger than item2. If the partition function values are equal, then increase the level and compare partFunc(item1,1) to partFunc(item2,1).

  In short, any distribution sorting algorithm can be used in a generic way provided the user gives it a partitioning function with a formalized signature and a maximum level for its application.

  Let's see some example partitioning functions for various data types:

  • integers from 0 to N - maximum level is log256(N) and the partition function will return the bytes in the integer from the most significant to the least
    • ex: 65534 (0xFFFE) would return 255 (0xFF) for level 0 and 254 (0xFE) for level 1. 26 would return 0 and 26 for the same levels.
  • integers from -N to N - similarly, one could return 0 or 1 for level 0 if the number is negative or positive or return the bytes of the equivalent positive numbers from 0 to 2N 
  • strings that have a maximum length of N - maximum level would be N and the partition function would return the value of the character at the same position as the level
    • ex: 'ABC' would return 65, 66 and 67 for levels 0,1,2.
  • decimal or floating point or real values - more math intensive functions can be found, but a naive one would be to use a string partitioning function on the values turned to text with a fixed number of digits before and after the decimal separator.
  • date and time - easy to turn these into integers, but also one could just return year, month, day, hour, minute, second, etc based on the level
  • tuples of any of the types above - return the partition values for the first item, then the second and so on and add their maximum levels

  One does not have to invent these functions, they would be provided to the user based on standard types in code factories. Yet even these code factories will be able to encode more information about the data to be sorted than mere comparison functions. Stuff like the minimum and maximum value can be computed by going through all the values in the array to be sorted, but why do it if the user already has this information, for example.

  Assuming one cannot find a fixed length to the values to be sorted on, like real values or strings of any length, consider this type of sorting as a first step to order the array as much as possible, then using something like insertion or bubble sort on the result.

Finding a value or computing distinct values

  As an additional positive side effect, there are other processes on lists of items that are considered generic because they use a formalized form function as a parameter. Often found cases include finding the index of an item in a list equal to a given value (thus determining if the value exists in a list) and getting the distinct values from an array. They use an equality function as a parameter which is formalized as returning true or false. Of course, a comparison function could be used, depending on if its result is 0 or not, but a partitioning function can also be used to determine equality, if all of the bytes returned on all of the levels are equal.

  But there is more. The format of the partition function can be used to create a hash set of the values, thus reducing the complexity of the search for a value from O(n) to O(log n) and that of getting distinct values from O(n^2) to O(n log n)!

  In short, all operations on lists of items can be brought together and optimized by using the same format for the function that makes them "generic": that of a partitioning function.

Conclusion

  As you can see, I am rather proud of the concepts I've explained here. Preliminary tests in Javascript show a 20 fold improvement in performance for ten million items when using RadixSort over the default sort. I would really love feedback from someone who researches algorithms and can even test these assumptions under benchmark settings. Them being complex as they are, I will probably write multiple posts on the subject, trying to split it (partition it?) into easily digestible bits

 The concept of using a generic partitioning function format for operations on collections is a theoretical one at the moment. I would love to collaborate with people to get this to production level code, perhaps taking into account advanced concepts like minimizing cache misses and parallelism, not only the theoretical complexity.

 More info and details at Towards generic high performance sorting algorithms

Intro

  There is a saying that the novice will write code that works, without thinking of anything else, the expert will come and rewrite that code according to good practices and the master will rewrite it so that it works again, thinking of everything. It applies particularly well to SQL. Sometimes good and well tried best practices fail in specific cases and one must guide themselves either by precise measurements of by narrow rules that take decades to learn.

  If you ever wondered why some SQL queries are very slow or how to write complex SQL stored procedures without them reaching sentience and behaving unpredictably, this post might help. I am not a master myself, but I will share some quick and dirty ways of writing, then checking your SQL code.

Some master rules

  First of all, some debunking of best practices that make unreasonable assumptions at scale:

  1. If you have to extract data based on many parameters, then add them as WHERE or ON clauses and the SQL engine will know how to handle it.

    For small queries and for well designed databases, that is correct. The SQL server engine is attempting to create execution plans for these parameter combinations and reuse them in the future on other executions. However, when the number of parameters increases, the number of possible parameter combinations increases exponentially. The execution optimization should not take more than the execution itself, so the engine if just choosing one of the existing plans which appears more similar to the parameters given. Sometimes this results in an abysmal performance.

    There are two solutions:

    The quick and dirty one is to add OPTION (RECOMPILE) to the parameterized SELECT query. This will tell the engine to always ignore existing execution plans. With SQL 2016 there is a new feature called Query Store plus a graphical interface that explores execution plans, so one can choose which ones are good and which ones are bad. If you have the option, you might manually force an execution plan on specific queries, as well. But I don't recommend this because it is a brittle and nonintuitive solution. You need a DBA to make sure the associations are correct and maintained properly.

    The better one, to my own surprise, is to use dynamic SQL. In other words, if you have 20 parameters to your stored procedure, with only some getting used at any time (think an Advanced Search page), create an SQL string only with the parameters that are set, then execute it.

    My assumption has always been that the SQL engine will do this for me if I use queries like WHERE (@param IS NULL OR <some condition with @param>). I was disappointed to learn that it does not always do that. Be warned, though, that most of the time multiple query parameters are optimized by running several operations in parallel, which is best!

  2. If you query on a column or another column, an OR clause will be optimal. 

    Think of something like this: You have a table with two account columns AccId and AccId2. You want to query a lot on an account parameter @accountId and you have added an index on each column.

    At this time the more readable option, and for small queries readability is always preferable to performance improvement, is WHERE AccId=@accountId OR AccId2=@accountId. But how would the indexes be used here, in this OR clause? First the engine will have to find all entries with the correct AccId, then again find entries with the correct AccId2, but only the entries that have not been found in the first search.

    First of all, SQL will not do this very well when the WHERE clause is very complex. Second of all, even if it did it perfectly, if you know there is no overlap, or you don't care or you can use a DISTINCT further on to eliminate duplicates, then it is more effective to have two SELECT queries, one for AccId and the other for AccId2 that you UNION ALL afterwards.

    My assumption has always been that the SQL engine will do this automatically. I was quite astounded to hear it was not true. Also, I may be wrong, because different SQL engines and their multitude of versions, compounded with the vast array of configuration options for both engine and any database, behave quite differently. Remember the parallelism optimization, as well.

  3. Temporary tables as slow, use table variables instead.

    Now that is just simple logic, right? A temporary table uses disk while a table variable uses memory. The second has to be faster, right? In the vast majority of cases this will be true. It all depends (a verb used a lot in SQL circles) on what you do with it.

    Using a temporary table might first of all be optimized by the engine to not use the disk at all. Second, temporary tables have statistics, while table variables do not. If you want the SQL engine to do its magic without your input, you might just have to use a temporary table.

  4. A large query that does everything is better than small queries that I combine later on.

    This is a more common misconception than the others. The optimizations the SQL engine does work best on smaller queries, as I've already discussed above, so if a large query can be split into two simpler ones, the engine will be more likely able to find the best way of executing each. However, this only applies if the two queries are completely independent. If they are related, the engine might find the perfect way of getting the data in a query that combines them all.

    Again, it depends. One other scenario is when you try to DELETE or UPDATE a lot of rows. SQL is always "logging" the changes that it does on the off chance that the user cancels the query and whatever incomplete work has been done has to be undone. With large amounts of data, this results into large log files and slow performance. One common solution is to do it in batches, using UPDATE (TOP 10000) or something similar inside a WHILE loop. Note that while this solves the log performance issue, it adds a little bit of overhead for each executed UPDATE

  5. If I have an index on a DATETIME column and I want to check the records in a certain day, I can use CAST or CONVERT.

    That is just a bonus rule, but I've met the problem recently. The general rule is that you should never perform calculations on columns inside WHERE clauses. So instead of WHERE CAST(DateColumn as DATE)=@date use WHERE DateColumn>=@date AND DateColumn<DATEADD(DAY,1,@date). The calculation is done (once) on the parameters given to the query, not on every value of DateColumn. Also, indexes are now used.

Optimizing queries for dummies

So how does one determine if one of these rules apply to their case? "Complex query" might mean anything. Executing a query multiple times results in very different results based on how the engine is caching the data or computing execution plans.

A lot of what I am going to say can be performed using SQL commands, as well. Someone might want to use direct commands inside their own tool to monitor and manage performance of SQL queries. But what I am going to show you uses the SQL Management Studio and, better still, not that horrid Execution Plan chart that often crashes SSMS and it is hard to visualize for anything that the most simple queries. Downside? You will need SQL Management Studio 2014 or higher.

There are two buttons in the SSMS menu. One is "Include Actual Execution Plan" which generates an ugly and sometimes broken chart of the execution. The other one is "Include Live Query Statistics" which seems to be doing the same, only in real time. However, the magic happens when both are enabled. In the Results tab you will get not only the query results, but also tabular data about the execution performance. It is amazingly useful, as you get a table per each intermediary query, for example if you have a stored procedure that executes several queries in a row, you get a table for each.

Even more importantly, it seems that using these options will start the execution without any cached data or execution plans. Running it several times gives consistent execution times.

In the LiveQuery tables, the values we are interested about are, in order of importance, EstimateIO, EstimateCPU and Rows.

EstimateIO is telling us how much of the disk was used. The disk is the slowest part of a computer, especially when multiple processes are running queries at the same time. Your objective is to minimize that value. Luckily, on the same row, we get data about the substatement that generated that row, which parameters were used, which index was used etc. This blog is not about how to fix every single scenario, but only on how to determine where the biggest problems lie.

EstimateCPU is saying how much processing power was used. Most of the time this is very small, as complex calculations should not be performed in queries anyway, but sometimes a large value here shows a fault in the design of the query.

Finally, Rows. It is best to minimize the value here, too, but it is not always possible. For example a COUNT(*) will show a Clustered Index Scan with Rows equal to the row count in the table. That doesn't cause any performance problems. However, if your query is supposed to get 100 rows and somewhere in the Live Query table there is a value of several millions, you might have used a join without the correct ON clause parameters or something like that.

Demo

Let's see some examples of this. I have a Main table, with columns ID BIGINT, Random1 INT, Random2 NVARCHAR(100) and Random3 CHAR(10) with one million rows. Then an Ind table, with columns ID BIGINT, Qfr CHAR(4) and ValInd BIGINT with 10000 rows. The ID table is common with the Main table ID column and the Qfr column has only three possible values: AMT, QTY, Sum.

Here is a demo on how this would work:

DECLARE @r1 INT = 1300000
DECLARE @r2 NVARCHAR(100) = 'a'
DECLARE @r3 CHAR(10) = 'A'
DECLARE @qfr CHAR(4) = 'AMT'
DECLARE @val BIGINT = 500000

DECLARE @r1e INT = 1600000
DECLARE @r2e NVARCHAR(100) = 'z'
DECLARE @r3e CHAR(10)='Z'
DECLARE @vale BIGINT = 600000

SELECT *
FROM Main m
INNER JOIN Ind i
ON m.ID=i.ID
WHERE (@r1 IS NULL OR m.Random1>=@r1)
  AND (@r2 IS NULL OR m.Random2>=@r2)
  AND (@r3 IS NULL OR m.Random3>=@r3)
  AND (@val IS NULL OR i.ValInd>=@val)
  AND (@r1e IS NULL OR m.Random1<=@r1e)
  AND (@r2e IS NULL OR m.Random2<=@r2e)
  AND (@r3e IS NULL OR m.Random3<=@r3e)
  AND (@vale IS NULL OR i.ValInd<=@vale)
  AND (@qfr IS NULL OR i.Qfr=@qfr)

I have used 9 parameters, each with their own values, to limit the number of rows I get. The Live Query result is:

You can see that the EstimateIO values are non-zero only on the Clustered Index Scans, one for each table. Where is how the StmtText looks like: "|--Clustered Index Scan(OBJECT:([Test].[dbo].[Ind].[PK__Ind__DEBF89006F996CA8] AS [i]),  WHERE:(([@val] IS NULL OR [Test].[dbo].[Ind].[ValInd] as [i].[ValInd]>=[@val]) AND ([@vale] IS NULL OR [Test].[dbo].[Ind].[ValInd] as [i].[ValInd]<=[@vale]) AND ([@qfr] IS NULL OR [Test].[dbo].[Ind].[Qfr] as [i].[Qfr]=[@qfr])) ORDERED FORWARD)".

This is a silly case, but you can see that the @parameter IS NULL type of query condition has not been removed, even when parameter is clearly not null.

Let's change the values of the parameters:

DECLARE @r1 INT = 300000
DECLARE @r2 NVARCHAR(100) = NULL
DECLARE @r3 CHAR(10) = NULL
DECLARE @qfr CHAR(4) = NULL
DECLARE @val BIGINT = NULL

DECLARE @r1e INT = 600000
DECLARE @r2e NVARCHAR(100) = NULL
DECLARE @r3e CHAR(10)=NULL
DECLARE @vale BIGINT = NULL

Now the Live Query result is:

Same thing! 5.0 and 7.2

Now, let's do the same thing with dynamic SQL. It's a little more annoying, mostly because of the parameter syntax, but check it out:

DECLARE @sql NVARCHAR(Max)

DECLARE @r1 INT = 300000
DECLARE @r2 NVARCHAR(100) = NULL
DECLARE @r3 CHAR(10) = NULL
DECLARE @qfr CHAR(4) = NULL
DECLARE @val BIGINT = NULL

DECLARE @r1e INT = 600000
DECLARE @r2e NVARCHAR(100) = NULL
DECLARE @r3e CHAR(10)=NULL
DECLARE @vale BIGINT = NULL


SET @sql=N'
SELECT *
FROM Main m
INNER JOIN Ind i
ON m.ID=i.ID
WHERE 1=1 '
IF @r1 IS NOT NULL SET @sql+=' AND m.Random1>=@r1'
IF @r2 IS NOT NULL SET @sql+=' AND m.Random2>=@r2'
IF @r3 IS NOT NULL SET @sql+=' AND m.Random3>=@r3'
IF @val IS NOT NULL SET @sql+=' AND i.ValInd>=@val'
IF @r1e IS NOT NULL SET @sql+=' AND m.Random1<=@r1e'
IF @r2e IS NOT NULL SET @sql+=' AND m.Random2<=@r2e'
IF @r3e IS NOT NULL SET @sql+=' AND m.Random3<=@r3e'
IF @qfr IS NOT NULL SET @sql+=' AND i.Qfr=@qfr'
IF @vale IS NOT NULL SET @sql+=' AND i.ValInd<=@vale'

PRINT @sql

EXEC sp_executesql @sql,
  N'@r1 INT, @r2 NVARCHAR(100), @r3 CHAR(10), @qfr CHAR(4),@val BIGINT,@r1e INT, @r2e NVARCHAR(100), @r3e CHAR(10),@vale BIGINT',
  @r1,@r2,@r3,@qfr,@val,@r1e,@r2e,@r3e,@vale

Now the Live Query results are:

At first glance we have not changed much. IO is still 5.0 and 7.2. Yet there are 3 less execution steps. There is no parallelism and the query has been executed in 5 seconds, not 6. The StmtText for the same thing is now: "|--Clustered Index Scan(OBJECT:([Test].[dbo].[Ind].[PK__Ind__DEBF89006F996CA8] AS [i]), ORDERED FORWARD)". The printed SQL command is:

SELECT *
FROM Main m
INNER JOIN Ind i
ON m.ID=i.ID
WHERE 1=1  AND m.Random1>=@r1 AND m.Random1<=@r1e

Conclusion

Again, this is a silly example. But with some results anyway! In my work I have used this to get a stored procedure to work three to four times faster!

One can optimize usage of IO, CPU and Rows by adding indexes, by narrowing join conditions, by reducing the complexity of executed queries, eliminating temporary tables, partitioning existing tables, adding or removing hints, removing computation from queried columns and so many other possible methods, but they amount to nothing if you cannot measure the results of your changes.

By using Actual Execution Plan together with Live Query Statistics you get:

  • consistent execution times and disk usage
  • a clear measure of what went on with each subquery

BTW, you get the same effect if you use SET STATISTICS PROFILE ON before the query. Yet, I wrote this post with someone that doesn't want to go into extra SQL code in mind. Also, when calculating performance, it is recommended to add a DBCC FREEPROCCACHE line before execution OR add the option RECOMPILE to your query (this doesn't work on a stored procedure execution, you would have to change the SP queries to include RECOMPILE).

I wish I had some more interesting examples for you, guys, but screenshots from the workplace are not something I want to do and I don't do any complex SQL work at home. I hope this helps. 

  When I was looking at Javascript frameworks like Angular and ReactJS I kept running into these weird reducers that were used in state management mostly. It all felt so unnecessarily complicated, so I didn't look too closely into it. Today, reading some random post on dev.to, I found this simple and concise piece of code that explains it:

// simple to unit test this reducer
function maximum(max, num) { return Math.max(max, num); }

// read as: 'reduce to a maximum' 
let numbers = [5, 10, 7, -1, 2, -8, -12];
let max = numbers.reduce(maximum);

Kudos to David for the code sample.

The reducer, in this case, is a function that can be fed to the reduce function, which is known to developers in Javascript and a few other languages, but which for .NET developers it's foreign. In LINQ, we have Aggregate!

// simple to unit test this Aggregator ( :) )
Func<int, int, int> maximum = (max, num) => Math.Max(max, num);

// read as: 'reduce to a maximum' 
var numbers = new[] { 5, 10, 7, -1, 2, -8, -12 };
var max = numbers.Aggregate(maximum);

Of course, in C# Math.Max is already a reducer/Aggregator and can be used directly as a parameter to Aggregate.

I found a lot of situations where people used .reduce instead of a normal loop, which is why I almost never use Aggregate, but there are situations where this kind of syntax is very useful. One would be in functional programming or LINQ expressions that then get translated or optimized to something else before execution, like SQL code. (I don't know if Entity Framework translates Aggregate, though). Another would be where you have a bunch of reducers that can be used interchangeably.