"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

April 20, 2010

Algorithms - Stack, Queue

Pseudo Code and Approach for Programming Questions
Question #1. Finding all leaders in a array. An element in array is called leader if all following elements are smaller than it.
Example: 10,5,6,4,9,5,4,3,2,1. 9 is a leader as all elements after 9 are less than it.
Approach I: Parse from last element (i=n;i>0;i--) to first element. If a[n] < a[n-1] then a[n] it is a leader. This will complete in O(N) time
Approach II: Follow the same bubble sort approach. O(N2)
Max Value in an Array. O(N) Time
using System;{
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace MaxValue
class Program
{
    static void Main(string[] args)
    {
        int[] numbers = new int[10] {100, 10, 5, 4, 20, 40, 90, 88, 99, 1 };
        int currentmax = new int();
        currentmax = numbers[0];
        for(int i = 1; i < 10; i++)
       {
            if(currentmax < numbers[i])
            currentmax = numbers[i];
       }
       Console.WriteLine("Max Value is " + currentmax);
       Console.ReadLine();
    }
}
}
Question #2, You have an Integer Array {1,2,3,4,5,6}. A function which accepts integer array and a number. You need to find atleast 2 possible combinations in the array which produce sum equal to the number
Example: For Array {1,2,3,4,5,6,7} and Number 8. The two possible pairs are {6,2}, {7,1}
Approach 1: Take every element in the array and sum it up with all elements in array and see does it equate to the number.
Example: 1+2, 1+3, 1+4....1+7...If its equal to 8 set it to flag = 1. Same do for next element 2...When flag is 2 (2 elements found) skip the loop. This is again O(N2)

Question#3, How to find whether a string is Palindrome without extra space
Example: LIRIL. This is a palindrome. Start comparision from N to 0 until half of length of message
int palflag = 0
for(i=0;j=strlen(message)-1; i< strlen(message)/2+1;i++,j--)
{
  if(a[i]!= a[j])
   {
      palflag = 1
   }
}
if palflag==0 then Palindrome

Yeah ! Time to relearn and refresh Data structures. Posting below working example and code....

/* C++ Program to Simulate Stack Operations  - Program written using C Free 5.0*/
#include<stdio.h>
#include<stdlib.h>
#include<conio.h>
#define MAX 5
#define TRUE 1
#define FALSE 0

struct stack
{
      int top;
      int items[MAX];
};

/* Declare Functions */
int overflow(struct stack *);
int empty(struct stack *);

/* Add-push element to stack */
void push(struct stack *, int);

/* Remove-pop element from stack */
int pop(struct stack *);

/* Display elements in stack*/
void display(struct stack *);

int main()
{
      int x, choice;
      struct stack s1, *s;
      s = &s1;
      s->top= -1;
      do
      //loop to provide options on operations on stack
      {
           
            printf("\n 1.PUSH \n 2.POP \n 3.Display \n 4. EXIT \n");
            printf("Enter your choice \n");
            scanf("%d",&choice);
            printf("Choice is %d\n", choice);
            switch(choice)
            {
                  case 1:
                        if(overflow(s))
                        {
                            printf("STACK OVERFLOW \n");                         
                        }
                        else
                        {
                              printf("Enter element to be pushed \n");
                              scanf("%d",&x);
                              printf("%d\n",x);
                              push(s,x);
                        }
                        break;
                  case 2:
                        if(empty(s))     
                              printf("STACK UNDERFLOW \n");
                        else
                              printf("the popped element is %d", pop(s));
                              break;
                  case 3:
                        if(empty(s))
                              printf("STACK EMPTY\n");
                        else
                              display(s);
                              break;
                  case 4:
                        printf("EXITING \n");
                        exit(1);
                  default:printf("Enter 1,2,3 or 4 ONLY \n");
            }
      }while(choice!=4);
      return 0;
}

/* check for overflow */
int overflow(struct stack *s)
{
      printf("value of top variable is %d \n", s->top);
      if(s->top ==(MAX-1))   
            return(TRUE);
      else
            return(FALSE);
}

/*check if stack is empty */
int empty(struct stack *s)
{
      return((s->top==-1)?TRUE:FALSE);
     
}

/* pop element from stack */
int pop(struct stack *s)
{
      return(s->items[(s->top)--]);
}

/* push an element into stack */
void push(struct stack *s, int x)
{
      s->items[++(s->top)]=x;
      printf("value of top variable is %d \n", s->top);
}

