APplication performance issue : SqlServer & Oracle

Question

Hi,

We have a applicaiton in Silverlight,WCF, NHibernate. Currently it is supporting SQL Serve and Oracle database.

As it's huge data, it is running ok on SQL Sevrer. But on Oracle it is running very slow.

For one functionality it takes 5 Sec to execute on SQL Server and 30 Sec on Oracle. I am not able to figure out what will be issue.

Two things that i want to share with you about our database.

1) Database: contains one base table contains column of type

SQLServer: [Text]

Oracle: [NCLOB]

2) Our database structure is too much normalized.

May be in the oracle i have used NCLOB, that is the cause of the performance. I mean i don't know the details about it....

Can anyone please let me know what will be cause? Or Which actions do i need to follw to improve the performance as equal as SqlServer.?

Thanks in advance. Mahesh.

Answer 1

Firstly, NCLOB and Text are not equivalent. Change the NCLOB to be NVARCHAR2 and try your tests again. Also make sure that there are similar indexes on Oracle for the tests to be fair. Oracle should easily perform as fast as SQL Server.

True, a fully normalised can be slow for lookups as you may have to JOIN on many tables, but fast for inserts as you only have to insert into one place. You have to strike a balance. Analyse how your database is being used, and make a choice. Perhaps just denormalising a couple of tables to gain performance would help.

What I have done in our companies database is to create a mini data warehouse, where we keep the core tables fully normalised, then a stored proc does the heavy lifting of joining many tables and adding its data to a data warehouse table. The website is now incredibly fast as it just looks at the data in one table. Try something similar.

Answer 2

As Simon said, change NCLOB to NVARCHAR2 and then test.
If that does not help try following:

• check if connection is properly configured - on command line issue TNSPING myserver. That will show if you have network latency problems.
• Open Oracle Enterprise Manager and go to performance tab. There you will find several indicators that can show you bottlenecks. See this two first:
  • Check Advisor Central for all time consuming operations (not only SQL, but all other problems that can be caused by suboptimal configuration)
  • Check Top Activity to see what queries are using most of the resources - there you will find SQL, execution plans and tunning suggestions

If this does not help, find competent DBA.