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

[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