My First Monad ... Simple ... Yet Immensely Useful

Motivation

Ever dreamed about putting all your exception handling or logging stuff at a single place? Imagine the maintenance problems it can solve. How to do it? Use a monad.

Note: Same code is shown in both images and text. Output is shown only in image.

What is a Monad?

A monad is a wrapper. What is a wrapper? A wrapper is a class that has an object of another class. What is the difference between an ordinary class and a monad? A monad has one special function, a higher-order function. What is a higher-order function? A higher-order function is a function that can take in argument a function. What do the higher-order function do with this input-function? It apply it on the object that the monad is containing. Here is the code:

public class LoggerMonad
{
    public T Value
    {
        get;
        private set;
    }

    public LoggerMonad(T value)
    {
        Value = value;
    }

    public LoggerMonad Bind(Func func)
    {
        Console.WriteLine(func.Method.Name + " is called at " + DateTime.Now.ToString());
        return func(Value).ToLoggerMonad();
    }

    public void Bind(Action action)
    {
        Console.WriteLine(action.Method.Name + " is called at " + DateTime.Now.ToString());
        action(Value);
    }

}

public static class LoggerMonadExtensionMethods
{
    public static LoggerMonad ToLoggerMonad(this T value)
    {
         return new LoggerMonad(value);
    }
}

Explanation

Lets go through it very slowly. You have a class called LoggerMonad. This class is your monad. It can wrap itself around any object, means it can contain an object of any type whatsoever. This class has a constructor which do the initialization stuff. Like all good constructors it do only one thing, initialize fields. There is one higher-order function too, called Bind which is overloaded twice. We will talk about it later. First a quick overview of generics.

Generics

We are using c# generics here. LoggerMonad is a generic class. A generic class is any class that contains a generic field or a generic method. A generic field is a field whose type is not specified inside the class, its specified elsewhere in code. Its still a static type means at the time of compilation the type would be known. Its not a dynamic type means the type is not unclear until runtime. C# is static-typed language which means that it do not allow dynamic types, uptil version 3.5. A generic method is a method that takes in argument or return a variable of generic type.

How To Use This Class?

int i = 1;
LoggerMonad monad = new LoggerMonad(i);

Further Explanation

You not have to use the monad this way. The above code is just a demonstration of how generics work. LoggerMonad can contain a field of any type, what type? the type that is specified when the constructor is called. You can use any type, means you are not limited to integer or numeric types. Note that we do not need to specify type above when calling constructor. Its because c# compiler is intelligent enough to infer the type of "i" variable itself.

Higher-Order Functions

Now, lets talk about the higher-order function. Its overloaded and its generic. The first one, which takes in input a "func", is to be called when you want to return something from your input-function. The second one, which takes in input an "action", is to be called when you don't want to return anything from your input-function. Note that the main point here is what you want to return from the input-function, not the Bind function. You can say that the Bind function is a wrapper around your input-function, so its like a method-level wrapping.

The Input-Function

The input-function itself can return a thing of any type. We have only one Bind method for it, so we have to return a general type of thing. Its because we don't want to have a separate Bind function for all types in c#, including your custom types. How do we do it? Remember that our LoggerMonad can contain an object of any type, so we simply return a LoggerMonad. That solves the problem. Note that our LoggerMonad has a public get around its field T, therefore the client-code can easily access the underlying value that is returned by the input-function.

Extension Methods

The last part of the listing is an extension method. Since its an extension method so you can put it in any static class of your code. I personally prefer to make a separate class for each type and put the related extension methods there. Note that the argument contains a "this", which means that the method ToLoggerMonad is an extension method. The method simply wraps the input value in a new LoggerMonad object and return that object.

public class Program
{
    public static void Main(string[] args)
    {
        string sText = "North Pole is 20,000 km away from the South Pole at any direction except top.";
        int iBeginAt = 0;
        int iEndAt = 10;
        string sCountry = "Pakistan";
        List list = new List;
        int iNumber = 105;
        TestSubstring(sText, iBeginAt, iEndAt);
        TestCount(sText);
        TestStartsWith(sText, sCountry);
        TestAddInList(list, iNumber);

        Console.Read();
    }

