Performance issue with Crystal when upgrading Oracle to 11g

[former_member344325]

Dear,

I am facing performance issue in crystal report and oracle 11g as below:

In ther report server, I have created a ODBC for connect to another Oracle 11g server. also in report server I have created and published a folder to content all of my crystal report. These report can connect to oracle 11g server via ODBC.

and I have a tomcat server to run my application in my application I refer to report folder in report server.

This way can work with SQL server and oracle 9 or 10g but it facing performance issue in oracle 11g.

please let me know the root cause.

Notes: report server, tomcate server are win 32bit, but oracle is in win 64bit, and i have upgraded DataDirect connect ODBC version 6.1 but the issue can not resolve.

Please help me to solve it.

Thanks so much,

Anh

[former_member142702 · Accepted solution]

I would recommend to try to run the SQL statement of your CR report (In CR Desinger under Database just select Show SQL statement ) directly against the oracle database (using eg. sqlplus) and see how long it takes. There is a possibility that your Oracle 11g database is not tuned (eg. indexes or statistics for the accessed tables are missing).

IN order to assess the situatio it will be helpful if you can provide more details about the performance problems you are seeing eg. what was the execution time before and after the upgrade etc.

Regards,

Stratos

[former_member344325 · Accepted solution]

Dear,

I tested with your suggestion, it's still slow.

The issue is:

Previous, we use Oracle 10g 32-bit, it runs very fast, just need 20 seconds for 1 report. But after upgrade Oracle to 11g 64-bit, it took 20 minutes.

We also tested run that query directly in SQL developer of Oracle 11g, it's still fast.

I think the issue caused by ODBC driver. I am not sure this. Because we use data direct ODBC driver 32-bit because application server is 32-bit.

Please help to solve it.

Thanks,

Anh

[former_member142702 · Accepted solution]

Can you confirm that you have tried to run the query with SQLplus directly from the BO server and it was slow?

Regards,

Stratos

[former_member344325 · Accepted solution]

I run as your suggestion, in CR designer, it's still slow. But if I open SQL developer (tools of Oracle), it's still fast as Oracle 10g.

[former_member11696 · Accepted solution]

Hi Anh,

Use a third party ODBC test tool now. SQL Plus will be using the Native Oracle client so you can't compare performance.

Download our old tool called SQLCON: https://smpdl.sap-ag.de/~sapidp/012002523100006252882008E/sqlcon32.zip

Connect and then click on the SQL tab and paste in the SQL from the report and time that test.

I believe the issue is because the Oracle client is 64 bit, you should install the 32 bit Oracle Client. If using the 64 bit client then the client must thunk ( convert 64 bit data to 32 bit data format ) which is going to take more time.

If you can use OLE DB or using the Oracle Server driver ( native driver ) should be faster. ODBC puts another layer on top of the Oracle client so it too takes time to communicate between the layers.

Thank you

Don