Forum Discussion

HeinziAT's avatar
HeinziAT
Brass Contributor
Feb 20, 2024

Feb 2024 updates: Access->SQL Server performance issues in the Monthly Enterprise Channel

The February 2024 updates apparently introduced an issue that causes a severe performance problem with DAO recordsets, linked SQL Server tables and the legacy MDAC/WDAC SQL Server driver.

 

Here are the full repro steps:

 

Create large (300,000 rows) SQL Server table
 
CREATE TABLE _test (a int PRIMARY KEY);
 
WITH cte AS (
    SELECT 1 AS nr
    UNION ALL
    SELECT nr + 1 FROM cte WHERE nr < 300000
)
INSERT INTO _test
SELECT nr
  FROM cte
OPTION (MAXRECURSION 0);
 
Attach the table to Access (don't forget to modify the connection string)
 

Sub attach_table()
Dim td As TableDef

 

    Set td = CurrentDb.CreateTableDef()
    td.Name = "_test"

    td.Connect = "ODBC;Driver={SQL Server};SERVER=...;Database=...;Trusted_Connection=yes"
    td.Attributes = dbAttachSavePWD
    td.SourceTableName = "_test"
    CurrentDb.TableDefs.Append td
End Sub

 
Reproduce the problem
 

Sub repro_problem()
Dim t As Double
Dim rs As DAO.Recordset

 

    Set rs = CurrentDb.OpenRecordset("_test")
    t = Timer
    rs.MoveLast
    Debug.Print Timer - t
End Sub

 

On my system, repro_problem takes 
  • 13 seconds with the Monthly Enterprise Channel (v2312, Build 17126.20190), but
  • 0.2 seconds with the Semi-annual Enterprise Channel (v2308, Build 16731.20550) and
  • 0.2 seconds with the Current Channel (v2401, Build 17231.20236)

 

Notes:
  • This issue can be reproduced with the legacy MDAC SQL Server ODBC driver, but not with the new ODBC Driver 17 (didn't test 11, 13 and 18).
  • The issue only occurs if the SQL Server table has a primary key.
  • On a personal note, this issue is particularly embarassing for us, since we recently told all our customers to switch from the Current Channel to the Monthly Enterprise Channel to avoid the frequent Access bugs introduced by feature updates. That worked out well...
  • The code above is sample code to reproduce the problem, not production code. I know that there are more efficient ways to query SQL Server data from Access.
  • I tried to format the code snippets above as "code samples", but that causes the techcommunity editor to reject my posting since it contains "invalid HTML". Oh, well...
 
access
  • Karl_Donaubauer's avatar
    Karl_Donaubauer
    Feb 23, 2024

    Hi,

     

    Microsoft fixed the problem last night. There's no new build. You just have to restart Access to restore normal performance with the "SQL Server" driver.

     

    I have also updated our documenting blog article. As written there, it would be good if affected users could briefly report back whether the fix works for them.

     

    HeinziAT Extremely bad luck with this unusual order of appearance of the bug. S*** happens. At least you contributed to a quicker fix by reporting it here.

     

    Servus
    Karl
    ****************
    Access Forever
    Access News
    Access DevCon
    Access-Entwickler-Konferenz AEK

    • HeinziAT's avatar
      HeinziAT
      Brass Contributor
      Feb 20, 2024

      Thanks for the reminder! It's only been a month and I already forgot about the Jan 2024 issue.

       

      I just tried the old repro instructions we had for the Jan 2024 issue, and - voilà - that issue can be reproduced in the Feb 2024 Monthly Enterprise Build. So, yes, it seems that your intuition got it right and this is indeed the same problem. Let's hope Microsoft can do a fix-by-feature-gate-switch here as well.

       

      Oh, and by the way, the instructions in your blog on how to enable the "Choose Update Channel" button were extremely helpful in debugging this issue. 🙂

       

    • KentGorrell's avatar
      KentGorrell
      Copper Contributor
      Feb 24, 2024

      Karl_Donaubauer Semi colon issue still seems to be a problem on machines updated to 2312 on Monday February 19. At least Machines with this version fail to refresh tdf links to SQL Server. No issue on the machines that updated to 2311. 

      The Connect string looks OK, no missing semi colon but the .RefreshLink fails in VBA (but refreshes OK using the linked table manager).

      Given that MS tries to fix this stuff via a backdoor rather than issuing a new build, there is no way of knowing from the version/build if a machine should or should not be affected.

      Not a great way to manage a SDLC.

Resources