Microsoft KB Archive/821534

= Some Complex Queries Run Slower After You Apply SQL Server 7.0 SP4 =

Article ID: 821534

Article Last Modified on 10/25/2005

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-





SYMPTOMS
After you apply Microsoft SQL Server 7.0 Service Pack 4 (SP4), some complex queries may run much slower if all the following conditions are true:
 * The query's FROM clause is made up of several SELECT statements.
 * The SELECT statements join several tables and views with FULL OUTER JOIN clauses, RIGHT OUTER JOIN clauses, and LEFT OUTER JOIN clauses
 * At least one of the views is made up of several SELECT statements that have subqueries that are concatenated through UNION clauses.

A query that runs in less than one second in SQL Server 7.0 SP3 may take more than 60 seconds to complete in SQL Server 7.0 SP4.



CAUSE
SQL Server 7.0 SP4 contains a change to the SQL Server query optimizer that improves the overall performance. However, this change may adversely affect some complex queries.



RESOLUTION
A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SQL Server 7.0 service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the hotfix. For a complete list of Microsoft Product Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

The English version of this hotfix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.   Date         Time   Version            Size    File name -  04-Oct-2002  14:59  2000.34.4.0        28,944  Dbmssocn.dll 06-Sep-2002 14:55  2000.33.6.0        53,520  Distrib.exe 06-Sep-2002 14:55  2000.33.6.0        98,576  Logread.exe 05-May-2003 09:34                     54,904  Opends60.dbg 05-May-2003 09:34  2000.41.2.0       155,920  Opends60.dll 06-Sep-2002 14:56  2000.33.6.0       250,128  Rdistcom.dll 06-Sep-2002 14:55  2000.33.6.0        82,192  Replmerg.exe 06-Sep-2002 14:56  2000.33.6.0        78,096  Replres.dll 17-Sep-2002 13:52                      7,941  Securityhotfix.sql 06-Sep-2002 14:56  2000.33.6.0       160,016  Snapshot.exe 29-May-2003 19:21                     59,214  Sp4_serv_uni.sql 14-Jan-2003 16:33  2000.37.13.0      344,064  Sqlagent.exe 06-Sep-2002 14:55  2000.33.6.0        45,056  Sqlcmdss.dll 15-May-2003 15:18  2000.41.14.0    2,629,632  Sqldmo.dll 16-May-2003 04:29  2000.41.14.0       81,920  Sqlmap70.dll 30-Jun-2003 13:29  2000.42.30.0    5,062,928  Sqlservr.exe 04-Oct-2002 14:59  2000.34.4.0        45,328  Ssmsso70.dll 15-May-2003 15:18  2000.41.14.0       24,848  Ssnmpn70.dll 26-Sep-2002 11:30                     28,408  Ums.dbg 26-Sep-2002 11:27  2000.33.25.0       57,616  Ums.dll 16-May-2003 04:31  2000.41.14.0      151,552  Xpweb70.dll Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section of this article.



MORE INFORMATION
To install and activate this hotfix, follow these steps:
 * 1) Obtain the hotfix that is described in the &quot;Resolution&quot; section.
 * 2) Apply the hotfix to the computer that is running SQL Server 7.0.
 * 3) Activate trace flag 9154 as a startup option or at query runtime by using the DBCC TRACEON command.

To activate trace flag 9154 as a startup option, follow these steps:
 * 1) In SQL Server Enterprise Manager, right-click the computer that is running SQL Server 7.0, and then click Properties.
 * 2) Click Startup Parameters.
 * 3) In the Parameter text box, type -T9154.

Note Make sure that you use an uppercase T.
 * 1) Click Add, and then click OK.
 * 2) Restart the SQL Server 7.0 service.

To activate trace flag 9154 at query runtime, follow these steps:  Start SQL Query Analyzer, and then connect to the computer that is running SQL Server 7.0.  Run the following command: DBCC TRACEON (9154, -1)   Run the affected query.

Note The trace flag remains active until the SQL Server service is restarted. You can also deactivate the trace flag by using the following command: DBCC TRACEOFF (9154, -1) 

Trace flag 9154 disables the changes in SQL Server 7.0 SP4 that have a negative impact on some complex queries.

The following sample query is affected by this problem: SELECT view_2.column2 FROM (SELECT *     FROM   view_1      WHERE  column1 = '80PROP' OR column1 IS NULL) b

FULL OUTER JOIN (SELECT *     FROM   table_1      WHERE  column1 = '80PROP') a     ON a.column1 = b.column1 AND a.column2 = b.column2

RIGHT OUTER JOIN view_2 ON a.column2 = view_2.column2 OR b.column2 = view_2.column2

LEFT OUTER JOIN view_3 ON view_2.column2 = view_3.column2

WHERE (view_2.IdSchema = 'GR1') AND (view_2.IdLingua = '001') AND (view_3.IdSegnalazioneRichiesta = 'C1'        OR  view_3.IdSegnalazioneRichiesta = 'V1'         OR  view_3.IdSegnalazioneRichiesta = '1') AND (b.column1 = '80PROP'        OR b.column1 IS NULL) OR (view_2.IdSchema = 'GR1') AND (view_2.IdLingua = '001') AND (b.column1 = '80PROP'        OR b.column1 IS NULL) AND (a.ImportoRE + a.ImportoRN + a.ImportoNRE + a.ImportoNRN <> 0)

ORDER BY view_2.column2 GO

Keywords: kbbug kbfix kbqfe kbsqlserv700presp5fix kbhotfixserver KB821534

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.