/* Display elements from stack */
void display(struct stack *s)
{
      int i;
      printf("The stack is \n");
      for(i=s->top;i>=0;i--)
      {    
            printf("Element is %d\n",s->items[i]);
            printf("value of top variable is %d \n", s->top);
      }
}
Running time of push and pop function is O(1)

#include<stdio.h>
#include<process.h>
#include<stdlib.h>
#include<conio.h>
#define QF 10
/* Program to demonstrate simple circular queue */
/* 0(1) or fixed amount of time for addition  and deletion */
/*in order to insert and delete, rear and front are advanced one position clockwise*/

/* Method to check queue is full */
int qfull(int count)
{
      return(count==QF-1)?1:0;
}

/* Check if queue is empty */
int qempty(int count)
{
      return(count==0)?1:0;
}

/*Insert element at rear(last) position */
void insert_rear(int item, int *count, int *r, int *q)
{
      if(qfull(*count))
      {
            printf("Queue is full \n");
            return;
      }
      //Element assigned at last position
      *r = (*r+1)%QF;
      q[*r]=item;
     (*count)++;
}

/* Delete element in front position of queue */
void delete_front(int *f, int *count, int *q)
{
      if(qempty(*count))     
      {
            printf("QEmpty\n");
            return;
      }
      printf("Element deleted is %d \n",q[(*f)]);
      (*f)=(*f+1)%QF;
      (*count)--;
}

/*Display elements in the queue */
void display(int f, int count, int *q)
{
      int i;
      printf("In display loop f=%d, r=%d",f,count);
      if(qempty(count))
      {
            printf("Q is Empty \n");
            return;
      }
      printf("Contents of Queue are \n");
      for(i=f;i<=count;i++)
      {
            printf("%d\n",q[i]);
      }
}

int main()
{
      int choice, f,r,q[10],item;
      int count=0;
      f=0;r=-1;
      for(;;)
      {
            printf("\n 1.Insert in Front of Queue ");
            printf("\n 2.Delete from Front of Queue ");
            printf("\n 3.Display\n ");
            printf("\n 4.Exit\n ");
            scanf("%d",&choice);
            switch(choice)
            {
                  case 1:
                        printf("\n Enter element to be inserted\n");
                        scanf("%d",&item);
                        insert_rear(item,&count,&r,q);
                        break;
                  case 2:
                        delete_front(&f,&count,q);
                        break;
                  case 3:
                        display(f,count,q);
                        break;
                  case 4:
                        printf("EXITING \n");
                        exit(1);
                  default:printf("Enter 1,2,3,4 ONLY \n");
            }
      }     return 0;
}

Merging two sorted Arrays. Wikipedia & algolist.net I referenced to explain examples for bubble, selection & O(N) optimized solution

#include<stdio.h>
int main()
{

      int a[5]={90,100,190,300,1000};
      int b[5]={10,80,380,390,1100};
      int i=5;
      int c[10];
      int d[10];
      int e[10];
      int min, temp=0;
      int k=0,l=0;
      int iPos,iMin,j;
      for(i=0;i<5;i++)
      {
            c[k]=a[i];
            d[k]=a[i];
            k++;
            c[k]=b[i];
            d[k]=b[i];
            k++;
      }
      printf("Merged Array is \n");
      for(i=0;i<10;i++)
      {
            printf("%d\n",c[i]);
      }

      /* Bubble Sort  O(N2)*/
      for (iPos = 0; iPos < 10; iPos++)
      {
        for (i = iPos+1; i < 10; i++)
          {
            if (c[iPos] > c[i])
              {
                      temp = c[iPos];
                  c[iPos]=c[i];
                  c[i]=temp;
              }
          }
      }
      printf("Sorted Array -Bubble Sort is \n");
      for(i=0;i<10;i++)
      {
            printf("%d\n",c[i]);
      }
     
      /* Selection Sort O(N2)*/
      /*http://en.wikipedia.org/wiki/Selection_sort */
      for (iPos = 0; iPos < 10; iPos++)
      {
        iMin = iPos;
        for (i = iPos+1; i < 10; i++)
          {
            if (d[i] < d[iMin])
              {
                iMin = i;
              }
          }
      temp = d[iPos];
      d[iPos]=d[iMin];
      d[iMin]=temp;
      }
      printf("Sorted Array -Selection Sort is \n");
      for(i=0;i<10;i++)
      {
            printf("%d\n",d[i]);
      }

      /* O(N) Logic - http://www.algolist.net/Algorithms/Merge/Sorted_arrays */
      /*    int a[5]={90,100,190,300,1000};
            int b[5]={10,80,380,390,1100};
      */
      i = 0;
      j = 0;
      k = 0;
      int m=5,n=5;
      while (i < m && j < n)
            {
            if (a[i] <= b[j])
                        {
                  e[k] = a[i];
                  i++;
            }
                   else
                  {
                  e[k] = b[j];
                  j++;
            }
            k++;
      }
      if (i < m)
            {
            for (int p = i; p < m; p++)
                        {
                  e[k] = a[p];
                  k++;
            }
      } else
            {
            for (int p = j; p < n; p++)
                        {
                  e[k] = b[p];
                  k++;
            }
      }
      printf("Sorted Array - O(N) is \n");
      for(i=0;i<10;i++)
      {
            printf("%d\n",e[i]);
      }
}

