on 08-24-2010 6:48 PM
Hi there,
I'm hoping that Ludek or someone out there can offer some advice or help troubleshoot a critical issue we have encountered since upgrading from Crystal Reports 9 to Crystal Reports2008.
We have a VB.NET 2008 application running on Microsoft .NET 3.5. We are using Crystal Reports 2008 runtime, service pack 3 -- using the CrystalDecisions.Windows.Forms.CrystalReportViewer in the app to view reports. In the GAC on all our client computers, we have versions 12.0.1100.0 and 12.0.2000.0 of CrystalDecisions.CrystalReports.Engine, CrystalDecisions.Shared, and CrystalDecisions.Windows.Forms.
These reports are created using Crystal Reports 2008 Developer, and they run against Oracle 11g databases, using the Oracle Provider for OLEDB as the connection method.
In our code, we use the ConnectionInfo.SetConnection method to pass login information at runtime, as follows:
'-- Set database connection info for the main report
For Each oConnectionInfo In oCrystalReport.DataSourceConnections
oConnectionInfo.SetConnection(gsDBDataSource, "", gsDBUserID, gsDBPassword)
Next oConnectionInfo
'-- Set database connection info for each subreport
For Each oSubreport In oCrystalReport.Subreports
For Each oConnectionInfo In oSubreport.DataSourceConnections
oConnectionInfo.SetConnection(gsDBDataSource, "", gsDBUserID, gsDBPassword)
Next oConnectionInfo
Next oSubreport
The code above runs quickly, and without error. The problem is that, aside from a very specific circumstance, the reports run extremely slow. After this code, the CrystalReportViewer runs the report and does whatever data-retrieval, etc. it needs to do:
oReportViewer.ReportSource = oCrystalReport
oReportViewer.Zoom(1) '-- Page Width
System.Windows.Forms.Application.DoEvents()
The problem comes...please continue on this thread for readability
Edited by: Sciences on Aug 24, 2010 9:34 PM
continued... This is actually the continuation, couldn't edit the above form to remove link to other post.
The problem comes in when we try to run the report against a different data source name than what was originally specified in the .rpt file via the Crystal Reports Developer application. If gsDBDataSource is the same name as what the report originally targeted (say, "Oracle11gTest"), then the report runs just fine. But if gsDBDataSource is something different (say, "Oracle11gProduction"), then report runs doggedly slow.... horribly slow. The numbers we are mostly along these lines: A report which runs in 30 seconds or so in Crystal Developer or through the Viewer with the "same" data source name, will take over 5 minutes to complete when run through the Viewer and the code above specifies a different data source.
We have spent many hours and long nights even getting to the point of understanding exactly what triggers the problem; but we are no closer to a true resolution than when we started.
We also did some performance monitoring, and the client machine (where the application with the CrystalReportViewer lives) is not spinning any extra CPU cycles during this time -- but the Oracle database server is. Oracle.exe is running on that server, taking 40% - 60% of the CPU during the time the report is running. It's performing the exact same queries as it does when running a "FAST" report, but it's just taking an extra long time communicating with the client when running a "SLOW" report..... or something!!!???
Any leads would be greatly appreciated!
Edited by: Sciences on Aug 24, 2010 10:04 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
First, many thanks for the excellent description of the issue. I wish more post were like this.
OK. I'd start to troubleshoot this as follows:
1) If you do this same datasource change in the CR designer, do you see similar speed issues there?
2) Check if you have the option "Verify on first print" enabled (File | report Options). If this is enabled, disable it, unless you absolutely need it.
3) Look at the number of tables the report is bringing in and remove any unused tables. (Database menu | Database expert - and don't forget to do this for the subreport also)
4) Split out the subreport (remember this is not meant to be a solution, just a test )
5) Run the main report without the subreport. Do we have the speed issue here?
6) Run the subreport as a main report on it's own. Do we have the speed issue here?
7) Try a simple report. One table, one field. Does this exhibit the same speed issue?
Let me know the result of the above. I'll ask Don see if he has other ideas (Don is my personal database Guru...).
Ludek
Thanks for the reply, Ludek. You've got some good suggestions, here; but it mostly just validates the fact that we've been going in the same direction as you regarding troubleshooting. Replies for some of the specific items:
1) If we do the same datasource change in the designer, the report still runs normal/fast (this is not new news with Crystal -- in the past the Designer has always been faster/prettier/more functional, while the Viewer has traditionally been the neglected step-child).
2) We changed this option, as well as several others -- no change.
3) through 7) We tried many things along these exact same lines. Certainly, the report gets a bit faster the more we break out / pare down... but ultimately, still slower if we change the datasource name at runtime.
It appears that the amount of slow-down is commensurate with the amount of data being calculated or transferred: a small number of tables or few rows = a slower report; a large number of tables or many rows = a very slow report. So, it's not like there's something happening in the background that takes a fix amount of time or anything -- the slowness increases with the amount of data.
Any other thoughts? FWIW, this happens on several different machines (WinXP, or Windows Server 2003), and it doesn't matter whether the database is hosted on Windows 2003 (as is the case in our sandobox environment) or on Solaris (as is the case in our production environment). The troublesome factor really does appear to be something on the CLIENT.
Hello,
What happens if you create a new report in CR 2008 to the DEV DB and then set that to the production DB server in your app? Takes the conversion from 9 to 12 out of the picture.
Do you or have you had more than one version of the Oracle Client installed? If so, I've run into this problem also, completely un-install all clients and then re-boot to remove oci*.exe from running in the back ground, and then re-install the client.
And/Or Check your PATH statement, only have one set and correct version and location pointing to the Client install folder. If you must have more than one version then create a Batch file that updates the path for each version, one only. Others have done this and it resolved the issues.
I also don't see if you have installed any CR Patches? What version are you on? If you have not try installing Service Pack 2 and re-test...
Thank you
Don
Thanks for the suggestions, Don. I think we're on the same path as you in terms of troubleshooting this, because we already tried creation a new CR2008 report from scratch -- and the effect is the same (slow report). We are also current with SP3.
We've also been postulating that it has something to do with the Oracle client, or some sort of connectivity that is managed at the client layer. A couple of the machines have had Oracle install/uninstall/reinstall to adjust the install location, a couple other had just one Oracle client ("administrator" option) install. On one of the latter 2 machines, we performed a full uninstall and got it as clean as possible, rebooted, and reinstalled.
Still no change unfortunately The PATH variable looks fine, as well. Only one oracle entry: C:\oracle\product\11.2.0\client_1\bin;
Edited by: Sciences on Aug 27, 2010 2:14 AM
Hello,
We're also in the process of upgrading from CR9 to CR2008. We haven't experienced the same slowdowns as you have, however. I noticed the manner in which you set the connection at runtime is different then the way we do it, so it might be worthwhile trying this way instead, to see if it helps
//Report is a CrystalDecisions.CrystalReports.Engine.ReportDocument object
foreach (Table table in Report.Database.Tables)
{
TableLogOnInfo logonInfo = table.LogOnInfo;
if (logonInfo != null)
{
if (DBConnector.CurrentConnector.ConnectionStringBuilder.IntegratedSecurity)
{
logonInfo.ConnectionInfo.IntegratedSecurity = true;
}
else
{
logonInfo.ConnectionInfo.IntegratedSecurity = false;
logonInfo.ConnectionInfo.UserID = DBConnector.CurrentConnector.ConnectionStringBuilder.UserID;
logonInfo.ConnectionInfo.Password = DBConnector.CurrentConnector.ConnectionStringBuilder.Password;
}
logonInfo.ConnectionInfo.ServerName = ReportConfiguration.ODBCName;
logonInfo.ConnectionInfo.DatabaseName = DBConnector.CurrentConnector.ConnectionStringBuilder.InitialCatalog;
table.ApplyLogOnInfo(logonInfo);
}
}
//Repeat same loop thru tables of subreports as well.
Please note that we use SQL Server, instead of Oracle, so if the issue is provider specific, then this probably won't help, but it's worth a shot.
@meditim: thanks for the info. But this was actually the way we were passing the connection info originally (setting it for each one of the tables in the main report & subreport). We experienced the slow reports at that time; and in researching this, we saw the new connection method which some people seem to prefer.
So, unfortunately -- the slow-down happens with both methods of setting connection info.
Hi all,
We are still experiencing this problem, and we're wondering if there is any additional information, or even a solution? Currently, we have all of our reports pointing to the Production database, so they will run at normal speed in the Production environment. But if we need to run them against a Dev or Test database, it's painfully slow unless we manually change the data source in Crystal Reports Developer.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I would look into using inProc RAS to change the connection using the DatabaseController.ReplaceConnection method. The reason is, this method has a parameter that tells the engine if it should verify the database while changing or not. The method you are currently using will verify the database if the connection changes.
Something like this:
private void ReplaceConnection_Click(object sender, EventArgs e)
{
CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
ISCDReportClientDocument rcd;
rcd = rptClientDoc;
rptClientDoc.DatabaseController.LogonEx("ServerName", "xtreme", "sb", "password");
//Create the logon propertybag for the connection we wish to use
CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag logonDetails = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
logonDetails.Add("Auto Translate", -1);
logonDetails.Add("Connect Timeout", 15);
logonDetails.Add("Data Source", "ServerName");
logonDetails.Add("General Timeout", 0);
logonDetails.Add("Initial Catalog", "Orders");
logonDetails.Add("Integrated Security", "True");
logonDetails.Add("Locale Identifier", 1033);
logonDetails.Add("OLE DB Services", -5);
logonDetails.Add("Provider", "SQLOLEDB");
logonDetails.Add("Use Encryption for Data", 0);
logonDetails.Add("Owner", "dbo"); // schema
//Create the QE (query engine) propertybag with the provider details and logon property bag.
CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag QE_Details = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
QE_Details.Add("Database DLL", "crdb_ado.dll");
QE_Details.Add("QE_DatabaseName", "Orders");
QE_Details.Add("QE_DatabaseType", "OLE DB (ADO)");
QE_Details.Add("QE_LogonProperties", logonDetails);
QE_Details.Add("QE_ServerDescription", "ServerName");
QE_Details.Add("QE_SQLDB", "True");
QE_Details.Add("SSO Enabled", "False");
QE_Details.Add("Owner", "dbo");
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;
oldConnInfos = rcd.DatabaseController.GetConnectionInfos(null);
for (int I = 0; I < oldConnInfos.Count; I++)
{
oldConnInfo = oldConnInfos<i>;
newConnInfo.Attributes = QE_Details;
newConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
rcd.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);
}
}
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.