Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by email or phone:


+972 52-548-6969

, @ Q c

Posts: 6,434 | Comments: 47,591

filter by tags archive

Tip: Searching Stored Procedures Text

time to read 2 min | 355 words

Here is another tip for those who need to explore big SQL databases with a lot of stored procedures and quite a bit of logic. This will gives you a list of all the procedures that has a certain string in them:


      @str nvarchar(max)


select distinct name from sys.procedures procs join syscomments comments

on procs.object_id = comments.id where [text] like '%' + @str + '%'

VB.Net oddity

time to read 1 min | 51 words

Can someone explain why VB.Net has a keyword called "AddessOf" ? I would expect such a thing from a low level language, and even C# could be excused if it had this. But AdressOf seems to me like a throwback to ancient times where passing function pointers around was the norm.   

How to kill the C# Compiler

time to read 8 min | 1573 words

My legendary skills in breaking code have now reached a new level, in which I managed to break the C# Compiler. Check out this code:


public delegate void Proc();


public class CrashAndBurnAttribute : System.Attribute


    public CrashAndBurnAttribute(Proc p)

    { }



public class CrashAndBurnClient


    [CrashAndBurn(delegate { return; })]

    public void Foo()




If you try to run compile it from VS.Net you’ll get a nice crash dialog, and the result of running the compilation from the command line is:

Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.42

for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727

Copyright (C) Microsoft Corporation 2001-2005. All rights reserved.


crash.cs(11,16): error CS1706: Expression cannot contain anonymous methods

error CS0583: Internal Compiler Error (0xc0000005 at address 5A10A8E0): likely culprit is 'COMPILE'.


        An internal error has occurred in the compiler. To work around this problem, try simplifying or changing the program near the locations listed below. Locations at the top of the list are closer to the point at which the internal error occurred. Errors such as this can be reported to Microsoft by using the /errorreport option.


crash.cs(11,25): error CS0585: Internal Compiler Error: stage 'COMPILE'

crash.cs(12,14): error CS0584: Internal Compiler Error: stage 'COMPILE' symbol 'CrashAndBurnClient.Foo()'

crash.cs(12,14): error CS0584: Internal Compiler Error: stage 'COMPILE' symbol 'CrashAndBurnClient.Foo()'

crash.cs(12,14): error CS0584: Internal Compiler Error: stage 'COMPILE' symbol 'CrashAndBurnClient.Foo()'

crash.cs(9,14): error CS0584: Internal Compiler Error: stage 'COMPILE' symbol 'CrashAndBurnClient'

crash.cs: error CS0584: Internal Compiler Error: stage 'COMPILE' symbol '<global namespace>'

crash.cs: error CS0586: Internal Compiler Error: stage 'COMPILE'

error CS0587: Internal Compiler Error: stage 'COMPILE'

error CS0587: Internal Compiler Error: stage 'BEGIN'

This is actually sad, since I really wanted to be able to do pass delegates to attributes.

Performance tidbits

time to read 14 min | 2667 words

I recently had to write a lazy loaded property that picked what its value will be based on the environment it run on. Basically, I had the same old problem of keeping data for the current context only. This usually means the current thread, but in ASP.Net, it is the current request.

My solution for it was simple:



So I can store/retrieve data without caring in what environment I’m running on by simply using:


CurrentData.Items["current-db-connection"] = new SqlConnection();

I need this ability to store data that is important for the current logical execution thread (be that an ASP.Net request or a real thread). The usual method of implemention the Items property on the CurrentData class would be something like:

public static ICurrentDataStore Items




        if (items == null)

            items = CreateCurrentDataStoreForEnviornment();

        return items;




But I thought that I could get rid of the null check, this should allow the JIT to view the property as a simple accessor, so it would be just as fast as accessing a field directly. I could’ve simply instasitated the items field to the proper value based on the environment, but I wanted to mimic a real lazy loaded property. I ended up with this class:


public class DataStoreSwitcher : ICurrentDataStore


    private Action<ICurrentDataStore> set;


    public DataStoreSwitcher(Action<ICurrentDataStore> set)


        this.set = set;



    #region ICurrentDataStore Members


    public object this[object key]




            ICurrentDataStore real = EnsureCorrectDataStoreForEnvironment();

            return real[key];




            ICurrentDataStore real = EnsureCorrectDataStoreForEnvironment();

            real[key] = value;




    private ICurrentDataStore EnsureCorrectDataStoreForEnvironment()


        ICurrentDataStore real;

        if (HttpContext.Current == null)

            real = new CurrentThreadDataStore();


            real = new CurrentRequestDataStore();


        return real;






I initialize the variable like this:


