Migrate from Oracle to Microsoft (Views) – Part III

24. September 2016

database, Microsoft, migration, migration ORA to MS, MS-SQL, ORA vs MS, ORA-SQL, Oracle, performance, PL/SQL, SQL, T-SQL, tuning

1 Comment

Continued from Migrate from Oracle to Microsoft (Views) – Part II here part III with the main focus on very interesting topic testing ;-).
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?
Measure performance
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]   
...

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

--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]

...

Compare content

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)…

Migrate from Oracle to Microsoft (Views) – Part II

24. September 2016

databases

database, Microsoft, migration, migration ORA to MS, MS-SQL, ORA vs MS, ORA-SQL, Oracle, performance, PL/SQL, SQL, T-SQL, tuning

2 Comments

Continued from Migrate from Oracle to Microsoft (Views) – Part I here part II with more focus on the performance part.
Performance issues recursive queries
When it comes to performance I had one big issue with the built-in function SYS_CONNECT_BY_PATH which is used for rekursiv queries in Oracle. This function is well know by the Oracle optimizer which makes this function unbelievable fast and there is no similar function on SQL Server. Even more complex was, that this queries was used on nested views, so the SQL was quite complex.

I started to translate it to an CTE view which is the pendant in SQL server and the answer to recursive queries in SQL Server. But I ended up having big performance issues because the queries where to complex. I had good performance measures on an easy simple query but invinitive response with more complex.

So what’s the solution to that?
Inline User-Defined Functions. Of course it…

Migrate from Oracle to Microsoft (Views) – Part I

24. September 2016

databases

CTE, database, Microsoft, migration, migration ORA to MS, MS-SQL, ORA vs MS, ORA-SQL, Oracle, performance, PL/SQL, SQL, T-SQL, tuning

1 Comment

Sometimes you have to migrate from an Oracle database to a Microsoft SQL Server. I’m not going into the reason that could be behind. I had different projects where I had to achieve this goal. The issues that I faced is what I would like to share with you.
Different functions
When it comes to rewrite code from Ora-SQL to MS-SQL, in my case it were Oracle Views that had to be transfered to Microsoft Views, then the first thing you figure out is, that there’s not the same built in functions on both sides. For example the well know TO_NUMBER() or TO_CHAR() in Oracle dosen’t exist in Microsoft. So what you do?
Below you see a list of the common functions compared between the two technologies:

Description
Oracle
MS SQL Server

Smallest integer >= n
CEIL
CEILING

Modulus
MOD
%

Truncate number
TRUNC
<none>

Max or min number or string in list
GREATEST,LEAST
<none>

Translate NULL to n
NVL
ISNULL

Return NULL if two values are equal
DECODE
NULLIF

String concatenation
CONCAT(str1,str2)
str1 + str2

Convert ASCII…