Variable-based Table-Valued Function vs Inline Table-Valued Function

Variable-Based Table-Valued Function, a.k.a. Multistatement Table-Valued Function are performance killers, for a good explanation, see the answer here: http://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function

Here's a sample variable-based table-valued Function:

create function getSamplePersonOrgsViaVariableBasedTableValuedFunction()
returns @t table
(
    BusinessEntityID int primary key clustered,
    OrgUnits nvarchar(max)
)
as
begin
    insert into @t(BusinessEntityID, OrgUnits)
    select p.BusinessEntityID, eorg.orgUnits
    from Person.Person p
    cross apply
    (
        select 'blah'
    ) eorg(orgUnits);
     
    return;
     
end;
go

Here's a sample inline table-valued function:

create function getSamplePersonOrgsViaInlineTableValuedFunction() returns table
as
return
    select p.BusinessEntityID, eorg.orgUnits
    from Person.Person p
    cross apply
    (
        select 'blah'
    ) eorg(orgUnits)
go

For 50 invocations of this sample query on inline table-valued function, this took 1 second only

SELECT p.BusinessEntityID, p.FirstName, p.LastName, gsp.orgUnits
FROM Person.Person p
join getSamplePersonOrgsViaInlineTableValuedFunction() gsp on p.BusinessEntityID = gsp.BusinessEntityID
where p.BusinessEntityID < 9
go 50

Contrast that efficiency to variable-based table-valued function, variable-based table-valued function took 9 seconds:

SELECT p.BusinessEntityID, p.FirstName, p.LastName, gsp.orgUnits
FROM Person.Person p
join getSamplePersonOrgsViaVariableBasedTableValuedFunction() gsp on p.BusinessEntityID = gsp.BusinessEntityID
where p.BusinessEntityID < 9
go 50

Eager loading is the root cause of the performance problem for variable-based table-valued function, i.e. even we only need 8 rows on source table (p.BusinessEntityID < 9), when the source table is joined to variable-based table-valued function, the source table has to wait first for the result (19,972 rows) of the variable-based table-valued function before it is finally being joined to.


Inline table-valued function is very efficient and smart, it treats the function like a view, i.e., the query of the inline table-valued function is expanded to an actual tables when being joined to another table, behaves exactly like the joining of a table to a table-deriving query / view / CTE. Hence when the inline table-valued function is joined to a source table, when you fetch 8 rows only on source table (p.BusinessEntityID < 9), the query will also fetch 8 rows only on inline-table-valued function too.


On variable-based table-valued function, the result of the function is the one being expanded then put to another table bucket (variable table), hence causing performance problem, so when for example we have 8 rows then we join it to a variable-based table-valued function, we are joining the 8 rows to eagerly-loaded 19,972 rows.


To illustrate the efficiency of inline table-valued function, let's cause a divide by zero when the record encounters Person ID number 9.

alter function getSamplePersonOrgsViaInlineTableValuedFunction() returns table
as
return
    select p.BusinessEntityId, eorg.orgUnits
    from dbo.Person.Person p
    cross apply
    (
        select 'blah ' + convert(varchar,case when p.BusinessEntityId = 9 then 1 / 0 else 7 end)
    ) eorg(orgUnits)
go
 
alter function getSamplePersonOrgsViaVariableBasedTableValuedFunction()
returns @t table
(
    BusinessEntityId int primary key clustered,
    OrgUnits nvarchar(max)
)
as
begin
    insert into @t(BusinessEntityId, OrgUnits)
    select p.BusinessEntityId, eorg.orgUnits
    from dbo.Person.Person p
    cross apply
    (
        select 'blah ' + convert(varchar,case when p.BusinessEntityId = 9 then 1 / 0 else 7 end)
    ) eorg(orgUnits);
     
    return;
     
end;
go

This query will not cause divide-by-zero error on inline table-valued function, a proof that the function doesn't fetch organization units after BusinessEntityID number 8

SELECT p.BusinessEntityID, p.FirstName, p.LastName, gsp.orgUnits
FROM Person.Person p
join getSamplePersonOrgsViaInlineTableValuedFunction() gsp on p.BusinessEntityID = gsp.BusinessEntityID
where p.BusinessEntityID < 9

This query gets a divide-by-zero error though, a proof that the query fetch all the 19,972 rows of the variable-based table-valued function first before it is being joined to.

SELECT p.BusinessEntityID, p.FirstName, p.LastName, gsp.orgUnits
FROM Person.Person p
join getSamplePersonOrgsViaVariableBasedTableValuedFunction() gsp on p.BusinessEntityID = gsp.BusinessEntityID
where p.BusinessEntityID < 9