    public static void TestSubstring(string sText, int iBeginAt, int iEndAt)
    {
        Func funcSubstring = s => s.Substring(iBeginAt, iEndAt);

        string sSubstring = sText.Substring(iBeginAt, iEndAt);
        Console.WriteLine("sSubstring = " + sSubstring);

        string sSubstring_Monad = sText.ToLoggerMonad().Bind(funcSubstring).Value;
        Console.WriteLine("sSubstring_Monad = " + sSubstring_Monad);
    }

    public static void TestCount(string sText)
    {
        Func funcCount = s => s.Count();

        int iCount = sText.Count();
        Console.WriteLine("iCount = " + iCount);

        int iCount_Monad = sText.ToLoggerMonad().Bind(funcCount).Value;
        Console.WriteLine("iCount_Monad = " + iCount_Monad);
    }
}

Explanation of Client-Code

The above is our client-code. Client-code is always the code that uses the library. Here, our library is all the code in the first image, that is, the LoggerMonad class and the extension method.

The Four Methods In Client-Code

Our client-code calls four methods. Inside each of these methods, a call is made to an instance method directly, that is, without using the monad, and then the same call is made using the monad. You only have to see one of these methods to see the comparison. So, if I have an object called "sText", whatever its type is, and i have a method called "Substring" inside that object, then I can simply call it this way: "sText.Substring(argument1, argument2);" where argument1 and argument2 are any two objects of integer type. Its a simple call but it has following maintenance problems:

1. Most of the time the objects we are using are of library classes whose code we cannot modify. So we cannot put anything inside the methods we are calling.



2. If we want to do something, such as logging, before we call a method, then wherever in our code we have called that method we have to write the logging code there. A copy-paste can do that, but later on, if we want to change the logging related code then we have to go through all of these places and change code there. Even that could be done. What can never be done is change that code after you have ship your library. Its a scenario where you have made a library of your own, deployed that library on computers of your customers, then make a client-code that uses that library, so far so good. But later on if you want to change your client-code without touching your library code then you cannot do that in anyway.



3. You may simply forget to insert the logging code at some places or make some mistake so that the logging code is called differently at different places.
   Its better to keep all the logging code at one place so you only have to change one class if ever a change requirement comes. Note that whatever is said above about logging goes also for exception handling or whatever stuff you want to do before or after execution of your methods.

The First Client-Code Method In Detail

Lets go through first of the methods in our client code, the TestSubstring method. Compare the call at line no. 65 with the call at line no. 68. The first call need not be explained because its the way you actually do your callings now, the new thing is the second way of calling. What we have done is that we have first converted our object "sText" into a LoggerMonad by calling the extension method. Once its a LoggerMonad it can access the Bind method. The overloaded Bind method we are interested here is the one that returns a value. Take a quick look at line no. 21 (image 1) to see the signature of the method.

The first thing to note is that it takes a function in argument, not an action. The difference in two is that a function always returns a value while an action never. The function, that is, the input-function is a generic function. Its last type "R" is the return type. This gives our flexibility to return any type from our function. We are not limited to returning the same type of the wrapped-field inside the monad. This is actually different than the classic use of monads in functional languages such as haskell, and its a technique I invented. It do not deviate us from the essence of monads, because its an enhancement, not a replacement.

We do have to tell the return type. That is what "" is doing at line no. 68. Though we are returning a string from a string object "sText", we are not limited to return the same type. This is demonstrated in the other 3 functions called from the Main method. Lets go back to line no. 21. Take another look at the signature and this type focus on the return type. Although the monad itself is of type "" (see line no. 8 above), the return type is "". Means we apply the Bind function on any LoggerMonad of type T and return another LoggerMonad of any type R. That is why we have put no restriction on the type the LoggerMonad contains. The LoggerMonad can contain a field of any type.

The Bind method applies the function it takes in input on the field the class is containing. This is what the line "func(value)" is doing. We are taking in input a function and applying that function on the "value". Now, here is where it becomes tricky, and this is the thing which is stinging in your object-oriented brain since you have seen the code in the first image. What is going on?

A Look Back At Procedural Programming

