Coder's junkyard: 2011

Sunday, December 04, 2011

Join hints in Sql Server

SQL Server employs three types of join operations:

*      Nested loops joins

*      Merge joins

*      Hash joins

If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons. For more information about nested loops, see Understanding Nested Loops Joins.

If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation. If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance. However, hash join operations are often much faster if the two input sizes differ significantly from each other. For more information, see Understanding Merge Joins.

Hash joins can efficiently process large, unsorted, non indexed inputs.

Source : MSDN Advanced Query Tuning Concepts:

Wednesday, June 01, 2011

Parsing HTML string with ease

You need Microsoft.mshtml.dll reference. Along with follwing piece of code to load a html string into DOM model

string MsgBodyHtml = "<html>your html string data goes here</html>";

object[] oPageText = { MsgBodyHtml };

HTMLDocument doc = new HTMLDocumentClass();

IHTMLDocument2 doc2 = (IHTMLDocument2)doc;

doc2.write(MsgBodyHtml);

//Now doc2 can be parsed as we do in javascript style.
//Here we go
// In this sample HTML page contains mulitple tables followed by span ( category is written in them)

foreach (IHTMLElement ef in (IHTMLElementCollection)doc2.all.tags("SPAN"))

{

HTMLSpanElement il = (HTMLSpanElement)ef;

if (il.outerText != null && il.outerText.Length > 0 && il.outerText.Contains("Category "))

{

try

{

HTMLTableClass htTable = (HTMLTableClass)((HTMLDTElement)il.parentElement.parentElement).nextSibling;

Category_Body = htTable.outerText;

}

catch (Exception exx) { }

}

Monday, May 30, 2011

Boosting SQL Server throughput - Part II

This is continuation to part 1 of boosting sql server throughput series. I had been away from this series for a long long time. It is all about finding missing indexes on a particular database/table. You can modify this script as well to cater your needs. Missing indexes- refers to indexes those should have been on that table to optimize its performance. Here we go,

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

IF OBJECT_ID('dbo.Proc_MissingIndexes', 'P') IS NOT NULL DROP PROCEDURE dbo.Proc_MissingIndexes
GO

-- EXECUTE Proc_MissingIndexes 'model', '%', 1

CREATE PROCEDURE dbo.Proc_MissingIndexes
    @SchemeName sysname='',
    @TableName sysname='',
    @Sort Tinyint=1,
    @Delimiter VarChar(1)=','
AS

SELECT
    sys.schemas.schema_id, sys.schemas.name AS schema_name,
    sys.objects.object_id, sys.objects.name AS object_name, sys.objects.type,
    partitions.Rows, partitions.SizeMB,
    CASE WHEN @Delimiter=',' THEN sys.dm_db_missing_index_details.equality_columns ELSE REPLACE(sys.dm_db_missing_index_details.equality_columns, ',', @Delimiter) END AS equality_columns,
    CASE WHEN @Delimiter=',' THEN sys.dm_db_missing_index_details.inequality_columns ELSE REPLACE(sys.dm_db_missing_index_details.inequality_columns, ',', @Delimiter) END AS inequality_columns,
    CASE WHEN @Delimiter=',' THEN sys.dm_db_missing_index_details.included_columns ELSE REPLACE(sys.dm_db_missing_index_details.included_columns, ',', @Delimiter) END AS included_columns,
    sys.dm_db_missing_index_group_stats.unique_compiles,
    sys.dm_db_missing_index_group_stats.user_seeks, sys.dm_db_missing_index_group_stats.user_scans,
    sys.dm_db_missing_index_group_stats.avg_total_user_cost, sys.dm_db_missing_index_group_stats.avg_user_impact,
    sys.dm_db_missing_index_group_stats.last_user_seek, sys.dm_db_missing_index_group_stats.last_user_scan,
    sys.dm_db_missing_index_group_stats.system_seeks, sys.dm_db_missing_index_group_stats.system_scans,
    sys.dm_db_missing_index_group_stats.avg_total_system_cost, sys.dm_db_missing_index_group_stats.avg_system_impact,
    sys.dm_db_missing_index_group_stats.last_system_seek, sys.dm_db_missing_index_group_stats.last_system_scan,
    (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0) AS Score
FROM
    sys.objects
    JOIN (
        SELECT
            object_id, SUM(CASE WHEN index_id BETWEEN 0 AND 1 THEN row_count ELSE 0 END) AS Rows,
            CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
        FROM sys.dm_db_partition_stats
        WHERE sys.dm_db_partition_stats.index_id BETWEEN 0 AND 1 --0=Heap; 1=Clustered; only 1 per table
        GROUP BY object_id
    ) AS partitions ON sys.objects.object_id=partitions.object_id
    JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
    JOIN sys.dm_db_missing_index_details ON sys.objects.object_id=dm_db_missing_index_details.object_id
    JOIN sys.dm_db_missing_index_groups ON sys.dm_db_missing_index_details.index_handle=sys.dm_db_missing_index_groups.index_handle
    JOIN sys.dm_db_missing_index_group_stats ON sys.dm_db_missing_index_groups.index_group_handle=sys.dm_db_missing_index_group_stats.group_handle
WHERE
    sys.dm_db_missing_index_details.database_id=DB_ID()
    AND sys.schemas.name LIKE CASE WHEN @SchemeName='' THEN sys.schemas.name ELSE @SchemeName END
    AND sys.objects.name LIKE CASE WHEN @TableName='' THEN sys.objects.name ELSE @TableName END
ORDER BY
    CASE @Sort
        WHEN 1 THEN
            (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0)*-1
        WHEN 2 THEN
            (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0)
        ELSE NULL
    END,
    CASE @Sort
        WHEN 3 THEN sys.schemas.name
        WHEN 4 THEN sys.schemas.name
        WHEN 5 THEN sys.schemas.name
        ELSE NULL
    END,
    CASE @Sort
        WHEN 1 THEN sys.dm_db_missing_index_group_stats.user_seeks*-1
        WHEN 2 THEN sys.dm_db_missing_index_group_stats.user_seeks
    END,
    CASE @Sort
        WHEN 3 THEN sys.objects.name
        WHEN 4 THEN sys.objects.name
        WHEN 5 THEN sys.objects.name
        ELSE NULL
    END,
    CASE @Sort
        WHEN 1 THEN sys.dm_db_missing_index_group_stats.avg_total_user_cost*-1
        WHEN 2 THEN sys.dm_db_missing_index_group_stats.avg_total_user_cost
        WHEN 4 THEN
            (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0)*-1
        WHEN 5 THEN
            (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0)
        ELSE NULL
    END,
    CASE @Sort
        WHEN 3 THEN sys.dm_db_missing_index_details.equality_columns
        ELSE NULL
    END
GO
--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

Wednesday, April 06, 2011

What’s new in SQL 2008

Perquisites: You should have SQL 2008 and .NET Framework 4

Table-Valued Parameters

1) Create your data type first,

The following statement creates a table type named CategoryTableType that consists of CategoryID and CategoryName columns:

CREATE TYPE dbo.CategoryTableType AS TABLE

( CategoryID int, CategoryName nvarchar(50) )

2) Create a stored procedure, which will cosume this newly created table 'type'

After you create a table type, you can declare table-valued parameters based on that type. The following Transact-SQL fragment demonstrates how to declare a table-valued parameter in a stored procedure definition. Note that the READONLY keyword is required for declaring a table-valued parameter.

CREATE PROCEDURE usp_UpdateCategories

(@tvpNewCategories dbo.CategoryTableType READONLY)

-- You can use this @tvpNewCategories table for insert/update/select joins, anything as a normal table

UPDATE dbo.Categories

SET Categories.CategoryName = ec.CategoryName

FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec

ON dbo.Categories.CategoryID = ec.CategoryID;

3) Passing table from code to stored procedure

 // Assumes connection is an open SqlConnection object.

using (connection)

// Create a DataTable with the modified rows.

DataTable addedCategories =

  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.

SqlCommand insertCommand = new SqlCommand(

    "usp_InsertCategories", connection);

insertCommand.CommandType = CommandType.StoredProcedure;

SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(

    "@tvpNewCategories", addedCategories);

tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.

insertCommand.ExecuteNonQuery();

