Ayende @ Rahien

Hi!
My name is Ayende Rahien
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 5,949 | Comments: 44,548

filter by tags archive

Tip: Searching Stored Procedures Text


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:

CREATE PROCEDURE dev_FindInSP

      @str nvarchar(max)

AS

select distinct name from sys.procedures procs join syscomments comments

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

How to kill the C# Compiler



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



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

{

    get

    {

        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]

    {

        get

        {

            ICurrentDataStore real = EnsureCorrectDataStoreForEnvironment();

            return real[key];

        }

        set

        {

            ICurrentDataStore real = EnsureCorrectDataStoreForEnvironment();

            real[key] = value;

        }

    }

 

    private ICurrentDataStore EnsureCorrectDataStoreForEnvironment()

    {

        ICurrentDataStore real;

        if (HttpContext.Current == null)

            real = new CurrentThreadDataStore();

        else

            real = new CurrentRequestDataStore();

        set(real);

        return real;

    }

 

    #endregion

}

 

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


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
GROUP BY [To]

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!


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


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


[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])

AS

(

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

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

      UNION ALL

      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!


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)

FUTURE POSTS

No future posts left, oh my!

RECENT SERIES

  1. The RavenDB Comic Strip (3):
    28 May 2015 - Part III – High availability & sleeping soundly
  2. Special Offer (2):
    27 May 2015 - 29% discount for all our products
  3. RavenDB Sharding (3):
    22 May 2015 - Adding a new shard to an existing cluster, splitting the shard
  4. Challenge (45):
    28 Apr 2015 - What is the meaning of this change?
  5. Interview question (2):
    30 Mar 2015 - fix the index
View all series

RECENT COMMENTS

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats