Table Spool (Lazy Spool) in SQL Server 2005

I have a web app that recently timing out and the timing out exception is actually coming from the SQL Server. When I run the execution plan I found that there is one item called Table Spool (Lazy Spool) which is costing about 20%. I thought it was caused by my full text search but when I drilled down further more is because of DISTINCT keyword. So I decided to change to use GROUP BY instead. In my case it is only a single column so It won’t make any difference at all. Once I’ve changed that my web application running fast and no more timeout

I got this explanation from this website

Explain Distinct:
3) We do an all-AMPs RETRIEVE step from … by way of an
all-rows scan with no residual conditions into Spool x
(group_amps), which is redistributed by hash code to all AMPs.
Then we do a SORT to order Spool 1 by the sort key in spool field1
eliminating duplicate rows.

First there’s a redistribution, then duplicate rows are removed:
Efficient, if there are just a few rows per value [per AMP].
Spool size is always about the same, but may be extremely skewed → 2646: No more Spool Space

Explain Group By:
3) We do an all-AMPs SUM step to aggregate from … by way
of an all-rows scan with no residual conditions, and the grouping
identifier in field 1025. Aggregate Intermediate Results are
computed globally, then placed in Spool x.

First each AMP removes duplicate rows locally (first aggregate) and hashes/redistributes the resulting
rows, then there’s a second aggregation to remove duplicate rows:
Efficient, if there are lots of rows per value [per AMP].
Large number of rows per value Spool → small spool size
Small number of rows per value Spool → large spool size
Spool is never skewed.

Other interesting fact quoted from this article/discussion—lazy-spool/

INDEXING: Take a look at your indices to make sure that they’re all covering the columns that you’re selecting out of the tables. You’ll want to aim to get all the columns included in JOINs and WHERE clauses within the indices. All other columns that are in the SELECT statements should be INCLUDEd, or covered, by the index.

OPERATORS: See if you can get rid of the not equals (“<>”) operators, in favor of a single greater than or less than operator. Can this statement and T.CurrentHorizon <> 0 be changed to this and T.CurrentHorizon > 0?

JOINS: Get rid of the subqueries that are JOINing to tables outside of themselves. For instance, this line and FV2.elementId = FV.elementID might be causing some problems. There’s no reason you can’t move that out of a subquery and into a JOIN to dbo.aowCollectedFact FV, given that you’re GROUPing (DISTINCT) in the main query already.

DISTINCT: Change it to a GROUP BY. I’ve got no reason other than, because it’s good practice and takes two minutes.

LAST NOTE: The exception to all the above might be to leave the final subquery, the IF NOT EXISTS, as a subquery. If you change it to a JOIN, it’ll have to be a LEFT JOIN...WHERE NULL statement, which can actually cause spooling operations. No great way to get around that one.

Simple Paging using jQuery -Pajinate

By using this library, it allows you to do the paging through the HTML DOM from the client side  (Note: this is not about the ideal way or not the ideal way, I know the ideal way is to do paging server side)

To implement you just need to do 3 things:

1. Create a div container that wraps the container of item that you want to repeat and the navigation div, you can call it whatever you want

2. Create a div inside the container with class “page_navigation”

3. put class “content” on the container of the list item


Code Snippet
  1. <div id=”page_container”>
  2.     <div class=”page_navigation”></div>
  3.     <ul class=”content”>
  4.         <li>
  5.             <p>One</p>
  6.         </li>
  7.         <li>
  8.             <p>Two</p>
  9.         </li>
  10.         <li>
  11.             <p>Three</p>
  12.         </li>
  13.         <li>
  14.             <p>Four</p>
  15.         </li>
  16.         <li>
  17.             <p>Five</p>
  18.         </li>
  19.         <li>
  20.             <p>Six</p>
  21.         </li>
  22.         <li>
  23.             <p>Seven</p>
  24.         </li>
  25.         <li>
  26.             <p>Eight</p>
  27.         </li>
  28.     </ul>
  29. </div>

and I put this code on the document.ready event based on the id set on item 1