/* Program to swap Pair of words */
#include<stdio.h>
#include<string.h>
int main()
{
      char a[100]="abcdefgh";
      int len = strlen(a);
      printf("length of A is %d\n",len);
      int i,j;
      char c;
      /* Input abcdefgh, output cdabghef */
      for(i=0;i<len;i+=4)
      {
            c = a[i+2];
            printf("%c\n",c);
            a[i+2] = a[i]; //Assign 2 with 0
            a[i] = c; //Assign 0 with 2
            c = a[i+3];
            printf("%c\n",c);
            a[i+3] = a[i+1];//Assign 3 with 1
            a[i+1]=c;//Assign 1 with 3
      }
      printf("Modified string is %s\n",a);
}

/* C Program - Longest Palindrome in given string O(N2) Solution*/
#include<stdio.h>
#include<string.h>
char s[100]="MaxLengthStringAAAAA";
int length;
int checkpalindrome(int, int);
int main()
{
      length = strlen(s);
      int i,j;
      int count,currentcount;
      int startpos, endpos;
      count=0, currentcount=0;
      printf("length is %d\n",length);
      for(i=0;i<length;i++)
      {
            for(j=i+1;j<length;j++)
            {
                  printf("i value is %d, j value is %d\n",i,j);
                  if(checkpalindrome(i,j))
                  {
                        currentcount = j-i;
                        if(currentcount > count)
                        {
                              count=currentcount;
                              startpos =i;
                              endpos = j;
                        }
                  }
            }
      }
      printf("\nResult is Start %d, End is %d\n",startpos,endpos);
      printf("Longest palindrome in a string Result \n");
      for(i=startpos;i<=endpos;i++)
      {
            printf("%c",s[i]);
      }
      return 0;  
}
int checkpalindrome(int start, int end)
{
      int a,b;
      int flag=1;
      for(a=start,b=end;a<b;a++,b--)     
      {
            printf("character at postion a is %c, b is %c\n",s[a],s[b]);
            if(s[a]!=s[b])
            {
                  flag=0;
                  break;     
            }
      }
      printf("Flag value is %d\n",flag);
      return flag;
}
C Program convert Numbers into words
Program to calculate size of tree
Program to determine if two trees are identical
There are N petrol pumps along a circular path. Every petrol pump gives some amount of fixed petrol. Need not to be unique and in no particular order means it is random. We have a car and we need to find a petrol pump which can provide so much of petrol that we can take a full round of the circle. Mileage of car is fixed

1: Choose any node to start;
2: Get fuel;
3: Go clockwise;
4: If fuel is over, go to 5, else go to 6:
5: Move your car clockwise to the next node;
6: Check if it is the start node;
7: If it isn’t, go to 2, else STOP;

Happy Reading!!

April 12, 2010

Deadlock - Lets Solve it

Many Thanks for Balmukund and Roji for helping me learn and write this post. The more I learn, The more i understand I know less and need to learn more.....

From Bart Blog Definition is - A deadlock is a circular blocking chain, where two or more threads are each blocked by the other so that no one can proceed

It is two processes Holding locks and waiting to acquire lock on resource held by other process. This results in a deadlock

Lets Try to Repro a Simple Cyclic Deadlock. Lets setup required tables.
  • Transaction A now holds an exclusive lock on row 1, and is blocked until transaction B finishes and releases the share lock it has on row 2.
  • Transaction B now holds an exclusive lock on row 2, and is blocked until transaction A finishes and releases the share lock it has on row 1.
Step 1 - Setup Tables for Demo (SQL 2008 R2)

USE tempdb
IF OBJECT_ID ('TestTable1') IS NOT NULL DROP TABLE TestTable1
IF OBJECT_ID ('TestTable2') IS NOT NULL DROP TABLE TestTable2
CREATE TABLE TestTable1
(
    Number INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(20) NOT NULL
)
CREATE TABLE TestTable2
(
    Number INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(20) NOT NULL
)

STEP 2 - Populate Data
DECLARE @I INT
SET @I = 100
WHILE 1 = 1
BEGIN
    SET @I = @I + 1
    INSERT INTO TestTable1(Name)
    VALUES (CONVERT(CHAR(6),@I)+'Test')
    INSERT INTO TestTable2(Name)
    VALUES (CONVERT(CHAR(6),@I)+'Test')
    IF @I=10000
    BREAK;
END

STEP 3 - Transaction A in New Query Editior Window

USE tempdb
    BEGIN TRAN
    UPDATE TestTable1 SET Name = 'Updated Name'
    WHERE Number = 1000
    WAITFOR DELAY '00:00:15'
    UPDATE TestTable2 SET Name = 'Updated Name'
    WHERE Number = 1000

STEP 4 - Transaction B in New Query Editor Window
USE tempdb

    BEGIN TRAN
    UPDATE TestTable2 SET Name = 'Updated Name'
    WHERE Number = 1000
    WAITFOR DELAY '00:00:15'
    UPDATE TestTable1 SET Name = 'Updated Name'
   WHERE Number = 1000

STEP 5 - Got Deadlock for one of the transaction

STEP 6 - Modify code to handle Deadlock and retry
Have a Look at Attachment and Demo Code by Adam Mechanic on Best Practices for Exception Handling and Defensive Programming

Modify the code as below for retry attempt logic

STEP 7 - Transaction A in NEW Window
USE tempdb

DECLARE @Retries INT
SET @Retries = 3
WHILE @Retries > 0
BEGIN
BEGIN TRAN
     BEGIN TRY
        UPDATE TestTable2 SET Name = 'Updated Name XXX1'
        WHERE Number = 1000
        SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
        RAISERROR ('Inside Tran2', 0, 1) WITH NOWAIT
        WAITFOR DELAY '00:00:35'
        UPDATE TestTable1 SET Name = 'Updated Name XXX2'
        WHERE Number = 1000
        COMMIT TRAN
        BREAK;
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 1205
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Inside Tran2 Deadlock', 0, 1) WITH NOWAIT
            SET @Retries = @Retries - 1
            IF @Retries = 0
           BREAK;
        ELSE
           CONTINUE
       END
      ELSE
      BEGIN
         RAISERROR ('Test Message', 0, 1) WITH NOWAIT
         SET @Retries = 0
      END
    END CATCH
END
GO
STEP 8 - Transaction B in New Window
USE tempdb

DECLARE @Retries INT
SET @Retries = 3
WHILE @Retries > 0
BEGIN
    BEGIN TRAN
    BEGIN TRY
    UPDATE TestTable1 SET Name = 'Updated Name XXX1'
    WHERE Number = 1000
    SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
    RAISERROR ('Inside Tran2', 0, 1) WITH NOWAIT
    WAITFOR DELAY '00:00:35'
    UPDATE TestTable2 SET Name = 'Updated Name XXX2'
    WHERE Number = 1000
    COMMIT TRAN
BREAK;
END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 1205
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Inside Tran2 Deadlock', 0, 1) WITH NOWAIT
            SET @Retries = @Retries - 1
            IF @Retries = 0
            BREAK;
            ELSE
                CONTINUE
       END
       ELSE
       BEGIN
            RAISERROR ('Test Message', 0, 1) WITH NOWAIT
            SET @Retries = 0
       END
    END CATCH
END
GO
STEP 9 - Output for Transactions
Transaction A
(1 row(s) affected)
(3 row(s) affected)
Inside Tran1
(1 row(s) affected)

Transaction B

(1 row(s) affected)
(3 row(s) affected)
Inside Tran2
Inside Tran2 Deadlock
(1 row(s) affected)
(3 row(s) affected)
Inside Tran2
(1 row(s) affected)

Step 10 - You see the transaction deadlocks and retry logic takes care of retry attempts
Hope this is useful and you can use it in your implementation. Thanks to balmukund and roji...
Happy Reading.......
Reference - Try-Catch to resolve Deadlocks
Behavior of WITH NOWAIT option with RAISERROR in SQL Server

Repro a Classic Deadlock Scenario
STEP 1
USE deadlocktest

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TestTable1 WHERE Number = 1000
--DO SOME Processing
WAITFOR DELAY '00:00:10'
UPDATE TestTable1 SET Name = 'Updated Name'
WHERE Number = 1000