Lets go back to the simple world of procedural programming, where there are no objects. Since you are an object-oriented programming so you are supposed to be already an expert in procedural programming, therefore you should understand the basic concept I am going to refer now. In the procedural programming term, when we say "apply a function on a variable v" then we mean that we pass the variable v as an input argument to the function. That is not what we mean here, in the higher-order function sense. When we say that Bind applies the input-function to the field "value" we do not mean "apply" in the same sense as in procedural programming.

Applying A Function

What do we mean then? We mean this: "call the function of the object". The arguments are not mentioned here, in this code. The code "func(value)" of line no. 24 is same as the code "value.func()". Let it sink in your brain. We are not passing value to the func, we are applying func on value, we are calling func of value. We are calling a method called func which is inside value. We are calling value to call its method func. What parameters do func takes then? See line no. 68.

sText.ToLoggerMonad().Bind(funcSubstring).Value;

The Bind Function

Bind is taking in input a function called funcSubstring and then internally calling it on the object the monad is containing. Lets not talk about the return value now. Take a look at what funcSubstring is, line no. 63, listed below too:

Func funcSubstring = s =>
s.Substring(iBeginAt, iEndAt);

We have defined a function here. The function itself takes two arguments "iBeginAt" and "iEndAt". Once inside the Bind the "s" is replaced by the wrapped-field Value. What finally happens is this "sText.Substring(iBeginAt, iEndAt)".
It looks like an unnecessarily long way of calling a method. Instead of doing this:

sText.Substring(iBeginAt, iEndAt)

we have to do this:

Func funcSubstring = s => s.Substring(iBeginAt, iEndAt);
sText.ToLoggerMonad().Bind(funcSubstring).Value;

Why Use Such A Long Code?

It means instead of one simple line we uses two lines. Why? We do not really have to give a name to the input-function. We can very well do this:

sText.ToLoggerMonad().Bind(s => s.Substring(iBeginAt, iEndAt));

We gave a name to the input-function because we are doing logging here and we want to know the name of the function that is called. See output below:

Drawbacks of Monads

At the end, our calling do gets longer, instead of "sText.Substring(iBeginAt, iEndAt)" we have to do "sText.ToLoggerMonad().Bind(funcSubstring).Value". Still we have two advantages: all of our binding logic gets at single place, we can effectively put code inside library functions.

Where Is The Logging Code Anyways?

See line no. 23. Here we are logging at screen. You can put your code here to log in a database or file or whatever.

The Second Overload of Bind Method

We have only one thing left to talk about now. Its the second overload of the Bind method, the one that takes an "action" argument. Its to be called when the input-function do not returns anything. We have to use overload here because c# don't allow "R" to be void in the first overload. See line no. 27. Note that the Action is of type "T", we not have any return type so there is no "R" there. The Bind itself is of return type void. In the client-code we need not worry about which Bind to call because the c# compiler can handle it itself. See line no. 101 below.

/* code continues in class Program */
    public static void TestCount(string sText)
    {
        Func funcCount = s => s.Count();

        int iCount = sText.Count();
        Console.WriteLine("iCount = " + iCount);

        int iCount_Monad = sText.ToLoggerMonad().Bind(funcCount).Value;
        Console.WriteLine("iCount_Monad = " + iCount_Monad);
    }

    public static void TestStartsWith(string sText, string sCountry)
    {
         Func funcStartsWith = s => s.StartsWith(sCountry);

         bool isStartsWith = sText.StartsWith(sCountry);
         Console.WriteLine("isStartsWith = " + isStartsWith);

         bool isStartsWith_Monad = sCountry.ToLoggerMonad().Bind(funcsStartsWith).Value;
         Console.WriteLine("isStartsWith_Monad = " + isStartsWith_Monad);
    }

    public static void TestAddInList(List list, int iNumber)
    {
         Action> actionAddInList = l => l.Add(iNumber);

         list.Add(70);         
         Console.WriteLine(list[0].ToString());

         list.ToLoggerMonad().Bind(actionAddInList);
         Console.WriteLine(list[0].ToString() + " " + list[1].ToString());
    }

We pass on the action actionAddToList to the Bind method, which overload of Bind is not our concern. Ofcourse this could be simplified as following:

list.ToLoggerMonad().Bind(l => l.Add(iNumber));

You can enclose your calling of function or action in the overloads of Bind inside try catch and put your exception-handling logic there if you want to have an ExceptionHandlerMonad instead of a logger monad. You can have both by putting both of these logics in the Bind method.

Anonymouse Function

Lets take a closer look at "s => s.Substring(iBeginAt, iEndAt)". This is body of function. This function has no name, so its an anonymous function. This is the input-function we are supplying to the Bind function. Remember that Bind is a higher-order function so it can take in input a function. It can also return a function but we do not need that functionality in any monad so we not use it.

What do the above line means? It means that this function requires an argument called "s". We need not worry about the type of "s", that is specified and handled somewhere else in our code. If there are multiple arguments then we have to separate them by commas and use a bracket, such as "(s, m)" instead of "s". In a monad we apply the input-function on the wrapped-value as explained earlier, see line no. 24 and 30 for a quick reference. It means we only have to supply one argument, the wrapped-value. In all monads, the input-function, the function that is anonymous, always takes only one argument.

What do this input-function returns? It returns whatever the last line in it returns. Here it returns whatever "s.Substring(...)" returns. The return type could be anything, but whatever it is, it can be converted to a LoggerMonad, therefore our Bind method has to have only one return type, "LoggerMonad".

The above is not actually true. The input-function may not return anything at all, means it can have a "void" return type. In that case we use the other overload of Bind explained earlier.

The Entire Code

I haven't shown code in sequence above, for better focus. Following is the complete code with output:

Sql Server Optimization

This article answer questions like:

1. Do restart of sql server service result in loss of optimization?

2. Do indexing helps in gaining performance? To what extent?

3. To what extent sql server optimize query when executed multiple times?

I have put 10 million rows in the test table to clearly see any performance gain. Upto 10,000 rows size of a table, the execution time is mostly flat.

PREPARATION

STEP 1: Create a table.

CREATE TABLE [dbo].[Person](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[NICNo] [varchar](50) NOT NULL,
[Addresss] [varchar](100) NOT NULL,
[Dob] [datetime] NOT NULL,
[Employed] [bit] NULL,
CONSTRAINT [PK_Person_Duplicate] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



STEP 2: Put 10 million non-unique rows in the table.

DECLARE @i INT
SET @i = 1

WHILE @i <= 1000000
BEGIN
INSERT INTO person(Name, NICNo, [Address] , Dob)
VALUES('Person No. ' + Convert(VARCHAR, @i), Convert(VARCHAR, @i), 'Some address ' + CONVERT(VARCHAR, @i), GETDATE() - 2)

SET @i = @i + 1
END



STEP 3: Make half "Persons" unemployed and other half employed.

UPDATE TABLE Person SET Employed = 0 WHERE ID <= 5000000

UPDATE TABLE Person SET Employed = 1 WHERE ID > 5000000



STEP 4: Write Paging With Sorting Stored Procedure For Person

CREATE Procedure [dbo].[MoreEfficient_Paging_Person]
@SortColumn varchar(50),
@SortDirection varchar(4),
@PageSize int,
@PageNumber int
AS

DECLARE @MaxRows int
SET @MaxRows = @PageSize * @PageNumber

DECLARE @Count int
SET @Count = (SELECT COUNT(*) FROM Person)

IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )

IF(@SortDirection = 'ASC')
BEGIN
SELECT t.ID, t.Name, t.NicNo, t.Addresss, t.Dob, t.Employed
FROM (
SELECT Top (@PageSize) ID, Name, NicNo, Addresss, Dob, Employed
FROM (
SELECT TOP (@MaxRows) ID, Name, NicNo, Addresss, Dob, Employed
FROM Person
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'NicNo' THEN NicNo
WHEN 'Address' THEN Addresss
WHEN 'Dob' THEN Dob
WHEN 'Employed' THEN Employed
END),
ID
)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'NicNo' THEN NicNo
WHEN 'Address' THEN Addresss
WHEN 'Dob' THEN Dob
WHEN 'Employed' THEN Employed
END) DESC,
ID DESC
)
AS bar
INNER JOIN Person AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'NicNo' THEN bar.NicNo
WHEN 'Address' THEN bar.Addresss
WHEN 'Dob' THEN bar.Dob
WHEN 'Employed' THEN bar.Employed
END),
bar.ID
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT t.ID, t.Name, t.NicNo, t.Addresss, t.Dob, t.Employed
FROM (
SELECT Top (@PageSize) ID, Name, NicNo, Addresss, Dob, Employed
FROM (
SELECT TOP (@MaxRows) ID, Name, NicNo, Addresss, Dob, Employed
FROM Person
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'NicNo' THEN NicNo
WHEN 'Address' THEN Addresss
WHEN 'Dob' THEN Dob
WHEN 'Employed' THEN Employed
END),
ID

)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'NicNo' THEN NicNo
WHEN 'Address' THEN Addresss
WHEN 'Dob' THEN Dob
WHEN 'Employed' THEN Employed
END) DESC,
ID DESC
)
AS bar
INNER JOIN Person AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'NicNo' THEN bar.NicNo
WHEN 'Address' THEN bar.Addresss
WHEN 'Dob' THEN bar.Dob
WHEN 'Employed' THEN bar.Employed
END) DESC,
bar.ID
END




