SetTableLocation vs. ReplaceConnection Performance

[patrick_simons2]

Hi,

we have some reports which contains tables (SQL Server) connected to different databases and also to SQL Server temporary tables (cf. ##).

Actually our Print-BusinessLogic (VB.Net 2010 SP6) steps through the main report and through the subreports, and replaces the ConnectionInfo and other Props like the QualifiedName...

using:

reportClientDoc.DatabaseController.SetTableLocationEx(checkTable, modifTable)

I found the following statement here: SetTableLocation Method

It is recommended that you use the ReplaceConnection method, which is designed for enhanced performance.

Use the SetTableLocation method to change the location of a database table that is active in a report. This is especially useful if a report uses a database that has a different location on your system.

As Ludek stated out: (When to use the 'Replace Connection' method using the Crystal Reports or InProc RAS SDK for .NET)

When not to use replace connection? Any time you need to do any table level modifications.

For a report with 3 subreports (each with 10 tables), the whole procedure takes nearly 2 seconds.

Can I use DatabaseController.ReplaceConnection() instead? I think not.

Any opinions?

Patrick

[former_member183750 · Accepted solution]

You can. Two seconds to update 30 tables plus what ever the main report has, does not sound that bad(?).  The method does not only involve replacing the tables, but each table also has to be verified and this is usually where the performance takes the biggest hit.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[patrick_simons2 · Accepted solution]

Thanks Ludek.

You know, customers always want their programs as fast as possible. If a report takes 6 seconds being showed up in a preview window on a fast system, eliminating 2 seconds would be not so bad. 2 seconds on a fast system could become 4 seconds or more on a slower one .

So if I understand you well, I could use ReplaceConnection instead except when I want to rename the tablename itself (f.ex. for SQL temporary tables where we add always a unique ID..) - right?

ReplaceConnection() would replace the server and the database for all other tables and commands?

PS. At the end of the whole TableLocation-procedure I always start a VerifyDatabase() anyway.

Patrick

[patrick_simons2 · Accepted solution]

BTW how can I use ReplaceConnection on a subreport?

[former_member183750 · Accepted solution]

Loop through all the sections looking for subreports and set the log on info, same as the main report. That should work, A bit of a sample code:

//loop through all the sections to find all the report objects

foreach (CrystalDecisions.CrystalReports.Engine.Section crSection in crSections)

{

    crReportObjects = crSection.ReportObjects;

    //loop through all the report objects to find all the subreports
    foreach (CrystalDecisions.CrystalReports.Engine.ReportObject crReportObject in crReportObjects)

    {

        if (crReportObject.Kind == ReportObjectKind.SubreportObject)

        {

            // typecast the reportobject to a subreport object once you find it
            crSubreportObject = (CrystalDecisions.CrystalReports.Engine.SubreportObject)crReportObject;
            string mysubname = crSubreportObject.SubreportName.ToString();

            //open the subreport object
            crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);


//use ReplaceConnection on a subreport here

- Ludek

[patrick_simons2 · Accepted solution]

I have one report where the ReplaceConnection works. On the other side there's another report with connections to 3 different SQL-Server databases, where the ReplaceConnection won't work; in this case I have to run through the tables and use additionally SetTableLocation to retrieve data from the correct databases.

Any ideas why?

PS. when I run through the tables of the 2nd report, the full qualified tablenames have been correctly replaced by ReplaceConnection.

What is the purpose of LogonEx() in relation to ReplaceConnection? It's a must?

Patrick

[former_member183750 · Accepted solution]

Logonex does not replace the connection. E.g.; if you want to change from ODBC to OLEDB, you cannot use logonex.

From the help files, logonex:

Sets the user and password on the server and database that you specify.

replaceconnection:

This method is the most flexible way to change the data source connection information at runtime in the RAS API.

- Ludek

[patrick_simons2 · Accepted solution]

I redrawed a new report from scratch (CR2008) with only one table. I also stay on OLE DB - no need to change this.

The logic below will not change the Connection correctly; the data is still retrieved from the old database:

oldConnInfos = databaseController.GetConnectionInfos(Nothing)
        For Each oldConnInfo As CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo In oldConnInfos
            With oldConnInfo
                .CopyTo(newConnInfo)
                oldDatabase = .Attributes.StringValue("QE_DatabaseName")
                newDatabase = GetNewDatabaseName(oldDatabase)                
            End With
            With newConnInfo
                .Attributes("QE_ServerDescription") = serverName
                .Attributes("QE_DatabaseName") = newDatabase
                DirectCast(.Attributes("QE_LogonProperties"), CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag).Item("Data Source") = serverName
                DirectCast(.Attributes("QE_LogonProperties"), CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag).Item("Initial Catalog") = newDatabase
                .Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
                .UserName = userName
                .Password = userPassword
            End With
            databaseController.ReplaceConnection(oldConnInfo, newConnInfo, Nothing, _
                                                 CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB)
        Next

If I use LogonEx or not doesn't change anything, as you can see I specify also the correct authentication in the newConnInfo.

If I run through the tables and use SetTableLocationEx - it works correctly; strange...

Do you see something wrong?

Thanks,

Patrick

[former_member183750 · Accepted solution]

Hi Patrick

Maybe it will be best to have the code written out for you. See this KBA for a utility that will do that for you.

- Ludek

[patrick_simons2 · Accepted solution]

Thanks Ludek,

This utility generates code using the SetTableLocation-function, not for ReplaceConnection. My original code already uses the SetTableLocation and works well. It's only for performance issues that I'm testing ReplaceConnection.

So do you see something wrong in my code above? Or could you ask Don for advice?

Patrick

[patrick_simons2 · Accepted solution]

Any ideas?

[former_member11696 · Accepted solution]

Hi Patrick,

So SetLocation and LogonEX are used when the database structure does not change from the original report design. I other words the DB structure does not change, this includes table names which is not in your case.

With SetLcoation all you need to do is basically set the User name/PW/Database and Server name or simply the user name and PW if the production DB is used when designing reports. CR assumes with this method the report and DB structure never changes so it's a relatively easy process to update the report, no fully qualified table names need to be validated in the report object references so you get good performance.

Because of the way you are building your temp table this changes the fully qualified name so CR must go through and update any references to those table objects. This is what ReplaceConnection could be used for but there are over heads using this method.

ReplaceConnection is more for when you change the Database driver, say from OLE DB to ODBC or from SQL Server to Oracle, CR forces an update of all field references so it can fully qualify and verify the changes and apply them. This takes time to verify the DB....

So because you are changing table names everytime you should use the setLocation API to update the Report info.

If you can, don't manage TEMP tables within CR code. For example if a Stored Procedure is used to dump all of the data into a result table Cursor it should be kept in session per user running the Report and SP. By not using specific TEMPDB tables CR doesn't have to fully qualify the change each time.

Of course I'm not clear on exactly why you are doing it this way but if you can stop using the TEMPDB, CR will play much nicer with performance.

As for the reference info suggesting ReplaceConnection provides better performance is more of a general suggestion because it was assumed you were not using TEMPDB tables.

What you amy want to do is look at indexing, I find having indexes on key fields that CR uses gives a massive improvment in performance, SQL Server doesn't have to build them on the fly...

But you may want to test ODBC, ODBC does build it's own indexing so it may actaully give you better performance but at the same time because it's another layer on top of OLE DB you may lose.. but worth testing...

Hope that helps

Don

[Former Member · Accepted solution]

Hi,

ReplaceConnection on Subreport does not work for me. Can you also provide the code snippet to replace connection of sub report while we iterate through them as per you code?

[former_member11696 · Accepted solution]

Use the same code you use when setting the main report log on info. Just use the SubreportObject.

Don