Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002

    Angry Unanswered: SQL2K performance problem

    We have upgraded to SQL2K (SP2) and discovered some severe performance degradation and wonder if anyone out there has some insights into whether this is simply a SS bug or a mistake on our part.

    Given the following table:
    CREATE TABLE phrase (
    phraseKey int IDENTITY,
    name varchar(254) NULL,
    PRIMARY KEY (phraseKey),
    UNIQUE (name)

    and the following statement (submitted from our Java app via JDBC):
    SELECT phraseKey FROM phrase WHERE name = @1

    In SQL 7, it runs in 10 ms as follows:
    Convert Unicode parm value to applicable code set and Seek across XAK1phrase for that value

    In SQL 2K, it runs in 500 ms as follows:
    Scan XAK1phrase looking for an entry whose value (converted to Unicode) matches the Unicode parm value

    Bottom line: The index is useless!

    Did MS really intent to switch the CONVERT from the parm (only one of them) to the DB column (300,000 of them in our table)? This looks like a big bug to me, but I find no mention of such in any of the forums I've checked, let alone the MS KB.

  2. #2
    Join Date
    May 2002
    You want to run "sp_updatestats" after installing svcpack.

  3. #3
    Join Date
    May 2002
    A user on another board pointed me to the real problem which is that MS purposely changed the behavior to correct a bug and now warns (though not very loudly) that there are some pretty severe negative ramifications!...

    This is documented in KB article Q271566:;en-us;Q271566

    It corrects a failing of SQL Server 7, where data was
    sometimes converted with a loss of precision.

    You can always assure the best use of indexes by
    converting the variables and constants explicitly to the
    datatype of the column you are using for comparison.
    In general, it's worthwhile to make all conversions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts