SQL Server Performance

Need the help of SQL server guru's...

Discussion in 'SQL Server 2005 General Developer Questions' started by image, Oct 24, 2007.

  1. image New Member

    Dear SQL Server guru's...
    I really need your help this time ... I have a problem in SQL Server 2005 ...
    I have the following tables:
    1 - DOC_DATA, which have the columns:
    - DD_ID: GUID, primary key.
    - PAGE_NUMBER: integer.
    - IN_DATE: date time.
    2- REC_DATA
    - REC_ID: GUID, primary key.
    - DD_ID: GUID, primary key, references DOC_DATA.DD_ID
    - RECORD_NUMBER: varchar, not null.
    The relation between both tables is clear, each document has many records.
    given that the REC_DATA.RECORD_NUMBER contents can be either numeric or alphanumeric
    based on DOC_DATA.DD_ID.
    based on the business logic sometimes we need to convert the REC_DATA.RECORD_NUMBER
    to numeric using the CONVERT function and sometimes we deal with it as varchar.
    If we tried to retrieve the records from REC_DATA based on correct conversion to
    numeric of REC_DATA.RECORD_NUMBER as in the following query:
    SELECT DISTINCT
    DOC_DATA.DD_ID,
    REC_DATA.RECORD_NUMBER,
    convert(numeric,REC_DATA.RECORD_NUMBER)
    FROM
    DOC_DATA
    left outer join
    REC_DATA
    on
    DOC_DATA.DD_ID = REC_DATA.DD_ID
    where
    DOC_DATA.DD_ID = '93ade332-08e1-4576-89c5-62877730ee23'
    and
    convert(numeric,REC_DATA.RECORD_NUMBER) = 52017 ;
    it will generate the error:
    Msg 8114, Level 16, State 5, Line 116
    Error converting data type varchar to numeric.
    however; if we tried the same query but replaced the equality in the where
    condition :
    convert(numeric,REC_DATA.RECORD_NUMBER) = 52017
    by > or <, it will successfully execute the conversion and return the
    needed records.
    if we tried the same query without the where condition :
    convert(numeric,REC_DATA.RECORD_NUMBER) = 52017
    as follows:
    SELECT DISTINCT
    DOC_DATA.DD_ID,
    REC_DATA.RECORD_NUMBER,
    convert(numeric,REC_DATA.RECORD_NUMBER)
    FROM
    DOC_DATA
    left outer join
    REC_DATA
    on
    DOC_DATA.DD_ID = REC_DATA.DD_ID
    where
    DOC_DATA.DD_ID = '93ade332-08e1-4576-89c5-62877730ee23' ;
    It will successfully execute and return the needed records. which means
    that all REC_DATA.RECORD_NUMBER when DOC_DATA.DD_ID ='93ade332-08e1-4576-89c5-62877730ee23'
    have numeric values.
    So, there is a conflict ...
    if all REC_DATA.RECORD_NUMBER when DOC_DATA.DD_ID ='93ade332-08e1-4576-89c5-62877730ee23'
    have numeric values ... WHY when we try to use exactly the same conversion
    at the where clause it fails if it is equality while succeed in the > and < ...
    Our system was first successfully implemented using SQL Server 2000, however; when we
    upgraded to SQL Server 2005, the above errors appeared.
    we tried the latest hot fixes and service packs ... but still the problem exist.
    Can someone please explain to me why is this happening and how can we
    solve the problem? ... it is an urgent issue....
    Waiting your help ...
    Thank you for your time ...
    Wishing you all smooth day without having such problems ....
    image, Oct 24, 2007
    #2

  2. FrankKalis Moderator

    FrankKalis, Oct 25, 2007
    #3

  3. martins New Member

    I have also encountered this problem before, and it has to do with the order in which SQL Server decides to process your query. The link provided by Satya will give you more of an understanding.
    The only "safe" way to get around this is by using a case statement where you check whether the value is numeric with the isnumeric() function first.
    martins, Oct 25, 2007
    #4

  4. FrankKalis Moderator

    [quote user="martins"]
    I have also encountered this problem before, and it has to do with the order in which SQL Server decides to process your query. The link provided by Satya will give you more of an understanding.
    The only "safe" way to get around this is by using a case statement where you check whether the value is numeric with the isnumeric() function first.
    [/quote]
    Ahem, from time to time there are also others who post answers to the fora here. [H][:)]
    FrankKalis, Oct 25, 2007
    #5

  5. martins New Member

    hehehe...yeah we try Frank [:D]
    It's a bit difficult to jump Satya in the queue though. I think he should be aka "Flash Gordon", cause he usually gets in there before anybody else [:p]
    Just another add on guys...this problem is not only a SQL2k5 issue. I have come across this in SQL2k too, and the only reason for it was that the execution plan changed (for some obscure reason) to evaluate the convert before the isnumeric in the where-clause.
    martins, Oct 25, 2007
    #6

  6. FrankKalis Moderator

    We had a similar thread on the German MS newsgroup and the example given there succeeded on SQL 2000 SP 4, failed on SQL Server 2005.
    It also succeeded on Oracle, DB2, and Teradata. Here's the script:
    CREATE TABLE tbl1(ID uniqueidentifier)
    CREATE TABLE tbl2(ID uniqueidentifier, tbl1_ID uniqueidentifier, [name]
    nvarchar(128))
    CREATE TABLE tbl3(ID uniqueidentifier, tbl2_ID uniqueidentifier, [value]
    nvarchar(2048))

    DECLARE @run int
    DECLARE @ID1 uniqueidentifier, @ID2 uniqueidentifier
    SET @run = 0

    SET nocount on

    WHILE @run <1000
    BEGIN
    SELECT @ID1 = newid(), @ID2 = newid()
    INSERT INTO tbl1(ID)
    SELECT @ID1

    INSERT INTO tbl2(ID, tbl1_ID, [Name])
    SELECT @ID2, @ID1, 'class'

    INSERT INTO tbl3(ID, tbl2_ID, [value])
    SELECT newid(), @ID2, 'user'

    IF @run % 2 = 0
    INSERT INTO tbl3(ID, tbl2_ID, [value])
    SELECT newid(), @ID2, 'computer'

    SET @ID2 = newid()

    INSERT INTO tbl2(ID, tbl1_ID, [Name])
    SELECT @ID2, @ID1, 'GUID'

    INSERT INTO tbl3(ID, tbl2_ID, [value])
    SELECT newid(), @ID2, CAST(newid() AS nvarchar(2048))

    SET @run = @run +1

    END

    SET nocount off

    -- TEST STATEMENTS
    -- this should work
    SELECT newid(), tbl1.ID, CAST(gdv.[Value] AS uniqueidentifier)
    FROM tbl1
    INNER JOIN tbl2 cl ON cl.tbl1_ID = tbl1.ID AND cl.[Name] = 'class'
    INNER JOIN tbl3 clv ON clv.tbl2_ID = cl.ID AND clv.[Value] = 'user'
    INNER JOIN tbl2 gd ON gd.tbl1_ID = tbl1.ID AND gd.[Name] = 'GUID'
    INNER JOIN tbl3 gdv ON gdv.tbl2_ID = gd.ID
    LEFT JOIN tbl3 cp ON cp.tbl2_ID = cl.ID AND cp.[Value] = 'computer'
    WHERE cp.ID IS NULL
    OPTION (HASH JOIN)

    -- this not
    SELECT newid(), tbl1.ID, CAST(gdv.[Value] AS uniqueidentifier)
    FROM tbl1
    INNER JOIN tbl2 cl ON cl.tbl1_ID = tbl1.ID AND cl.[Name] = 'class'
    INNER JOIN tbl3 clv ON clv.tbl2_ID = cl.ID AND clv.[Value] = 'user'
    INNER JOIN tbl2 gd ON gd.tbl1_ID = tbl1.ID AND gd.[Name] = 'GUID'
    INNER JOIN tbl3 gdv ON gdv.tbl2_ID = gd.ID
    LEFT JOIN tbl3 cp ON cp.tbl2_ID = cl.ID AND cp.[Value] = 'computer'
    WHERE cp.ID IS NULL
    OPTION (LOOP JOIN)


    DROP TABLE tbl1
    DROP TABLE tbl2
    DROP TABLE tbl3
    FrankKalis, Oct 25, 2007
    #7

  7. image New Member

    Thank you so much for all of you ...
    I understand the source of the problem now ... I have changed the where clause to use the CASE statement as below ...
    SELECT DISTINCT
    DOC_DATA.DD_ID,
    REC_DATA.RECORD_NUMBER,
    convert(numeric,REC_DATA.RECORD_NUMBER)
    FROM
    DOC_DATA
    left outer join
    REC_DATA
    on
    DOC_DATA.DD_ID = REC_DATA.DD_ID
    where
    DOC_DATA.DD_ID = '93ade332-08e1-4576-89c5-62877730ee23'
    and
    1 =
    case
    when convert(numeric,REC_DATA.RECORD_NUMBER) = 52017
    then 1
    else 0
    end;
    it works fine now ... but this will affect our system, since I need to review all the queries that is being built dynamically in ...
    by the way, I tried to change the compatibility level to SQL server 2000 ... but the problem did't solved ... I hope Microsoft will fix this problem soon...
    thanks again ... [:)] ...
    _____________________
    Sammer A. Al-Mashaqbeh.
    image, Oct 25, 2007
    #8

  8. Adriaan New Member

    I wouldn't wait for MS to solve this backward compatibility issue, as it probably goes too deep into the core software.
    If you're having to deal with pseudo-numeric data, you should consider tidying up the application so that the data is always truly numeric, and you don't have to do this type of check at this level.
    Adriaan, Oct 25, 2007
    #9

  9. FrankKalis Moderator

    [quote user="Adriaan"]
    I wouldn't wait for MS to solve this backward compatibility issue, as it probably goes too deep into the core software.
    If you're having to deal with pseudo-numeric data, you should consider tidying up the application so that the data is always truly numeric, and you don't have to do this type of check at this level.
    [/quote]
    I don't think MS will consider this a backward compatibility issue. You can workaround it with CASE expressions or something similar, but as I mentioned in my reply in the other thread, but error happened while logically modelling the model. A column should only store values from one domain.
    FrankKalis, Oct 25, 2007
    #10

  10. Madhivanan Moderator

    Madhivanan, Oct 25, 2007
    #11

Share This Page