TEST SEQUENCE

Query : EXEC MoreEfficient_Paging_Person 'Employed', 'ASC', 10, 100000
Basic : A table Person with 10 million rows. A column Employed of type bit.
Format : (nth execution : Execution time in seconds).

Test No. 1
Specific : Automatic index PK_Person, clustered.
System : Memory usage 1.87 GB out of 2 GB.

1: 117

Test No. 2
Specific : No index at all, not even PK_Person.
System : Memory usage 1.87 GB out of 2 GB.

1: 131 seconds

Test No. 3
Specific : No index at all, not even PK_Person.
Specific : Restarted sql server service. The following 11 runs are in a row, without restarting sql server. After 1st exec, memory usage is 1.87 GB.
System : Memory usage 678 MB out of 2 GB at start.

1: 202
2: 157
3: 135
4: 123
5: 115
6: 114
7: 112
8: 110
9: 123
10: 123
11: 123


Test No. 4
Specific : No index at all, not even PK_Person.
Specific : Restarted sql server service. The following 10 runs are in a row, without restarting sql server. After 1st exec, memory usage is 1.87 GB.
System : Memory usage 678 MB out of 2 GB at start.

1: 231
2: 137
3: 118
4: 112
5: 104
6: 112
7: 119
8: 114
9: 110
10: 117


Test No. 5
Specific : No index at all, not even PK_Person.
Specific : Restarted sql server service. The following 10 runs are in a row, without restarting sql server. After 1st exec, memory usage is 1.87 GB.
System : Memory usage 678 MB out of 2 GB at start.

1: 212
2: 134
3: 102
4: 115
5: 119
6: 119
7: 108
8: 116
9: 107
10: 120

Test No. 6
Specific : Person has only one index, PK_Person.
Specific : Restarted sql server service. The following 10 runs are in a row, without restarting sql server. After 1st exec, memory usage is 1.87 GB.
System : Memory usage 678 MB out of 2 GB.
Setup : I made a new table Person_Duplicate, set its primary key (that automatically set its PK_Person_Duplicate index). Then copy all data from all

columns (except ID column) from Person to Person_Duplicate. Then deleted Person. Then renamed Person_Duplicate to Person.

1: 123
2: 87
3: 84
4: 81
5: 82
6: 103
7: 85
8: 89
9: 82
10: 84


Test No. 7
Specific : Person has two indexex, a clustered index on ID column (identity column) and a non-clustered index on Employed column.
Specific : Restarted sql server service. The following 10 runs are in a row, without restarting sql server. After 1st exec, memory usage is 1.87 GB.

1: 131
2: 82
3: 79
4: 80
5: 80
6: 79
7: 81
8: 82
9: 80
10: 81

ANALYSIS

Finding No. 1 : Comparing Test No. 1 with Test No. 2

The difference between the two tests is that first test have primary index, second don't. When the table has default index, searching took 117 seconds, when

it don't, searching took 131 seconds. Its a difference of 11%, not a huge one but may be important. Note that both tests run in identical system conditions,

1.87 GB busy RAM. The sql server service consume more than 1 GB RAM.

Conclusion => Removal of primary index don't affect 1st execution time much (increased 11%) in the first run.

Finding No. 2: Comparing Test No.s 3 to 5