STEP 2
USE deadlocktest

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TestTable1 WHERE Number = 1000
--DO SOME Processing
WAITFOR DELAY '00:00:10'
UPDATE TestTable1 SET Name = 'Updated Name'
WHERE Number = 1000

Both Transactions STEP1 and STEP2 listed above would deadlock each other.

More Reads - The Curious Case of the Dubious Deadlock and the Not So Logical Lock

Happy Reading!!

April 08, 2010

Web Service - Performance Testing Fundamentals

[You may also like - Using open source tools for performance testing]
[Next Post in Series - Web Services and Web Testing using VSTS]
[You may also like - 7 Most Stunning Website Crashes/ Failures of 2011 predominantly due to high traffic]
Performance testing is all about ensuring scalability and availability of the system to respond to business needs. Performance testing will ensure application can handle the desired load. Technically we must be answer below questions.
  • Identify response time for requests during Average/Peak Load
  • Numbers of Requests handled in XX Hours
  • Avg. Response Time for each request. %% Requests meeting SLA, Missing SLA during different Loads
  • Monitor System Behavior using counters - Database -> Transactions/Sec, Deadlocks/Sec
“How to performance test website / webservice” - Guidelines Listed Below
Functional Knowledge
Before you performance test your applications know all work flows/ business logic implemented in your application. Identify Data Access Layer/Business Logic layer and understand how it works. When you know the business logic flow in the application identifying the core test scenarios and test mix would be easy deal. Here the key is you need to know the application to evaluate numbers provided by business eg: how many orders per hour / site visits per peak hour.

Ex:
On an typical business Day
1. 20% is Sales through the Site
2. 30% is Orders Update through the Site
3. 20% of time No Access
4. 30% Enquiries (Search/Enquiries)
During festive Season
1. 60% is Sales through the Site
2. 20% is Orders Update through the Site
3. 5% of time No Access
4. 15% Enquiries (Search/Enquiries)

With this distribution in place you can identify the workflows for each of the transaction (Order Placement/Enquiry/Search). Outcome of this phase is you know the functionality implementation of this application to identify critical workflows in the system.

Identify Performance Test Scenarios (Performance Goals)
Here is where business requirements are converted into performance test scenarios
Ex: 19000 orders a day is expected volume, Peak volume per hour 5000 -> This translates to 1.3 requests/sec for peak volume, Average .21 Req/sec for average load
All business scenarios will be converted into test scenarios and expected result (test pass criteria) will also be mentioned in the test scenario document.

Test Scenario 1 – Average Load
1. 20% is Sales through the Site
2. 30% is Orders Update through the Site
3. 30% Enquiries (Search/Enquiries)
Test Scenario 2 – Peak Load
During festive Season
1. 60% is Sales through the Site
2. 20% is Orders Update through the Site
3. 20% Enquiries (Search/Enquiries)

The outcome of this phase is
• Test scenarios are identified
• Test Pass Criteria identified
• Test Scenarios are reviewed and signed off by stakeholders of the application

Tools/Scripts, Environmental Setup
In a Real-world scenario DEV/Test environment would not have same Hardware configuration and setup compared to Production environment. Production environment would have NLB Servers, High end processors. It is very important to test in production-like environment. Performance test environment would mimic production environment in terms of hardware configuration/setup.

Test data is another crucial factor for performance testing. It’s recommended 90% of tests must pass in a test run to consider the results for analysis. Test Data must have the same volume of production data available in production. Using production data copy is ideal. But it may not be possible because of security or privacy concern. Sample data can be created by repeating a pattern of data. 
  • Identify tools to running the tests; Code test scripts/generate test data.
  • Performance counters need to be identified and setup (ex: Sql Performance counters, ASP.NET Counters, Biztalk counters specific to the App)
Outcome of this phase is environment is ready, test scripts are coded for identified performance test scenarios.

Test Runs – Test Execution
In a performance test run each request-response time is recorded. Time is the crucial factor in performance testing. Test runs are normally done for 30 mins to 2 hour duration window for short test. During these tests, the results are documented to give performance insights. Also, errors and problems are reported and fixed. It’s recommended to conclude to do 3 test runs for each identified scenarios with same test data. Consistency of the behavior of the system, test results obtained across test runs need to be captured.
In the end, a test run for an extended period of time such as 12 to 24 hours should be done to check for excessive resource usage or resource leakage.

Results Collection and Analysis
Logs are collected; performance counter values are collected and analyzed to valuate test pass criteria

Result would look like this
Functionality
  • Test Mix
  • Req/Sec Results
  • System Health Behavior / Observations
  • SLA Met, SLA Slipped during different Loads
