Continued from Migrate from Oracle to Microsoft (Views) – Part II here part III with the main focus on very interesting topic testing 😉.
When I implemented all about 20 views from Oracle to SQL Server, I had to test the performance and not least important, the content of the views if their still contain the same. And how do you do that the easiest way?
For measuring the performance you simply select alls Views with a SELECT COUNT(*) on both databases. Don’t forget to clear the cache before each run. To generate all the COUNT-Statements I created a little script, just to make life easier right ;-):
- First you create a database linked server on your SQL Server database. If you don’t know how to do this, please read the following How-to.
- If you have only a few comparison you easily create EXCEPT-SELECT-Query and compare your Oracle Views over the database link (linked-Server) to your Microsoft Views.
- If you have many views, I created a query to generate these queries including the fields names and most important in the same order which is important if you do an EXCEPT-Query.
Have fun, let me know if you have questions or comment to add notes or annotations.