Skip to Content
0
Former Member
Apr 29, 2009 at 06:43 PM

Subreports performance issue

33 Views

I'm working on an existing application we have ported from CR 6.5 to CR XI R2 (among lots of other changes).

The application is a Java Swing application and we are using a Java ReportViewerBean object to show reports (converted from 6.5 to XI) in a thick client.

<BR/><BR/>

Everything works fine, but we are having performance issues with reports having a lot of subreports. In particular we have a report that has 18 subreports using 19 DB tables.

<BR/><BR/>

We have a method that sets the Table Location on a report and all its subreports invoking setTableLocation() on all the tables used by the report and the subreports (see code at the end).

For the report with 18 subreports the time required to execute 19 times setTableLocation() is more than a minute and an half, which is unacceptable.

<BR/><BR/>

If we don't call setTableLocation() on all the tables used by the report and its subreports, we get a "Error finding JNDI name (<DATABASE NAME>)" error in the report viewer.

<BR/><BR/>

I've looked around to see if there's a more efficient way to set up the datasource and tablesource location on reports and subreports, but I only found code similar to the one we are using.

<BR/><BR/>

Any suggestion?

<BR/><BR/>

Thanks

Davide

<BR/><BR/><BR/>

CODE:<BR/>

public static void changeDataSource(<BR/>
			ReportClientDocument clientDoc,<BR/>
			String reportName, <BR/>
			String tableName,<BR/>
			String username, <BR/>
			String password, <BR/>
			String connectionURL,<BR/>
			String driverName,<BR/>
			String jndiName<BR/>
		) throws ReportSDKException {<BR/>

		PropertyBag propertyBag = null;<BR/>
		IConnectionInfo connectionInfo = null;<BR/>
		ITable origTable = null;<BR/>
		ITable newTable = null;<BR/>

		// Declare variables to hold ConnectionInfo values.<BR/>
		// Below is the list of values required to switch to use a JDBC/JNDI<BR/>
		// connection<BR/>
		String TRUSTED_CONNECTION = "false";<BR/>
		String SERVER_TYPE = "JDBC (JNDI)";<BR/>
		String USE_JDBC = "true";<BR/>
		String DATABASE_DLL = "crdb_jdbc.dll";<BR/>
		String JNDI_OPTIONAL_NAME = jndiName;<BR/>
		String CONNECTION_URL = connectionURL;<BR/>
		String DATABASE_CLASS_NAME = driverName;<BR/>

		// The next few parameters are optional parameters which you may want to uncomment<BR/>
		// You may wish to adjust the arguments of the method to pass these values in if necessary<BR/>
		// String TABLE_NAME_QUALIFIER = "new_table_name";<BR/>
		// String SERVER_NAME = "isserv307.princeton.edu";<BR/>
		// String DATABASE_NAME = "landrt";<BR/>
		// String CONNECTION_STRING = "!" + CONNECTION_URL + "!" + "jdbc:oracle:thin:{userid}/{password}@" + SERVER_NAME + ":1521:" + DATABASE_NAME;<BR/>
		// String URI = "new_URI";<BR/>

		// Declare variables to hold database User Name and Password values<BR/>
		String DB_USER_NAME = username;<BR/>
		String DB_PASSWORD = password;<BR/>
		
		// Obtain collection of tables from this database controller<BR/>
		if (reportName == null || reportName.equals("")) {<BR/>
			Tables tables = clientDoc.getDatabaseController().getDatabase().getTables();<BR/>
			for(int i = 0;i < tables.size();i++){<BR/>
				origTable = tables.getTable(i);<BR/>
				if (tableName == null || origTable.getName().equals(tableName)) {<BR/>
					newTable = (ITable)origTable.clone(true);<BR/>

					// We set the Fully qualified name to the Table Alias to keep the method generic<BR/>
					// This workflow may not work in all scenarios and should likely be customized <BR/>
					// to work in the developer's specific situation. The end result of this<BR/>
					// statement will be to strip the existing table of it's db specific identifiers. <BR/>
					// For example Xtreme.dbo.Customer becomes just Customer<BR/>
					newTable.setQualifiedName(origTable.getName());<BR/>

					// Change properties that are different from the original datasource<BR/>
					// For example, if the table name has changed you will be required<BR/>
					// to change it during this routine<BR/>
					// table.setQualifiedName(TABLE_NAME_QUALIFIER);<BR/>

					// Change connection information properties<BR/>
					connectionInfo = newTable.getConnectionInfo();<BR/>

					// Set new table connection property attributes<BR/>
					propertyBag = new PropertyBag();<BR/>

					// Overwrite any existing properties with updated values<BR/>
					propertyBag.put("Trusted_Connection", TRUSTED_CONNECTION);<BR/>
					propertyBag.put("Server Type", SERVER_TYPE);<BR/>
					propertyBag.put("Use JDBC", USE_JDBC);<BR/>
					propertyBag.put("Database DLL",DATABASE_DLL );<BR/>
					propertyBag.put("JNDIOptionalName",JNDI_OPTIONAL_NAME );<BR/>
					propertyBag.put("Connection URL", CONNECTION_URL);<BR/>
					propertyBag.put("Database Class Name", DATABASE_CLASS_NAME);<BR/>
					// propertyBag.put("Server Name", SERVER_NAME); //Optional property<BR/>
					// propertyBag.put("Connection String", CONNECTION_STRING); //Optional property<BR/>
					// propertyBag.put("Database Name", DATABASE_NAME); //Optional property<BR/>
					// propertyBag.put("URI", URI); //Optional property<BR/>
					connectionInfo.setAttributes(propertyBag);<BR/>

					// Set database username and password<BR/>
					// NOTE: Even if the username and password properties do not change<BR/>
					// when switching databases, the<BR/>
					// database password is *not* saved in the report and must be set at<BR/>
					// runtime if the database is secured.<BR/>
					connectionInfo.setUserName(DB_USER_NAME);<BR/>
					connectionInfo.setPassword(DB_PASSWORD);<BR/>

					// Update the table information<BR/>
					clientDoc.getDatabaseController().setTableLocation(origTable, newTable);<BR/>

				}<BR/>
			}<BR/>
		}<BR/>
		// Next loop through all the subreports and pass in the same<BR/>
		// information. You may consider<BR/>
		// creating a separate method which accepts<BR/>
		if (reportName == null || !(reportName.equals(""))) {<BR/>
			IStrings subNames = clientDoc.getSubreportController().getSubreportNames();<BR/>
			for (int subNum=0;subNum<subNames.size();subNum++) {<BR/>
				Tables tables = clientDoc.getSubreportController().getSubreport(subNames.getString(subNum)).getDatabaseController().getDatabase().getTables();<BR/>

				for(int i = 0;i < tables.size();i++){<BR/>
					origTable = tables.getTable(i);<BR/>
					
					if (tableName == null || origTable.getName().equals(tableName)) {<BR/>
						newTable = (ITable)origTable.clone(true);<BR/>

						// We set the Fully qualified name to the Table Alias to keep the method generic<BR/>
						// This workflow may not work in all scenarios and should likely be customized <BR/>
						// to work in the developer's specific situation. The end result of this<BR/>
						// statement will be to strip the existing table of it's db specific identifiers. <BR/>
						// For example Xtreme.dbo.Customer becomes just Customer<BR/>
						newTable.setQualifiedName(origTable.getName());<BR/>

						// Change properties that are different from the original datasource<BR/>
						// table.setQualifiedName(TABLE_NAME_QUALIFIER);<BR/>

						// Change connection information properties<BR/>
						connectionInfo = newTable.getConnectionInfo();<BR/>

						// Set new table connection property attributes<BR/>
						propertyBag = new PropertyBag();<BR/>

						// Overwrite any existing properties with updated values<BR/>
						propertyBag.put("Trusted_Connection", TRUSTED_CONNECTION);<BR/>
						propertyBag.put("Server Type", SERVER_TYPE);<BR/>
						propertyBag.put("Use JDBC", USE_JDBC);<BR/>
						propertyBag.put("Database DLL",DATABASE_DLL );<BR/>
						propertyBag.put("JNDIOptionalName",JNDI_OPTIONAL_NAME );<BR/>
						propertyBag.put("Connection URL", CONNECTION_URL);<BR/>
						propertyBag.put("Database Class Name", DATABASE_CLASS_NAME);<BR/>
						// propertyBag.put("Server Name", SERVER_NAME); //Optional property<BR/>
						// propertyBag.put("Connection String", CONNECTION_STRING); //Optional property<BR/>
						// propertyBag.put("Database Name", DATABASE_NAME); //Optional property<BR/>
						// propertyBag.put("URI", URI); //Optional property<BR/>
						connectionInfo.setAttributes(propertyBag);<BR/>

						// Set database username and password<BR/>
						// NOTE: Even if the username and password properties do not<BR/>
						// change when switching databases, the<BR/>
						// database password is *not* saved in the report and must be<BR/>
						// set at runtime if the database is secured.<BR/>
						connectionInfo.setUserName(DB_USER_NAME);<BR/>
						connectionInfo.setPassword(DB_PASSWORD);<BR/>

						// Update the table information<BR/>
						clientDoc.getSubreportController().getSubreport(subNames.getString(subNum)).getDatabaseController().setTableLocation(origTable, newTable);<BR/>
					}<BR/>
				}<BR/>
			}<BR/>			
		}<BR/>
	}

Edited by: Davide Bonicelli on Apr 29, 2009 8:43 PM