Happy Computing! ツ

Stored Procedure Name + F12 + Tada!

Repartee is something we think of twenty-four hours too late



Someone made an astute observation that the ap + tab + stored proc name + tab + tada (tada is not included, you have to utter it yourself :p) combo shortcut has a problem on scripting the content, i.e. it strips out the comments on the top of the code.



I should have replied that it's okay to remove those comments, those comments are just a pile of name(s) of the original developer(s) tacked on one after another, too tacky :p Those comments does not in any way helps a new dev on deciphering the intent of the code. Hmm.. but it has a use, it helps others on their witch-hunt on the culprit(s) of erring code heheh



But I would not give an answer that would strip others of their rights to maintain comments on top of the code, any preferences for that matter, cut the drama :P



Without further ado, this is the new shortcut:

exec + stored proc name + F12



RedGate will script back the whole contents of the stored procedure to a new query editor, i.e. RedGate will also include the comments on top of the stored proc.



Other shortcuts:

For view, type: ssf + tab + view name + F12

For function, type: ssf + tab + function name + backspace + F12



An observation, there's no need to put EXEC or SELECT * FROM, just type in the stored proc / view / function name directly, then press F12. Sometimes, RedGate is flaky though, type in EXEC or ssf anyway :-)





Happy Computing! ツ

Debunking the myth that CTE is just a cursor

Some devs who have fervent belief that CTE is just a cursor got me worked up


Hearing that broken theory from them, I decided to create a proof-of-concept that would prove otherwise. As we only have limited keystrokes left in our hands, I decided to practice first at play.typeracer.com, being able to type fast is certainly a win if one is very worked up to quickly prove something by quickly making a proof-of-concept and quickly blogging it afterwards, all needed be done on one sitting. So that's how seriously worked up I am to prove something lol


Heheh scratch that, I already made an analysis and proof an eon ago that CTE is not slow.


A CTE is just an inline view, and in turn is just a table-deriving query, blogged it here: http://www.ienablemuch.com/2013/07/debunking-myth-that-cte-is-slow.html


CTE is not slow, not understanding the intent and how the query works is what makes a query slow, blogged it here: http://www.ienablemuch.com/2012/05/recursive-cte-is-evil-and-cursor-is.html



Please do note that a **recursive** CTE is being done sequentially, hence the "cursor-like" belief of some devs, however, the recursive CTE sequential steps is not being done on cursor. The CTE's loop is made on C++, which makes CTE faster, while a CURSOR's loop is being done via T-SQL, T-SQL loop is slow. C#'s loop is even an order of magnitude faster than T-SQL loop. Push the loop on C++, i.e., push the loop on CTEs. For non-recursive CTE, rest assured, the query still operates in set-based manner, not in cursor-like manner.



Time to stop spewing the nonsense that CTE is just a cursor.



Happy Coding!

Using Partial Index On SQL Server To Enforce Uniqueness On Denormalized Design

On my last post, using partial index (aka filtered index) I showed you how we can still enforce unique constraint even when soft deletions is the chosen mechanism for deleting records.


We will put again unique filtered index to good use. Given this table:

create table PeriodReviewers
(
     PeriodId int not null references Period(PeriodId),
     RevieweePersonId int not null references Person(PersonId),
     ReviewerPersonId int not null references Person(PersonId),

     IsPrimaryReviewer bit not null default(0),

     constraint pk_PeriodReviewers primary key(PeriodId, RevieweePersonId, ReviewerPersonId)
);

These are all valid data:

PeriodId    RevieweePersonId    ReviewerPersonId    IsPrimaryReviewer
1           Marcus              Buddy               1
1           Marcus              Raymund             0
1           Marcus              Ely                 0
1           Buddy               Raymund             1
1           Buddy               Ely                 0
1           Raymund             Ely                 1

Invalid data such as the following can be blocked:

PeriodId    RevieweePersonId    ReviewerPersonId    IsPrimaryReviewer
1           Marcus              Buddy               1
1           Marcus              Raymund             0   
1           Marcus              Raymund             0   <-- this is blocked, two Raymunds
1           Marcus              Ely                 0
1           Buddy               Raymund             1
1           Buddy               Ely                 0
1           Raymund             Ely                 1

However the above unique constraint can't defend the system on invalid data such as the following:

