Tuesday, April 15, 2014

Optimization through materialization

Saw a slow query which for the life of me I can't figure out why joining on inline TVF is slow


SELECT p.Fullname, o.Organizations
FROM Person p
JOIN dbo.fn_GetPersonOrganizations() o ON P.PersonId = B.PersonId

And was optimized by pre-materializing the rows of the inline TVF by dumping its result to a temporary table first then joining to temporary table instead. As much as we don't like to use a temporary table and make a more solid solution by fixing the problem of the inline TVF itself instead, we don't have the luxury of time as the ticket is a hotfix one; besides, we are having a hard time discerning if the problem really lies on the TVF itself, the TVF's code is very tight and looks performant(at the risk of sounding like a sales brochure). So temporary table we go:


SELECT p.Fullname, o.Organizations
FROM Person p
JOIN #Organizations o ON P.PersonId = B.PersonId



Table variable don't need to be cleaned up, so we could use table variable instead of temporary table; however, temporary table could be faster than table variable, but then temporary table need to be cleaned up, so we come up with this alternative approach instead:


SELECT p.Fullname, o.Organizations
FROM Person p
JOIN
(
    SELECT TOP 4000000000 *
    FROM dbo.fn_GetPersonOrganizations()
) o ON P.PersonId = B.PersonId


We found out that TOP blah achieves the same effect of materializing the rows immediately; same fast performance as temporary table. We were able to speed up the query, but we don't know yet why we need to materialize the TVF's results to gain performance on joins.



Happy Coding! ツ

Tuesday, April 1, 2014

Generics not letting you become a full-fledged objected-oriented programmer?

Hello implements IGreeter, but the compiler won't allow us to return an IList<Hello> to IList<IGreeter>, that's very fundamental OOP, but all our hard-earned knowledge of OOP can't do its magic due to the covariance problem between generic types



Fear not, there's a solution to that, starting C# 4, IEnumerable is covariant to IList, we can just substitute IEnumerable for IList if we don't need to add elements to the collection.



Think of OOP generics papercuts prior to C# 4



Happy Coding! ツ

Sunday, March 30, 2014

Highfalutin Code #1

"By the time you've got multiple joins, query expressions almost always win" -- Jon Skeet


But.. there are coders who simply love to write more code, perhaps they feel that the more convoluted-looking their code are the smarter they are. Hence most of them even when the code can sometimes use and be made more readable with Linq, will still use lambda instead. They feel they have elite skills just by using lambda.


var itemsOwner = 
    items
        .Join (people, i => i.OwnerId, p => p.PersonId, (i, p) => new { Item = i, Person = p })
        .Join (countries, ip => ip.Person.CountryId, c => c.CountryId, (ip, c) => new { ItemPerson = ip, Country = c })
        .Select (x => new { x.ItemPerson.Item.ItemName,  x.ItemPerson.Person.PersonName, x.Country.CountryName });



Those coders need to be in the movie Crank, let's see how they can write multiple joins in no time, let's see if they can still breath after two joins.


Then the continent name needed be shown on the output:

var itemsOwner = 
    items
        .Join (people, i => i.OwnerId, p => p.PersonId, (i, p) => new { Item = i, Person = p })
        .Join (countries, ip => ip.Person.CountryId, c => c.CountryId, (ip, c) => new { ItemPerson = ip, Country = c })
        .Join (continents, ipc => ipc.Country.CountryId, z => z.ContinentId, (ipc, z) => new { ItemPersonCountry = ipc, Continent = z })
        .Select (x => new { 
            x.ItemPersonCountry.ItemPerson.Item.ItemName, 
            x.ItemPersonCountry.ItemPerson.Person.PersonName, 
            x.ItemPersonCountry.Country.CountryName,
            x.Continent.ContinentName
        });


That's the problem with lambda joins, the aliases of multiple lambda joins cascades to the aliases of the subsequent joins. The aliases become unwieldy and deeply nested.



And there's too many variations of that lambda join, some would prefer the code below, deciding early what properties to project during lambda join. Ironically, though Linq and lambda promotes the use of deferred execution, but here we are, we have a coder deciding upfront that it's better to project user-shown properties right there in the lambda joins.


var itemsOwner = 
    items
        .Join (people, i => i.OwnerId, p => p.PersonId, (i, p) => new { i.ItemName, p.PersonName, p.CountryId })
        .Join (countries, ipc => ipc.CountryId, c => c.CountryId, (ipc, c) => new { ipc.ItemName, ipc.PersonName, c.CountryName });




And then you need to include the brand, oh the DRY headache!

