After our initial investigation. And now that we are more interested in implementing the unit tests for Postgres, a concern about the permeance was brought up.
In our initial version, and as mentioned before, we are doing a full database reset. In other words, every time a new test scenario is executed, we delete the database, recreate it, install ODS and install AMT. This process is actually consuming too much time. The goal with this extension is to see the possibility of improving this performance reviewing 2 possible options:
- Instead of doing this full database reset, what about doing a truncate on all tables?
- Instead of doing this full database reset, implement Respawn. More information about this tool here
The main idea is to compare both approaches and see if one of them has a better performance.
This is what I did to compare performance
Truncate all tables
Changed a little the previous implementation and added some validations to check if the database already existed, in such a case, instead of deleting it and recreating it, etc. a query is executed to truncate all the tables (Meaning that the database is created just the first time, assuming that it doesn't exist). Before executing this truncate, there is a query to build (runtime) this truncate.
Since all the test had been implemented on Postgres for DateDim view. I executed these 51 unit tests and it took 43 seconds (average, because I executed them several times).
Respawn
Similar to the previous approach, I changed the deletion and recreation of the database, to implement Respawn.
I executed the 51 unit tests associated to DateDim and it took 42 seconds (average, because I executed them several times).
So, as you can see, the performance is super similar. Meaning that, whether we implement the All tables truncate or Respawn, the performance should not be a variable in the equation. Rather we should consider other reasons to implement one or the other.
Additionally I executed the unit test associated to DateDim, SQL Server, just Data Standard 3.2. In other words, the exact same tests (the 51 unit tests). And it took 42 seconds. Essentially the same amount of time.
Conclusion
We can implement either the all tables truncate or Respawn. Whether we are going to implement one or the other is still an open question. But I think the approach we take should not be based on the performance.