Functional Testing -> Performance Testing -> Test pass criteria passes-> Signoff for Production Release else reiterate the cycle.

A few real-time performance test scenarios listed in link
  • Test Limits of a Download Server for Large Downloads: Probing the maximum number of users without deterioration of performance, identify CPU-IO-Network bottleneck issues,
  • Webserver Burn-In Test: Testing a server with constant load for 8 hours and deterimine the stability of the system, response times to user requests, identify CPU-IO-DB-Network bottleneck issues,
  • Probing a Webserver with 3000 Users: Running a test with up to 3000 users against an IIS6 server and monitoring the performance, response time for requests, identify CPU-IO-DB-Network bottleneck issues
Bookmarks

What Does Performance Testing Mean?
Performance Testing Guidance for Web Applications
http://channel9.msdn.com/wiki/default.aspx/PerformanceWiki.PerformanceTestingGuidance
http://blogs.msdn.com/nikhiln/archive/2007/02/05/howto-performance-test-asp-net-web-services-using-vsts.aspx
http://www.codeplex.com/PerfTesting

April 07, 2010

SQL Tips

Today I got a Question on PIVOT function. I just learnt it today. This link helped me to understand it. Syntax as provided is very clear

SELECT columns
FROM table
PIVOT
(
    Aggregate Function(Measure Column)
    FOR Pivot Column IN ([Pivot Column Values])
)
AS Alias

Aggregate functions Example - Avg, MIN, MAX, SUM
Modified the example for all entries in column (Variable)
Example
SELECT *
FROM #temp123
PIVOT
(
      SUM(VaribleValue)
      FOR [Variable]
IN ([Sales],[Expenses],[Taxes],[Profit])
)
AS p

