Pagination in SQL Server

Pagination is one of the intersting problem faced by all kind of applications. Pagination in earlier version of SQL Server is achieved by Common Table Expression(CTE). SQL Server 2012 integrated pagination syntax in most elegant and intutive way.

Let's assume the following sample table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[test](
 [val] [int] NULL
) ON [PRIMARY]

GO

Fill the table with integer 1 to 100. Let's assume that we want to find the 5th page with page size of 5 records. That means we want record 20 to 25

Here is the simple syntax to achieve the pagination.

SELECT val FROM test ORDER BY val OFFSET 20 ROWS FETCH NEXT 5 ROWS ONLY

OFFSET specifies starting point and FETCH NEXT X ROWS specifies the PageSize. This feature will work only if you use orderby. MySQL has this feature long time back.This is one of the most expected feature from SQL Server.Finally SQL Server 2012 came up with this feature.


Happy Coding.

Microsoft ignored the Power of Report Model

When Microsoft announced the SQLServer 2012  features,  There was  a shock for   SSRS2008 or earlier version users. The bad news is that Microsoft is going to discontinue Report Model approach in SSRS 2012. There will be backward compatibility so that old report model will work fine. But you can't create new report model using SQL Server 2012 technologies. That means  Report Model feature will be discontinued in future.

 Report Model approach is  relatively least popular technology from SQL Server Reporting Services. Business users know what information they need and how their report should look, but they usually don’t have the technical skills necessary to create a rich report design, to understand real-world database schemas, or to use formal query languages such as SQL. In contrast, IT staffers understand report design, the technical aspects of the data source schema, and formal query languages, but they are not available on demand to analyze, design, and deploy urgent or one-time reports. Therefore, businesses need a reporting solution that enables information workers to understand and  create reports on demand in short term without the dependency of IT staff.

Report Model Approach creates  a metadata layer on top of the database schema and preparing it to present the most-frequently requested data in the most intuitive and accessible manner by using business taxonomy. The metadata layer will then translate report design, filtering, and parameters into the correct and optimized native queries against the underlying physical data source.

Since Report creation using Report model is well integrated into Report builder, This feature allows basic business user to create simple reports using report model and Experienced developer can create complex report using SQL from the same SSRS application.

Even though SSRS 2012 provided Web UI for SSAS Cube reporting, there is no user friendly solution for Report generation from RDBMS.

But real power of Report model technology in SSRS is from some other least known area. Let's see about it.

Report Creation in Cloud Environment

Now world is moving towards cloud . Application servers, Database servers etc, are moving to cloud. creating Report from cloud hosted Database Server is a major challenge  As SaaS is gaining popularity with multitenant architecture, Real world problem of report creation from multitenant cloud hosted database is unimaginable.

 In cloud hosted application, Reports needs to be created using some web app or through click once application. Fortunately SSRS provided Report Builder tool which is a click once application. All the access to SSRS happens through web services. It is possible to make the protocol as https.

One of the primary challenge is the access to the database from Report generation tool. Normally ODBC,OLEDB and other connectors connects SQLServer database using 1433 port.If the application is hosted in cloud and if it is shared by multiple applications/customers, It is not practically possible to open 1433 port . You cannot open the Database server to "Public". Most of the Reporting tools access the database using ODBC/OLEDB or other access through 1433.

As per Microsoft, SSRS provided 3 Tier architecture even for report creation. Client Application always contact SSRS App Server and SSRS Report server contact database. This is the architecture that we want. But It is not happening. SSRS issue in this architecture  is discussed well in this post.

Then how to make a non-1433 port access to design/create the report from Report builder. Here is the power of Report builder comes. All the calls made from Report builder to create Report Model approach requires http or https port alone. It doesn't require 1433 port access.

Even though Report Model creation needs some overhead in metadata creation and maintenance , At least this approach solves one major technical problem in cloud . Of course, There are lots of bugs and usability issues with Report model based Report creation , Because of the above advantage, lots of Cloud users tends to use Report model. With The release of SQLServer 2012, Microsoft  announced its intention to stop supporting Report Model without providing proper alternative for RDBMS based reporting.

It is the time for new vendor to come up with reporting product which can design/create report without 1433 port access. If someone comes with above feature with multi tenant RDBMS support, He will rule Reporting Products world in the future.

Handling JavaScript Resources in Rich Client ASP.NET Applications

             Rich client application using tonnes of Javascript files are relatively new to .NET world. Most of the ASP.NET based applications rely on view creation in aspx and code behind files. User friendly client interactive applications forces the designers to move view creation and interaction logic to client browser. Earlier flash and Silverlight played a very good job in rich client Web application. Now world is moving towards HTML 5 and retirement of flash and Silverlight are nearing. As HTML 5 is becoming standard, .NET world forced to adopt it and needs to use JavaScript based thick client as front end.  Single Page Application using ASP.NET,Knockout.js,upshot.js etc are getting popular.  As more and more logic are added in client side through huge number of Javascript files, Handling of JavaScript files pose a major challange. If application is SaaS based cloud application, question becomes more complex. There are few tips  which will be helpful to handle JavaScript resources in ASP.NET based cloud application.