Code Snippet
  1. <SCRIPT>
  2.     jQuery(document).ready(function () {
  3.         jQuery(‘#page_container’).pajinate({ items_per_page: 2 });
  4.     });
  5. </SCRIPT>

The source code can be downloaded from here

and you can read the documentation from this github page

Yield keyword in .NET

I believe some of you already know about this but for me I never used it. Yield keyword has been existed since .NET 2.0 so I decided to look up of what it does and try to understand it

Based on MSDN

Yield is used in an iterator block to provide a value to the enumerator object or to signal the end of iteration, it takes one of the following form

Based on my understanding

Yield is a concatenation for a collection, or in SQL we normally use UNION

Yield break; is used to exit from the concatenation (remember it is not used to skip !)

One practical sample that I can think of is to get the enumerable of exception from inner exception (e.g stack trace)

sample code

Code Snippet
  1. class Program
  2.     {
  3.         ///<summary>
  4.         /// simple function to return IEnumerable of integer
  5.         ///</summary>
  6.         ///<returns></returns>
  7.         private static IEnumerable<int> GetIntegers()
  8.         {
  9.             for (int i = 0; i <= 10; i++)
  10.                 yield return i;
  11.         }
  12.         ///<summary>
  13.         /// simple function to return collection of class
  14.         ///</summary>
  15.         ///<returns></returns>
  16.         private static IEnumerable<MyClass> GetMyNumbers()
  17.         {
  18.             for (int i = 0; i <= 10; i++)
  19.                 if (i > 5)
  20.                     yield break;
  21.                 else
  22.                     yield return new MyClass() { Number = i };
  23.         }
  24.         internal class MyClass
  25.         {
  26.             public int Number { get; set; }
  27.             public string PrintNumber
  28.             {
  29.                 get {
  30.                     return “This is no “ + Number.ToString();
  31.                 }
  32.             }
  33.         }
  34.         static void Main(string[] args)
  35.         {
  36.             Console.WriteLine(“Simple array of integer”);
  37.             foreach (var number in GetIntegers())
  38.                 Console.WriteLine(number.ToString());
  39.             Console.WriteLine();
  40.             Console.WriteLine(“Collection of classes”);
  41.             foreach (var myclass in GetMyNumbers())
  42.                 Console.WriteLine(myclass.PrintNumber);
  43.             Console.ReadLine();
  44.         }
  45.     }


Simple array of an integer
10Collection of classes
This is no 0
This is no 1
This is no 2
This is no 3
This is no 4
This is no 5

Knockout MVVM Javascript

Knockout allows you to bind the HTML to your javascript object. It simplifies DOM manipulation and allow the portability of the javascript object and action. It is pretty much the same concept as MVVM in silverlight. You can wire up the function with button click easily, you can have for each against your array (e.g like repeater). It is so elegant, but debugging sometimes can be challenging as well. I’ve used Knockout along with JSON that allows me to build rich and interactive website

2 powerful function: ko.observable – this allow knockout to monitor this object value, ko.observableArray this is the extension of ko.observable against the array. With observable, knockout will keep tracking the value of that property and allow the DOM that has been bind against it to refresh

You can bind initial data from your MVC model to the variable in javascript and bind it, in this sample below, I use ToJson extension function

Code Snippet
  1. namespace System.Web.Mvc
  2. {
  3.     public static class HtmlHelperExtensions
  4.     {
  5.         ///<summary>
  6.         /// Serializes an object to Javascript Object Notation.
  7.         ///</summary>
  8.         ///<param name=”item”>The item to serialize.</param>
  9.         ///<returns>
  10.         /// The item serialized as Json.
  11.         ///</returns>
  12.         public static string ToJson(this object item)
  13.         {
  14.             return new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(item);
  15.         }
  16.     }
  17. }

Sample code

Code Snippet
  1. <script type=”text/javascript”>
  2.     var initialData = @(new MvcHtmlString(Model.ToJson()));
  3.     function JobResultViewModel()
  4.     {
  5.         var self = this;
  6.         self.Jobs = ko.observableArray(initialData.JobSearchResults);
  7.         self.Search = ko.observable(initialData.JobSearchModel);
  8.         self.Pageno = ko.observable(initialData.PageNo);
  9.         self.TotalPage = ko.observable(initialData.TotalPage);
  10.         self.TotalRecord = initialData.TotalRecord;
  11.         self.ShowNextButton = ko.computed(function(){
  12.                                             return self.Pageno() < (self.TotalPage() – 1);
  13.                                             });
  14.         self.LoadNextPage = function() {
  15.                                $.getJSON(@Url.Action(“ResultJson”), {  Keyword: (self.Search().Keyword == null) ? “” : self.Search().Keyword,
  16.                                                                       ProfessionId: self.Search().ProfessionId,
  17.                                                                       RoleIds: self.Search().RoleId,
  18.                                                                       SalaryTypeId: self.Search().SalaryTypeId,
  19.                                                                       SalaryFromId: self.Search().SalaryFromId,
  20.                                                                       SalaryToId: self.Search().SalaryToId,
  21.                                                                       LocationId: self.Search().LocationId,
  22.                                                                       AreaIds: (self.Search().AreaId.length == 0) ? 0 : self.Search().AreaId,
  23.                                                                       WorkTypeId: self.Search().WorkTypeId,
  24.                                                                       Pageno: self.Pageno() + 1
  25.                                                                    }, function (SearchResult) {
  26.                                                                             $.each(SearchResult, function(i, item)
  27.                                                                             {
  28.                                                                                 self.Jobs.push(item);
  29.                                                                             });
  30.                                                                             self.Pageno(self.Pageno() + 1);
  31.                                                                             //we need to refresh the repeater when we use jquery mobile ONLY
  32.                                                                             $(“#JobRepeater”).listview(“refresh”);
  33.                                                                         });
  34.                                         }
  35.     }
  36.     ko.applyBindings(new JobResultViewModel());
  37. </script>
  38. <h2>Result</h2>
  39. <h1>There are <span data-bind=”text: TotalRecord”></span> jobs</h1>
  40. <a>Save Search</a>
  41. <ul name=”JobRepeater” id=”JobRepeater” data-role=”listview” data-bind=”foreach: Jobs”>
  42.     <li><a data-bind=”attr: { href: UrlAction, title: JobName },text : JobName”></a><span data-bind=”text: Description”></span></li>
  43. </ul>
  44. <div data-bind=”visible: ShowNextButton”>
  45.     <input type=”button” id=”btn_load_next” value=”Load More” data-bind=”click: LoadNextPage”/>
  46. </div>


Knockout Tutorial

Knockout Tips

Custom Authorize Attribute and HTTP 403

In this post, I want to outline in how to create your own Authorize tag and to make sure when you call JSON method with your custom authorize attribute to throw HTTP403 – Forbidden

1. You need to create your own Attribute inherits from AuthorizeAttribute

2. AuthorizeCore is the logic that defines whether you are authorized or not

3. OnAuthorization defines the behaviour when you are not authorized. In this case we want to throw HTTP 403 – forbidden. By doing this in your Javascript, you can catch this 403 error and throw friendly error message to the user

Code Snippet
  1. public class CustomAuthorizeAttribute : AuthorizeAttribute
  2.     {
  3.         protected override bool AuthorizeCore(HttpContextBase httpContext)
  4.         {
  5.             if (httpContext == null) throw new ArgumentNullException(“httpContext”);
  6.             return (SessionData.Member != null && SessionData.Member.MemberId > 0);
  7.         }
  8.         public override void OnAuthorization(AuthorizationContext filterContext)
  9.         {
  10.             base.OnAuthorization(filterContext);
  11.             if (filterContext.Result == null)
  12.             {
  13.                 return;
  14.             }
  15.             else if (filterContext.Result.GetType() == typeof(HttpUnauthorizedResult)
  16.                 && filterContext.HttpContext.Request.IsAjaxRequest())
  17.             {
  18.                 filterContext.Result = new ContentResult();
  19.                 filterContext.HttpContext.Response.StatusCode = 403;
  20.             }
  21.         }
  22.     }

You don’t need to do anything in your controller to implement HTTP403, it is all derived from the custom attribute, you just need to use the attribute and everything will be taken care of. Sample usage

Code Snippet
  1. [CustomAuthorize]
  2.         public ActionResult SaveJobJSON(int jobid)
  3.         {
  4.             string message = string.Empty;
  5.             bool successful = false;
  6.             JobsSavedService JobsSavedService = new JobsSavedService();
  7.             successful = JobsSavedService.SavedJobForMember(jobid, ref message);
  8.             JobsSavedService = null;
  9.             return Json(new { successful = successful, message = message }, JsonRequestBehavior.AllowGet);
  10.         }

Log4Net – Logging for .NET

Before I was thinking of rewriting my own logging for my application but after having a careful thought, I decided just to get one from NuGet package instead of reinventing the wheel. I heard a lot about Log4Net and decided to try that out. It’s really simple to get this logging up and running on your application

This is what you need to do:

1. install Log4Net from NuGet

2. Modify your web.config to add a new section and new configuration – In this case, I decided to use FileLogger. but you can have different type logger if you want (e.g event log, console, etc) or you can even build your own one that implemented ILog, this configuration allow you to create a new log file when the log file already reached the limit

Code Snippet
  1. <configSections>
  2.     <section name=log4nettype=log4net.Config.Log4NetConfigurationSectionHandler, log4net />
  3.   </configSections>
  4.   <log4net>
  5.     <root>
  6.       <levelvalue=DEBUG />
  7.       <appender-ref ref=LogFileAppender />
  8.     </root>
  9.     <appender name=LogFileAppender type=log4net.Appender.RollingFileAppender >
  10.       <param name=Filevalue=C:\\log.txt />
  11.       <param name=AppendToFilevalue=true />
  12.       <rollingStyle value=Size />
  13.       <maxSizeRollBackups value=10 />
  14.       <maximumFileSize value=10MB />
  15.       <staticLogFileName value=true />
  16.       <layout type=log4net.Layout.PatternLayout>
  17.         <param name=ConversionPattern value=%-5p%d{yyyy-MM-dd hh:mm:ss} – %m%n />
  18.       </layout>
  19.     </appender>
  20.   </log4net>

3. Code implementation – in this case I decided to register it in my global container and retrieve it later. You need to call log4net.Config.XmlConfigurator.Configure in order for Log4Net to get the settings from web.config – This is very important otherwise you will find that there is no log being generated

Code Snippet
  1. protected void Application_Start()
  2.         {
  3.             AreaRegistration.RegisterAllAreas();
  4.             RegisterGlobalFilters(GlobalFilters.Filters);
  5.             RegisterRoutes(RouteTable.Routes);
  6.             BundleTable.Bundles.RegisterTemplateBundles();
  7.             RegisterDependency();
  8.         }
  9.         #region “Dependency Injection”
  10.         private static Container Container;
  11.         public static T GetInstance<T>() where T : class
  12.         {
  13.             return Container.GetInstance<T>();
  14.         }
  15.         protected void RegisterDependency()
  16.         {
  17.             //Create a main containers
  18.             var container = new Container();
  19.             // 2. Configure the container (register)
  20.             container.RegisterPerWebRequest<IUnitOfWork>(() => new UnitOfWork(new PosDataContext()));
  21.             //Register logger
  22.             log4net.Config.XmlConfigurator.Configure();
  23.             container.Register<ILog>(() => log4net.LogManager.GetLogger(typeof(MvcApplication)));
  24.             container.Verify();
  25.             Container = container;
  26.         }
  27.         #endregion

calling the logger from my code (controller in this context)

Code Snippet
  1. public ActionResult Index()
  2.         {
  3.             ViewBag.Message = “Menu”;
  4.             MvcApplication.GetInstance<ILog>().Info(“Test Info”);
  5.             MvcApplication.GetInstance<ILog>().Error(“Test Error”);
  6.             MvcApplication.GetInstance<ILog>().Debug(“Test Debug”);
  7.             return View();
  8.         }

PS: if you want to implement your own logger, then I’d recommend you to have your logger to implement ILog interface and change it through web.config but don’t wrap/call Log4Net inside your custom logger because you don’t want to limit the rich functionality of Log4Net with your custom logger

Another tips, is when you started to add logging in your application then there should be some performance overhead eventhough it is minor. In the example below MyFunctionOutput function will still be called eventhough you disabled the logging on the config, in order to make sure the debugging code only executed

Code Snippet
  1. MvcApplication.GetInstance<ILog>().Debug(“My Function Output:” + MyFunctionOutput());

When you want to have the debug/logging omitting the log whenever the logging is enabled then you can wrap around the logging with this property (there are another properties IsErrorEnabled, IsInfoEnabled, etc)

Code Snippet
  1. if (MvcApplication.GetInstance<ILog>().IsDebugEnabled)
  2.             {
  3.                 MvcApplication.GetInstance<ILog>().Debug(“My Function Output:” + MyFunctionOutput());
  4.             }

Pass Model from Javascript to MVC Controller

Initially, I always pass individual object properties through JSON and form the model in the controller as I wasn’t sure how to pass/form a model from Javascript to controller


Code Snippet
  1. [AllowAnonymous]
  2.         public ActionResult JsonRegister(string username, string password, string confirmpassword,
  3.                                             string email, string confirmemail)
  4.         {
  5.             MemberModel.RegistrationModel model = new MemberModel.RegistrationModel() {
  6.                                                                                         UserName = username,
  7.                                                                                         Password = password,
  8.                                                                                         ConfirmPassword = confirmpassword,
  9.                                                                                         Email = email,
  10.                                                                                         ConfirmEmail = confirmemail };


Code Snippet
  1. $.getJSON(@Url.Action(“JsonRegister”), { UserName: $(“#UserName”).val(),
  2.                                                     Password: $(“#Password”).val(),
  3.                                                     ConfirmPassword: $(“#ConfirmPassword”).val(),
  4.                                                     Email: $(“#Email”).val(),
  5.                                                     ConfirmEmail: $(“#ConfirmEmail”).val()
  6.                                                 }, function (RegisterResult) {
  7.                                                         if (RegisterResult.success) {
  8.                                                             $(‘#RegistrationFields’).hide();
  9.                                                             $(‘#ErrorMessage’).text();
  10.                                                             $(‘#RegistrationMessage’).css(‘display’, );
  11.                                                         }
  12.                                                         else {
  13.                                                             $(‘#ErrorMessage’).css(‘display’, ).text(RegisterResult.errors);
  14.                                                         }
  15.         });

the code above is working just fine but I still feel that there is room for improvement. Below is the code that shows how you can still have your controller accepting the model instead of expanding the properties in the model as the parameters to the controller. The solution is just to have the model being assigned to a variable (in this context called as data) before passing it to the JSON, my previous code was forming the actual object in the JSON code


Code Snippet
  1. [AllowAnonymous]
  2.         public ActionResult JsonRegister(MemberModel.RegistrationModel model)
  3.         {
  4.             string error = string.Empty;
  5.             if (ModelState.IsValid)


Code Snippet
  1. var data = { UserName: $(“#UserName”).val(),
  2.             Password: $(“#Password”).val(),
  3.             ConfirmPassword: $(“#ConfirmPassword”).val(),
  4.             Email: $(“#Email”).val(),
  5.             ConfirmEmail: $(“#ConfirmEmail”).val()
  6.         };
  7.         $.getJSON(@Url.Action(“JsonRegister”), data, function (RegisterResult) {
  8.             if (RegisterResult.success) {
  9.                 $(‘#RegistrationFields’).hide();
  10.                 $(‘#ErrorMessage’).text();
  11.                 $(‘#RegistrationMessage’).css(‘display’, );
  12.             }
  13.             else {
  14.                 $(‘#ErrorMessage’).css(‘display’, ).text(RegisterResult.errors);
  15.             }
  16.         });