ACC2000: Queries Are Slower After You Install Microsoft Jet 4.0 Service Pack 4 or Service Pack 5 (302496)

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

After you apply Microsoft Jet 4.0 Service Pack 4 (SP4) or Microsoft Jet 4.0 Service Pack 5 (SP5) to the system, some queries in Access run five times slower.

Note Microsoft Jet 4.0 Service Pack 4 (SP4) is included with Microsoft Windows 2000 Service Pack 1 (SP1). Microsoft Jet 4.0 Service Pack 5 (SP5) is included with Microsoft Windows 2000 Service Pack 2 (SP2).

CAUSE

This problem occurs when the query includes a user-defined function. If you run a query that contains a user-defined function that accepts no parameters in the WHERE clause, Jet 4.0 Service Pack 3 (SP3) and earlier versions first evaluate the function one time, and then use the results of this function in a WHERE clause that is sent to the back-end server. Jet 4.0 SP4 or SP5 runs the whole back-end query first. Then it applies the results of the function call to the query after the data is fetched. This makes performance slower.

RESOLUTION

This problem was corrected in Jet 4.0 Service Pack 6 (SP6) and later.
For additional information about how to obtain the latest Jet 4.0 service pack, click the following article number to view the article in the Microsoft Knowledge Base:

239114 How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.