BUG: 6.5 Parse/Compile Time Is Greater than 6.0 on Large Join (169630)

SYMPTOMS

When processing a large join, the parse and compile time as indicated by SET STATISTICS TIME ON can be much greater for SQL Server 6.5 than it was for SQL Server 6.0. In one scenario a 9-way join took approximately 9.4 seconds to parse and compile on SQL Server 6.5, but only 1.3 seconds on version 6.0. Once parsed and compiled, the actual query execution time was similar between SQL Server versions 6.5 and 6.0. The problem happens whether the query is ad-hoc or is in a stored procedure.

This problem should not directly cause additional blocking or deadlocking, because no additional time is spent in the execution phase, and lock duration should be unchanged. However, this problem may use additional CPU resources.

All versions of SQL Server 6.5 through SQL Server 6.5 Service Pack 2 exhibit this behavior.

WORKAROUND

Use trace flag 342, which disables the costing of pseudo merge joins. This significantly reduces the amount of time spent in the parse and compile phase, and in the queries thus far examined has no negative execution performance impact. For instructions on how to use trace flags, see the SQL Server 6.5 documentation.

Alternatively you can use SET FORCEPLAN ON, which forces the join order indicated in the query FROM clause.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.