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

June 29, 2010

Database Testing

[You may also like MSBI Testing, Database Performance Tuning]

This post is based on my experience as Database Tester. Database Testing involves the following Activities for a OLTP System
  • Verify Table Schema, Column names as per Design Document
  • Verify Column Length and DataType
  • Verify Unicode Support (Storing Chinese/Japanese Charaters) - NVARCHAR Datatype
  • Verify Indexes on Table (Clustered, Non-Clustered), Triggers for Auditing
  • Verify Primary Key and Foreign Key Constraints defined on the Tables as in Design Document
  • Verify Default Values of Columns, NOT NULLABLE columns, Constraints defined on Columns
Database Testing (Performance Tuning & Functional Testing)
This is more towards White-box testing, Verifing the SQL Statements, Queries in the Procedure from performance perspectice. The objective here is to ensure
  • Verify Access Methods (Seeks over SCANS)
  • Verify JOINs used and ensure its Optimal (Nested vs Merge Vs Hash)
  • Very Logging and Auditing Enabled for Error Handling
  • Verify Errors are logged and they contain Error Message, Supporting Details of Error
  • Verify Coding Guidelines (Set based Operation vs Cursors, Try-Catch Block). Please refer coding guidelines section
  • Verify Isolation levels used. Recommend used of Read-Committed Snapshot isolation level to avoid potential blocking issues
  • Verify Rollback is handled in Transactions (Eg. Order Creation is failed due to PK Violation, Entire Transaction should be rolled back, All tables associated with the transaction need to be rolled back)
  • Use Profiler, Activity Montiors, DMV queries to find top IO, CPU consuming queries, You can find more details in Performance Tuning Section
Related Reads -

Testing the Migration of Data From One Database to Another
SQLbits Session - Database Testing-Minimizing "If it can break, it will."
Database Testing - John Morrison's Blog
Agile database development 101
SQL University: Database testing and refactoring tools and examples
Database Security Testing
Tools for Everyday Testing – Part 1
Tools for Everyday Testing – Part 2
Tools for Everyday Testing – Part 3


Happy Reading!!!

June 27, 2010

Operators - Revisited

I shifted my focus to Linux-java. I always love SQL Server :). Thanks to Roji & Balmukund my SQL mentors. This blog post is on operators. This is based on my earlier presentation on SQLCommunity site link

Operators
  • Fundamental Unit of Execution
  • Blocking (Hash, Sort – In memory Operators) & Non-Blocking (Seeks, Scans etc..)
  • Common Operators – Seek, SCAN, Bookmark Lookup, JOINs (Nested, Merge, Hash)
  • Execution Plan need to be interpreted from right to left
  • An operator can have More than One Input and One Output
Following different kinds of operators we will check
  •  Join Operators
  •  Bookmark lookup operator
  •  Seek & Scan Operator
  •  Spool Operator
  •  Option for (Hint)
  •  Fast N (Hint)
Example Walkthru
--STEP 1

CREATE TABLE TSCAN
(
    Col1 INT IDENTITY(1,1),
    Col2 VARCHAR(40)
)

--STEP 2
DECLARE @I INT
SET @I = 1
WHILE 1 = 1
BEGIN
    INSERT INTO TSCAN(Col2)
    VALUES(CONVERT(VARCHAR(20),@I)+'VALUE')
    SET @I = @I + 1
    IF @I > 10000
    BREAK;
END

--STEP 3 (Table SCAN)

SELECT Col1 FROM TSCAN WHERE Col1 = 100

Clustered Index Seek
--STEP 4
--Now Lets Create an Index on Col1
CREATE CLUSTERED INDEX CIX_TSCAN on TSCAN(Col1)

--STEP 5 (Clustered Index Seek)
SELECT Col1 FROM TSCAN WHERE Col1 = 100
--STEP 6 (Clustered Index SCAN)
SELECT Col1, Col2 FROM TSCAN WHERE Col2 = '100Value'

