"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" ;

May 30, 2011

SQL Server Query Tuning

SQL Server Query Tuning two good posts

Query Tuning Fundamentals: Density, Predicates, Selectivity, and Cardinality
Summary from post
  • Density - Measure of how often duplicates would occur
  • Predicates - Join columns / filter clauses
  • Cardinality - Number of rows returned by query operator
Wide vs. Narrow Plans
Turning a Scan into a Seek With A Pointless Predicate

Thanks to authors of the post. This post is a good reference for me.

More Reads
SQL Server 2008 - Cache Dynamic SQL
Performance Tuning Redefined with SQL Server 2008
Who dropped objects from database?
SQL Performance Trouble Shooting – the basics from Microsoft
Usefull DMV Query
24HOP: SQL Server Performance Tools
How can you tell if an index is being used?
Troubleshooting Performance Problems in SQL Server 2008
Performance Monitor, SQL Counters and Tresholds
What are plan guides and how it could help in managing performance of the queries
How to Find the Amount of Fragmentation on Your SQL Server Instance
SQL Bits Session - High Scale OLTP – LL From SQLCAT Performance Labs
SQL Bits - The Dark Arts-a.k.a Performance Tuning, indexing and query plans
SQL Server Index Tuning for Mere Mortals
The Difficulty with Deadlocks
Completely Legal SQL Performance Enhancements Video
Automating SQL Execution Plan analysis
Tool Link




Very Good Reads!!

T.S.T - T-SQL Test Tool

In continuation with previous post, we will look at TST - TSQL Test Tool. Tool can be dowloaded from link. Documentation is very good and clear. Let's see step-by-step example for this tool .

Step 1 - Change to below value in TST.BAT File
SET TST_SqlServer=.\SQLExpress

Step 2. Executed Set up (TST is the core database required for running Tests)


Step 3. Setup TSTQuickStart Database (Example Database with Testcases in it)

Step 4. From previous posts, lets reuse the procs and write tests for the same
Reusing Tables and Procedure from post. AppDB is the Sample DB created for Demo.

Step 5. Create Test Procedure


CREATE PROCEDURE SQLTest_SaleCity
AS
BEGIN
DECLARE @City VARCHAR(100), @Sum INT
SET @City = 'Chennai'
DECLARE @TEMPDIAG TABLE (Name VARCHAR(100) NOT NULL, SaleDate DATETIME NOT NULL, SaleValue INT NOT NULL, City VARCHAR(100) NOT NULL)
INSERT INTO @TEMPDIAG
EXEC SaleCity @City
SELECT @Sum=SUM(SaleValue) FROM @TEMPDIAG
EXEC TST.Assert.Equals 'Sale in Chennai is', 9500, @Sum
END
GO
Step 6. Run the Test Procedure

Step 7. Creating Second Test Procedure
CREATE PROCEDURE SQLTest_SaleValue
AS
BEGIN
DECLARE @FromDate DATETIME, @ToDate DATETIME, @Sum INT
SET @FromDate = GETDATE()-100
SET @ToDate = GETDATE()
DECLARE @TEMPDIAG TABLE (Name VARCHAR(100) NOT NULL, SaleDate DATETIME NOT NULL,
SaleValue INT NOT NULL, City VARCHAR(100) NOT NULL)
INSERT INTO @TEMPDIAG
EXEC SaleValue @FromDate,@ToDate
SELECT @Sum=SUM(SaleValue) FROM @TEMPDIAG
EXEC TST.Assert.Equals 'Sum of Sales', 58090, @Sum
END
GO


Step 8. Run the Test Procedure
Benefits
  • Ready to use
  • Assertions are very helpful. More than 10 plus asserts are provided
  • Generating XML results
  • Exhaustive documentation
For DB test automation below parameters pass for TST - TSQL unit
  • Ability to run suite of test cases
  • Ability to run individual test case
  • Handling Assertions without any extra coding
  • Data Driven Testing can be implemented, This approach involves QA writing TSQL Code
  • Below is a ideal case for Test Automation Scenario
    • Invoke Web Services - Example post
    • Invoke TST and verify values present
Good to have Additions
  • Rerunning Failed Cases
  • Email reports, GUI Interface
TST - is a good tool for DB Test Automation.

Demo Video



More Reads -
A Method For Testing Database Design
Database unit testing patterns
Taking this to next step, You can have a console app which can execute and email results.
Run a sql script file save the result in a text file and email it
Run all of these scripts against the database
Testing With Databases
CodePlex - xUnit.net - unit testing tool for the .NET Framework
TSQLUnit testing framework