Limitations

1)      You cannot pass table-valued parameters to user-defined functions.

2)     Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters.

3)     Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.

4)      You cannot use ALTER TABLE statements to modify the design of table-valued parameters.

Monday, April 04, 2011

New features in C# 3.0

Prerequisites :You should have Visual Studio 2008 Professional Edition

Implicitly Typed Local Variables

Let us start by discussing the implicitly typed local variables feature.
C# 3.0 introduces a new keyword called "var". This new keyword enables you to declare a variable whose type is implicitly inferred from the expression used to initialize the variable. For example, consider the following line of code:
var age = 30;
The preceding line initializes the variable age to value 30 and automatically gives it the type of integer. Note that the variable age is a strongly typed variable (integer in this case) and doesn't carry the overhead of a generic object type such as System.Object class.
Note that the var keyword is not a completely new type and it just signals the compiler to take a look at the right-hand side of the expression to decipher the type. If the right-hand side is an int, the compiler will replace the var keyword with int. Here are some of the key characteristics of implicitly typed local variables.

They need to be declared and initialized in the same statement.
They can't be initialized to null.
They can't be used as class members.
They are primarily used to declare anonymous types (which you will see later in this article) as part of a LINQ expression.

Implicitly Typed Arrays

In the previous section, you have seen how to use the var keyword for implicitly typing variables. In addition to implicitly declaring variables, you can also use the var keyword for declaring arrays as well. For example, consider the following lines of code:

 int[] numbers = new int[] { 1, 2, 3, 4, 5};

string[] names = new string[] { "Dave", "Doug", "Jim" };

By using the var keyword, you can rewrite the preceding lines of code as follows:

 var numbers = new[] { 1, 2, 3, 4, 5};

var names = new[] { "Dave", Doug, "Jim" };

The compiler infers the type of the array elements at compile-time by examining the values from the initialization expression.

Auto Implemented Properties

Whenever you declare a class, most of the times the class is used only as a placeholder with getters and setters for holding property values without any additional logic. For example, consider a simple class like the following:

 public class Person

{

    int _id;

    string _firstName;

    string _lastName;



    public int ID 

    {

        get{return _id;}

        set{_id = value;}

    }

    public string FirstName

    {

        get{return _firstName;}

        set{_firstName = value;}

    }

    public string LastName

    {

        get{return _lastName;}

        set{_lastName = value;}

    }

    public string FullName

    {

        get{return FirstName + " " + LastName;}

    }

}

As you can see from the above class declaration, it doesn't contain any additional logic. The get and set properties are repetitive and they simply set or get the values of the properties without adding any value. In C#, you can simplify that by leveraging the new feature named Auto-Implemented properties. By taking advantage of this new feature, you can rewrite the above code as follows:

 public class Person

{

    public int ID { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string FullName

    {

        get

        {

            return FirstName + " " + LastName;

        }

        private set {;}

    }

}

In the above code, when you declare a property, the compiler automatically creates a private, anonymous field that is available only to the property's get and set accessors. Note that the auto implemented properties must declare both a get and set accessor. However if you need to create a read-only property, modify the scope of the set accessor to be private

Object Initializers

In previous versions of C#, you would create the object in one step and then populate its properties in a separate next step. However with C# 3.0, you can create the object as well as set its properties in a single line of code. For example, consider the following lines of code:

 Person obj = new Person();

obj.ID = 1;

obj.FirstName = "Thiru";

obj.LastName = "Thangarathinam";

MessageBox.Show(obj.FullName);

In the above code, you create the Person object and set its properties appropriate values. By leveraging the object initializers feature, you can simplify the above code as follows:

 Person obj = new Person { ID = 1, FirstName = "Thiru", LastName = "Thangarathinam" };

MessageBox.Show(obj.FullName);

In the above code lines of code, you directly assign the values to the properties of the Person object. Although you can simulate this behavior using constructor for the object, object initializers reduce the need to have a specific constructor for every variation of argument variation we need over time.
This becomes all the more important in cases where you need to write LINQ based select projection queries which transform and create a new object for the results of the query.

Collection Initializers

In C# 2.0, when you want to populate a collection, you need to write the following line of code:

 List<string> names = new List<string>();

names.Add("David");

names.Add("Tim");

names.Add("Doug");

Now with the introduction of the new collection initializers feature in C# 3.0, you can shorten the above lines of code to a single line of code:
List<string> names = new List<string> {"David", "Tim", "Doug"};

Anonymous Types

As the name suggests, anonymous types allow you to create a type on-the-fly at compile time. The newly created type has public properties and backing fields defined for the members you initialize during construction. For example, consider the following line of code:
var obj=new{ID=1,FirstName="Thiru",LastName="Thangarathinam"};
In the above line, you just specify the various attributes you want to have in the anonymous class and assign the instantiated object to a variable of type "var". The actual type assigned to obj is determined by the compiler. Since the compiler assigns the name of the type only at compile time, you can't pass an anonymous type to another method and it can only be used within the method they were declared.
When the compiler sees the above code, it automatically declares a class as follows:

 class __Anonymous1

{

    private int _id = 1;

    private string _firstName = "Thiru";

    private string _lastName = "Thangarathinam";



    public int ID

    {

        get{return _id;}

        set{_id = value;}

    }

    public string FirstName

    {

        get{return _firstName;}

        set{_firstName = value;}

    }

    public string LastName

    {

        get{return _lastName;}

        set{_lastName = value;}

    }

}

Anonymous Types use the Object Initializer to specify what properties the new type will be declare. This allows us to reduce code looking similar to this:
Note that the anonymous types are just meant to be placeholders for quickly defining entity types and you can't add methods or customize the behavior of an anonymous type

Extension Methods

Another important feature introduced with C# is the ability to add new static methods to existing classes, known as extension methods. Using this new feature, you can extend the built-in classes (such as the String class) to support your custom requirements. For example, you can add a new method named "IsValidZipCode" to the string class that validates the zip code format. Let us discuss the code required to accomplish this:

 namespace StringExtensions

{

    public static class CustomStringExtension

    {

        public static bool IsValidZipCode(this string input)

        {

            Regex regEx = new Regex(@"^\d{5}$");

            return regEx.IsMatch(input);

        }

    }

}

As part of the declaring the arguments for the IsValidZipCode, you specify the name of the type to which the extension method should be added as the first parameter. In this case, since we want the IsValidZipCode method to be added to the string class, you specify string as the first parameter. Once you are inside the IsValidZipCode() method, you can access all of the public properties/methods/events of the actual string instance that the method is being called on. In this example, you return true or false depending on whether it is a valid zip code or not.
Now that you have implemented the extension method, the next step is to invoke it from the client application. To be able to do that, you first need to import the namespace in which the CustomStringExtension is located.
using StringExtensions;
Once you have imported the namespace, the next step is to declare a variable of type string and invoke the IsValidZipCode() method.

 private void btnTestExtensionMethod_Click(object sender, EventArgs e)

{

    string zip = "85226";

    if (zip.IsValidZipCode())

        MessageBox.Show("Valid Zipcode format");

    else

        MessageBox.Show("Invalid Zipcode format");

}

As you can see from the preceding lines of code, the extension methods allow you to write cleaner and easy-to-maintain code.
Here are some of the key characteristics of extension methods:

The extension method as well as the class that contains the extension method should be static.
Although extension methods are static methods, they are invoked as if they are instance methods.
The first parameter passed to the extension method specifies the type on which they operate and it is preceded by the "this" keyword.
From within the extension method, you can't access the private variables of the type you are extending.
Instance methods take precedence over extension methods in situations where they have same signature.

Lambda Expressions

Anonymous methods is a new feature introduced with C# 2.0 that enables you to declare your method code inline instead of with a delegate function. Let us take a look at a simple anonymous method:

 public Forms()

{

    check = new CheckBox(...);

    text  = new TextBox(...);

    checkBox.CheckedChanged += delegate

    {

        text.Text = "...";

    };

}

As you can see in the above code, you don't have to explicitly declare a new method to link it with an event. C# 3.0 introduces an even simpler syntax, lambda expressions, which you write as a parameter list followed by the "=>" token, followed by an expression or a statement block.
Lambda expressions are simply functions and they are declared in the context of expressions than as a member of a class. It is an inline expression or a statement block which can be used to pass arguments to a method or assign value to delegate. All lambda expressions use the lambda operator => and the left side of the operator denotes the results and the right side contains the expression to be evaluated. For instance, consider the following lambda expression:
age => age + 1
The above function takes one argument named age, and returns age + 1 as the result. As you can see, Lambda expressions follow the below syntax:
(parameter-list) => expression;
where expression can be any C# expression or a block of code. Just like anonymous methods you can use a lambda expression in place of a delegate. Here are some sample lambda expressions and their corresponding delegates.

 //Explicitly typed parameter

(Person obj) => MessageBox.Show(obj.FirstName.ToUpper());



//Implicitly typed parameter

(obj) => obj.FirstName == "Thiru";



//Explicitly typed parameter

(int a, int b) => a + b



//Implicitly typed parameter

(x, y) => { return x + y; }

As you see from the preceding lines of code, lambda expressions can be written in such a way that it can infer the parameter type from the signature of the delegate it is assigned to.
Conclusion

In this article, you have understood the object oriented features introduced with C# 3.0. Specifically,

How to use auto-implemented properties for creating placeholder classes
How to initialize object and collections using a simple, intuitive syntax
How to extend built-in classes using extension methods
How to use anonymous types for creating classes on-the-fly
How to use Lambda expressions to declare method inline

As you can see, the new features of C# make the development of .NET applications a breezy experience by reducing the number of lines of code to perform common operations such as initializing an object.

Monday, February 07, 2011

If the end goals are blurry or not specified, then they will never be attainable.

Seeing (Imagining, forecasting within boundary) is what we achieve in long run.

Somehow clients always seem to expect more than we are prepared to deliver. This expectation gap is more the result of a failure to communicate than it is of anything else, and this lack of communication starts at the beginning of a project and extends all the way to the end. This definitely does not have to be the case. It is the project manager’s job to utilize effective and efficient communication to sort out customers needs and to appropriately set customer expectations and team expectations early in the project to ensure the end goals are correct and attainable. If the end goals are blurry or not specified, then they will never be attainable.

Sorting Wants versus Needs

The root cause of many problems that come up in the course of a project originate in a disconnect between what the client says they want and what they really need. The disconnect may come about because the client is swept up in a euphoria over the technology and is so enamored with what they see for potential technologies and solutions that they have convinced themselves they have to have it without any further thought of exactly what it is they really need.

The disconnect can also come about because the client does not really know what they need. It is the job of the project manager and team to ask the right questions and extract the needs out from behind the wants. If there is any reason to believe that what the client says they want is different from what they need, the project manager has the responsibility of sifting and sorting this out ASAP. It would be a mistake to proceed without having the assurance that wants and needs are in alignment. You don’t want to start the project not knowing that the solution is in fact what will satisfy the client. The project Statement of Work, or SOW, developed early in the discussion phases of the project will begin to lay this foundation for the project team but may not fully sort out the customer needs from wants.

Problems with listening and communication

If I had to pick one area where most projects run into trouble, I would pick the very beginning. For some reason, people have a difficult time understanding what they are saying to one another. How often do you find yourself thinking about what you are going to say while the other party is talking? If you are going to be a successful project manager, you must stop and listen. Proactive thinking and planning is great – but not at the expense of hearing what the customer is expressing early in the project planning process. An essential skill that project managers need to cultivate is good listening skills.

Deliver on the right criteria

Through proper, efficient, and effective communication, the project team should be able to become aware of what it is exactly that the customer needs…rather than wants. It may be difficult to break through the barriers that are hiding these true customer needs and even harder to convince the customer that what they say they want isn’t exactly what they are indicating they need.

The thing to remember – and to remind the customer of – is that you have the team of implementation experts and the customer came to you with the project. It is your job to deliver the right project to them and a solution that their end users can actually use. You’ll need to convince them that your proposed solution is actually what they need, though it may be contradicting what they say they want.