cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Report Performance to export a pdf file using java sdk

0 Kudos

Hi,

I developed an application using CR4E and it works but a runtime the application is slower than old application developed with activex components. We are using two different database Oracle 11G and Microsoft Access.

I've tested export time. By Oracle the application exports file in 6/7 seconds, but Access exports file in about 18/20 seconds. I tried to modify some script of my code but nothing else is happened. Can you give some suggestion to improve the performance?

This is the part of my code that open report e set data connection and where the application slows down.

Hope you will able to help me.

Thanks in advance.

           ReportClientDocument rcd=new ReportClientDocument();
            rcd.open(name_Rpt, OpenReportOptions._discardSavedData);

             ReadReport readReport=new ReadReport();
             readReport.dbConnection(dbConn, dbUser, dbPass,dbodbc,tableName,id_Request,rcd);

             end = System.currentTimeMillis();
             time=(end-start)/1000; 
             System.out.println("\n time to open connection and set data "+time + " sec");
public void dbConnection(String dbConn,String dbUser, String dbPass,Jdbc_db dbodbc,String tableName,long id_Request,ReportClientDocument rcd) throws Exception { String serverName = null; try{ for(String s:rcd.getDatabaseController().getServerNames()) { // System.out.println(" ServerName "+s); serverName=s; } if(serverName.contains("DBACCESS_Name")) { dbAccess(dbodbc,tableName,id_Request,rcd); } else { dbOracle(dbConn,dbUser,dbPass,dbodbc,tableName,id_Request,rcd); } } catch (ReportSDKException e) { Utility.updateTableCmd(String.valueOf(e.errorCode()), e.getMessage(), id_Request, dbodbc, tableName); } catch (Exception e) { Utility.updateTableCmd("7", e.getMessage(),id_Request, dbodbc, tableName); } } public void dbAccess(Jdbc_db dbodbc,String tableName,long id_Request,ReportClientDocument rcd) throws Exception { String connURL = "jdbc:ucanaccess://C://DATABASE//DBNAME.mdb"; String dataBaseClassName="net.ucanaccess.jdbc.UcanaccessDriver"; String username="admin"; String password=null; try { if(rcd.getSubreportController().getSubreportNames().size()>0) { for(String subReportName: rcd.getSubreportController().getSubreportNames()) { String tableName=rcd.getSubreportController().getSubreportDatabase(subReportName).getTables().getTable(0).getName(); CRJavaHelper.changeDataSource(rcd, subReportName, tableName, username, password, connURL, dataBaseClassName, null); } } else { CRJavaHelper.changeDataSource(rcd, username, password, connURL, dataBaseClassName, null); rcd.getDatabaseController().logon("admin",null); } } catch (ReportSDKException e) { Utility.updateTableCmd(String.valueOf(e.errorCode()), e.getMessage(), id_Request, dbodbc, tableName); } } public void dbOracle(String dbConn,String dbUser, String dbPass,Jdbc_db dbodbc,String tableName,long id_Request,ReportClientDocument rcd) throws Exception { String dataBaseClassName="oracle.jdbc.driver.OracleDriver"; try { if(rcd.getSubreportController().getSubreportNames().size()>0) { for(String subReportName: rcd.getSubreportController().getSubreportNames()) { String tableName=rcd.getSubreportController().getSubreportDatabase(subReportName).getTables().getTable(0).getName(); //CRJavaHelper.logonDataSource(rcd, dbUser, dbPass); CRJavaHelper.changeDataSource(rcd, subReportName, tableName, dbUser, dbPass, dbConn, dataBaseClassName, null); rcd.getDatabaseController().logon(dbUser,dbPass); } } else { CRJavaHelper.changeDataSource(rcd, dbUser, dbPass, dbConn, dataBaseClassName, null); rcd.getDatabaseController().logon(dbUser,dbPass); } } catch (ReportSDKException e) { Utility.updateTableCmd(String.valueOf(e.errorCode()), e.getMessage(), id_Request, dbodbc, tableName); } } /** * Changes the DataSource for a specific Table * @param clientDoc The reportClientDocument representing the report being used * @param subreportName "" for main report, name of subreport for subreport, null for all reports * @param tableName name of table to change. null for all tables. * @param username The DB logon user name * @param password The DB logon password * @param connectionURL The connection URL * @param driverName The driver Name * @param jndiName The JNDI name * @throws ReportSDKException */ public static void changeDataSource (ReportClientDocument clientDoc, String subreportName, String tableName, String username, String password, String connectionURL, String driverName, String jndiName) throws ReportSDKException { PropertyBag propertyBag = null; IConnectionInfo connectionInfo = null; ITable origTable = null; ITable newTable = null; // Declare variables to hold ConnectionInfo values. // Below is the list of values required to switch to use a JDBC/JNDI // connection String TRUSTED_CONNECTION = "false"; String SERVER_TYPE = "JDBC (JNDI)"; // String SERVER_TYPE = "Server Oracle"; String USE_JDBC = "true"; String DATABASE_DLL = "crdb_jdbc.dll"; // String DATABASE_DLL = "crdb_oracle.dll"; // String JNDI_OPTIONAL_NAME = jndiName; String CONNECTION_URL = connectionURL; String DATABASE_CLASS_NAME = driverName; // The next few parameters are optional parameters which you may want to // uncomment // You may wish to adjust the arguments of the method to pass these // values in if necessary // String TABLE_NAME_QUALIFIER = "new_table_name"; // String SERVER_NAME = "new_server_name"; // String CONNECTION_STRING = "new_connection_string"; // String DATABASE_NAME = "new_database_name"; // String URI = "new_URI"; // Declare variables to hold database User Name and Password values String DB_USER_NAME = username; String DB_PASSWORD = password; // Obtain collection of tables from this database controller if (subreportName == null || subreportName.equals ("")) { Tables tables = clientDoc.getDatabaseController ().getDatabase ().getTables (); for (int i = 0; i < tables.size (); i++) { origTable = tables.getTable (i); if (tableName == null || origTable.getName ().equals (tableName)) { newTable = (ITable) origTable.clone (true); // We set the Fully qualified name to the Table Alias to keep the // method generic // This workflow may not work in all scenarios and should likely be // customized to work // in the developer's specific situation. The end result of this // statement will be to strip // the existing table of it's db specific identifiers. For example // Xtreme.dbo.Customer becomes just Customer // newTable.setQualifiedName (origTable.getAlias ()); // Change properties that are different from the original datasource // For example, if the table name has changed you will be required // to change it during this routine // table.setQualifiedName(TABLE_NAME_QUALIFIER); // Change connection information properties connectionInfo = newTable.getConnectionInfo (); // Set new table connection property attributes propertyBag = new PropertyBag (); // Overwrite any existing properties with updated values propertyBag.put ("Trusted_Connection", TRUSTED_CONNECTION); propertyBag.put ("Server Type", SERVER_TYPE); propertyBag.put ("Use JDBC", USE_JDBC); propertyBag.put ("Database DLL", DATABASE_DLL); // propertyBag.put ("JNDIOptionalName", JNDI_OPTIONAL_NAME); propertyBag.put ("Connection URL", CONNECTION_URL); propertyBag.put ("Database Class Name", DATABASE_CLASS_NAME); // propertyBag.put("Server Name", SERVER_NAME); //Optional property // propertyBag.put("Connection String", CONNECTION_STRING); //Optional property // propertyBag.put("Database Name", DATABASE_NAME); //Optional property // propertyBag.put("URI", URI); //Optional property connectionInfo.setAttributes (propertyBag); // Set database username and password // NOTE: Even if the username and password properties do not change // when switching databases, the // database password is *not* saved in the report and must be set at // runtime if the database is secured. connectionInfo.setUserName (DB_USER_NAME); connectionInfo.setPassword (DB_PASSWORD); // Update the table information clientDoc.getDatabaseController ().setTableLocation (origTable, newTable); // connectionInfo.setKind(ConnectionInfoKind.DBFile); // // int replaceParams = DBOptions._ignoreCurrentTableQualifiers + DBOptions._doNotVerifyDB; // // Now replace the connections // clientDoc.getDatabaseController().replaceConnection(connectionInfo, connectionInfo, null, replaceParams); } } } // Next loop through all the subreports and pass in the same // information. You may consider // creating a separate method which accepts if (subreportName == null || !(subreportName.equals (""))) { IStrings subNames = clientDoc.getSubreportController ().getSubreportNames (); for (int subNum = 0; subNum < subNames.size (); subNum++) { Tables tables = clientDoc.getSubreportController ().getSubreport (subNames.getString (subNum)).getDatabaseController ().getDatabase ().getTables (); for (int i = 0; i < tables.size (); i++) { origTable = tables.getTable (i); if (tableName == null || origTable.getName ().equals (tableName)) { newTable = (ITable) origTable.clone (true); // We set the Fully qualified name to the Table Alias to keep // the method generic // This workflow may not work in all scenarios and should likely // be customized to work // in the developer's specific situation. The end result of this // statement will be to strip // the existing table of it's db specific identifiers. For // example Xtreme.dbo.Customer becomes just Customer // newTable.setQualifiedName (origTable.getAlias ()); // Change properties that are different from the original // datasource // table.setQualifiedName(TABLE_NAME_QUALIFIER); // Change connection information properties connectionInfo = newTable.getConnectionInfo (); // Set new table connection property attributes propertyBag = new PropertyBag (); // Overwrite any existing properties with updated values propertyBag.put ("Trusted_Connection", TRUSTED_CONNECTION); propertyBag.put ("Server Type", SERVER_TYPE); propertyBag.put ("Use JDBC", USE_JDBC); propertyBag.put ("Database DLL", DATABASE_DLL); // propertyBag.put ("JNDIOptionalName", JNDI_OPTIONAL_NAME); propertyBag.put ("Connection URL", CONNECTION_URL); propertyBag.put ("Database Class Name", DATABASE_CLASS_NAME); // propertyBag.put("Server Name", SERVER_NAME); //Optional property // propertyBag.put("Connection String", CONNECTION_STRING); //Optionalproperty // propertyBag.put("Database Name", DATABASE_NAME); //Optional property // propertyBag.put("URI", URI); //Optional property connectionInfo.setAttributes (propertyBag); // Set database username and password // NOTE: Even if the username and password properties do not // change when switching databases, the // database password is *not* saved in the report and must be // set at runtime if the database is secured. connectionInfo.setUserName (DB_USER_NAME); connectionInfo.setPassword (DB_PASSWORD); // Update the table information clientDoc.getSubreportController ().getSubreport (subNames.getString (subNum)).getDatabaseController ().setTableLocation (origTable, newTable); } } } } }

Accepted Solutions (0)

Answers (1)

Answers (1)

DellSC
Active Contributor
0 Kudos

When you combine data from multiple data connections in a single report, Crystal cannot join the data from the two during the query. Instead, it pulls ALL of the data from both into memory and then joins and filters it there. This is why it takes longer in Crystal.

I know that there are ways in Oracle to create database links to external databases. If it's possible to create a db link to Access, I would do that and then create a view or views of the Access data and just use Oracle as the data source for the report.

-Dell

0 Kudos

Thanks a lot for your reply. Sorry, maybe I didn't write too clear... not combine data from multiple data connections in a single report, but my application reads database name and chooces the kind of data connections to open. In my case if the report file contains an Access Db name when it will export pdf, it will get data from only db Access.

Yesterday I checked the application log and I found that the application stops for about 7 seconds when does connect with db...Take a look the log:

2019-03-04 11:46:11 DEBUG com.crystaldecisions.reports.common.commandmanager command SETUP: NotUndoableCommand
2019-03-04 11:46:11 DEBUG com.crystaldecisions.reports.common.commandmanager command PERFORM: NotUndoableCommand
2019-03-04 11:46:11 DEBUG com.crystaldecisions.reports.common.commandmanager -- command is NOT UNDOABLE -> purge undo stack
2019-03-04 11:46:11 DEBUG com.crystaldecisions.reports.common.commandmanager --- Purging command SetReportDateCommand
2019-03-04 11:46:11 DEBUG com.crystaldecisions.reports.common.commandmanager --- Purging command SetReportDateCommand
2019-03-04 11:46:11 DEBUG com.crystaldecisions.reports.common.commandmanager --- Purging command SetSummaryInfoCommand
2019-03-04 11:46:11 DEBUG com.healthmarketscience.jackcess.impl.DatabaseImpl Finished reading system catalog. Tables: [DB Access Table] (Db=DbName.mdb)
2019-03-04 11:46:18 INFO com.businessobjects.reports.sdk.JRCCommunicationAdapter received request: fetchSubreportNames
2019-03-04 11:46:18 DEBUG com.crystaldecisions.reports.common.commandmanager command SETUP: NotUndoableCommand
2019-03-04 11:46:18 DEBUG com.crystaldecisions.reports.common.commandmanager command PERFORM: NotUndoableCommand
2019-03-04 11:46:18 DEBUG com.crystaldecisions.reports.common.commandmanager -- command is NOT UNDOABLE -> purge undo stack
2019-03-04 11:46:18 INFO com.businessobjects.reports.sdk.JRCCommunicationAdapter received request: addTableRequest

Any suggestions for that?

Thanks a lot in advance for any help

DellSC
Active Contributor
0 Kudos

There's not much you can do about the amount of time it takes to connect to the database, although you might try turning off "Verify on First Refresh" in the Report Options to see if that will help.

I do see a couple of things here that might also be affecting the speed of the report.

1. Make sure that you do NOT have "Save Data with Report" turned on when you save the .rpt file. When it is turned on, it takes longer for the report to load and Crystal has to take the time to purge the data before refreshing the report.

2. Are you using multiple commands in your report? If so, are you linking them together? That will cause the report to take longer to run for the same reasons that connecting to multiple databases will - Crystal has to do the joins in memory. For more information about how to work with commands, see this blog post.

-Dell