Happy Reading!!

May 28, 2011

Webinar - Test Manually In The Cloud With Sauce Scout

Attended Webinar conducted by Sauce Labs (May 26th). Video for this would be available soon in link. They have a strong cloud based infrastructure setup supporting multiple browsers, platforms.

Listed below are notes taken during the session
  • Sauce Builder Tool (Record & Play Back)
  • Support Every Programming Language for Automation builder
  • Scout Tool is for manual Testing
Key Benefits of Scout Tool (Manual Test Tool)
  • Pick a browser and run your test
  • Advantage - No need to connect to every machine for running tests
  • Exploratory navigation tests recorded and played back, Would help to identify repro steps
Dashboard - Features
  • In built dashboards for Pass / Failed cases in test runs
  • Usage across browsers (Time spent in testing across browsers) is also tracked and reported as Stats
Bug Creation
  • Create bug and share it with embed / permanent link
Pricing
  • Based on usage of resources
Instead of having dedicated hardware setup, cloud based setup can be used as 'Pay per Use'.
There is another webinar coming up this week from Pushtotest, You can register it in link

If Microsoft can provide cloud based implementation of VSTT 2010 with infrastructure support for testing multiple browsers in cloud. I bet this would be a good business.
Happy Reading!!

Biztalk Applications Testing - Learnings

I hardly remember Biztalk BAM tables, that I checked during my test role. All sample exercises you can find in posts tagged biztalk exercise

Testing involved
  • Schema Validations (pass with incorrect / missing values)
  • One of the posts exercises is for invoking web services from biztalk, In this scenario retries, failures need to be checked when webservices is accessible / not accessible.
  • Check for cases how messages are handled in cases on suspended messages (password failure, port connectivity failures)
  • Further depending upon the orchestration logic we have to look at possible failure cases and retry logic for the same
  • BRE rules Testing
  • BAM queries are another good way to analyze time taken for execution, Queries are provided below SQL Server Query for suspended messages by application, SQL Server Query for BizTalk traffic by application
Further reads on this topic

Happy Reading!!

May 27, 2011

Installing SQL Server 2008 R2 Express Edition

Installed SQL Server 2008 R2 Express Edition. Could not connect using SSMS.

Tried the following steps
After making changes, Still had issues.
Dropped a note to SQL Expert Balmukund (My mentor). He proposed below options

Try
(local)\SQLEXPRESS
OR
.\SQLEXPRESS

Finally Worked !!
Thanks Balmukund....

Happy Reading!!

May 25, 2011

Selenium - Soft Assertions

Thanks Tarun for providing feedback on Selenium Automation Assignment. His observation is correct. If one of Assertion fails this should not stop verifying rest of the cases.
On Google Search found a couple of solutions for this problem
Would work on this suggestions soon :).
Happy Reading!!

May 24, 2011

Exploring MySql - Getting Started

In This post we are going to explore world of MySql 
  • Downloaded Mysql for Win 7 - mysql-5.5.12-winx64 (Link)
  • Downloaded Toad for MySql
  • Installed MySql and Toad 
  • All set for some exploration with Mysql
  • Creating Databases
  • Writing Procedures
  • Checking Execution Plan
Welcome to world of MySql. Everytime when I try things like this, I recollect my inspiration from Bala.
Connect Using MySql Command Line Client

--Create a Database
CREATE DATABASE TestDB;

--Show Databases
SHOW DATABASES;

--USED Previous Example Post and Create those tables in MySQL
CREATE TABLE TestTable1
(
    Number INTEGER AUTO_INCREMENT,
    A VARCHAR(20) NOT NULL,
    B VARCHAR(20) NOT NULL,
    C VARCHAR(20) NOT NULL,
    D VARCHAR(20) NOT NULL,
    PRIMARY KEY  (NUMBER)
);

--SEE TABLE Created Details
DESCRIBE TestTable1;

--Entering Data in Tables Using Stored Procedure
DELIMITER $$ 
CREATE PROCEDURE dowhile() 
BEGIN 
DECLARE I INT DEFAULT 5; 
v1loop: WHILE I < 10000 DO    
INSERT INTO TestTable1(A,B,C,D)    
SELECT CONCAT(I,'A'), CONCAT(I,'B'), CONCAT(I,'C'), CONCAT(I,'D');    
SET I = I + 1; 
END WHILE v1loop; 
END$$ 
DELIMITER ;

