How QA Matrix Meet Dev Metric?
Article written by Sreenivas Mothukuru
Most recently our team has received a user story from Product Owner (PO) where in we are asked to compare the records in excel sheet with the data in MySQL database to look for the matching records. At first glance the requirement sounds pretty simple and for a few minutes I was in an impression that the task can be done quickly. Now the million dollar question is
"When the requirement is so simple ... what made PO delegate the task to our team?"
This question encouraged me to revisit and review the user story requirements in detail. Then I understood that we need to compare each record from Excel sheet (i.e., ~1200 records) with the data in MySQL table (i.e., ~190,00,000 records) to look for the matching records. In brief, this task demands enormous comparisons to get desired result.
One of the solutions is to pick each record one by one from Excel sheet and query MySQL database. This way most of the comparison will be taken care by well constructed SQL query, however this process need manual intervention to execute the query modifying WHERE clause. For an individual to execute a query and copy the results to a file may take approximately 2 minutes. Then for ~1200 records it will take ~40 hours (i.e. ~5 days) to complete the task. If shared between 6 members the same job will be done within 7 hours (i.e., within a day).
Is there any better approach to get things done in minimal time without pulling many individuals for this task?
The answer is "Yes".
Plan A:
A simple Java program with an array of Excel records are passed to query which checks for the matching records (one after another) and the result set is copied to a text file. This way I was able to make the program compare all records in excel sheet with ~190,00,000 records in MySQL database in just 20 minutes.
- Semi Automated approach: ~1200 manual comparisons [~40 hours (i.e. ~5 days)]
- Automated approach: Comparison done using Java program [20 minutes]
But, there are some limitations with this approach. Below questions urged me to go for plan B.
#1: What if the Excel sheet has duplicate records?
#2: What if somebody else want to sort / search / query with specific criteria using the result set?
Answer #1: By eliminating duplicate records from Excel sheet will reduce the turnaround time.
The alternate way is to create a table, insert Excel records into it and then query distinct records from it. For this purpose, I chose MySQL database. For this I quickly wrote another java program to insert Excel records into new table.
Now everything is set.
Answer #2: Further sort / search operations are not possible with the resulted text file.
Plan B:
Extended the first java program to fix problem #2. This time result set is inserted to a new table instead of text file.
This approach helped me achieve my goal within the available time. However, this is not the only solution. If not plan B there are plan C, D, E ... Z ;)
#1: What if the Excel sheet has duplicate records?
#2: What if somebody else want to sort / search / query with specific criteria using the result set?
Answer #1: By eliminating duplicate records from Excel sheet will reduce the turnaround time.
The alternate way is to create a table, insert Excel records into it and then query distinct records from it. For this purpose, I chose MySQL database. For this I quickly wrote another java program to insert Excel records into new table.
Now everything is set.
Answer #2: Further sort / search operations are not possible with the resulted text file.
Plan B:
Extended the first java program to fix problem #2. This time result set is inserted to a new table instead of text file.
This approach helped me achieve my goal within the available time. However, this is not the only solution. If not plan B there are plan C, D, E ... Z ;)
Comments
Post a Comment