var itemsOwner = 
    items                   
        .Join (people, i => i.OwnerId, p => p.PersonId, (i, p) => new { i.ItemName, p.PersonName, p.CountryId, i.BrandId })
        .Join (brands, ipcb => ipcb.BrandId, b => b.BrandId, (ipcb, b) => new { ipcb.ItemName, ipcb.PersonName, ipcb.CountryId, b.BrandName })
        .Join (countries, ipcb => ipcb.CountryId, c => c.CountryId, (ipcb, c) => new { ipcb.ItemName, ipcb.PersonName, c.CountryName, ipcb.BrandName });


See the problem with lambda joins? Aside from too many variations (deciding upfront to project the user-shown properties during joinings vs deferring that decision on final join or select), there's also the hard problem of giving a proper alias for the subsequent joins.



Then there's a change in the requirement to show the brand name after the item name, you feel the aliases should reflect that fact, so you got to change the alias too:

var itemsOwner = 
    items                   
        .Join (people, i => i.OwnerId, p => p.PersonId, (i, p) => new { i.ItemName, i.BrandId, p.PersonName, p.CountryId })
        .Join (brands, ibpc => ibpc.BrandId, b => b.BrandId, (ibpc, b) => new { ibpc.ItemName, b.BrandName, ibpc.PersonName, ibpc.CountryId })
        .Join (countries, ibpc => ibpc.CountryId, c => c.CountryId, (ibpc, c) => new { ibpc.ItemName, ipcb.BrandName, ibpc.PersonName, c.CountryName });




The highfalutin coder experiences the problems with unwieldy alias names cascading to subsequent joins, nested aliases, and giving good names for aliases, he decided to wise up; he decided he can avoid all the headaches above just by giving a generic name for all aliases:


var itemsOwner = 
    items                   
        .Join (people, src => src.OwnerId, p => p.PersonId, (src, p) => new { src.ItemName, src.BrandId, p.PersonName, p.CountryId })
        .Join (brands, src => src.BrandId, b => b.BrandId, (src, b) => new { src.ItemName, b.BrandName, src.PersonName, src.CountryId })
        .Join (countries, src => src.CountryId, c => c.CountryId, (src, c) => new { src.ItemName, src.BrandName, src.PersonName, c.CountryName });


By the time all the original developers in the project are replaced, new developers will be left scratching their heads why the old developers didn't bother to give self-describing names for aliases.



Let's say you want to switch the join order (MySQL is notorious on requiring certain order on joins to gain performance) of people and brands..
var itemsOwner = 
    items                   
        .Join (people, src => src.OwnerId, p => p.PersonId, (src, p) => new { src.ItemName, src.BrandId, p.PersonName, p.CountryId })
        .Join (brands, src => src.BrandId, b => b.BrandId, (src, b) => new { src.ItemName, b.BrandName, src.PersonName, src.CountryId })
        .Join (countries, src => src.CountryId, c => c.CountryId, (src, c) => new { src.ItemName, src.BrandName, src.PersonName, c.CountryName });


..you cannot nilly-willy re-order the joins without incurring changes on code, you can not just cut-and-paste the code:
var itemsOwner = 
    items                   
        .Join (brands, src => src.BrandId, b => b.BrandId, (src, b) => new { src.ItemName, b.BrandName, src.OwnerId })
        .Join (people, src => src.OwnerId, p => p.PersonId, (src, p) => new { src.ItemName, src.BrandName, p.PersonName, p.CountryId })
        .Join (countries, src => src.CountryId, c => c.CountryId, (src, c) => new { src.ItemName, src.BrandName, src.PersonName, c.CountryName });




Now contrast the humble developer's Linq join code to codes above. The code is very readable:

var itemsOwner = 
    from i in items
    join p in people on i.OwnerId equals p.PersonId
    join b in brands on i.BrandId equals b.BrandId
    join c in countries on p.CountryId equals c.CountryId    
    select new { i.ItemName, b.BrandName, p.PersonName, c.CountryName };


Then switch the join order of people and brands, there's no changes on code needed be done, you can just cut-and-paste the code:

var itemsOwner = 
    from i in items
    join b in brands on i.BrandId equals b.BrandId
    join p in people on i.OwnerId equals p.PersonId    
    join c in countries on p.CountryId equals c.CountryId    
    select new { i.ItemName, b.BrandName, p.PersonName, c.CountryName };



If the coder doesn't see problems with lambda joins and still prefer to write joins using it instead of Linq, he need to star in the movie Crank. I would hazard a guess he would die by the time he is in the third or fourth join; or worse yet was required to insert another join between the existing joins, good luck breathing with that task!