3 4 5

1: 202 231 212
2: 157 137 134
3: 135 118 102
4: 123 112 115
5: 115 104 119
6: 114 112 119
7: 112 119 108
8: 110 114 116
9: 123 110 107
10: 123 117 120
11: 123

The only difference between these three tests is that after ending of each test, the sql server service is restarted.

It can be easily noted that as a test continues, from first run to tenth run, the sql server optimized the query reducing execution time. This somewhat

settled down at the 4rth run, a hyper optimization is done from 5th to 8th run, from 9th run onwards the execution time stabilizes to where it was at the

4rth run. The average of first runs is (202 + 231 + 212 = ) 645, the average of 4rth runs is (123 + 112 + 115 = ) 350, the average of 8th runs is (110 + 114

+ 116) = 340, the average of 10th runs is (123 + 117 + 120 = ) 360.

Conclusion => At 4th run, execution time is halved (reduced 46%).

Conclusion => At 8th run, execution time is hyper optimized to half (reduced 48%)

Conclusion => At 10th run, execution time stabilized to half (reduced 45%).

It can also be noted that once the sql server service is restarted, all the gains in optimization achieved at the end of previous test is lost, the execution

time of first run of all tests is approximately the same, so do every nth run. From one test to another, even though gains in optimization are lost due to

restart of sql server service, still some gains in optimization are lost, so there is a general trend downwards of nth query as test numbers proceed (look

horizontally at chart, there is a slop downwards from left to right).

Conclusion => Restarting sql server service results in lost of all previous optimizations.

Conclusion => If sql server service restart at end of each test, then nth run of each test executes in same time, with a slight downward trend forward.

Comparing these tests with test no. 1 we can find that although the first run in these tests are made when system was at a lower usage level of RAM, still

execution times are considerably high (202, 231, 212) as compare to test no. 1 (117). Since the only difference between the two series of tests (1st series =

test no.1, 2nd series = test no. 3, 4, 5) is absence of any index in the latter series, therefore the higher execution time of latter series must be due to

absence of that index. Taking averages of each series (117,215), the difference is of 47% or almost half. So, the conclusion here is:

Conclusion => Just by putting index on primary key, execution time of first run can be halved.

Finding No. 3: Comparing Test No. 1, 6, 7.

Test No.1 run with PK_Person (primary index) but under heavy system load and with a lots of queries ran before. Test No. 6 and 7 each ran with restart of sql

server service, test no. 6 has only one index PK_Person (similar to test no. 1) and test no. 7 has two indexes. These two indexes are PK_Person and

IDX_Person_Employed. Note that all the queries in this entire article order the page by Employed column. Also note that half rows in Person table have

Employed column set to 0 and other half have it set to 1.

1 6 7

1: 117 123 131
2: 87 82
3: 84 79
4: 81 80
5: 82 80
6: 103 79
7: 85 81
8: 89 82
9: 82 80
10: 84 81

The first run of test 6 takes almost same execution time (a rise of 5%) as first run of test 1. Nothing new there.

Conclusion => Restart of sql server service, don't result in any difference in execution time of first runs.

Comparing averages of 6 and 7, 90 vs 85.5, there is a reduction of 5%, nothing important.

Conclusion => Putting a non-clustered index on a column that can have only two values, and have half rows set to value 1 and rest half rows set to value 2,

and then ordering by this column, there is almost no difference (a reduction of 5%) in execution time between having that non-clustered index and not.

Golden Code: Maintain Sorting While Moving to Next Page In Data Grid

default.aspx:


       < asp : datagrid id="grd" runat="server" allowpaging="true" pagesize="4" allowsorting="true" autogeneratecolumns="false" onpageindexchanged="grd_PageIndexChanged" onsortcommand="grd_SortCommand">
       < columns >
           < asp : boundcolumn headertext="ID" datafield="ID" sortexpression="ID" />
           < asp : boundcolumn headertext="Name" datafield="Name" sortexpression="Name" />
           < asp : boundcolumn headertext="Marks" datafield="Marks" sortexpression="Marks" />
       < / columns >  
       < pagerstyle horizontalalign="Center" mode="NextPrev" />
       
   
   




default.aspx.cs: (Code behind file)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

