A strange problem with 'select' performance

  • My statement is as followed:

    I have a table with 2 million records and do a query with the SQL

    "select * from TBL where fld_1=@value"

    (fld_1 is the primary key and defined as char(8)).

    1)when @value is N'10000009', it takes about 0.5 second.

    2)when @value is '10000009',it takes about 0.1 second.

    3)when @value is string which ends with 9 and length is 8 like N'10000019', N'10000129',it takes about 0.5 second.

    4)when @value is string which ends with 9 and length is less than 8,such as N'10000008',it takes about 0.1 second.

    5)when @value is string which does not end with 9, just like N'10000001', N'10000003', N'100015'.

    ,it takes 0.1 second.

    6)when fld_1 is defined as nchar(8) and @value is N'10000009',it takes about 0.1 second.

    7)when all of the indexes(the default primary index also included) are dropped, it takes the same time whatever the @value is.

    In my project a complexible SQL is used and the difference described above is expanded about 30 times.

    Does anyone know the special '9'?

  • Anytime that you use a unicode constant, the query will be slower. That's because of implicit conversion that's required on the column which prevents any index seeks.

    None of the values you've mentioned are unicode so why are you making them unicode?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It was because of the coding fault at the beginning

    and now I want to know why this phenomenon happened.

  • It sounds like what you're saying is that the somewhat unusual performance differences are dependent on the indexes. If that's the case, we would probably need to know the table structure (CREATE statement) and the CREATE INDEX statements.

    BTW, I like your word "complexible".. sort of a concatenation of "complex" and "flexible"..

  • johny (4/23/2009)


    and now I want to know why this phenomenon happened.

    As I said, if the column is varchar and the string literal is NVarchar (as in your slow examples), the column will be implicitly converted to nvarchar. That implicit conversion makes it impossible for SQL to use an index seek to satisfy the query, it has to scan. A scan will (usually) take longer than a seek.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • There can also be a difference depending on the number of rows that fit the criteria you selected, since returning the data is part of the excution time. Might that be part of it?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • To tung:

    The table is created as follows:

    CREATE TABLE T_JUMIN_B_TBL (

    MANAGEMENT_NO CHAR(8) NOT NULL,

    PERSONAL_NO VARCHAR(8) NOT NULL,

    FAMILY_NAME NVARCHAR(50) NULL,

    FAMILY_NAME_Y NVARCHAR(50) NULL,

    ...

    ...

    MODDATE DATETIME NULL) ON 'KFS_DATA'

    GO

    ALTER TABLE T_JUMIN_B_TBL

    ADD CONSTRAINT T_JUMIN_B_TBL_PK PRIMARY KEY CLUSTERED (MANAGEMENT_NO)

  • To GilaMonster:

    It should take a same time for all the records when using NVARCHAR.

    But now the records ending with '9' take much more time than the non-9 records.

    This is the point that puzzled me:(

    To GSquared:

    The number of rows selected is 1 each time.

  • johny (4/26/2009)


    To GilaMonster:

    It should take a same time for all the records when using NVARCHAR.

    But now the records ending with '9' take much more time than the non-9 records.

    This is the point that puzzled me:(

    Are the execution plans different? Can you post them?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I am now in Japan and the SQLServer's language is Japanese and my english is not good enough to translate the execution plans exactly.

    You can create a table to do the test using script below and examine the results in person if possible. The attachment is the explain plan in my computer. It is just a reference. Thank you.

    ===========================================================

    CREATE TABLE T_TEST_9 (

    ID CHAR(8) NOT NULL,

    MODDATE DATETIME NULL)

    GO

    ALTER TABLE T_TEST_9

    ADD CONSTRAINT T_TEST_9_PK PRIMARY KEY CLUSTERED (ID)

    Go

    DECLARE @col_value AS CHAR(8)

    DECLARE @int_value AS INT

    SET @int_value = 10000001

    WHILE @int_value < 13000000 --the count of records

    BEGIN

    set @col_value = convert(CHAR(8),@int_value)

    INSERT INTO T_TEST_9

    (ID)

    VALUES

    (@col_value)

    set @int_value = @int_value + 1

    END

    GO

    ============================================================

    " select * from T_TEST_9 where ID=N'10000009' "

    takes more time than

    " select * from T_TEST_9 where ID=N'10000008' "

  • In addition,the records ending with 'z' also take more time than other records.

  • Any chance you can post the plans as .sqlplan files (zipped)? The xml should be the same across all languages so, if I load it into my management studio, I'll get it in English

    I ran the tests that you gave. (SQL 2008 RTM Developer Edition, x64)

    select * from T_TEST_9 where ID=N'10000009'

    Table 'T_TEST_9'. Scan count 1, logical reads 9333, physical reads 0

    SQL Server Execution Times:

    CPU time = 672 ms, elapsed time = 667 ms.

    select * from T_TEST_9 where ID=N'10000008'

    Table 'T_TEST_9'. Scan count 1, logical reads 9333, physical reads 0

    SQL Server Execution Times:

    CPU time = 672 ms, elapsed time = 668 ms.

    select * from T_TEST_9 where ID=N'1000000z'

    Table 'T_TEST_9'. Scan count 1, logical reads 9333, physical reads 0

    SQL Server Execution Times:

    CPU time = 672 ms, elapsed time = 666 ms.

    I don't see any difference in execution time. I ran them multiple times and the execution times are very, very close each time.

    Can you confirm that, when run multiple times, the second is slower than the first. If you swap the order of the two, is the one that ends with 9 still slower?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes. The records that end with '9' take more time every time.

    And the attachment is the execution plans(.sqlplan).

    Thanks.

  • johny (4/28/2009)


    Yes. The records that end with '9' take more time every time.

    Can you post output of statistics IO and Statistics time?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply