Monday, November 10, 2008

Compressing Viewstate

Perhaps the primary statistic by which clients measure web applications is response time. Obviously, web pages that take too long to appear, or at least start to appear, frustrate end-users.

One of the many reasons a web page can be slow is merely the quantity of information that must be sent to a user’s browser; pictures, video, maps, and huge quantities of text can lead to poor response times, even though the server and all processes behind it are running efficiently.

ASP.NET has a page state management mechanism known as “ViewState”, by which changes to page markup (altered text, colors, dynamic formatting etc.) are retained. On a postback that encoded ViewState data is made available to the server, and the .NET environment automatically updates the state of web control properties to represent the combination of the original markup and the ViewState-retained changes. ASP.NET provides an optional SessionPageStatePersister to retain the ViewState in the user’s Session data, and other persisters can be written whereby the ViewState may be stored anywhere you desire. By default ASP.NET persists ViewState as base-64 encoded text in a hidden input field in the HTML delivered to the browser.

Developers need to understand how ViewState works, and what it does (and does not do), to suss how their web pages actually function. For an excellent and detailed writeup on ViewState, see Dave Reed’s article Truly Understanding ViewState.

One issue we experienced on a current project in Calgary was with excessive ViewState size. For example, we frequently use a custom control to generate a customized scrollable table of data on a web page, and these tables frequently contain hundreds of rows and many columns of data. Unless switched off, ViewState is maintained automatically for all this generated data, in effect causing the same data to be transmitted twice to the user’s browser. Often we want the ViewState retained as a necessary evil, however, to support client-side sorting of the data rows.

Sometimes these large tables are inside an UpdatePanel or an AJAX TabContainer, both of which retain their own ViewState plus all the ViewState of all the controls inside them. Now we have even more ViewState to cope with, and for tables contained in multiple tabs in a TabContainer, the actual ViewState size can get unexpectedly large; we have seen a relatively benign popup page with almost 6MB of ViewState! Response time was rather unpleasant for locally connected users, and downright unacceptable for those at remote sites.

Rather than refactoring individual pages, we took a more global approach: compressing the ViewState before delivery. This approach was very successful, primarily because of what the ViewState contains. Remember that it represents all data not explicitly coded in the HTML markup, which includes all dynamic formatting and placement specifications. For a large table, there is massive repetition of various property settings, ideal data for compression algorithms.

Our implementation was simplified by the fact that all our code-behind partial classes inherit a project-wide BasePage, which is where we placed the compression and decompression logic. In ASP.NET, the System.Web.UI.Page class contains two overrideable methods that were ideal for our compression logic: SavePageStateToPersistenceMedium() and LoadPageStateFromPersistenceMedium().
Sample compression code is shown below, and requires the SharpZipLib compression library.

using ICSharpCode.SharpZipLib.Zip.Compression;
using ICSharpCode.SharpZipLib.Zip.Compression.Streams;
...
private const int BUFFER_SIZE = 65536;
private int viewStateCompression = Deflater.NO_COMPRESSION;

public int ViewStateCompression
{
    get { return viewStateCompression; }
    set { viewStateCompression = value; }
}

protected override void SavePageStateToPersistenceMedium(Object state)
{
    if (ViewStateCompression == Deflater.NO_COMPRESSION)
    {
        base.SavePageStateToPersistenceMedium(state);
        return;
    }

    Object viewState = state;
    if (state is Pair)
    {
        Pair statePair = (Pair) state;
        PageStatePersister.ControlState = statePair.First;
        viewState = statePair.Second;
    }

    using (StringWriter writer = new StringWriter())
    {
        new LosFormatter().Serialize(writer, viewState);
        string base64 = writer.ToString();
        byte[] compressed = Compress(Convert.FromBase64String((base64)));
        PageStatePersister.ViewState = Convert.ToBase64String(compressed);
    }
    PageStatePersister.Save();
}

private byte[] Compress(byte[] bytes)
{
    using (MemoryStream memoryStream = new MemoryStream(BUFFER_SIZE))
    {
        Deflater deflater = new Deflater(ViewStateCompression);
        using (Stream stream = new DeflaterOutputStream(memoryStream, deflater, BUFFER_SIZE))
        {
            stream.Write(bytes, 0, bytes.Length);
        }
        return memoryStream.ToArray();
    }
}

Looking at the “Save” logic, you’ll see that the default setting for compression is to not do any compression at all. Other deflater settings include DEFAULT_COMPRESSION, BEST_SPEED and BEST_COMPRESSION. The ViewStateCompression property must be set before ViewState is retrieved, in the Page_PreInit() or Page_Init() method. ViewState is available as the Second part of a Pair object (the first part references ControlState, which is different than the page’s ViewState. We decided not to compress it due to its limited use and small size. See Bean Software’s ControlState Property Demystified). We grab the ViewState object hierarchy, serialize it using the same System.Web.UI.LosFormatter that ASP.NET uses to serialize ViewState, compress it using SharpZipLib, System.Convert it to a base-64 string again, and hand it to the PageStatePersister to be written out. Use the PageStatePersister to write to the normal __VIEWSTATE hidden field; the AJAX toolkit gets upset if you manually write it to any other field.

The reverse is done on a PostBack:

protected override Object LoadPageStateFromPersistenceMedium()
{
    if (viewStateCompression == Deflater.NO_COMPRESSION)
        return base.LoadPageStateFromPersistenceMedium();

    PageStatePersister.Load();
    String base64 = PageStatePersister.ViewState.ToString();
    byte[] state = Decompress(Convert.FromBase64String(base64));
    string serializedState = Convert.ToBase64String(state);

    object viewState = new LosFormatter().Deserialize(serializedState);
    return new Pair(PageStatePersister.ControlState, viewState);
}

private byte[] Decompress(byte[] bytes)
{
    using (MemoryStream byteStream = new MemoryStream(bytes))
    {
        using (Stream stream = new InflaterInputStream(byteStream))
        {
            using (MemoryStream memory = new MemoryStream(BUFFER_SIZE))
            {
                byte[] buffer = new byte[BUFFER_SIZE];
                while (true)
                {
                    int size = stream.Read(buffer, 0, BUFFER_SIZE);
                    if (size <= 0)
                        break;

                    memory.Write(buffer, 0, size);
                }
                return memory.ToArray();
            }
        }
    }
}

If no compression were originally applied, we call the base method to do its thing. Otherwise, the state information is derived from the hidden HTML field, and the ViewState portion of it is converted from base-64, decompressed, reconverted to base-64, and deserialized into its original object hierarchy.
Some experimentation should be done to determine the optimal sizes of the various buffers; here we used our elite programming skills to pick a workable size (we guessed). Likewise, likely no single type of compression (default, max or fast) is optimal in all circumstances.

So how does a developer determine whether ViewState compression is required at all? One could view the page source, copy the value of the __VIEWSTATE hidden input field, paste it into an editor and determine the column width. A better approach is to display the size of the ViewState (during development) as part of a FooterInfoControl on the page itself. Our MasterPage.Master displays the footer control, which contains other controls, one of which is the ViewstateSizeControl itself:

public class ViewstateSizeControl : Label
{
 private const string SCRIPT = "$('{0}').innerText = document.forms[0].__VIEWSTATE.value.length;";

 protected override void OnLoad(EventArgs e)
 {
  if (Visible)
  {
   Page.ClientScript.RegisterStartupScript(
    typeof(Page),
    UniqueID,
    string.Format(SCRIPT, ClientID),
    true);
  }
  base.OnLoad(e);
 }
}

This allows the developers and testers to see how big the ViewState is, both before and after compression, with no extra effort. Quite handy! And how well does this compression mechanism work? In most cases, you should expect at least a 90% reduction in ViewState size. For example, the aforementioned 6MB of data actually compressed to less than 60K. Quite effective!

ViewStateCompressionDemo

Tuesday, October 14, 2008

SharePoint 2007 - Access denied when crawling SharePoint Content

Access is denied. Verify that either the Default Content Access Account has access to this repository, or add a crawl rule to crawl this repository. If the repository being crawled is a SharePoint repository, verify that the account you are using has "Full Read" permissions on the SharePoint Web Application being crawled. (The item was deleted because it was either not found or the crawler was denied access to it.)

Solution is to Disable the loopback check
How ?  add the following registry key


1. Click Start, click Run, type regedit, and then click OK.
2. In Registry Editor, locate and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa

3. Right-click Lsa, point to New, and then click DWORD Value.
4. Type DisableLoopbackCheck, and then press ENTER.
5. Right-click DisableLoopbackCheck, and then click Modify.
6. In the Value data box, type 1, and then click OK.
7. Quit Registry Editor, and then restart your computer.
8. Start full crawling

Tuesday, July 15, 2008

How to Create a MOSS 2007 VPC Image

This article has a great series that walks you through How to Create a MOSS 2007 VPC Image


Enjoy.


http://www.pptspaces.com/sharepointreporterblog/Lists/Posts/Post.aspx?ID=28

Wednesday, June 11, 2008

How to install SharePoint Server 2007 on a single machine

One of my first ever blog articles (and by far most popular to date) was a set of instructions on how to install Beta1 of SharePoint Server 2007 on a single machine. I removed this article because it was too much of an overhead updating it with the various Betas and the official guides were being developed. Now that SharePoint is RTM, I do still get a lot of questions from customers on how to do a simple installation of SharePoint (with SQL 2005) on a single machine to be used for a stand-alone development, demonstration or simple 'play-pen' server (normally on a virtual machine). This guide will outline all of the main steps to setup such an environment.

Please bear in mind that this is just an unofficial guide to getting SharePoint 2007 installed quickly and easily in a demo / test environment. This guide will not necessarily observe best practices with regard to security etc. For production setups, you should seek guidance from the official documentation which is available on TechNet (http://technet2.microsoft.com/Office/en-us/library/3e3b8737-c6a3-4e2c-a35f-f0095d952b781033.mspx?mfr=true).

Pre-Install
There are several things that you must do before you even insert the SharePoint 2007 CD they are:
  • Install Windows 2003 R2 with the latest service pack (2 at time of writing) and all of the latest Windows Updates.
NOTE: Please do not use NewSID to change the SID of the machine if you are using a copy of another VM, this breaks things in SharePoint. My advice is to build Windows from fresh or to use Sysprep if you are using a copy of a VM.
  • Join your machine to a domain or create a domain by running DCPromo.exe from the Start > Run dialog.
  • Install the .net frameworks v3.0 and v2.0 from Windows Update. You can also download the full redistributable packages if your server is not online.
  • Install Windows 'Application Server' from Add/Remove Programs in Control Panel with default settings
  • Prepare a service account in your active directory domain to use for all Sharepoint services.

NOTE: Do not use the main domain\administrator account. This causes a problem if ever you wish to install Project Server 2007 on the same machine.
  • Give your service account local administrator rights and logon as this account throughout the entire installation process.
  • Install SQL 2005 (and latest service pack) with typical settings.

  • Assign your service account to the 'Security Administrators' and 'Database Creators' server roles in SQL server (You will need to use SQL Server Management Studio).

Base SharePoint Server Install
You are now ready to install SharePoint 2007 itself, follow these steps:
  • Login as your service account
  • Insert your CD (or attach your ISO image) and run setup.exe if it does not autorun.
NOTE: If you get an error about web service extensions here, ensure that 'ASP.net V2.0.50727' web service extension is allowed in IIS. If it is not in the list, perform a 'repair' on .net 3.0 framework using add/remove programs and then the web service extension will appear in the list. This is caused when IIS is installed after the .net framework
  • Enter your CD key and accept the license agreement.
  • Choose 'Advanced' on the installation type dialog.

NOTE: The definition of 'Advanced' means that you are using full SQL server (which may or may not be on the same machine). If you had selected 'Basic' then it would have installed the cut down version of SQL (MSDE).
  • Select 'Complete' on the Server Type screen and click 'Install Now'. The setup will now commence and you'll get a blue progress bar.
  • Once installed you will get a screen with a check box that reads "Run the SharePoint products and Technologies Wizard now". Ensure this is ticked and click 'Close'.
  • After a short pause, you'll get a 'Welcome' screen. Click 'Next'.
  • You will get a warning that the wizard is about to reset several services, click 'Yes'.
  • You'll be asked about the farm configuration, select to 'No, I want to create a new server farm'.
  • Provide the database server (your server name) and your account details (account in the domain\user format). Leave the database name as the default. Click 'Next'.
  • Leave the authentication mode as 'NTLM', set a specific port number is desired (not required) and click 'Next'.

NOTE: In a production environment, you would most likely use Kerberos where possible (if your infrastructure supports it).
  • You'll get a summary screen; click 'Next' to kick-off the process.

NOTE: If it fails here, it is most likely that you do not SQL setup correctly. Ensure your service account is in the right groups. Please also note that this section can take a very long time, especially step 2 (up to 45 minutes).
  • You'll get a success screen at the end, click 'Finish'.
  • The wizard will attempt to load the central administration window. You may need to login here, use your service account. You may also get prompted to add the site to your trusted sites; go ahead and do that.

NOTE: This authentication prompt is caused by the secure version of IE on Windows 2003 Server. You can turn if off by modifying the security settings in IE.

Services on Server Configuration
The first bit of configuration to do is set your server to host all services. You do not strictly have to enable all of these services, but I find it helps if you are using the machine to test / investigate functionality.
  • When the Central Administration screen appears, go to 'Operations' tab, then 'Services on Server'.
  • Start the 'Document Conversions Load Balancer Service'.
  • Start the 'Document Conversions Launcher Service', you'll have to choose the 'Load Balancer Server'; there should only be one option. If there are no options, ensure that the 'Document Conversions Load Balancer Service' has been started.
  • Start the 'Excel Calculation Services'.

  • Start the 'Office SharePoint Servers Search' service, observing the following guidelines:
    • Tick both Query and Indexing check boxes
    • Specify a contact email address (this can be any address)
    • Enter your service account in the 'Farm Search Service Account' section
    • Accept all other defaults and click 'Start'
  • Leave all remaining services in their default configuration

Web Application Setup
The next stage is to create the 3 web applications that will be required to host the basic set of sites for a typical deployment, these are:
  • Shared Service Provider Administration Site (Recommended to be called 'SSPAdmin')
  • My Site Host (Recommended to be called 'MySite')

  • The Main Intranet (or 'Portal') Site (Recommended to be called 'Intranet')

It is much simpler if all of these sites are on port 80 in IIS; this means that you do not have to remember to enter the ports all of the time. However having all three sites on port 80 means that each needs their own Host Header (required by IIS to differentiate between sites on the same port). The simplest way to do this is to create new 'Host (A)' records in DNS for each of your three sites. These should point to the IP address of your server; to do this follows these steps:
  • Open the DNS Management tool from Administration Tools on your domain controller
  • Navigate to your DNS zone
  • Create new 'Host (A)' record
  • Enter the Host header (i.e. 'SSPAdmin', 'MySite' or 'Intranet') for the site and the IP address of your server

  • Click 'Add Host' and repeat for each of the three sites

Now the DNS entries are configured, we can create the three web applications in SharePoint; follow these steps for all three of your web applications (i.e. 'SSPAdmin', 'MySite' or 'Intranet'):
  • In Central Administration, go to the 'Application Management' tab
  • Click 'Create or Extend Web Application' and then click 'Create a new Web Application'

  • Fill out the new web application screen observing the following points:
    • Change the New IIS Site description to read something like 'SharePoint – 80 - <Host header name>' where <Host header name> is the name of the web application your are creating (i.e. 'SSPAdmin', 'MySite' or 'Intranet')
    • Ensure the 'Port' is set to 80
    • Set the 'Host Header' to match the DNS record you created (i.e. 'SSPAdmin', 'MySite' or 'Intranet')
    • Change the 'Application Pool Name' to match the 'New IIS Site Description'
    • Enter your service account for the Application Pool account settings
    • Change the 'Database Name' to read something like 'WSS_Content_<Host header name>' where <Host header name> is the name of the web application your are creating (i.e. 'SSPAdmin', 'MySite' or 'Intranet')
    • Leave all other settings on default and click 'OK'

  • Repeat for all three web applications (i.e. 'SSPAdmin', 'MySite' or 'Intranet')

Shared Service Provider Setup
The next stage is to create the Shared Service Provider (SSP). The SSP is required in order to provide several key services such as Search or My Site. You can read more about SSP on my blog article about it here. To configure the SSP, follow these steps:
  • In Central Administration, go to the 'Application Management' tab
  • In the 'Office SharePoint Server Shared Services' section, click 'Create or Configure This Farms' Shared Services'
  • Click 'New SSP'

  • Fill out the 'New Shared Services Provider' screen observing the following guidelines:
    • For the 'SSP Administration Site' web application (the first one you get asked for), choose the web application that you created earlier (suggested name was 'SharePoint – 80 - SSPAdmin')
    • For the 'My Site Location' web application (the second one you get asked for), choose the web application you created earlier (suggested name was 'SharePoint – 80 - MySite')
    • Enter your service account for the 'SSP Service Credentials'
    • Leave all other settings on default and click 'OK'

  • The creation of an SSP can take some time (up to 1 hour on a virtual machine). When it is finished you will see a 'Success!' screen, Click OK.

Collaboration Portal Site Collection Setup
The next stage is to create a collaboration portal which is one of the more feature-filled site types and represents a typical intranet environment. To do this, follow these steps:
  • In Central Administration, go to the 'Application Management' tab
  • In the 'SharePoint Site Management' section, choose 'Create Site Collection'

  • Fill out the 'Create Site Collection' observing the following guidelines:
    • Ensure you have selected the 'Intranet' web application you created earlier (suggested name was 'Intranet')
    • Give your site a title ('Intranet' is suggested)
    • In the 'Template Selection' section, choose 'Collaboration Portal' from 'Publishing' tab
    • Enter you service account for the 'Primary Site Collection Administrator'
    • Leave all other settings on default and click 'OK'

  • When the 'Top-Level Site Successfully Created' message appears you have created the site, simply click the link that is provided (something like http://intranet/)

Configure Indexing
The final step of the process is to configure indexing so that you have some search results. Though this step is optional, it is recommended as it will enable you to use the powerful search capabilities of SharePoint. To configure the index, follow these steps:
  • In Central Administration, click the 'SharedServices1' link on the left-side navigation (or whatever you name your SSP)
  • When the SSP Administration site appears, click on 'Search Settings' in the 'Search' section
  • On the 'Configure Search Settings' page, click 'Content Sources and Crawl Schedules'
  • Edit the 'Local office SharePoint Server Sites' content source by hovering your mouse over it and choosing 'Edit'

  • Fill out the 'Edit Content Source' observing the following guidelines:
    • Set a full crawl schedule to be at least once a day
    • Set a incremental crawl schedule for every 10 minutes
    • Tick the 'Start Full Crawl of this Content Source' tick-box
    • Click 'OK'

  • A crawl will now start. Initial crawls normally take up to 10 minutes.
The process is now complete. User should be able to access the main collaboration portal from http://intranet (or whatever you called the DNS record).
I hope this was useful, please comment with any errors or amendments

Sunday, June 1, 2008

ASP.Net Quick Tips

Select the Release mode before making the final Build for your application.

This option is available in the Top Frame just under the Window Menu option. By default, the Mode is Debug

Use Foreach loop instead of For loop for String Iteration


Use strString=string.Empty instead of strString=""  And perhaps instead of strString=null also (?)


GIF and PNG are similar,
but PNG typically produces a lower file size. (True, but some browsers not supporting PNG format)



Deploy application with debug="false"

When we develop asp.net application using Visual Studio, the default value for debug attribute is true. This setting will help developers to debug the application in development environment. For example, executing the application in this mode will not cache the resource files rendered by WebResources.axd handler. This prevents the need to clear the temporary cache every time when the developer needs to check the changes done. There will be other useful things done for developers for debugging like debug symbols, settings that will enable breakpoints etc. These setting will give a poor performance in production if released in the default debug mode (false).

So, never release your website with debug mode set to true. It should be set to false in web.config when moving to production. &lt;compilation debug="true"&gt;

Alternate will be in machine.config. If you are a server administrator, make this change in machine.config <deployment retail=”true”/> so that it will enforce the debug attribute in the application’s web.config to false. It also disables the page output tracing and the ability to show the detailed exception report to the remote users when there is an exception.

<configuration>
    <system.web>
          <deployment retail=”true”/>
    </system.web>
</configuration>



Turn off Tracing

Turn off Tracing unless until required. (by default it's off, use on the pages where it's required)
<trace enabled="false" requestLimit="10" pageOutput="false" traceMode="SortByTime" localOnly="true"/>

Using Server.Transfer instead of Response.Redirect 


Response.Redirect sends a metatag to the client that makes the client send a new request to the server by using the new URL. Server.Transfer avoids this indirection by making a server-side call.

View State Optimization
Avoid using ViewState for storing huge objects or disable it when you don’t need it. ViewState is also used by server controls so that they can retain their state after postback. You can also save your objects that are marked Serializable in the ViewState. ASP.NET serializes all objects and controls in the ViewState and transmits them in a hidden field to the browser. If not managed properly ViewState can increase page size and therefore increase network traffic. Also precious CPU cycles are used for Serialization and De-Serialization of ViewState objects. Disable ViewState if:

  • Your pages don’t do postback.

  • You controls are not bound to a data source or they don’t handle server events like OnClick, OnSelectedIndexChanged etc or their properties are set on each postback

  • You recreate controls on every postback.
You can disable ViewState in both web.config or @Page directive
      <pages enableViewState="false">      or
      <%@ Page EnableViewState="false"%>


 

Page.IsPostBack is your friend


Use the Page.IsPostBack property to ensure that you only perform page initialization logic when a page is first loaded and not in response to client postbacks. The following code fragment shows how to use the Page.IsPostBack property.



if (!Page.IsPostBack) //IsPostBack property will be false for the first time
{
  //load the dataset for the first time
}
else               
{
  //use the loaded dateset for other post back requests
}




Remove unnecessary Http Modules for faster pipeline


Make sure you don’t execute code needlessly. I don’t know how many web developers forget about checking IsPostBack! It seems like such a basic thing to me! Needless processing!

There are several ASP.NET default HttpModules which sit in the request pipeline and intercept each and every request. For example, SessionStateModule intercepts each request, parses the session cookie and then loads the proper session in the HttpContext. Not all of these modules are always necessary. For example, if you aren't using Membership and Profile provider, you don't need FormsAuthentication module. If you aren't using Windows Authentication for your users, you don't need WindowsAuthentication. These modules are just sitting in the pipeline, executing some unnecessary code for each and every request.

The default modules are defined in machine.config file (located in the $WINDOWS$\Microsoft.NET\Framework\$VERSION$\CONFIG directory).

&lt;httpModules&gt;
&lt;add name="OutputCache" type="System.Web.Caching.OutputCacheModule"/&gt;
&lt;add name="Session" type="System.Web.SessionState.SessionStateModule"/&gt;
&lt;add name="WindowsAuthentication" ="System.Web.Security.WindowsAuthenticationModule"/&gt;
&lt;add name="FormsAuthentication" type="System.Web.Security.FormsAuthenticationModule"/&gt;
&lt;add name="PassportAuthentication" type="System.Web.Security.PassportAuthenticationModule"/&gt;
&lt;add name="RoleManager" type="System.Web.Security.RoleManagerModule"/&gt;
&lt;add name="UrlAuthorization" type="System.Web.Security.UrlAuthorizationModule"/&gt;
&lt;add name="FileAuthorization" type="System.Web.Security.FileAuthorizationModule"/&gt;
&lt;add name="AnonymousIdentification" type="System.Web.Security.AnonymousIdentificationModule"/&gt;
&lt;add name="Profile" type="System.Web.Profile.ProfileModule"/&gt;
&lt;add name="ErrorHandlerModule" type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/&gt;
&lt;add name="ServiceModel" type="System.ServiceModel.Activation.HttpModule, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/&gt;httpModules&gt;

You can remove these default modules from your Web application by adding nodes in your site's web.config. For example:

&lt;httpModules&gt;        
&lt;remove name="Session" /&gt;     
&lt;remove name="WindowsAuthentication" /&gt;    
&lt;remove name="PassportAuthentication" /&gt;    
&lt;remove name="AnonymousIdentification" /&gt;     
&lt;remove name="UrlAuthorization" /&gt;     
&lt;remove name="FileAuthorization" /&gt;    
&lt;remove name="OutputCache" /&gt;    
&lt;remove name="RoleManager" /&gt;    
&lt;remove name="Profile" /&gt;    
&lt;remove name="ErrorHandlerModule" /&gt;    
&lt;remove name="ServiceModel" /&gt;  httpModules&gt;


Connection Pooling

Creating a connection to a database is a resource intensive process and takes time. Connection pooling allows you to reuse these connections saving time and resources. When a new connection is requested the connection pool managers first searches in the connection pool and if doesn’t finds one, it creates a new one. There are various things that need to be done to use connection pooling effectively:

  • Avoid Connection Leakage. This means that you opened a connection but didn’t close it. If you don’t close the connection the connection pool manager will never put it in the pool for later reuse until the GC is called.

  • Use the same connection string. Connection pool manager searches for similar connection in the pool by the connection string.

  • Use SQL Servers and .NET CLR Data performance counters to monitor pooling.

  • Open connections as late as possible and close them as early as possible

  • Don’t share same connection between multiple function calls. Instead open a new connection and close it in each function.

  • Close transactions prior to closing the connection.

  • Keep at least one connection open to maintain the connection pool.
Avoid DataBinder.Eval

Avoid calling DataBinder.Eval multiple times for example in case of grids, repeaters etc. Instead use Continer.DataBind. DataBinder.Eval uses reflection to evaluate the arguments and therefore can decrease performance if called numerous times.

Thursday, May 1, 2008

SQL Server - Insert value in Identity column

SQL IDENTITY_INSERT is allow values to be inserted into the identity column of a table.
Identity column creates a numeric sequence value for you automatic.

It only alow one table in a session can have the IDENTITY_INSERT property set to ON.
If you need to change the table, you need to set the existing table IDENTITY_INSERT back to OFF.

SET IDENTITY_INSERT TableName OFF

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

SET IDENTITY_INSERT TableName ON

Wednesday, April 2, 2008

SQL SERVER – Get Date of All Weekends of the Year

Beginning SQL Server 2005 Programming (Programmer to Programmer)

how to generate the date for all the Sundays in the upcoming year?

DECLARE @Year AS INT,

@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME


-- You can change @year to any year you desire


SELECT @year = 2010
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)


-- Creating Query to Prepare Year Data


;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear


)
SELECT FromDate AS Date, Dayname
FROM CTE
WHERE DayName IN ('Saturday','Sunday')
/*
WHERE DayName IN ('Saturday,Sunday') -- For Weekend
WHERE DayName NOT IN ('Saturday','Sunday') -- For Weekday
WHERE DayName LIKE 'Monday' -- For Monday
WHERE DayName LIKE 'Sunday' -- For Sunday
*/
OPTION (MaxRecursion 370)

The result will be dates along with days in next column as expected.

Inside Microsoft SQL Server 2005: T-SQL Querying (Solid Quality Learning)Microsoft SQL Server Standard Edition 2005 32 Bit CD/DVD 5 Client

Wednesday, March 5, 2008

ASP.NET - Visual Studio 2008 Web Deployment Projects

Visual Studio 2008 Web Deployment Projects is an add-in to Visual Studio 2008 which provides developers with advanced compilation and deployment options. A Web Deployment project is extensible, enabling web developers to create pre-build and post-build actions.

Web Deployment projects do not change the way Visual Studio 2008 Web Sites or Web Application Projects build. Instead, they take an existing Web project as input and generate a precompiled Web as an output. A Web Deployment project does not change the files in the source Web site project in any way either.

Visual Studio 2008 Web Deployment Projects has all the functionalities that Visual Studio 2005 Web Deployment Projects had. Developers can read more about this core functionality at Using Web Deployment Projects with Visual Studio 2005.

Sunday, February 24, 2008

SQL SERVER – Paging Query Technique

I decided to checkout new function ROW_NUMBER() in SQL Server 2005. ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. I have compared both the following query on SQL Server 2005.

SQL 2005 Paging Method

SQL 2005 Paging Method Using Derived Table

USE AdventureWorks

GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140

SELECT FirstName, LastName, EmailAddress
FROM (
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC) PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO

SQL 2005 Paging Method Using CTE

USE AdventureWorks

GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120;
SET @EndRow = 140;
WITH PersonContact AS
(
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC)
SELECT FirstName, LastName, EmailAddress
FROM PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO








Above Image of Execution Plan displays that the performance for both of them is same with regarding to each other in one batch. This MAY NOT be true when there is complex query in issue. For most of the time, it is will be same.

Friday, January 18, 2008

SQL SERVER – DDL, DML, DCL and TCL Commands

DML
DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.
SELECT – Retrieves data from a table
INSERT -  Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table

DDL
DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.
CREATE – Creates objects in the database
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to initial value.

DCL
DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.
GRANT – Gives user’s access privileges to database
REVOKE – Withdraws user’s access privileges to database given with the GRANT command

TCL
TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.
COMMIT – Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in transactions
SAVE TRANSACTION – Sets a savepoint within a transaction