Another lambda join variation, not compounding objects in one alias, giving each joined object their own alias. Drawback is, subsequent joins must repeat the objects from the previous joins, a WET code:
var itemsOwner = 
    items
        .Join (people, src => src.OwnerId, p => p.PersonId, (src, p) => new { Item = src, Person = p })
        .Join (brands, src => src.Item.BrandId, b => b.BrandId, (src, b) => new { src.Item, src.Person, Brand = b })
        .Join (countries, src => src.Person.CountryId, c => c.CountryId, (src, c) => new { src.Item, src.Person, src.Brand, Country = c })
        .Select (x => new { x.Item.ItemName, x.Person.PersonName, x.Brand.BrandName, x.Country.CountryName });


The coder forgot he is using MySQL, it's optimal to join on brands first, then people. As an exercise, try switching the join order of people and brands of the code above, good luck breathing! http://dotnetfiddle.net/3bvQDy




Do you still want to showboat your lambda join skills?

Friday, March 14, 2014

Our Reaction To New Technology

Do you feel technologies like AngularJS, NoSQL, REST and cloud are against the natural order of things(e.g. ASP.NET, ASP.NET MVC, jQuery, WCF)? There must be a reason why





If you are distraught by revolutionary technologies even you haven't reached 35 yet, what does excite you? Perhaps you aged way too early?


Are you willing to trim down old jobs from your resume?


Perhaps we should be as young as this CTO: https://twitter.com/b4nn0n/status/347020882511818752



Happy Coding! ツ

Thursday, March 6, 2014

Rethinking C# interface's abstractness and OOP anemicness

Sometimes when we don't (I don't) have a full mastery of the language. dotnetfiddle helps http://dotnetfiddle.net/5mfuRY

So I got to rethink C# interface's members as just abstracts too.. http://www.ienablemuch.com/2012/03/advantage-of-explicit-interface.html ..as the following doesn't work: http://dotnetfiddle.net/7X8QQ8


..or perhaps C# should offer some symmetry as java, C# should allow override keyword on members of implementing class http://dotnetfiddle.net/7X8QQ8


Or perhaps I just don't care about object-orientation anymore and leaning towards more javascript, hence sometimes forgetting fundamental OOP concepts such as that it is allowed for an implementing class to add virtual keyword on implemented interface members. Me trying to forget OOP making me dangerous? lol Don't worry, dotnetfiddle makes me a less dangerous C# developer


And really, sometimes the way we implement OOP (or we think it is OOP) is not really an OOP at all, our OOP is sometimes so anemic


Happy Coding! ツ

Thursday, January 9, 2014

I love the word The

Want to enforce SchemaName.TableName to your domain models? i.e., you wanted this:

var personList = 
    from p in session.Query<Person.Person>
    select p;

var storeList = 
    from s in session.Query<Sales.Store>
    select s;


You can't use namespace..

namespace Domain.Models
{
    namespace Person
    {
        public class Person
        {
        }
        public class BusinessEntityContact
        {
        }
    }


    namespace Sales
    {
        public class Store
        {
        }
    }
}


..as developers can opt out of Person or Sales namespace by importing the namespace through C#'s using, some would do this:

using Domain.Models.Sales;


.
.
.


var list = 
    from p in session.Query<Store>
    select p;




To achieve enforcement of Schema name on your domain classes, do this instead:

namespace Domain.Models
{
    public static class Person
    {
        public class Person
        {
        }
    
        public class BusinessEntityContact
        {
        }
    }

    public static class Sales
    {
        public class Store
        {
        }
    }
}

However that will not work, it's not allowed for the nested class to have the same name as its containing class. So we must use some convention to eliminate the compiler error, e.g.


namespace Domain.Models
{
    public static class PersonSchema
    {
        public class Person
        {
        }
    
        public class BusinessEntityContact
        {
        }
    }

    public static class SalesSchema
    {
        public class Store
        {
        }
    }
}

But I prefer prefixing the word The:


namespace Domain.Models
{
    public static class ThePerson
    {
        public class Person
        {
        }
    
        public class BusinessEntityContact
        {
        }
    }

    public static class TheSales
    {
        public class Store
        {
        }
    }
}


Using that convention, reading the code rolls off the tongue quite nicely:


var personList = 
    from p in session.Query<ThePerson.Person>
    select p;

var storeList = 
    from s in session.Query<TheSales.Store>
    select s;


Sorry Entity Framework, the Set method does not cut it:

var list = 
    from s in context.Set<TheSales.Store>
    select s;


Happy Coding! ツ

Wednesday, January 8, 2014

Partial Class Is A Boon For Code Generator Developers

I got this following exception with NHibernate..

Cannot instantiate abstract class or interface: TestInheritance.DomainModels.BusinessEntity

..with these AdventureWorks2012 domain models:

public abstract class BusinessEntity
{
    public virtual int BusinessEntityID { get; set; }
}

public class Person : BusinessEntity
{
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
}

public class Employee : Person
{
    public virtual string NationalIDNumber { get; set; }
    public virtual string JobTitle { get; set; }
}

public class Store : BusinessEntity
{
    public virtual string Name { get; set; }        
}

The code I tried:

var list = s.Query<BusinessEntity>().ToList();


Basically, I just want to get all business entities. Upon reading this..

After suffering this error message for a while the reason turned out to be almost logical: In an @Inheritance of type Joined, there was an entry in the root table but no entry in any of the inheriting tables. -- Kolov



..it became obvious why mapping only the above domain models produces an error, there's some BusinessEntityID in abstract BusinessEntity that is not in any of the domain models above, the fallback of NHibernate is to instantiate the base class when its ID is not in the inheritance tree, hence resulting to an exception, since abstract classes cannot be instantiated. In fact, we can also make the error go away by making the BusinessEntity domain model (an abstract class) a concrete class, however there's no sense making BusinessEntity a concrete class.


Armed with the above knowledge in mind, I queried which tables are referencing the BusinessEntity domain model:

SELECT  
  ForeignTableSchema = KCU1.TABLE_SCHEMA
  ,ForeignConstraintName = KCU1.CONSTRAINT_NAME
  ,ForeignTableName = KCU1.TABLE_NAME 
  ,ForeignColumnName = KCU1.COLUMN_NAME
  ,ForeignOrdinalPosition = KCU1.ORDINAL_POSITION

  ,ReferencedTableSchema = KCU2.TABLE_SCHEMA
  ,ReferencedConstraintName = KCU2.CONSTRAINT_NAME
  ,ReferencedTableName = KCU2.TABLE_NAME 
  ,ReferencedColumnName = KCU2.COLUMN_NAME
  ,ReferencedOrdinalPosition = KCU2.ORDINAL_POSITION

   
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
  ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
  AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
  AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
  ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
  AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
  AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
  AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 

WHERE KCU2.CONSTRAINT_NAME = 'PK_BusinessEntity_BusinessEntityID'
ORDER BY KCU1.TABLE_NAME

Here's the result:






Knowing that I forgot to include the Vendor domain model, I then mapped it, then the exception problem goes away! Just merely looking at the result above, it's not instantly obvious if the BusinessEntityAddress and/or BusinessEntityContact is the table(s) that is also causing the exception above. You can only infer it by looking at their primary key, if their primary key relates one-to-one to BusinessEntity, then those models needed be mapped too. However seeing they are not one-to-one to BusinessEntity, then not mapping them won't cause exception to the abstract BusinessEntity domain model, to wit:




As we can see, they are not an aggregate root, those domain models makes sense only within the domain of an another domain model. This is where things get tricky for the code generator, even if we can indicate that the BusinessEntity is an abstract domain model (hence we can prevent it from becoming the aggregate root to BusinessEntityAddress, BusinessEntityContact or any domain models for that matter), it's impossible for code generator to deduce on which aggregate root the BusinessEntityAddress and BusinessEntityContact belongs to. This is where the code generator needed an intervention from someone with business knowledge of the domain models, these models are meant to be mapped manually, partial class totally empowers this needed manual mapping.



Happy Coding! ツ



Mapping:
public class BusinessEntityMapping : ClassMapping<BusinessEntity>
{
    public BusinessEntityMapping()
    {
        Table("Person.BusinessEntity");
        Id(x => x.BusinessEntityID, m => m.Generator(NHibernate.Mapping.ByCode.Generators.Identity));            

    }
}


public class PersonMapping : JoinedSubclassMapping<Person>
{
    public PersonMapping()
    {

        Table("Person.Person");

        Key(k => k.Column("BusinessEntityID"));

        Property(x => x.FirstName);
        Property(x => x.LastName);
    }
}



public class EmployeeMapping : JoinedSubclassMapping<Employee>
{
    public EmployeeMapping()
    {
        Table("HumanResources.Employee");

        Key(k => k.Column("BusinessEntityID"));


        Property(x => x.NationalIDNumber);
        Property(x => x.JobTitle);
    }
}


public class StoreMapping : JoinedSubclassMapping<Store>
{
    public StoreMapping()
    {
        Table("Sales.Store");

        Key(x => x.Column("BusinessEntityID"));

        Property(x => x.Name);
    }
}


public class VendorMapping : JoinedSubclassMapping<Vendor>
{
    public VendorMapping()
    {
        Table("Purchasing.Vendor");

        Key(x => x.Column("BusinessEntityID"));

        Property(x => x.AccountNumber);
        Property(x => x.Name);
    }
}