namespace Data_Controls
{
   public partial class _Default : System.Web.UI.Page
   {
       protected void Page_Load(object sender, EventArgs e)
       {
           FillGrid();

           if (!Page.IsPostBack)
           {
               FillColumnsSortDirectionList();  
           }

           SetPagingSorting();
       }

       private void FillGrid()
       {
           DataView view = LoadView();

           this.grd.DataSource = view;
           this.grd.DataBind();
       }

       private void FillColumnsSortDirectionList()
       {
           DataView view = (DataView)this.grd.DataSource;
           List ColumnsSortDirectionList = new List();
         
           foreach (DataColumn column in view.ToTable().Columns)
               ColumnsSortDirectionList.Add(new Pair(column.ColumnName, "asc"));

           Session.Add("ColumnsSortDirectionList", ColumnsSortDirectionList);

           Session.Add("SortColumn", "ID");
       }

       private void SetPagingSorting()
       {
           ((DataView)this.grd.DataSource).Sort = Session["SortColumn"].ToString() + " " +
               Utility.PairList__GetValue((List)Session["ColumnsSortDirectionList"], Session["SortColumn"].ToString());
       }

       private DataTable LoadTable()
       {
           DataTable tab = new DataTable();
           tab.Columns.Add("ID", typeof(int));
           tab.Columns.Add("Name", typeof(string));
           tab.Columns.Add("Marks", typeof(double));

           tab.Rows.Add(new object[] { 1, "Atif", 85.40d });
           tab.Rows.Add(new object[] { 2, "Jamal", 70.00d });
           tab.Rows.Add(new object[] { 3, "Nawaz", 60.00d });
           tab.Rows.Add(new object[] { 4, "Salma", 75.00d });
           tab.Rows.Add(new object[] { 5, "Yasir", 78.00d });
           tab.Rows.Add(new object[] { 6, "Shabnam", 50.00d });
           tab.Rows.Add(new object[] { 7, "Naseem", 74.32d });
           tab.Rows.Add(new object[] { 8, "Tauseef", 58.00d });
           tab.Rows.Add(new object[] { 9, "Nasreen", 18.00d });
           tab.Rows.Add(new object[] { 10, "Sadiq", 90.00d });

           return tab;
       }

       private DataView LoadView()
       {
           return LoadTable().DefaultView;
       }

       protected void grd_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
       {
           this.grd.CurrentPageIndex = e.NewPageIndex;
           this.grd.DataBind();
       }

       protected void grd_SortCommand(object source, DataGridSortCommandEventArgs e)
       {
           Session["SortColumn"] = e.SortExpression;

           ColumnsSortDirectionList__ToggleValue(e.SortExpression);

           ((DataView)this.grd.DataSource).Sort = e.SortExpression + " " +
               Utility.PairList__GetValue((List)Session["ColumnsSortDirectionList"], e.SortExpression);
           this.grd.DataBind();
       }

       private void ColumnsSortDirectionList__ToggleValue(string _Name)
       {
           List ColumnsSortDirectionList = (List)Session["ColumnsSortDirectionList"];

           foreach (Pair p in ColumnsSortDirectionList)
           {
               if (p.Name == _Name)
               {
                   if (p.Value == "asc")
                       p.Value = "desc";
                   else if (p.Value == "desc")
                       p.Value = "asc";

                   break;
               }
           }
       }
     
   }
}

Pair.cs:

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

namespace Data_Controls
{
   public class Pair
   {
       public string Name;
       public string Value;

       public Pair(string _Name, string _Value)
       {
           this.Name = _Name;
           this.Value = _Value;
       }
   }
}

Utility.cs:

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

namespace Data_Controls
{
   public class Utility
   {
       public static string PairList__GetName(List _PairList, string _Value)
       {
           string returnValue = "";

           foreach (Pair p in _PairList)
           {
               if (p.Value == _Value)
               {
                   returnValue = p.Name;
                   break;
               }
           }

           return returnValue;
       }

       public static string PairList__GetValue(List _PairList, string _Name)
       {
           string returnValue = "";

           foreach (Pair p in _PairList)
           {
               if (p.Name == _Name)
               {
                   returnValue = p.Value;
                   break;
               }
           }

           return returnValue;
       }
   }
}