PeriodId  RevieweePersonId    ReviewerPersonId    IsPrimaryReviewer
1           Marcus            Buddy               1
1           Marcus            Raymund             1 <-- should error,only one primary reviewer to each person
1           Marcus            Ely                 0
1           Buddy             Raymund             1
1           Buddy             Ely                 0
1           Raymund           Ely                 1

There should be only one primary reviewer for each reviewee, but since our unique constraint is applied on these three fields: PeriodId + RevieweePersonId + ReviewerPersonId, invalid data such as above could creep in to our system.


This is the ideal solution:

create table PeriodReviewers
(
     PeriodId int not null references Period(PeriodId),
     RevieweePersonId int not null references Person(PersonId),
     ReviewerPersonId int not null references Person(PersonId),

     constraint pk_PeriodReviewers primary key(PeriodId, RevieweePersonId, ReviewerPersonId)
);
 
create table PeriodPrimaryReviewers
(
     PeriodId int not null references Period(PeriodId),
     RevieweePersonId int not null references Person(PersonId),

     ReviewerPersonId int not null references Person(PersonId),

     constraint pk_PeriodReviewers primary key(PeriodId, RevieweePersonId)
);

That's a fully normalized design. The advantage of normalization is we can save space on redundant data. On our business case, there can be only one primary reviewer for each person on a given period, so let's say we have 20 reviewers for each person on a given period, those other 19 reviewers, who are not primary reviewers, whose IsPrimaryReviewer are all set to false, those 19 IsPrimary fields are just wasting space, IsPrimaryReviewer field should be removed from the table. To normalize, just create a table that maintains each person's primary reviewer on a given period, as illustrated on the DDL above.

Normalize until it hurts, denormalize until it works -- Jeff Atwood

However fully normalizing the database might not be the norm nor an option, hence denormalized schema might be present in your system like the first one shown in this article. As we all know, denormalized design can be faster for queries. So for example, we wanted to list all the person's reviewer and indicate if that reviewer is a primary reviewer of the person. It's just this simple with denormalized table:

select
    
    PeriodId, 
    RevieweePersonId, 
    ReviewerPersonId, 
    IsPrimaryReviewer
    
from PeriodReviewers

Now, to do that on fully normalized tables:

select 

    emp.PeriodId, 
    emp.RevieweePersonId, 
    emp.ReviewerPersonId, 
    emp.IsPrimaryReviewer = 
        case when pri.ReviewerPersonId is not null then
            convert(bit, 1)
        else
            convert(bit, 0)
        end
        
from PeriodReviewers emp
left join PeriodPrimaryReviewers pri 
on  emp.PeriodId = pri.PeriodId
    and emp.RevieweePersonId = pri.RevieweePersonId
    and emp.ReviewerPersonId = pri.ReviewerPersonId

Using very normalized design, queries could become slower. Denormalized table makes for faster queries, as there's no need to join tables. However, denormalized design can cause data integrity problem. On denormalized design, concurrent update or not properly implemented application could let bad data slip in. So in our denormalized design, this bad data could slip in:

PeriodId    RevieweePersonId    ReviewerPersonId    IsPrimaryReviewer
1           Marcus              Buddy               1
1           Marcus              Raymund             1
1           Marcus              Ely                 0
1           Buddy               Raymund             1
1           Buddy               Ely                 0
1           Raymund             Ely                 1

As we can see, Marcus has two primary reviewers now. This kind of errors doesn't happen on normalized database design. If the denormalized design is already in place, it could be far more costly to redesign the database, and it's not guaranteed that fully normalizing the design won't affect query's performance. But how can we prevent bad data such as above from happening if we maintain the denormalized design?

Enter partial index, er.. filtered index. With SQL Server's filtered index (available since version 2008), we can create unique filtered index to prevent two or more primary reviewers for each person.


To note, the following is still allowed even we add unique filtered index, which is a valid business case.

PeriodId    RevieweePersonId    ReviewerPersonId    IsPrimaryReviewer
1           Marcus              Buddy               1
1           Marcus              Ely                 0
1           Marcus              Robin               0

However, upon further adding a reviewer for the same person on the same period, whose IsPrimaryReviewer field value is also set to true, that should be blocked by the database.

PeriodId    RevieweePersonId    ReviewerPersonId    IsPrimaryReviewer
1           Marcus              Buddy               1
1           Marcus              Ely                 0
1           Marcus              Robin               0
1           Marcus              Raymund             1   <-- adding this fourth row will not be allowed by the database.

To facilitate robust database design for the desired scenario above, we just need to add a unique index on those primary reviewers only, hence the filtered index nomenclature. In most RDBMSes this is called partial index. To wit, this will be the table definition:

create table PeriodReviewers
(
     PeriodId int not null references Period(PeriodId),
     RevieweePersonId int not null references Person(PersonId),

     ReviewerPersonId int not null references Person(PersonId),

     constraint pk_PeriodReviewers primary key(PeriodId, RevieweePersonId, ReviewerPersonId)
);

 
create unique index ix_PeriodReviewers_PrimaryReviewer
on PeriodReviewers(PeriodId, RevieweePersonId)
where IsPrimaryReviewer = 1;

We just need to create a unique index on Period and RevieweePersonId for primary reviewers only. When we try to add another primary reviewer for the same person on the same period, it will be stopped by the database, no bad data could creep in. That's how easy it is to prevent bad data on SQL Server. Validating business logic should be done on application layer, but it should also be done on the database too



Happy Computing! ツ

Linq's Composability and Reusability

Nothing can beat the composability and reusability of Linq, it enables one-stop-shop coding.


Case in point, getting a page from a filtered list and getting the total count of the filtered list is just a walk in the park with Linq, they can all be done on one spot.

using (var session = TheMapper.GetSessionFactory().OpenSession())
using (var tx = session.BeginTransaction())
{
    var products = session.Query<Product>();

    if (filter.ProductName != null) 
        products = products.Where(x => x.Name.Contains(filter.ProductName));                

    if (filter.ProductModelName != null)
        products = products.Where(x => x.ProductModel.Name.Contains(filter.ProductModelName));


    var result = new ProductResultDto();                
    
    result.Products =
            products
            .OrderBy(x => x.Name)
            .Take(filter.PageSize).Skip((filter.PageNumber - 1) * filter.PageSize)
            .Select(x => new ProductDto { ProductName = x.Name, ProductModelName = x.ProductModel.Name })
            .ToList();

    result.Count = products.Count();

    return result;                
}//using

How it is done when writing queries directly on database instead:

create procedure GetList(
    @ProductName nvarchar(400),
    @ProductModelName nvarchar(400),
    @PageNumber int,
    @PageSize int
)
as
begin


    declare @rn int = (@PageNumber-1) * @PageSize;

    with a as
    (
        select 
            RN = row_number() over(order by p.Name),
            ProductName = p.Name, ProductModelName = m.Name
        from Production.Product p
        left join Production.ProductModel m on p.ProductModelID = m.ProductModelID
        where 
            (
                @ProductName is null 
                or p.Name like '%' + @ProductName + '%'
            )
            and
            (
                @ProductModelName is null 
                or m.Name like '%' + @ProductModelName + '%'
            )
    )
    select 
        top (@PageSize)
        a.ProductName, a.ProductModelName 
    from a
    where a.rn > @rn;


    select count(*) as cnt
    from Production.Product p
    left join Production.ProductModel m on p.ProductModelID = m.ProductModelID
    where 
        (
            @ProductName is null 
            or p.Name like '%' + @ProductName + '%'
        )
        and
        (
            @ProductModelName is null 
            or m.Name like '%' + @ProductModelName + '%'
        );


end;

The above code is not DRY, you can see the filter being repeated in two places. We can avoid wetting the query by refactoring it:

create function GetList_Func(
    @ProductName nvarchar(400),
    @ProductModelName nvarchar(400)
) returns table
as
return
    select 
        RN = row_number() over(order by p.Name),
        ProductName = p.Name, ProductModelName = m.Name
    from Production.Product p
    left join Production.ProductModel m on p.ProductModelID = m.ProductModelID
    where 
        (
            @ProductName is null 
            or p.Name like '%' + @ProductName + '%'
        )
        and
        (
            @ProductModelName is null 
            or m.Name like '%' + @ProductModelName + '%'
        );
go



create procedure GetList_Refactored(
    @ProductName nvarchar(400),
    @ProductModelName nvarchar(400),
    @PageNumber int,
    @PageSize int
)
as
begin

    declare @rn int = (@PageNumber-1) * @PageSize;
    
    select 
        top (@PageSize)
        a.ProductName, a.ProductModelName 
    from GetList_Func(@ProductName, @ProductModelName) a
    where a.rn > @rn;

    select count(*) as cnt
    from GetList_Func(@ProductName, @ProductModelName);

end;

go

However, it's not one-stop-shop anymore. The query is now being defined in two places.



Happy Computing! ツ


One-stop-shop code:

namespace OrmFtw
{

    using System;
    using System.Collections.Generic;
    using System.Linq;

    using NHibernate.Linq;

    using OrmFtw.Models;
    using OrmFtw.Mapper;