--STEP 7 (Demo Bookmark Lookup)

ALTER TABLE TSCAN
ADD COL3 CHAR(100)
--Populate Data
DECLARE @I INT
SET @I = 1
WHILE 1 = 1
BEGIN
    UPDATE TSCAN
    SET COL3 = (CONVERT(VARCHAR(20),@I)+'COL3')
    WHERE Col1 = @I
    SET @I = @I + 1
    IF @I > 10000
    BREAK;
END

--STEP 8 (Bookmark Lookup)
CREATE INDEX IX_COL3 ON TSCAN (COL3)
SELECT Col1, Col2 FROM TSCAN WHERE Col3 = '100COL3'
--STEP 9 (Resolving Key Lookup)

--Create Index on Col3, Col2 for above query to result in Index Seek
DROP INDEX TSCAN.IX_COL2_COL3
CREATE INDEX IX_COL2_COL3
ON TSCAN(COL3)
INCLUDE (COl2)

SELECT Col1, Col2 FROM TSCAN WHERE Col3 = '100COL3'


(Working with JOINS)

--NESTED LOOPS (Smaller Inner Sets, Join Columns Indexed)
CREATE TABLE NLOOPTable1(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE NLOOPTable2(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

--Populate Data
DECLARE @I INT
DECLARE @J INT
SET @I = 1
SET @J = 1
WHILE 1 = 1
BEGIN
    IF @I < 1000
    INSERT INTO NLOOPTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@I)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    IF @J < 100000
    INSERT INTO NLOOPTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@I)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @I = @I + 1
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--NESTED LOOP
SELECT
N1.Col1, N2.Col2
FROM NLOOPTable1 N1
JOIN NLOOPTable2 N2
ON N1.Col1 = N2.Col1


--Merge JOIN

