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 will rise the complexity to one more level, but in my case it solve the performance issue from not responding to 1-5 second each view. At the most complex views, I had to create a normal function where I created temp-tables inside to boost the performance even more, for example if there where a recursive query on a complex query, then I stored this complex query to a temp table and queried recursively on this temp-table. With this the complexity for the recursive query disappeared and that made it so fast. At the end I created the view on top of this function. Means there wasn’t any difference to see for the users nor applications.
Links:
- https://msdn.microsoft.com/en-us/library/ms175972.aspx
- http://stackoverflow.com/questions/12971580/sys-connect-by-path-in-sql-server
Contact me if you want to see this views or just want to know more, I’m happy to help you.
Continue reading about testing in my part III Migrate from Oracle to Microsoft (Views) – Part III.