1. Reduce the number of JavaScript files - Always include the really needed Javascript file for a particular page. It is  unwise to globally include all Javascript for all the pages. It would be a nice architecture if you load the required Javascript files for a (ASP.NET)  page/module request  from page to  JS files mapping which is stored in common XML file. By this way,  you can centralize the needed JavaScript for each page in one place. It will improve the  maintainability of the code.

2. Minimize the size -  Normal JavaScript file contains lot of  page breaks, spaces, comments etc . They are necessary for the maintainability and debugging. but it increases the size of network transfer and performance of the application. Best way is to minimize the Javascript files by removing unnecessary  spaces,comments and line breaks. You can write your own program to trim those while application on load. Crockford created Jsmin library for this purpose in C language. There are lots of C# version of the library available in open source. Lot of  other open source library also available. These Minifier can reduce the size of the Javascript to half!.

Microsoft AJAX minifier is a command line tool which can minimize the Javascript file. It is possible to add this tool in visual studio under External tool. So that you can open a javascript file and run the tool from visual studio menu to minimize the files.

3. Bundle the  JavaScript files -  It is common to use large number of small sized JavaScript files in the application. It improves the maintainability and debugging. But browser has to make request for each JavaScript files. As number of request increases, browser round trip to server also increases. It can reduce the performance. It is always better to combine all the requested  JavaScript files to  one or couple of files and push those combined files to client. As specified in the previous point, if you have mapping between ASP.NET page name and JavaScript files,Based on requested page, you can get list of required JavaScript files , minimize each of them and combine the minimized file and return the combined file to client.

4. Version the JavaScript Resources - Always append some unique identifier with the JavaScript  It will avoid the browser cache problem. Some times, the changes in JavaScript files may not be downloaded to client browser because of older cached version of same file. By appending some Unique Identifier, especially  build version, whenever new build happens, browser will be prompted to download newer version of the file. By this way, Your support team need not instruct the customers to clear the browser cache  during every release.

5. Ability to use debug version - Make sure to turn off/on the minification and bundling option from web.config switch. It will be practically impossible to debug minified and bundled JavaScript. You need  unminified full version of JavaScript file for debugging purpose. When the code is moved to production, Javascript resources should be minified and bundled.

6. Compress the resources in IIS - Use inherant feature of IIS to compress the static resource content like Javascript files. To enable the compression, follow the steps
1. Open IIS Manager
2. Choose the site of interest.
3. Click Compression icon in right side
4. Check "Enable Static Content Compression"
This can reduce the network related traffic considerably.

7. Cache the Resources - Minification and bundling are really expensive jobs. Whenever you do minification and bundling, do it one time and cache the resources. Later on, retrieve the resources from cache.  These files can be created during first request or prebuilt files can be stored and cached before application access. Where to store the bundled and minified resources ? It is not a good idea to store the files in same virtual directory. If you store it in different folder or in memory , How to render to client.

System.Web.Hosting class in .NET provides  VirtualPathProvider class.
The VirtualPathProvider class provides a set of methods for implementing a virtual file system for a Web application. In a virtual file system, the files and directories are managed by a data store other than the file system provided by the server's operating system. For example, you can use a virtual file system to store content in a SQL Server database. You can customize this functionality so that you can store the files in altogether different folder in machine and can map the virtual path to the folder.

 8. Synchronize  data in all load balanced servers - If you use load balanced server, you can use separate server to service resources like css,images and JavaScript. Otherwise Make sure to update the minimized and bundled files in all the servers. It is good to make this process during build process instead of run time process.

Handling Bundling and Minification - ASP.NET 4.5 Way
Once innovative concepts and frameworks are out in the market, Microsoft has very good history of integrating into .NET in a developer friendly minimal coding style. Bundling and minification of the resource are not exception Manual handling of bundling and minification are discussed earlier. ASP.NET 4.5 included this feature as part of framework!.  This functionality can be achieved by minimal coding in ASP.NET 4.5.

Here is the usual way of including the JavaScript files.

