3 MIN READ 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 ;-):
--Generate Queries - Performance Test... SELECT v.TABLE_NAME, --stuff(c.list,1,1,'') as cols, 'PRINT ''' + v.Table_name + ''' ' + 'SELECT '''+v.Table_name+''' as Tbl_name, NULL as cnt UNION ALL ' + 'SELECT '''+v.Table_name+''' as Tbl_name, count(*) as cnt FROM [dbo].['+v.Table_name +'] '--WHERE 1=2 ' + CHAR(13)+CHAR(10) FROM INFORMATION_SCHEMA.VIEWS v --where v.TABLE_NAME LIKE '%What you like%' ORDER BY v.TABLE_NAME --To the generated output you just add below in front and you're ready to go: SET STATISTICS TIME ON PRINT 'My_View' SELECT 'My_View' as Tbl_name, NULL as cnt UNION ALL SELECT 'My_View' as Tbl_name, count(*) as cnt FROM [dbo].[My_View] ...
--Generate Queries - MINUS-Query... SELECT v.TABLE_NAME, --stuff(c.list,1,1,'') as cols, 'SELECT '''+v.Table_name+''' as Tbl_name, NULL as cnt, ' + stuff(c_null.list,1,1,'') + ' UNION ALL ' + 'SELECT '''+v.Table_name+''' as Tbl_name, count(*) as cnt, '+ stuff(c_null.list,1,1,'') + ' FROM [LinkedServerName]..[SchemaName].['+v.Table_name +'] ' + --' WHERE 1=2 '+ ' EXCEPT ' + 'SELECT '''+v.Table_name+''' as Tbl_name, count(*) as cnt, '+ stuff(c_null.list,1,1,'') + ' FROM [dbo].['+v.Table_name +'] ' + --' WHERE 1=2 ' + ' UNION ALL ' + 'SELECT '''+v.Table_name+''' as Tbl_name, null as cnt, '+ stuff(c.list,1,1,'') + ' FROM OPENQUERY(LinkedServerName,''SELECT * FROM SchemaName.'+v.Table_name +''') ' + --' WHERE 1=2 '+ 'EXCEPT ' + 'SELECT '''+v.Table_name+''' as Tbl_name, null as cnt, '+ stuff(c.list,1,1,'') + ' FROM [dbo].['+v.Table_name +'] ' + --' WHERE 1=2 ' + CHAR(13)+CHAR(10) , stuff(c.list,1,1,'') as cols FROM INFORMATION_SCHEMA.VIEWS v cross apply ( SELECT --',CAST(['+cast(c.COLUMN_NAME as varchar)+'] AS VARCHAR(3000)) AS ' +cast(c.COLUMN_NAME as varchar)+' ' as [text()] ',['+cast(c.COLUMN_NAME as varchar)+'] ' as [text()] FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.DATA_TYPE != 'ntext' AND c.TABLE_NAME = v.table_name -- GROUP BY c.TABLE_NAME ORDER BY ORDINAL_POSITION FOR XML PATH('') ) as c(list) CROSS APPLY ( SELECT ',NULL AS ['+cast(c.COLUMN_NAME as varchar)+']' as [text()] FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.DATA_TYPE != 'ntext' AND c.TABLE_NAME = v.table_name- -- GROUP BY c.TABLE_NAME ORDER BY ORDINAL_POSITION FOR XML PATH('') ) as c_null(list) --WHERE v.TABLE_NAME LIKE '%blabla%' ORDER BY v.TABLE_NAME
Have fun, let me know if you have questions or comment to add notes or annotations.
3,598 total views, 1 views today
1 Comment
Pingback: Migrate from Oracle to Microsoft (Views) – Part II | sspaeti.com – Blog