--Execute MySQL Stored Procedure
call dowhile();

--Create Index
CREATE INDEX ABCD ON TestTable1 (A,B,C,D);

--Check Index on table
SHOW INDEX FROM testdb.TestTable1;

--SELECT Statement Example
SELECT Number FROM TestTable1 WHERE A = '1000A' AND B = '1000B' AND C = '1000C';

--Viewing Query Plan in MySql
Explain SELECT Number FROM TestTable1 WHERE A = '1000A' AND B = '1000B' AND C = '1000C';

Conceptually all TSQL best practices of MSSQL should be applicable for MySql as well. I hope so. I could not connect MySql client using Toad. I did this exercise using MySQL 5.5 Command Line Client.
This was a Good Learning!!

Thanks to Stackoverflow for helping me fix stored procedure error.

More Reads


Happy Reading!!

May 23, 2011

Automation Assignment - Selenium, TestNG

[You may also like - Selenium Automation Tricks From Selenium Wiki]
Next Post in Series - Automation Assignment - Selenium, TestNG - Improvements Post II

Couple of weeks back I did an automation exercise. Below is the code I developed. Would love to hear feedback on this. Any improvements/suggestions for this welcome.

Task - Automation for cleartrip.com - Home page

Test Automation Approach
  • Page object approach, All tests and verifiers for home page in single class created for homepage
  • Covers cross browser testing IE and Firefox
  • Implements data driven testing (ex-One way flight tests)
  • Test listener implemented to log results after running each test
  • Standard coding practices (try-catch, comments for each methods implemented)
  • Console output, TestNG output, Email report generated in folder attached
Java project code
HomePageTest.java
import com.thoughtworks.selenium.DefaultSelenium;
import com.thoughtworks.selenium.Selenium;
import org.testng.Assert;
import org.testng.annotations.*;

public class HomePageTest {
      Selenium selenium;

      @Parameters( { "browserpath", "appurl" })
      @BeforeTest(alwaysRun = true)
      public void init(String browserpath, String appurl) {
            selenium = new DefaultSelenium("localhost", 4444, browserpath, appurl);
            selenium.start();
            selenium.deleteAllVisibleCookies();
            selenium.refresh();
      }

      @AfterTest
      public void tearDown() {
            selenium.stop();
      }

      /* Test for 1 way flight  */
      @Test(dataProvider = "dataProviderSearchFlightsOneWay")
      public void SearchFlightsOneWay(String from, String to) {
            try {
                  selenium.open("/");
                  selenium.type("//input[@id='origin_autocomplete']", from);
                  selenium.type("//input[@id='destination_autocomplete']", to);
                  selenium.click("//img[@alt='Calendar']");
                  selenium.click("link=20");
                  selenium.click("button_flight_search");
                  try {
                        Thread.sleep(10000);
                  } catch (InterruptedException e) {
                  }
                  selenium.waitForPageToLoad("25000");
                  verifyResults("1");
            } catch (Exception e) {
                  System.out.println("Exception Occurred " + e.toString());
            }

      }

      /* Test for round trip flight  */
      @Test
      public void SearchFlightsRoundTrip() {
            try {
                  selenium.open("/");
                  selenium.type("//input[@id='origin_autocomplete']", "MAA");
                  selenium.type("//input[@id='destination_autocomplete']", "BLR");
                  selenium.click("//img[@alt='Calendar']");
                  selenium.click("link=17");
                  selenium.click("id=rnd_trip");
                  selenium.click("//*[@id='rtn_date']");
                  selenium.click("link=30");
                  selenium.click("button_flight_search");
                  try {
                        Thread.sleep(10000);
                  } catch (InterruptedException e) {
                  }
                  selenium.waitForPageToLoad("15000");
                  verifyResults("2");
            } catch (Exception e) {
                  System.out.println("Exception Occurred " + e.toString());
            }
      }

      /* verifier for search results  */
      public void verifyResults(String Filter) {
            Assert.assertTrue(selenium.isElementPresent("mod_link"));
            Assert.assertTrue(selenium.isElementPresent("facebookLink"));
      }

      /* Test for train search  */
      @Test
      public void testTrainSearch() {
            try {
                  selenium.open("/");
                  selenium.click("link=Trains");
                  selenium.waitForPageToLoad("30000");
                  selenium.type("from_station", "chennai");
                  selenium.keyPressNative(java.awt.event.KeyEvent.VK_TAB + "");
                  selenium.type("to_station", "bangalore");
                  selenium.keyPressNative(java.awt.event.KeyEvent.VK_TAB + "");
                  selenium.select("trainClass", "label=AC 2 Tier (2A)");
                  selenium.click("//img[@alt='Calendar']");
                  selenium
                              .click("//div[@id='datePickerWrapper']/table[2]/tbody/tr[5]/td[4]/a");
                  selenium.keyPressNative(java.awt.event.KeyEvent.VK_TAB + "");
                  selenium.click("//input[@type='image']");
                  try {
                        Thread.sleep(10000);
                  } catch (InterruptedException e) {
                  }
                  selenium.waitForCondition(
                              "selenium.isElementPresent('id=mod_link')", "60000");
            } catch (Exception e) {
                  System.out.println("Exception Occurred " + e.toString());
            }

      }

      /* Test for Hotels Search  */
      @Test
      public void searchHotels() {
            try {

                  selenium.open("/");
                  selenium.click("link=Hotels");
                  selenium.waitForPageToLoad("30000");
                  selenium.type("city", "chennai");
                  selenium.click("//img[@alt='Calendar']");
                  selenium.click("link=12");
                  selenium.keyPressNative(java.awt.event.KeyEvent.VK_TAB + "");
                  selenium.click("//img[@alt='Calendar']");
                  selenium.click("link=29");
                  selenium.click("//input[@type='image']");
                  selenium.waitForPageToLoad("30000");
                  try {
                        Thread.sleep(10000);
                  } catch (InterruptedException e) {
                  }
                  selenium.waitForCondition(
                              "selenium.isElementPresent('id=mod_link')", "60000");
            } catch (Exception e) {
                  System.out.println("Exception Occurred " + e.toString());
            }
      }

      /* Test to verify headers  */
      @Test
      public void verifyHeaders() {
            try {
                  selenium.open("/");
                  Assert.assertTrue(selenium.isElementPresent("link=Hotels"));
                  Assert.assertTrue(selenium.isTextPresent("Hotels"));
                  Assert.assertEquals("Trains", selenium.getText("link=Trains"));
                  Assert.assertTrue(selenium.isElementPresent("link=Trains"));
                  Assert.assertEquals("Small World", selenium
                              .getText("link=Small World"));
                  Assert.assertTrue(selenium.isElementPresent("link=Small World"));
                  Assert.assertEquals("more", selenium.getText("link=more"));
                  Assert.assertTrue(selenium.isElementPresent("link=more"));
                  Assert.assertTrue(selenium.isElementPresent("link=My Trips"));
                  Assert.assertTrue(selenium.isElementPresent("link=Sign in"));
                  Assert.assertTrue(selenium.isElementPresent("link=Register"));
                  Assert.assertTrue(selenium.isElementPresent("link=Register"));
                  Assert.assertTrue(selenium
                              .isElementPresent("//ul[@id='global']/li[5]/a/strong"));
                  Assert.assertEquals("Tell us what you think", selenium
                              .getText("//ul[@id='global']/li[5]/a/strong"));
                  Assert.assertTrue(selenium
                              .isElementPresent("//ul[@id='global']/li[5]/a/strong"));
                  Assert.assertEquals("India", selenium
                              .getText("//a[@id='SetDomain']/span"));
            } catch (Exception e) {
                  System.out.println("Exception Occurred " + e.toString());
            }

      }

      /* Test to verify footers  */
      @Test
      public void verifyFooter() {
            try {
                  selenium.open("/");
                  Assert.assertTrue(selenium
                              .isElementPresent("//a[@id='SetDomain']/span"));
                  Assert.assertTrue(selenium.isElementPresent("link=About Us"));
                  Assert.assertTrue(selenium.isElementPresent("link=FAQs"));
                  Assert.assertTrue(selenium.isElementPresent("link=FAQs"));
                  Assert.assertTrue(selenium.isElementPresent("link=Support"));
                  Assert.assertTrue(selenium.isElementPresent("link=Customer Forum"));
                  Assert.assertTrue(selenium.isElementPresent("link=Blog"));
                  Assert.assertTrue(selenium
                              .isElementPresent("link=Cleartrip for Business"));
                  Assert.assertTrue(selenium.isElementPresent("link=Privacy"));
                  Assert.assertTrue(selenium.isElementPresent("link=Security"));
                  Assert.assertEquals("Security", selenium.getText("link=Security"));
                  Assert.assertTrue(selenium.isElementPresent("link=API"));
                  Assert.assertEquals("API", selenium.getText("link=API"));
                  Assert.assertEquals("Mobile", selenium
                              .getText("//div[@id='Footer']/div/ul[2]/li[11]/a"));
                  Assert.assertTrue(selenium.isTextPresent("API"));
                  Assert.assertTrue(selenium.isTextPresent("Terms of Use"));
                  Assert.assertTrue(selenium.isTextPresent("Privacy"));
                  Assert.assertTrue(selenium.isTextPresent("Cleartrip for Business"));
                  Assert.assertTrue(selenium.isTextPresent("Blog"));
                  Assert.assertTrue(selenium.isTextPresent("Customer Forum"));
                  Assert.assertTrue(selenium.isTextPresent("Support"));
                  Assert.assertTrue(selenium.isTextPresent("About Us"));
            } catch (Exception e) {
                  System.out.println("Exception Occurred " + e.toString());
            }

      }

