on 06-12-2013 4:27 AM
Hi Folks,
I have a client who recently migrated from Crystal Reports for Visual Studio 2005 (CRVS 2005) to CRVS 2010. They do the development in their workstation and then promote their web based project (containing the crystal report) to the web server. The server is currently on IIS 7 on WIN 2008. They have also performed clickonce deployment with SP 5 patch.
They also use Data Direct 5.3 drivers to connect to oracle 11g and run the stored procedures. I have verified that their query performance is good.
Reports used to run fine in their older environment, but now, after the upgrade, reports are running extremely slow. They have also mentioned that only the migrated reports are running slow in the new environment; the new reports run fine.
Any suggestions to troubleshoot this scenario?
Your input will be highly appreciated.
Rafiq
Hello Rafiq
There was a fix released in the most recent Service Pack for CRVS. The header of the description is:
ADAPT01688843 Poor performance when reporting off an Oracle Stored Procedure using the Native Oracle
This may not be precisely the issue but it will not be a bad idea to see if SP 6 helps. Links to SP 6 are here:
http://scn.sap.com/docs/DOC-7824
- Ludek
Senior Support Engineer AGS Product Support, Global Support Center Canada
Follow us on Twitter
Got Enhancement ideas? Try the SAP Idea Place
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Ludek. Could you tell me where I can find this specific ADAPT documentation?
Just to give you more background the client has confirmed that the report is slow to retrieve data and also when navigating between pages. This is specifically on the web server. But when they run it from the CRVS 2010 designer (on their client machine), the data retrieval is slow but the page by page navigation is not an issue.
So seems like they have data access issue, and you have addressed that, but also issues with content navigation after deployment to the web server.
Any clues what could cause the content navigation issue only after deployment to the web server?
much appreciated,
Rafiq
I think the paging differences may be due to the designer being a win app vs. the .NET project being a web app. E.g.; in a web app, the report will do a postback to the server for next page and this may be causing the paging performance issues. this can be overcome by placing the report into a session, thereby forgoing the postbacks.
The ADAPT is an internal database. I will be creating a KBA off of it this week. But here are some of the more salient parts of the issues description:
Title
Poor performance when reporting off an Oracle Stored Procedure using the Native Oracle connection in Crystal Reports.
Short description
Native connection to an Oracle Stored Procedure in Crystal Reports is 10-20 times slower than an ODBC connection to the same stored procedure.
Steps to reproduce
1. In Oracle, create a stored procedure that follows the guidelines found in the attached document:
Crystal Reports and Oracle Stored Procedure.
2. In Crystal Reports, create a Native connection to the Oracle stored procedure that have 10 000 or more records.
(CRDB_ORACLE.DLL )
3. Refresh the report with Native Oracle connection, and take note of the time it took to refresh the report.
4. In Microsoft ODBC Administrator, create an ODBC connection to the Oracle server using the Oracle ODBC driver, or the CR Oracle ODBC driver.
5. In Crystal Reports, set the data source of the report from the Oracle Native connection to the ODBC connection.
6. Refresh the report with the ODBC connection to Oracle, and take note of the time it took to refresh the report.
You will notice it can take 10 to 20 times longer for a Native connection to return the same data set from the Oracle
stored procedure, than when connecting via an ODBC connection to Oracle.
- Ludek
I see what you are saying. And I will check with them.
I am assuming that they used sessions for the previous version, since they had no issues there. Do you need to reconfigure the web sessions for visual studio 2010 after migration from VS2005? In order to bring the report in session, is this something we code in the CR Viewer level, or is it done at IIS web server? Could you provide some direction here?
Thanks,
Rafiq
Nope. No reconfiguration needed. See what you can find out based on the above info. If you get no joy, it may be a good idea if you'd attach one of the old reports and one of the new reports. I would not mind seeing if I can find any differences. To attach the file you have to click on the "Use advanced Editor" link. Rename the file to txt (otherwise it will not get attached).
Oh, re. sessions. Use the following workflow:
If not ispostback()
Dim rpt as new reportdocument()
Rpt.Load(pathtoreport)
Session.Add(“report”, report)
crystalReportviewer.reportsource = rpt
else
crystalReportviewer.reportsource = session(“report”)
end if
- Ludek
Message was edited by: Ludek Uher
Rafiq,
Slow reports was a huge problem for me a couple of months back. Here is a link to my post that includes my solution. The biggest thing I found was the need to make sure every report was checked and corrected. Crystal seems rather picky and not following all the suggested settings on every report seemed to have a negitive impact if the bad reports were only run once in a while.
http://scn.sap.com/thread/3341805
As you can see in my thread. I used two threads to pin point why the reports were running so slow and then fixed the ones that showed up extra slow first.
It appeared that the default printer sometimes got toggled back on when we weren't careful, too.
Good Luck,
Hugh
Thanks folks. I will test those options out. Great input:).
Few more things that I found out:
1) They do postback. I asked them to put the report in session for better performance. They are testing that, wil lfind out
2) They are using Data Direct ODBC driver 5.3. I think this is old. What is the recommended driver for VS 2010? Not sure if that has any impact
3) Deployment: Seems like they just do the report conversion in VS 2010 and then physically deploy the converted web project to the production server. They just copy the files into the production server directory. Is this recommended approach?
Thanks,
Rafiq
Hi Ludek,
By 2nd load I meant when the user clicks on an item in the group tree within the report. I have attached an image file below.
So basically what the business is doing is they open the report by clicking a web portal (1st load, takes 35 secs). Then within the report they click on an item in the group tree (2nd load), and report throws an error then. So users cannot proceed any further.
Is there an algorithm recommended to work with sessions (from setting up connection till loading into session)? I think they are not using the code correctly. When user clicks on an item in the group tree the report goes through a code logic and then it fails.That is the code I sent to you in my earlier message.
I have also sent an email to you with the old and converted reports as attachment. Let me know if you have received it.
Let me know if you need more details.
Thanks,
Rafiq
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Well, all that should be in session. E.g.; if going through a group tree takes a long time, then I suspect the report object is not in session. There is no particular "algorithm", but using the search string 'session crystal net' will get a number of KBAs and other resources.
And no, I have note got any report(s) from you so far. Just attach them here (rename to *.txt, go to the Advanced Editor and attach).
- Ludek
Hi Ludek,
I have received some feedback from the business. After applying/verifying the changes recommended by you all in this thread the business has seen some improvement with performance. Report in older version used to load in 10 secs, where as now it loads in 35 secs. It used to take over 43 secs before the new fixes. So seems like a good start but there are room for improvement.
I was able to get the two CR reports (one old and one new converted) and will attach it in zip file to your personal email. They have given us the reports with the corresponding web.config files. Not sure how to post all of them here, so I will send them to your email.
I have also recommended them (as per your advice) to try putting report in session. They have tried that option but has been seeing issues implementing it. I think they need direction verifying the logic of putting report in session. The initial load works but on 2nd load they see issues. Below is their code (they use vb):
+ It doesn't look like crReportDocument has all the info those are needed for the second load. It doesn't make sense to me cr will need the database location/connection or parameters for the second load, but I thought I would try to provide the info for the second load to see if it would bypass the error. So, I modified the code as the following:
Dim crReportDocument as New CrystalDecisions.CrystalReports.Engine.ReportDocument ' This object is defined as class member, not locally
....
Dim physicalPath as string = ..... This is <path/filename of the cr report>
crReportDocument.Load(physicalPath)
....
< here we setup the connection to database with user name /password for the crReportDocument object>
If Not IsPostBack then
CrystalReportViewer1.ReportSource = crReportDocument ' We assign cr report document to the cr viewer control in the page.
Session ("crreport") = crReportDocument
else
If Session("crreport") is nothing then
<log the error. I never hit this path, meaning cr report data has been stored in the session>
else
CrystalReportViewer1.ReportSource = Session("crreport")
end if
end if
<then, here we initiate parameters ceParameters for the cr report viewer control>
CrystalReportViewer1.ParameterFieldInfo = ceParameters
CrystalReportViewer1.RefreshReport
The result for this is:
- First load is the same as previous one - 35 secs.
- Second load (select a group tree item). This hang. I have the spinning picture standing there until I close the page. In the log, I have the following error :
I can't figure out what object CR looks for. My questions are:
+ Can you see what is missing or wrong in the logic of using the session?
+ What is the object CR looking for if you can trace it out from the error?
+ Is there any property that need to be set for the crystal viewer control in the asp page to tell cr using session?
Your advice will be much appreciated
Rafiq
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Rafiq
In the code, set the parameters after you log on to the database and before you place the report in session. No need to use .Refresh either. See if that helps.
Not sure what you mean by:
It doesn't look like crReportDocument has all the info those are needed for the second load.
Second load meaning another report? Same report re-loaded? Same report going to second page?
- Ludek
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.