    class Program
    {
        static void Main(string[] args)
        {
            // var result = GetList(new FilterRequest { ProductName = "Black", PageSize = 10, PageNumber = 2 });
            var result = GetList(new FilterRequest { ProductModelName = "Frame", PageSize = 10, PageNumber = 2 });

            Console.WriteLine("Total: {0}", result.Count);
            Console.WriteLine("Second Page: ");
            foreach (var prod in result.Products)
            {
                Console.WriteLine("Product: {0}", prod.ProductName);
                Console.WriteLine("Model: {0}", prod.ProductModelName);
            }

            Console.ReadKey();
        }

        private static ProductResultDto GetList(FilterRequest filter)
        {
            using (var session = TheMapper.GetSessionFactory().OpenSession())
            using (var tx = session.BeginTransaction())
            {
                var products = session.Query<Product>();

                if (filter.ProductName != null)
                    products = products.Where(x => x.Name.Contains(filter.ProductName));

                if (filter.ProductModelName != null)
                    products = products.Where(x => x.ProductModel.Name.Contains(filter.ProductModelName));


                var result = new ProductResultDto();

                result.Products =
                        products
                        .OrderBy(x => x.Name)
                        .Take(filter.PageSize).Skip((filter.PageNumber - 1) * filter.PageSize)
                        .Select(x => new ProductDto { ProductName = x.Name, ProductModelName = x.ProductModel.Name })
                        .ToList();

                result.Count = products.Count();

                return result;
            }//using

        }//GetList()
    }

    public class FilterRequest
    {
        public string ProductName { get; set; }
        public string ProductModelName { get; set; }

        public int PageNumber { get; set; }
        public int PageSize { get; set; }
    }

    public class ProductResultDto
    {
        public int Count { get; set; }

        public IList<ProductDto> Products { get; set; }
    }

    public class ProductDto
    {
        public string ProductName { get; set; }
        public string ProductModelName { get; set; }
    }

}



namespace OrmFtw.Models
{
    class Product
    {
        public virtual int ProductID { get; set; }
        public virtual string Name { get; set; }
        public virtual string ProductNumber { get; set; }

        public virtual ProductModel ProductModel { get; set; }
    }
}


namespace OrmFtw.Models
{

    public class ProductModel
    {
        public virtual int ProductModelID { get; set; }
        public virtual string Name { get; set; }
    }
}

namespace OrmFtw.ModelMappings
{
    using NHibernate.Mapping.ByCode.Conformist;
    using OrmFtw.Models;

    class ProductMapping : ClassMapping<Product>
    {
        public ProductMapping()
        {
            Table("Production.Product");
            Id(x => x.ProductID);
            Property(x => x.Name);
            Property(x => x.ProductNumber);


            ManyToOne(x => x.ProductModel, x => x.Column("ProductModelID"));

        }
    }
}


namespace OrmFtw.ModelMappings
{

    using NHibernate.Mapping.ByCode.Conformist;
    using OrmFtw.Models;

    class ProductModelMapping : ClassMapping<ProductModel>
    {
        public ProductModelMapping()
        {
            Table("Production.ProductModel");
            Id(x => x.ProductModelID);
            Property(x => x.Name);
        }
    }
}



namespace OrmFtw.Mapper
{
    using NHibernate;
    using NHibernate.Cfg;
    using NHibernate.Mapping.ByCode;
    using NHibernate.Cfg.MappingSchema;

    using OrmFtw.ModelMappings;

    public class TheMapper
    {
        static ISessionFactory _sessionFactory;
        public static ISessionFactory GetSessionFactory()
        {
            if (_sessionFactory != null)
                return _sessionFactory;

            var cfg = new Configuration();
            var mapper = new ModelMapper();

            mapper.AddMappings(
                new[] {
                    // Entities
                    typeof(ProductMapping), 
                    typeof(ProductModelMapping)
                });



            cfg.DataBaseIntegration(c =>
            {
                c.Driver<NHibernate.Driver.Sql2008ClientDriver>();
                c.Dialect<NHibernate.Dialect.MsSql2012Dialect>();
                c.ConnectionString = "Server=localhost; Database=AdventureWorks2012; Trusted_Connection=true;";

                c.LogFormattedSql = true;
                c.LogSqlInConsole = true;
            });


            HbmMapping domainMapping = mapper.CompileMappingForAllExplicitlyAddedEntities();

            cfg.AddMapping(domainMapping);

            _sessionFactory = cfg.BuildSessionFactory();

            return _sessionFactory;
        }


    }

}