--TWO Sorted (Indexed) Tables, Large Tables SQL Server Chooses Merge Join when the Join columns are indexed
CREATE TABLE MergeJOINTable1(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE MergeJOINTable2(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

DECLARE @J INT
SET @J = 1
WHILE 1 = 1
BEGIN
IF @J < 100000
    INSERT INTO MergeJOINTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    INSERT INTO MergeJOINTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--Merge JOIN
SELECT
N1.Col1, N2.Col2
FROM MergeJOINTable1 N1
JOIN MergeJOINTable2 N2
ON N1.Col1 = N2.Col1


 Hash Join
--Two Large Unindexed Tables, No Indexes Defined on Join Columns

CREATE TABLE HashJOINTable1(Col1 INT IDENTITY(1,1) , Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE HashJOINTable2(Col1 INT IDENTITY(1,1) , Col2 VARCHAR(40), Col3 VARCHAR(50))

--Populate Data
DECLARE @J INT
SET @J = 1
WHILE 1 = 1
BEGIN
IF @J < 100000
    INSERT INTO HashJOINTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    INSERT INTO MergeJOINTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--Hash JOIN
SELECT
N1.Col1, N2.Col2
FROM HashJOINTable1 N1
JOIN HashJOINTable2 N2
ON N1.Col1 = N2.Col1


Index Recommendations in Green you can find. Happy Reading!!!!

June 20, 2010

Learning Perl & Linux

Yep I am back to learning Perl. I worked in Perl long back. Posting sample programs from my notes I took earlier...
Perl (Practical Extraction and Report Language) - Interpreted Language. They are not compiled. Programs are directly executed by the CPU. More Details in link

Linux Commands
  • rm -i filename - remove file
  • mv test test1 - rename file
  • cp a.pl b.cp - copy file
  • chmod - change the mode
  • logname- print name of logged in user
  • df - disk free, disk space usage
  • grep - globally search for regular expression
  • grep print test1.pl - search for print word in test1.pl file
  • head -15 printdata.pl - list 15 lines of the file
Example 1 - Multiplication of Two Numbers
I learnt basic vi editor commands as well. :wq - save and quit, :i insert, :q - quit without save, vi filename (open / create file if it does not exists)

#!/usr/bin/perl
print 'Multiplication';
print 'A Number';
$aNumber = <>;
print 'B Number';
$bNumber = <>;
$result = $aNumber*$bNumber;
print 'result = ';
print $result;

Example 2 - Conditional Operators in Perl
#!/usr/bin/perl
$grade = <>;
if($grade < 8)
{
    print " < 8";
}
else
{
    print " > 8";
};
Example 3 - Dealing with text - strings
  • ne - not equal
  • ge - greater or equal
  • gt - greater than
  • le - less or equal
  • lt - less than
  • eq - equal
#!/usr/bin/perl
print 'program begin \n';
if ('a' eq 'a')
{
    print 'R1 - a is equal';
}
else
{
    print 'R1 - a is not equal to a ';
};
if ('a' lt 'b')
{
    print 'R2 - a less than b';
}
else
{
    print 'R2 - a is greater than b';
};
print '\n end of program';
<>;

Example 4 - Arrays in Perl
#!/usr/bin/perl
@array = (1,2,3,4);
$number = 0;
foreach $number (@array)
{
    print "$number\n"
}
<>;

More Reads
50 Most Frequently Used UNIX / Linux Commands (With Examples)

In Linux Terminal
vi ginfo.sh
Insert below contents
echo "Hello" $USER
echo "Today is ";date
exit 0
Save the file
:wq
Provide permissions to execute
chmod 755 ginfo.sh
Run the below command
./ginfo.sh

Thanks to Sarabjit for helping me learn below commands

grep "searchkeyword" filenamecontains* > /tmp/test.op
vi /tmp/test.op
find -name "filenamecontains*" -exec grep "searchkeyword" {} \; > /tmp/test.op
find -name "filenamecontains*" -mmin -60 -exec grep "filenamecontains*" {} \; > /tmp/test.op (Modified last 60 mins)
Happy Learning !!

June 19, 2010

Learning Selenium – Part I


Please find Selenium example Walkthru. (Win 7- 64 bit)

2. Download Testng from http://testng.org/doc/download.html

3. Run Command Prompt in administrator mode. Start Selenium Server. Command is - java -jar selenium-server.jar. To Run in Single Window, you need to specify java -jar selenium-server.jar -singleWindow

4. Second very good walkthrough is available in http://epyramid.wordpress.com/2008/11/26/setting-up-selenium-rc-testng-using-eclipse/. This link does not seem to exist now, You need to download Eclipse IDE for Java Developers. (Check Links - Link1)

5. Run Eclipse program as Administrator

6. Got Error ‘Failed to start new browser session: java.lang.RuntimeException: Firefox 3 could not be found in the path! Please add the directory containing firefox.exe to your PATH environment’

7. Fix is below line of code
selenium = new DefaultSelenium("localhost", 4444, "*firefox C:\\Program Files (x86)\\Mozilla Firefox\\firefox.exe",http://yahoo.com/);

8. Modified code as per example in below page - http://seleniumhq.org/docs/05_selenium_rc.html

9. Code Example
import com.thoughtworks.selenium.*;
import org.testng.annotations.*;
import static org.testng.Assert.*;
public class GoogleTest
{
public Selenium selenium;
public void setup()
{
    selenium = new DefaultSelenium("localhost", 4444, "*firefox C:\\Program Files (x86)\\Mozilla Firefox\\firefox.exe","http://google.com");
    selenium.start();
}
public void search()
{
    selenium.open("http://www.Google.com/");
    selenium.type("q", "selenium rc");
    selenium.click("btnG");
    selenium.waitForPageToLoad("30000");
    assertTrue(selenium.isTextPresent("seleniumhq.org"));
}
public static void main(String arg[])
{
    GoogleTest g = new GoogleTest();
    g.setup();
    g.search();
}
}

Snapshot of Example
















10. Export it as Ant Files. Select Export Option by right click on the Project node. Choose ANT build files from below window


11. Choose the Project to create ANT build file

Reference Link. Build file created with all the contents of the project.
TestNG makes Java unit testing a breeze
Cedric Beust - Test NG
Data Driven Testing using Selenium & TestNG Part 1 of 4
Selenium Remote Control and TestNG

More Reads
Login Scenario - Providing Login Detail from Excel Sheet
Data Driven Testing Using Selenium RC with TestNG
WebDriver with Excelsheet


Happy Reading!!

June 18, 2010

My Learnings @ Work & Life

Past 7 Years What I Learnt from Work & Life. I am summarizing it. I am still learning :)
Be good to yourself
  • Compare and benchmark against yourself
  • Do what you feel is right and even if you fail learn from it
  • Be adaptive to change
  • Talent outperforms visibility in long run
  • Share your learning’s and communicate your ideas
Be good to others
  • We would love to work with some people whom we worked in past, We would also love not to meet some people whom we worked in past. Decide how you want to be remembered
  • Help others succeed. Respect others ideas and Listen to them
  • Domination does not yield trust. It breeds hate
Keep Learning
  • Have your own learning plans and work for it
  • Change can’t happen overnight
  • Plan, Plan, Plan, Keep revising it, Track your progress
Find a mentor
  • Find a mentor for guidance, suggestions, sharing ideas

June 01, 2010

Learnings from Testing

Posted below are couple of interesting reads on Testing
Pairwise Testing - Jacek MSFT maintains this site. Objective is to generate optimal test cases, attain uniform test coverage, Identify single mode and double mode defects with identified combinations

Orthogonal Defect Classification - Here emphasis is more on granular details for defect injection phase identification. Example a code defect is a bug logged if the program does not produce expected result for positive test case. This defect could have been injected at coding, code review changes, unit testing, bug fix for unit testing, Integration testing or system testing phase code fixes during development. Identifying and nailing down on exact phase helps to identify the gaps in development and find areas for improvement.

Road map of testing. I like the experience notes section and quote - "The sooner a bug is found and fixed, the cheaper". Web-Site specific testing section is good provides lot of information.

'Monkey Testing' is one terminology that caught my attention. I found the definition of it from stpmag.com sep 05 issue. Monkey testing involves a program that points, clicks or types random things at random places on the screen.Advanced monkey testing moves into model based testing ,where you describe the various states software should travel through and run test to take random paths.

What is Exploratory Testing ?
From Wikpedia  "Simultaneous Learning, Test Design and Test Execution". Quality of Testing is dependant on Tester's skill.
Above all, Be it a Tester or Developer. When you Code for a critical feature or signoff for a Release, When it meets the business needs after go-live it gives you tremendous satisfaction, pride, encouragement to do better

Development Best Practices
  • Peer Reviews. Get your code reviewed by SME (Subject Matter Expert - Technical/Functional)
  • Design for Performance & Scalability
  • Encourage Acquiring Strong Domain Knowledge
  • Involve Support & Test during early stages of Design
  • Unit Testing and verify critical functionality works as expected
Testing Best Practices
  • Conduct Test Scenario Review before Code Drop. This ensures DEV Team can identify missed Unit Test cases/Integration Test Cases
  • Use Pair-wise testing, Orthogonal Defect Classification for Optimal Test cases, Detailed Analysis of Bugs
  • Test Supportability aspects of the application (Logging, Exception Handling Scenarios)
  • Test Automation with ROI focus
  • Work Closely with business for Test Scenarios Review, Feedback of Test Cases/Test Scenarios
Quality Assurance Vs Quality Control
QA - Process oriented, Pro-active, Identify Metrics for Test Execution, Bugs
QC - Testing is a QC Activity, Re-active, Testing, Triage and Logging Bugs

Good Read on Test Metrics

Happy Reading!!!