private static ICurrentDataStore items =

    new DataStoreSwitcher(

    delegate(ICurrentDataStore real)


        items = real;



In this particular point, there is very little to be gain from this technqiue, and if the case was a hefty resource (choosing database connection, for instance), it can certainly be useful. Beside, I like using delegate J, and it’s a cool thing to do.


Performance tuning mystery

time to read 1 min | 197 words

I got a stored procedure that was taking an undue amount of time to run, in was about half an hour, and that was really bad. I tried looking at the execution plan and add an index to make it faster, but it wasn't that effective.

The query was an update from a nested select (which contained a rather complex condition and a couple of joins), and I couldn't really figure out how to make it better. I tried running the pieces individually, and they run reasonably fast, so it was the interactions between them that killed the performance. The execution plan showed that the joins were costing very much in terms of performance.

During my attempts to figure it out, I moved the select into a temporary table, and then updated from it, like this:

SELECT To, SUM(p.Amount) TotalAmount
INTO #TempPaymetsAmounts
FROM Payments p, Payments p2, Orders o
WHERE ComplexCondition

UPDATE PaymentsTotal
SET Total = TotalAmount
FROM #TempPaymetsAmounts
WHERE SimplerCondition

That query run in less than 10 seconds.

The only thing that I did was move the select into a temporary table, but I'm not sure why this should have such a profound effect on the performance of the query.

Code reuse, cursors and NIH, oh my!

time to read 1 min | 113 words

I recently discovered that a certain class of programmers has a really bad case of Not Invented here. It got so bad that they decided to implement JOIN and GROUP BY by using cursors. Of course, due to the nature of the problem, they were so excited by their new discovery that they immediately rushed to propogate it thorughout the code base, using none else than the tried and true method of copy-paste-change-one-charater-and-then-move-on-never-looking-back.

I won't speak of the performance of this method, but I think that you can guess that there are orders of magnitude difference between this new way of dealing with the database and the standard method of operation in the industry.

SQL Frustrations

time to read 1 min | 105 words

I knew that it was bound to happen.

I started running into highly mysterious errors in SQL Server. The strangest among them is the Missing Columns Mystery.

When I run a stored procedure that calls to other SPs, and so on, I get an error about invalid columns somewhere deep in the stack. But when I run the SP directly, it passes perfectly.

Of course that when I check it, the table has the supposedly missing columns. It drives me crazy, and I can't really get the error consistently, if I run it for a couple of time, it seems to fix itself, but I've no idea how.

Hierarchical queries with Common Table Expressions

time to read 4 min | 762 words

[Via Mitch Denny's Blog ] [Via Sql Down Under]

It's usualy hard to efficently get hierarchical data using SQL. But apperantely SQL Server can do that easily. The key to this is to create common table expression that recursively points to itself, like this:

WITH AllEmployeesOf([EmployeeId], [Name], [ManagerName])



      SELECT m.[EmployeeId], m.[Name], convert(nvarchar(50),null) as ManagerName

            FROM [Employees] m WHERE @employee_id = m.[EmployeeId]


      SELECT e.[EmployeeId], e.[Name], AllEmployeesOf.[Name] as ManagerName

            FROM [Employees] e JOIN AllEmployeesOf ON

      e.[ManagerId] = AllEmployeesOf.[EmployeeId]


SELECT * FROM AllEmployeesOf

Before this I would resort to either getting all of the rows or issuing several queries. Nice.

Put down that cursor and step away from the database, Mister!

time to read 1 min | 139 words

I'm seeing too much cursors around right now. Way too many. I also keep seeing the same code over & over again. I know that I'm in trouble because to work with this code base I need to:
  • constantly use grep (and deriatives, like AstroGrep, which is cool);
  • get a diff program that support drag & drop from the shell as well as quickly changing only one of the files. (Found WinMerge, which looks cool);
  • pronounce variable names out loud in order to understand their meaning*;
  • watch ingenious workarounds to all sorts of limitations in the number of concurrent nested corsurs in the database;
  • cut code by 70% by deciding that I don't need to verify stuff over & over again.
Argh! Excuse me while I go bang my head against the wall.

* (Hebrew words in English characters)


No future posts left, oh my!


  1. Optimizing JavaScript and solving the halting problem (2):
    18 Aug 2017 - Part II
  2. RavenDB 4.0 (12):
    14 Aug 2017 - Maintaining transaction boundary integrity in a distributed cluster
  3. Public Service Announcement (2):
    11 Aug 2017 - ConcurrentDictionary.Count is locking
  4. PR Review (4):
    10 Aug 2017 - Errors, errors and more errors
  5. Production postmortem (19):
    07 Aug 2017 - 30% boost with a single line change
View all series


Main feed Feed Stats
Comments feed   Comments Feed Stats