CR for VS SP9 performance

I recently observed a performance boost for the SetTableLocation-method between SP5 and SP9. (The database is MS SQL-Server)

I can't find any Adaption-description in the SP6- to SP9-wiki's. Did I miss something?

BTW. what about SP10, already announced?

Patrick

SAP Managed Tags:
SAP BusinessObjects - BI Platform - SDK

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (1)

Hi Patrick

There are always some tweaks done here and there to improve performance. I know that over the last 9 service packs, we made improvements to how we handle looking for printer drivers, verifying databases, looking for original database connections, handling of parameters, connections to btrieve and dbase, addition of support for MS SQL 9SQL Native 11), etc., etc. Typically I try to document any fixes in a wiki, starting with Crystal Reports for Visual Studio 2010 - Service Pack 1 - Fixed Issues - Business Intelligence (Busi...

All, subsequent SPs are also documented and linked in the above wiki.

As far as SP 10 - soon. Target is end of June, but don't hold me to that. It may slip one or two weeks, you never know...

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Thanks.

It's already good that we see this performance boost from SP5 to SP9.

Nevertheless if we compare the performance from RDC to RAS (inproc) we and our customers realize a regression.

We use amongst others also temporary tables where the table names change because they contain a unique ID. Also the database for normal tables can change. So we run through each report and subreport and use SetTableLocationEx to replace the necessary values - it works, like it did in the same logic in RDC.

Do you have any idea why this logic in RAS is slower than the same logic in RDC?

Patrick

That is a rather broad question as there are so many variables to look after; database client, type of connection, actual code used, etc., and perhaps even seemingly unrelated variables such as printer drivers, version of CR the report was created in, OS, etc. So what you want is a specific use scenario, preferably starting with no subreports and start comparing the performance and tweaking the different variables. Also, what sort of performance difference are we looking at? One minute vs. one minute and one second (not really worth dicing into for that one second(?))? Again a use specific case with precise time differences would be helpful (e.g.; slower would not cut it. Nor would "few seconds to few seconds plus a few more seconds, etc.).

- Ludek

To give more specific information:

RAS via Crystal Reports for VS SP5 or SP9
RDC via Crystal Reports XI R2
Connection through OLE DB on MS SQL Server 2005
the report contains 17 normal tables, 2 temporary tables and no subreports.
the testdata is quite small, 4 records for the detail-table.
the report was created in CR XI R2.
the OS is Windows 7

some times:

using RDC:
- time until finished showing data in the Viewer: 2 seconds
- therefrom the function looping with SetTableLocationEx: 1 second
using RAS SP5:
- time until finished showing data in the Viewer: 6 seconds
- therefrom the function looping with SetTableLocationEx: 4 seconds
using RAS SP9:
- time until finished showing data in the Viewer: 4 seconds
- therefrom the function looping with SetTableLocationEx: 2 seconds

Maybe a question to ask to the R&D departement why there is a difference between RDC and RAS.

PS. if the report becomes bigger the times will increase progressively...

Thanks,

Patrick

Is the timing of the performance on first load of the report, or on refresh? Refresh should have a significantly better performance as on first load the app needs to load the CR runtime and do a bunch of configurations before actually processing the report.

Can you paste in the code used to logon with CRVS SP9?

- Ludek

We already implemented a preload of a simple dummy-report in the background when starting our app to load a maximum of Crystal-Dlls in memory.

So my time-values also occur when reloading the same report.

Here my simplified code for SetTableLocation:


Private Function SetTableLocationRAS(ByVal isMainReport As Boolean, _
                                         ByVal uniqueDbValue As String, ByRef databaseRenames As Dictionary(Of String, String), _
                                         ByVal serverName As String, _
                                         ByVal dbuserPassword As String) As Boolean
        Dim modifTable As CrystalDecisions.ReportAppServer.DataDefModel.Table
        Dim newTablename As String, newQualifiedTablename As String
        Dim oldDatabase As String, newDatabase As String
        Dim returnValue As Boolean = True

        Dim reportClientDoc As ISCDReportClientDocument = Nothing
        Dim subreportController As SubreportController = Nothing
        Dim tablesCollectionObject As IEnumerable
        If isMainReport Then
            ' Main
            reportClientDoc = _crystalMainReport.ReportClientDocument
            tablesCollectionObject = reportClientDoc.DataDefController.Database.Tables
        Else
            ' Sub                
            subreportController = _crystalMainReport.ReportClientDocument.SubreportController
            Dim sourceDatabaseForSubreport As CrystalDecisions.ReportAppServer.DataDefModel.Database = _
                                    subreportController.GetSubreportDatabase(_crystalSubReportClientDoc.Name)
            tablesCollectionObject = sourceDatabaseForSubreport.Tables
        End If

        ' run through all tables
        For Each checkTable As CrystalDecisions.ReportAppServer.DataDefModel.Table In tablesCollectionObject
            With checkTable
                oldDatabase = .ConnectionInfo.Attributes.StringValue("QE_DatabaseName")
                If .Name.ToUpper.Substring(0, 2) = "XT" Then
                    ' temporary model-table found
                    newDatabase = ""
                    newTablename = "##" & .Name.Substring(1) & uniqueDbValue
                    newQualifiedTablename = "tempdb.dbo." & newTablename                                   
                Else
                    ' normal table                     
                    newDatabase = .ConnectionInfo.Attributes.StringValue("QE_DatabaseName")
                    newTablename = .Name
                    newQualifiedTablename = newDatabase & ".dbo." & newTablename

                    ' get new databasename - databaseDrives 
                    For Each elem In databaseRenames
                        If elem.Key <> "" Then
                            If newDatabase.ToUpper.StartsWith(elem.Key.ToUpper & "_") Then
                                newDatabase = elem.Value
                                newQualifiedTablename = newDatabase & ".dbo." & newTablename
                                Exit For
                            End If
                        End If
                    Next
                End If

                ' change Connection-Properties
                modifTable = DirectCast(checkTable.Clone(True), CrystalDecisions.ReportAppServer.DataDefModel.Table)
                With modifTable.ConnectionInfo
                    With .Attributes
                        .StringValue("QE_DatabaseName") = newDatabase
                        .StringValue("QE_ServerDescription") = serverName
                        With DirectCast(.ObjectValue("QE_LogonProperties"), CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag)
                            .LongValue("Connect Timeout") = 300
                            .StringValue("Data Source") = serverName
                            .StringValue("Initial Catalog") = newDatabase
                            .BoolValue("Integrated Security") = (dbuserPassword = "WinAuth")
                        End With
                    End With
                    If dbuserPassword <> "WinAuth" Then
                        .UserName = _mainSQLUser
                        .Password = dbuserPassword
                    End If
                End With

                With modifTable
                    .QualifiedName = newQualifiedTablename
                    .Name = newTablename
                    .Alias = newTablename
                End With

                If isMainReport Then
                    Try
                        reportClientDoc.DatabaseController.SetTableLocationEx(checkTable, modifTable)
                    Catch ex As Exception
                        returnValue = False
                        SaveErrorText(ex.Message, "SetTableLocationEx (Main)")
                    End Try

                    ' Tests the status of the database connection to a table by querying the server to see if a connection to this table is active. 
                    If returnValue AndAlso Not reportClientDoc.DatabaseController.VerifyTableConnectivity(checkTable) Then
                        returnValue = False
                        SaveErrorText("", "VerifyTableConnectivity")
                        Exit For
                    End If
                Else
                    Try
                        subreportController.SetTableLocation(_crystalSubReportClientDoc.Name, checkTable, modifTable)
                    Catch ex As Exception
                        returnValue = False
                        SaveErrorText(ex.Message, "SetTableLocation (SR:" & _crystalSubReportClientDoc.Name & ")")
                    End Try
                End If                    
            End With
        Next

        Return returnValue
    End Function

Patrick

I'd recommend taking out verify db as this will slow things down for sure.

You may also want to see the logo n code as written out by the utility attached to this KBA.

Finally, if you want, you could attach one of your reports here and I can have a look - see what I see...

To attach, rename the rpt to txt, then in your reply hot the "Use advanced editor" link and attach.

- Ludek

Thanks for your reply Ludek.

PS. I already took the VerifyDatabase out of the code, actually I only use VerifyTableConnectivity() which produces no delay.

I also took a look on your code-Generation-utility some weeks ago, but there was no improvement to use.

Maybe you can forward my code to your dev-department; if they already made performance-tuning between SP5 and SP9, perhaps they can still boost it up a little more faster..... to attain the speed of RDC...

Patrick

FYI: I just tested the Performance with SP10 - there is no difference to SP9.

Patrick

Could you try with MS SQL 2008 or MS SQL 2012 or MS SQL 2013?

- Ludek

I made some tests now with SQL 2014 - no difference.

Did you get a feedback from R&D?

Tx,

Patrick

Hi Patrick

There is nothing R&D would do with the code sample. And from what I see, there is nothing wrong with it. However, I do have a question;

How is the performance when you run the report in the CR designer? Be it the one in the .NET IDE, or a stand-alone CR 2013 (30 day eval is here: SME Free Trials | SME Software | SAP).

- Ludek

Couple more suggestions I just thought of:

Find the clientSDKOptions.xml file in C:\Program Files (x86)\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Common\SAP BusinessObjects Enterprise XI 4.0\win32_x86. Change “VerifyDatabaseOnSetDataSource” from “True” to “False”.
Add the String Value "AllAtOnce_BackwardCompatible" under HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Crystal Reports\Database\QueryEngine and set the value to 1.
Refer to KBA 2003458 - Crystal Reports .NET Runtime application slow to load with no internet access

KBA 2003458 may not apply, or appear to apply, but it will not hurt to try(?) as this may come into play for other reasons also.

- Ludek

Hi Patrick,

You can't compare RDC to .NET, two completely different animals. RDC uses Native WIN32 API's ( wrapped in COM ) and .NET uses the Framework ( wrapped in COM ).

The real comparison is:

using RAS SP5:
- time until finished showing data in the Viewer: 6 seconds
- therefrom the function looping with SetTableLocationEx: 4 seconds
using RAS SP9:
- time until finished showing data in the Viewer: 4 seconds
- therefrom the function looping with SetTableLocationEx: 2 seconds

I have worked with Phoebe on a few of the DB connection Adapt's and she has improved the Table Look-up and Linking routines, nothing in a pubic Adapt though but aside effect of a DB performance Case.

If you want to see more details then turn on Crlogger, you have the dll just need to add the environment variable:

1553469 - How to enable Database logging in Crystal Reports for Visual Studio 2010

Look at the details after each connection is made and for each read, there will be a lot of them but if you total them all it may give you specifics on which API was improved.

Also note this WILL introduce performance issues so timings not valid in this case, but it will show the API's we are using. You can compare times between each read and then total each one and see where the improvement is.

I also added timings for PC databases this way ( C# ) but doing the same thing you are:

DateTime dtStart;

TimeSpan difference;

In your ForEach loop add this in:

dtStart = DateTime.Now;

rptClientDoc.DatabaseController.SetTableLocation(oldTable, newTable);

difference = DateTime.Now.Subtract(dtStart);

btnReportObjects.Text += oldTable.Name.ToString() + " Set in " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + ":" + difference.Milliseconds.ToString() + "\n";

May tell you if it's those DBTemp tables being used or not also. Possibly you can add more Table Indexing to improve performance also...

Updated Clients may take advantage of the some of the improvements the Clients have added. Try MS SQL Native 10 Client, it connects just fine to SQL 2005 and it is an improved engine.

So it could be improvements in both the CR DB Driver, .NET RAS Engine and crpe32 ( which is where the Table Look up routine was improved.

Don

PS - seems funny you posting a question why performance has improved... lol

PPS - Yes I did see you asking for more improvements

If I change the value in the XML-File the code runs 1 second faster but then I got an error:

"Crystal Report Windows Forms Viewer - Failed to retrieve data from the database. Details: [Database Vendor Code: 208]"

The registry key doesn't change anything.

BTW. testing in the designer is useless because my function above won't be used. To watch a report in the designer you have to set the table locations before refreshing the report. So it's no longer a generic report.

Patrick

Hi Don,

And Yes, I'm asking for more improvements - and No, I want to compare RDC to RAS. We have some ERP applications actually using RDC. The customers don't worry about the techniques internally used to print their reports. We want to migrate to RAS to keep up with the times. But when we do this and a customer's report takes 2 minutes instead of 1'10" before, the angry customer won't accept this - time is money, unfortunately in these times...

What I need is, send my code above to R&D (Phoebe) and let them tune it up as fast as RDC was - it should be 50% faster. And this at best for SP11 .

PS. your link does not work.

Patrick

Hi Patrick,

First I have to say the way you are accessing/building access to those temp tables is quite nice. The RDC had a LogOnSQLServerWithPrivateInfo that would piggy back on an existing Open Connection in ODBC and give you full access to the temp tables so it impersonated the same connection user access info.

Search for the KBA, top right corner, they are account bound to my log on info.

By changing that XML file is interesting the error you get, basically you told CR not to verify the database and it generated an error that was returned by the client.

If you search for this error on MSDN you'll find this MS reference to the SQL Error:

Error 208

Severity Level 16

Message Text

Invalid object name '%.*ls'.

Explanation

This error occurs when an object that does not exist is referenced. If the object exists, you might need to include the owner's name in the object name.

This suggests some database table or field name does not match with the new DB info you are setting in the report. This will cause CR to add time because it's having to look for and/or up date some field info and resolve the mis-matched info.

Check the Temp Table permissions, CR and the connection you are making may not have access to it on the first attempt. SQL Profiler may give you more info on this also.

You may be able to get around that error by changing your code and log on using a Full DB Admin account, so it has full access to the Temp Tables.

Or check your code, the way you dynamically build the table name may have some character in it causing this error or the temp table field does not match the original table info. May be a field type is not the same as the original field type.

Because this error is being generated have a look a this KBA ( search for it ) also.

1535347 - How to replace database connection information used by a Crystal Reports in a VS .NET project?

See if this API to update the connection info works better, it's basically the same as what you are doing but it will update the references in the report table and field collections the recommended way.

Using the .SetLocation assumes all of the DB info is the same, but it's not. You are using those Temp tables so technically you should be using the ReplaceConnection method to update the report DB info.

Logging on and then setting the location is if nothing except the DB server and log on info changes even when using the EX version of the API.

Don

Hi Don,

You point it in a good direction. I have activated the CrLogger.

When the XML-Flag <VerifyDatabaseOnSetDataSource> is set to True, the logger states out:

FILENAME LINE_NUMBER LOGGED_DATA LEVEL ... .\serverh.cpp 228 Beginning DbServerHandle::logon 20 .\serverh.cpp 3981 adoConnection->Open succeeded: dataLinkFile=,provider=SQLOLEDB,dataSource=TESTSERVER\TEST2014,userID=someUser,password=***** 10 .\serverh.cpp 3993 initialCatalog=TESTDB3,jetSystemDatabase=,jetDatabaseType=Access,jetDatabasePassword=,useIntegratedSecurity=0,includeInitialCatalogProp=1,includeSecurityProps=1,includeJetSecurityProps=0 10 ... .\ado.cpp 2981 Begin DbMatchLogonInfo 20 .\DbQueryBuilder.cpp 514 Query Targets: SQLOLEDB.1, NativeSQLServer 10

.\DbQueryBuilder.cpp 525 Successfully built query:

SELECT...

.\ado.cpp 1650 Beginning DbExecuteQuery 20

.\serverh.cpp 2754 Beginning DbServerHandle::openRowset 20

.\serverh.cpp 2930 adoRecordset->Open succeeded without async option: SELECT...
.\serverh.cpp 2947 Finishing DbServerHandle::openRowset 20

if <VerifyDatabaseOnSetDataSource> is set to False I got the error:

... .\ado.cpp 2981 Begin DbMatchLogonInfo 20 .\DbQueryBuilder.cpp 514 Query Targets: SQLOLEDB.1, NativeSQLServer 10

.\DbQueryBuilder.cpp 525 Successfully built query:

SELECT...

.\ado.cpp 1650 Beginning DbExecuteQuery 20

.\serverh.cpp 2754 Beginning DbServerHandle::openRowset 20

.\serverh.cpp 2914 adoRecordset->Open failed: SELECT...

.\adocommon.cpp 527 ADO error: 0x80040e37 Source: Microsoft OLE DB Provider for SQL Server Description: Invalid object name 'TestDB3.dbo.TestTable'

The error not only comes for temp tables but also for normal tables nevertheless server, database and table do exist!

Because our reports are generic reports, we have to change servernames, databases and for temp tables also the table names.

ReplaceConnection is not an option because it won't work on subreports, see my thread, where you stated out:


ReplaceConnection is more for when you change the Database driver, say from OLE DB to ODBC or from SQL Server to Oracle

So what does the XML-Flag or the VerifyDatabase internally make to get the report work. He should only check the tables, but it seems that he updates internal configurations?

I need more background....

Patrick

Hi Patrick,

The error indicates whatever security is being set in code on that temp table does not have sufficient permissions server side.

And to clarify, you are changing the CrystalSDKOptions.xml to enable Verifying or not?

In SQL Profiler what is it telling you when attempting to set location to the Temp tables? Who is the app logging in under?

Did you try using a SQL Server Admin account that has full access?

That error is being returned by the Server so it's SQL Server that is denying access to the tables and not CR.

ReplaceConnection does work on subreports so not sure why you are saying that. I had told you to use the same connection properties as the main report, you just need to set the objects correctly.

Don

Hi Don,

I made some tests with the SQL Profiler. The problem is not the security (I had always the sysadmin-role), the problems are the quotation marks internally used by CR to build the SQL-statements.

With VerifyDatabaseOnSetDataSource set to True, CR builds this SELECT (simplified sample) which works like a charm:

SELECT "xMyTempTable"."TField1", "xMyTempTable"."TField2"...

FROM "tempdb"."dbo"."##TTempTable123" "xMyTempTable"

With VerifyDatabaseOnSetDataSource set to False, CR builds this SELECT which throws an error ("Invalid object name 'tempdb.dbo.##TTempTable123'."):

SELECT "xMyTempTable"."TField1", "xMyTempTable"."TField2"...

FROM "tempdb.dbo.##TTempTable123" "xMyTempTable"

It seems there's a bug with this flag...

Any ideas?

Patrick

Hi Patrick,

I see you are using MDAC OLE DB Provider and the error is connecting to SQL to fill a Dataset?:

DbServerHandle::openRowset 20

.\serverh.cpp 2914 adoRecordset->Open failed: SELECT...

.\adocommon.cpp 527 ADO error: 0x80040e37 Source: Microsoft OLE DB Provider for SQL Server Description: Invalid object name 'TestDB3.dbo.TestTable'

That driver has limitations when connecting to newer server, limit by MS not CR. Change your driver to MS SQL Server Native 10 or Native 11.

Don

Yes we are using 'Microsoft OLE DB Provider for SQL Server' because someone from CR told us some years ago to no use the native client because there were a lot of bugs within. If I remember well when migrating from CR9 to CR XI, some foreign keys where lost - with OLE DB everything went always good.

BTW. the quotation mark bug also happens with the older SQL Server 2005.

I gave it a try and changed my report to 'SQL Server Native Client 11.0'. But I can't make it run with my RAS-library, some new errors are coming and I not have the time to rewrite my SetTableLocation-function for this driver.

A question to R&D: who inserts the quotation marks in the SQL statement - CR or the database driver?

Tx,

Patrick

Hi Patrick,

In older version CR only supported the MDAC version of the SQL client, now Microsoft only has limited support when connecting to newer servers so now you MUST use the Native 10 or Native 11 client.

CR does, we dynamically query the client to get the connection parameters and set them accordingly.

If you make a ODBC Connection and enable ODBC Tracing you can see the type of queries we make.

You can also enable CRLogger, ( Crystal database Logging component ) and see the API's crpe32 is making to talk to the DB.

Search got crlogger and you'll find a KBA on how to.

I suggest try updating the Report in CR Designer first to SQL Native 11 and then have a look at the SQL Statement and confirm it works there first.

Don

So I got finally work the native version. But the quotation mark error comes here also. So it doesn't have to do with the type of driver.

I activated the logger and in the line with :

.\DbQueryBuilder.cpp 525 Successfully built query:

SELECT...

the quotation marks are already on the wrong place.

I made also a test with the CR Designer 2011. Here both reports (OLEDB and Native) work without any quotation mark error.

So it should have something to do with the .Net viewer (RAS)....? Something for your R&D?

BTW. do you have somewhere an official statement from CR that the MS OLE-DB-driver should no longer be used?

Patrick

Hi Patrick,

PAM for CR for VS is here, but note it's not right, this one needs to be updated SQL 2008 and above you should not use OLE DB Provider or SQL Native from MDAC:

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/e06b8953-a62b-2d10-38b9-ca71f747e...

This MS KBA notes there are limitations:

http://msdn.microsoft.com/en-us/library/ms131035(v=sql.105).aspx

Can you attach your report?

If possible can you give me the SQL to generate the Tables being used also? If you don't want to attach I'll send you direct e-mail.

I found it works if you use the Native 10 client, even if nothing has changed in the tables. Everyone I have suggested updating the client too fixed the issue.

Odd that it's still generating the extract quotes, that was a bug before but fixed a long time ago. But could be due to the temp table names. If it has subreports did you update them also? And when updating select each table also, not just the

main connections. That way it's sure to update all references.

Don

Hi Don

I did some further research.

I shortened now to report to only one little table and let the SQLNCLI11 as driver.

It has something to do with SetTableLocationEx()-method to get the quote error when XML-VerifyDatabaseOnSetDataSource is set to False.

If I set my report in the CR-Designer (2011) to the correct tables and database, I only have to use


reportClientDoc.DatabaseController.LogonEx(serverName, newDatabase, _mainSQLUser, dbuserPassword)

instead of my SetTableLocationRAS() function (see my code above) and the report works, so it has nothing to to with the report itself, and also nothing with the driver (SQLOLEDB vs SQLNCLI).

As soon as SetTableLocationEx() is called once, the quote error comes.

Also when I don't change the props (because the settings were made in the Designer), the error happens. As you can see I don't change any prop here in my test after the clone-method:


For Each checkTable As CrystalDecisions.ReportAppServer.DataDefModel
...
  modifTable = DirectCast(checkTable.Clone(True), CrystalDecisions.ReportAppServer.DataDefModel.Table)
  reportClientDoc.DatabaseController.SetTableLocationEx(checkTable, modifTable)

Is there a relation between the XML-VerifyDatabaseOnSetDataSource and SetTableLocationEx()-method ?

Could you try your sample-report with SetTableLocationEx?

Patrick

Hi Don,

Did you find the time to make further tests? Did you contact R&D?

Patrick

I have not heard back from them. They are busy finalizing SP 11.

If you want to create a test app that emulates what your main app is doing I can test and escalate. Including the creation of the temp table.

Or purchase a support case, if it is a bug you'll get refunded.

Then I can do this full time, SCN issue are the bottom of the list of things to do... And this would take time to set up which I don't have right now.

Don

Ask a Question

Top Q&A Solution Author

User

Count