Modified code
DECLARE @cols NVARCHAR(2000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
    '],[' + t2.Variable
    FROM #temp123 AS t2
    ORDER BY '],[' + t2.Variable
    FOR XML PATH('')
    ), 1, 2, '') + ']'
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT * FROM #temp123
PIVOT
(
    SUM(VaribleValue)
    FOR Variable IN
    ( '+
        @cols +' )
) AS pvt'
EXECUTE(@query)

SQL Tip #2
Differences between ISNULL and COALESCE 
  • ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters
  • COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine
  • Different Outputs as provided below (SQL 2008 R2 I tried)
 SELECT ISNULL(NULL, 1) -- Output 1
 SELECT COALESCE(NULL, 1) -- Output 1
 SELECT ISNULL(NULL, NULL) -- Output NULL
 SELECT COALESCE(NULL, NULL, CAST(NULL as int)) -- Output NULL
 SELECT COALESCE(NULL, NULL) -- Output - Errors
Reference

April 06, 2010

SQL Tips

I am reading joe celko’s SQL Programming style book. Few notes on chapter 6 mentioned is very good. I am sharing a few. This would be useful for code review. I am using SQL Server 2008 R2 Environment.
--STEP 1

CREATE TABLE TestTable
(Col1 INT Identity(10,5),
Col2 INT ,
Col3 INT ,
DateOfSale Datetime
)

CREATE Clustered Index CIX_TestTable_Col1 ON TestTable(Col1)
CREATE Index IX_TestTable_Date ON TestTable(DateOfSale)

--STEP 2
INSERT INTO TestTable(Col2,Col3,DateOfSale)
SELECT 50,10,Getdate()-10
GO 5000

SQL Tip #1 – Query Involving Date Columns and where indexes are present do not manipulate

As seen in below example it results in SCAN when you apply datediff filter.
Bad Query
--Index Scan
SELECT Col1 FROM TestTable
WHERE DateDiff(Day,getdate(),DateofSale)-20 > 1
Better option
--Index Seek
SELECT Col1 FROM TestTable
WHERE DateofSale > GETDATE()-20

SQL Tip #2 - Use Exists instead of IN Operator

Bad Query
SELECT * FROM TestTable
WHERE Col1 in
(Select Col2 From NewTable)
Better Query
SELECT * FROM TestTable T1
WHERE Exists
(Select 1 FROM NewTable WHERE Col2 = T1.Col1)

SQL Tip #3 - Use CASE Instead of IF-ELSE Statements

SQL Tip #4 USE IN whenever it is logically correct than using OR Condition
Below Example is a simple differentiation when to use OR and when to use IN
Example Query
SELECT * FROM TestTable
WHERE Col1 = 10
OR Col1 = 20

Use of IN
SELECT * FROM TestTable
WHERE Col1 IN (10,20)

March 28, 2010

Agile!! Agile!! Agile!! Will it be Fragile!!

Pair Programming- Two People working together on same problem at same time/place. Advantages-better code, code review, Share knowledge, Less Probaility of Injecting bugs. Pairing Types (Beginner & Beginner, Beginner & Expert, Expert & Expert). Intro video. Can it be Three people working together PM-DEV-Test all three of them sit together and code/Test it. Probably it might work for small applications.

Startup mode -  Work without everything having to be spelt out.


XP - Focus on Small Releases/Iterations. Deliver business value in every iteration. XP Intro

TDD - Developer writes automated test cases for the desired function. Then he codes for actual functionality. TDD Intro. Can it be Developer writes code, Tester writes automated code for the test. When dev is ready with code, Test is ready with automated code. Test & Dev Phase are integrated. Test-Driven C#

Kanban Software Development - A modified SCRUM methodology.
Kanban comes from the Japanese for “visual card”.

Comparision is provided
Scrum batches work in fixed iterations - Kanban is typically (but not always) iterationless
Scrum has no work-in-progress limits - Kanban always limits WIP
Scrum can have large backlogs - Kanban discourages large queues.
Scrum measures velocity after each iteration - Kanban uses limits to adjust productivity in real-time
Source - Link

Whatever process or model that comes into market. A clear BRD and a Clear understanding of intended functionality by DEV-TEST-PM only can provide a good code that meets business demands. With the expectations set by business, we can target for minimal working software and iterations on top of it. Writing a Hello World program can follow all of the above methodology but not for an enterprise application. Not all Process would suit every project. I am not discouraging these processes but a reality check need to be done on how effective they can help a project.

1. Document and communicate clearly what can be delivered in the minimum period (Weeks/Days) - Release Cycles
2. Business needs working software that helps to run a business. Incomplete functionality delivered in days is worse than complete functionality delivered in weeks (Product Size & Complexity)
3. Quality Speaks first than Time taken for delivery. Know what you code for. (Agility Vs Stability)
4. Don’t let your estimates become commitments. Remember the difference between an estimate and a commitment and keep the two activities separate. Link
5. Prioritize-Gather-Manage Changes during Release Cycles

I agree with the definition of Agile posted in link
  • Deliver a continuous stream of potentially shippable product increments
  • At a sustainable pace
  • While adapting to the changing needs and priorities of their organization
A couple of useful reads
How I Learned to Program Manage an Agile Team after 6 years of Waterfall
Iteration Planning Meeting with CodePlex
How Agile Works – My Program Manager Cheat Sheet
The philosophy of Kanban is Kryptonite to Scrum
Interesting read - Software's Classic Mistakes--2008

With Agile SCRUM model below items, you can also observe
  • Confusing estimates with targets and stretching to meet dates
  • Excessive multi-tasking
  • Research-oriented Development (Underestimation of unfamiliar tasks)
2009 State of Agility Results  - Lists Reasons for following Agile
Excellent Suggestion on Test Role in Agile Projects
7 Practices to Agile QA
  • Test Involves in all Phases - Reqmts, Design, Code, Testing
  • Prioritize Test Cases
  • Target and Improve Automation Testing
  • Encourage Peer-Reviews (Design/Code/Test Scenarios)
  • Encourage Pair Programming Developer + Test (Act as SME)
Agile isn’t always Agile - Excerpt - "Just turns into a micro-management environment, where devs have to defend their daily work. Of all the work environments I hate the most, micro-management environments are THE worst. I don’t like working in them, and I don’t like creating them"

Lisa Crispin talks about the 'Trends in Agile Testing'
Agile Development - Evolutionary Design
'Releasing to Production Every Week'
My 7 principles to design the architecture for a software project.
A Simple, Definitive Metric of Software Development Productivity

Nice pic from http://www.utest.com/ presentation...



Great Read from Quora Read 
Quote of Jeff Nelson's answer to Why is it that when "pair programming" produces better code, almost no company practices it? on Quora

Happy Reading!!!

March 27, 2010

Code Readbility and Performance

Its always good when TSQL code we write with proper indentations, naming conventions, readability. Equally to all this points the code should be performance compliant. I found this post good one. I want to try the same. We have a table with 3 columns. All columns indexed. We need to make a search based on the three columns.

Code Required to setup our demo
USE TEMPDB
CREATE TABLE TestforCode
(
     ID INT IDENTITY(1,1) PRIMARY KEY,
     NAME VARCHAR(100),
     CITY VARCHAR(100)
)

DECLARE @I INT
SET @I = 1
WHILE 1=1
BEGIN
    INSERT INTO TestforCode(NAME, CITY)
    VALUES (('NAME ' + CONVERT(CHAR(20),@I)),('CITY ' + CONVERT(CHAR(20),@I)))
    IF @I > 10000
    BREAK;
    SET @I = @I+1
END

CREATE INDEX IX_TestforCode_City ON TestforCode(City)
CREATE INDEX IX_TestforCode_Name ON TestforCode(Name)

Input is all 3 columns I need to search based on it, Below two Queries would serve the purpose.
Query#1
DECLARE @ID INT, @Name VARCHAR(100), @City VARCHAR(100)

SET @ID = NULL
SET @City = 'City 1'
SET @Name = NULL
IF @ID IS NOT NULL
    SELECT * FROM TestforCode WHERE (ID = @ID)
ELSE
    IF @Name IS NOT NULL
    SELECT * FROM TestforCode WHERE (NAME = @Name)
ELSE
    IF @City IS NOT NULL
    SELECT * FROM TestforCode WHERE (CITY = @City)

Query#2
DECLARE @ID INT, @Name VARCHAR(100), @City VARCHAR(100)
SET @ID = NULL
SET @City = 'City 1'
SET @Name = NULL
SELECT * FROM TestforCode WHERE (ID = @ID) OR
(NAME = @Name) OR (CITY = @City)

Looking at this Query#2 looks sleek and simple but what about performance. End of Day You query should be optimal in terms of CPU, IO usage. Lets see Execution Plan for both Queries. Ctrl Key+M is the command to get actual execution plan.

Query#1











Query #2







Query #1 is the optimal query which uses non-clustered index on city column and a bookmarklookup to fetch other records. As much as code readability it also need to be performance compliant code.


More Reads

Top 10 Developer Mistakes That Won't Scale
Transact-SQL Tips and Tricks


Happy Learning!!!

SQL Tip

I wanted to simulate range locks. I tried some examples in web, I couldn't actually repro it. Roji suggested below repro steps. We can see range locks and also why you see range locks in this scenario.

Created a Test Table and Populated Test Data as below
USE TestDatabase
IF OBJECT_ID ('TestLocks') IS NOT NULL DROP TABLE TestLocks
CREATE TABLE TestLocks
(Id INT Identity (1,1) PRIMARY KEY CLUSTERED ,
Value CHAR(20) )

DECLARE @I INT
SET @I = 0
   WHILE 1=1
BEGIN
   INSERT INTO TestLocks(Value)
   VALUES ('Value' + CONVERT(VARCHAR(20), @I))
   SET @I = @I+1
   IF @I > 100
   BREAK;
END

Now we have learnt in earlier post Repeatable Read can cause phantom read.
Read Committed Isolation Level - Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time

In case of a Repeatble read for below query
BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT * FROM TestLocks WHERE ID> 50
SELECT * FROM sys.dm_tran_locks where resource_type <> 'Database'
ROLLBACK TRAN

Lock request mode is Shared - Shared locks are placed and are held until the transaction completes










Same transaction when ran under SERIALIZABLE Isolation level would have a Range Lock.

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT * FROM TestLocks WHERE ID> 50
SELECT * FROM sys.dm_tran_locks where resource_type <> 'Database'








SERIALIZABLE - highest level, where transactions are completely isolated from one another. From MSDN "Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction"....

Hope it helps...Happy Reading...

March 22, 2010

SQL Tips

SQL Tip #1

PATINDEX - This function Returns the starting position of the first occurrence of a pattern in a specified
Example - Check to See String contains only numbers. Verify only numbers are present.

IF PATINDEX('%[a-z]%','11') > 0
    PRINT 'String'
ELSE
    PRINT 'Numbers'

SQL Tip #2
Query to Check for Available Physical Memory
SELECT
(Physical_memory_in_bytes/1024.0)/1024.0 AS PhysicalMemoryMb
FROM
sys.dm_os_sys_info

SQL Tip #3
ACID Properties short RECAP :)
Atomicity - Transaction is one unit of work. All or None. Either all of its data modifications are performed or none of them are performed
Consistency - Transaction must leave database in consistent state. Maintain data integrity. Governing Data Structures for Indexes/Storage must be in correct state. If a Transaction violates a constraint it must be failed.
Isolation - Keep Transaction Seperate. Concurrency is governed by Isolation levels.
Durability - Incase of System failures changes persisit and can be recovered on abnormal termination

SQL Tip #4
Difference between SET and SELECT
  • You can assign one variable at a time using SET
  • But assigning Multiples variables in one SELECT, SELECT is faster