<head runat="server">
    <title></title>
    <script src="../../Scripts/WebForms/DetailsView.js" type="text/javascript"></script>
        <script src="../../Scripts/WebForms/Focus.js" type="text/javascript"></script>
        <script src="../../Scripts/WebForms/GridView.js" type="text/javascript"></script>
        <script src="../../Scripts/WebForms/Menu.js" type="text/javascript"></script>
        <script src="../../Scripts/WebForms/MenuStandards.js" type="text/javascript"></script>
        <script src="../../Scripts/WebForms/SmartNav.js" type="text/javascript"></script>
        <script src="../../Scripts/WebForms/TreeView.js" type="text/javascript"></script>
        <script src="../../Scripts/WebForms/WebForms.js" type="text/javascript"></script>
        <script src="../../Scripts/WebForms/WebParts.js" type="text/javascript"></script>
        <script src="../../Scripts/WebForms/WebUIValidation.js" type="text/javascript"></script>

</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:placeholder runat="server">
        

    </asp:placeholder>
    </div>
</form>
</body>

This code will make multiple JavaScript file request from client.Fiddler trace will show as below

You can see 10 different request from browser.  Apart from making multiple trips, total amount of data transferred is around 160 kb. Think about the scenario in which  browser made single request and gets lesser size of the returned data. That will give very good performance improvement.

Now ASP.NET 4.5 provides inbuilt support for bundling and minification of files. The core functionality of bundling and minification is found in System.Web.Optimization namespace. This feature is automatically included .

If you create the ASP.NET web form project, Use the following steps to achieve the bundling and minification functionality.

Create the Bundle

You can see the bundle creation code in BundleConfig class in the App_Start folder. The following code shows the bundle creation. You can create as many bundle as you want based on the need.This will help you to avoid unnecessary includes of unwanted JS files. Default new project created from visual studio has code to create these 3 bundles.
1.WebFormJs
2. MsAjaxJs
3. Modernizr

 public class BundleConfig
    {
        // For more information on Bundling, visit http://go.microsoft.com/fwlink/?LinkId=254726
        public static void RegisterBundles(BundleCollection bundles)
        {
            bundles.Add(new ScriptBundle("~/bundles/WebFormsJs").Include(
                  "~/Scripts/WebForms/WebForms.js",
                  "~/Scripts/WebForms/WebUIValidation.js",
                  "~/Scripts/WebForms/MenuStandards.js",
                  "~/Scripts/WebForms/Focus.js",
                  "~/Scripts/WebForms/GridView.js",
                  "~/Scripts/WebForms/DetailsView.js",
                  "~/Scripts/WebForms/TreeView.js",
                  "~/Scripts/WebForms/WebParts.js"));

            bundles.Add(new ScriptBundle("~/bundles/MsAjaxJs").Include(
                "~/Scripts/WebForms/MsAjax/MicrosoftAjax.js",
                "~/Scripts/WebForms/MsAjax/MicrosoftAjaxApplicationServices.js",
                "~/Scripts/WebForms/MsAjax/MicrosoftAjaxTimer.js",
                "~/Scripts/WebForms/MsAjax/MicrosoftAjaxWebForms.js"));

            // Use the Development version of Modernizr to develop with and learn from. Then, when you’re
            // ready for production, use the build tool at http://modernizr.com to pick only the tests you need
            bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
                "~/Scripts/modernizr-*"));
        }

Register the Bundle

Registering the bundle happens in the Application_Start method in the Global.asax file.

        void Application_Start(object sender, EventArgs e)
        {
            
            // Code that runs on application startup
            BundleConfig.RegisterBundles(BundleTable.Bundles);
            AuthConfig.RegisterOpenAuth();
        }

Use the Bundle

Here is the syntax about how to use it.

<head runat="server">
    <title></title>
 </head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:PlaceHolder runat="server">
        <%: Scripts.Render("~/bundles/WebFormsJs") %>

    </asp:PlaceHolder>
    </div>
    </form>
</body>
</html>

Scripts.Render("~/bundles/WebFormsJs") 

is the actual syntax which renders minified file.
With the minification, fiddler trace will be like this

The number of request is Minimised to one. Apart from this the total size is just 60 KB. Application  compressed and reduced the size to around 40%. This will make a big difference in internet scenario. Normal thick web client usually sends files in Megabytes and the benefit will be exponential in those cases!

The same technique can be used for CSS minification also.

Switching between Minified and Unminified Version 

During development, We need unminified and unbundled files for debugging. But during release, we may need bundled & minified code. We can't have two different code for development and release. Microsoft handles this in nice way!. To enable minification, you need to do any one of the following.

Mark the Compilation - debug to true in the web.config file

<compilation debug="true" targetFramework="4.5" />

or override the settings in global.asax with BundleTable.EnableOptimizations = true;

       void Application_Start(object sender, EventArgs e)
        {
            BundleTable.EnableOptimizations = true;
            // Code that runs on application startup
            BundleConfig.RegisterBundles(BundleTable.Bundles);
            AuthConfig.RegisterOpenAuth();
        }

If you don't do the above, Application will automatically return unbundled & unminified file for debugging purpose even with the bundling  Javascript syntax in aspx page!

Happy Coding!