Crystal Reports Performance degression under SQL Server 2025

[#1]

Hi,

A customer told us severe performance problems after moving SQL databases from SQL server 2022 to 2025.
A report parsing a large amount of data took 6 minutes on SQL 2022 and now 35 minutes on SQL 2025 - with same database contents!

After some research, I found a workaround, as proposed to me here:
Performance regression from SQL Server 2022 to 2025 

The problem comes from the new PBKDF2 login hashing:
SQL Server 2025 known issues - PBKDF2 hashing algorithm can affect login performance 

I activated the sql trace 4671, and the report was again "fast" enough.

Now my question: the report uses/joins 3 databases inside CR (cf. EXTERNAL JOIN). The design is a little bit poor. The report uses "Microsoft OLE DB Driver for SQL Server" as database driver which has connection pooling active by default. 
When I log the Audit Logins/Logouts in SQL Server profiler, I see that CR generates about 80 new connections per second (open & close) !

Is there something in CR to influence this connection behavior?

PS. I know, we should redesign the report and put the join logic into a sql command, view or whatever...

Thanks,
Patrick

[patrick_simons2]

Hi Don,

according to Overriding Provider Service Defaults 

the values for "OLE DB Services" are:

Default services enabledValue in connection string

All services (default)	"OLE DB Services = -1;"
All except pooling and automatic transaction enlistment	"OLE DB Services = -4;"
All except Client Cursor Engine	"OLE DB Services = -5;"
All except pooling, automatic transaction enlistment, and Client Cursor Engine	"OLE DB Services = -8;"
Pooling and automatic transaction enlistment only, session level aggregation only	"OLE DB Services = 3;"
No services	"OLE DB Services = 0;"

We use both OLE DB Providers in the Crystal Reports designer:
- Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) 
- Microsoft OLE DB Provider for SQL Server (SQLOLEDB) 

When creating a new connection to a database/table in CR, it always defaults to "OLE DB Services = -5;" -> all is enabled except "Client Cursor Engine", so connection pooling should be enabled. Don, I want connection pooling to be enabled but analyzing the SQL profiler, it doesn't seem to be the case. Maybe you can ask R&D what they think of? Otherwise people using CR and SQL Server 2025 will meet serious problems in future...
Does someone have experience with this setting?

Regards,
Patrick

[DonWilliams]

Hi Patrick,

Great you found solutions from Microsoft....

Since DB Servers have started using Connection Pooling it's something Crystal has not implemented, too much back end coding to detect and use that functionality. R&D possibly did tests and determined the use of pooling did nothing for performance within CR due to the extra steps to find an available pool to use and then keep that connection in focus rather than simply opening a new connection which CR does by default.

I believe when using ODBC you can disable Pooling in the DSN connection properties. Not sure about OLE DB.

A google search for an old post where the customer used this to disable for OLE DB:

I think I have found a solution.

In the report its self, Under Database > Set Datasouce Location, under properties, set OLE DB Services to -4.

Seems to have resolved the issue. I will continue to test. I will post back on Monday. Keep you fingers crossed.

I've also found by adding Indexing to the Tables used does improve performance....

So no there is nothing CR can do to connection behaviour, at least no simple way.

You could try converting to ODBC, using Set Location and see if that helps at all, often MS Clients, OLE DB/ODBC break each other, sometime one is better than the other... and sometime one doesn't even work....

Don

[DonWilliams]

FYI - I don't recall when but R&D did say they had no plans to enable Connection Pooling. I kind of recall it was due to the speed CR connects and releases. Best thing for you to do is speeding up the Queries. As you noted the reports are not optimized...