Recently I
worked on the SP performance tuning. My existing SP took 50 Minutes to run.
After tuning this SP it is taking approx. 24 minutes. I made some logical and
structural changes in my SP. At the end we need to validate the result of both
SP (existing and after changes).
For the
testing purpose I create two databases and loading the data form PROD. In one
data base I am keeping existing SP which took 50 min to run and another
database I have new SP.
The main of
my SP to manipulate data from the different table and load it into one table.
After executing both SP dates are loaded on the target table.
Now we need
to validate the result of both tables. Doing the manual data validation is not
possible because my table is having approx. 82 M records.
With the
help of EXCEPT or joins we can validate the result. Let’s see the example.
Here for the
example I have a table EMP_SOURCE and EMP_TARGET we will check both table’s
data.
See the
records on both tables.
In this
given example there is some discrepancy. Let’s see
Again we
need use EXCEPT with EMP_SOURCE
Here we got
data. It means both tables are not having same number of records and same data.
We can also
get this information using Join(Left and Right). If we get null value in the
columns it means not same records in the both table.
Right join
It means
some records are not in the Source table.
If in both
table having same records then EXCEPT return no records and joins return not
null records.
Now I am
keeping same records in both tables.
Using Except
Using joins