      /* Data Provider for oneway flight Search */
      @DataProvider(name = "dataProviderSearchFlightsOneWay")
      public Object[][] setDataforSearchFlightsOneWay() {
            System.out.println("Start Data Provider: SearchFlightsOneWay");
            Object[][] retkeyword = { { "MAA", "BLR" }, { "MAA", "BOM" } };
            System.out.println("End Data Provider: SearchFlightsOneWay");
            return (retkeyword);
      }

}
TestReporter.java
import org.testng.ITestContext;
import org.testng.ITestListener;
import org.testng.ITestResult;

/* Test listener class to log after running each test */
public class TestReporter implements ITestListener{
    public void onFinish(ITestContext arg0) {
    }

    public void onStart(ITestContext arg0) {
    }

    @Override
    public void onTestFailure(ITestResult arg0) {
        System.out.println("Finished Executing Test: "+arg0.getName()+"Status: Failed"+"\n Reason:"+arg0.getThrowable());
    }

    @Override
    public void onTestSkipped(ITestResult arg0) {
        System.out.println("Skipped test: "+arg0.getName()+".Reason"+arg0.getThrowable());
    }

   @Override
    public void onTestStart(ITestResult arg0) {
        System.out.println("Starting Test: "+arg0.getName());
    }
    @Override
    public void onTestSuccess(ITestResult arg0) {
        System.out.println("Finished Executing Test: "+arg0.getName()+"Status: Success.");
    }

   public void onTestFailedButWithinSuccessPercentage(ITestResult arg0) {
        System.out.println("Test failed but within success percentage");
    }
}

SuiteReporter.java
import org.testng.ISuite;
import org.testng.ISuiteListener;

public class SuiteReporter implements ISuiteListener{
    @Override
    public void onFinish(ISuite arg0) {
        System.out.println("Finished executing the suite");
        System.out.println("********Results*******");
    }

    @Override
    public void onStart(ISuite arg0) {
        System.out.println("Starting Execution");

        //Print suiteName
        System.out.println("Suite Name:"+arg0.getName());

        //Print HostName
        System.out.println("Host Name:"+arg0.getHost());//Returns null if it runs locally
    }
}

TestNG XML Code
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd">
<suite name="Suite" parallel="none" verbose="1">
      <listeners>
   <listener class-name="SuiteReporter" />
   <listener class-name="TestReporter" />
   </listeners>
   <test name="TestinInternetExplorer">
   <parameter name="browserpath" value="*iehta"/>
   <parameter name="appurl" value="http://www.cleartrip.com/"/>
   <classes>
      <class name="HomePageTest"/>
      <methods> 
        <include name="SearchFlightsOneWay" />
        <include name="SearchFlightsRoundTrip" /> 
        <include name="testTrainSearch" /> 
        <include name="searchHotels" /> 
        <include name="verifyHeaders" /> 
        <include name="verifyFooter" /> 
      </methods> 
    </classes> 
   </test>
   <test name="TestinFirefox">
   <parameter name="browserpath" value="*firefox C:\\Program Files (x86)\\Mozilla Firefox\\firefox.exe"/>
   <parameter name="appurl" value="http://www.cleartrip.com/"/>
   <classes>
      <class name="HomePageTest"/>
      <methods> 
        <include name="SearchFlightsOneWay" />
        <include name="SearchFlightsRoundTrip" /> 
        <include name="testTrainSearch" /> 
        <include name="searchHotels" /> 
        <include name="verifyHeaders" /> 
        <include name="verifyFooter" /> 
      </methods> 
    </classes> 
   </test>
</suite>

This is working Code. Developed this code in 2 days. This was a Good Learning Assignment.

Happy Reading!!