How to tune this query

  • Hi All,

    Can anybody shed a light on how to tune this query?

    select UID, ReferenceNumber, Type

    FROM myTable

    WHERE ReferenceNumber NOT LIKE 'TR%' AND

    UID in (Select UID from myTable where Type = 1

    UNION Select UID from myTable where ReferenceNumber

    not in (select ReferenceNumber from myTable where Type = 1))

    ORDER BY UID

    Mytable has around 50000 rows and return result 48000 rows. There are non-clustered indexes on UID,ReferenceNumber, Type. it runs 100 secs and logical read 408000.

    Thanks

    Judy

  • select UID, ReferenceNumber, Type

    FROM myTable

    WHERE ReferenceNumber NOT LIKE 'TR%' AND

    Type = 1

    Is thi squery giving different results than yours one?

    :w00t:

  • vidya_pande (3/31/2010)


    select UID, ReferenceNumber, Type

    FROM myTable

    WHERE ReferenceNumber NOT LIKE 'TR%' AND

    Type = 1

    Is thi squery giving different results than yours one?

    :w00t:

    Yes. The result is different.

  • Can you provide some table structures and sample data?

    Gethyn Elliswww.gethynellis.com

  • Table structure

    CREATE TABLE [dbo].[mytable](

    [ID] [bigint] IDENTITY(1,1) NOT NULL,

    [UID] [uniqueidentifier] NOT NULL,

    [ReferenceNumber] [varchar](20) NULL,

    [Type] [int] NOT NULL,

    [Col4] [varchar](50) NOT NULL,

    [Col5] [int] NOT NULL,

    [Col6] [int] NULL,

    [Col7] [uniqueidentifier] NULL,

    [Col8] [int] NOT NULL,

    [Col9] [varchar](max) NULL,

    [Col10] [int] NULL

    ....

    )

  • Please post complete table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Have you considered normalising that table?

    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'm not sure if I'm totally wrong, but isn't that WHERE condition identical to WHERE ReferenceNumber NOT LIKE 'TR%' ?

    Reason:

    With your first subquery you include all UIDs with Type = 1 and with your second subquery you add all remaining UIDs.

    With the following sample data I get identical result:

    DECLARE @tbl TABLE

    (

    uid INT,

    TYPE INT,

    ReferenceNumber CHAR(3)

    )

    INSERT INTO @tbl

    SELECT 1,1,'ABC' UNION ALL

    SELECT 1,2,'ABC' UNION ALL

    SELECT 2,1,'ABC' UNION ALL

    SELECT 2,1,'ABC' UNION ALL

    SELECT 2,3,'ABC' UNION ALL

    SELECT 2,1,'TRA' UNION ALL

    SELECT 2,2,'TRA' UNION ALL

    SELECT 3,1,'ARA' UNION ALL

    SELECT 3,1,'TRA' UNION ALL

    SELECT 3,3,'TRA'

    SELECT UID, ReferenceNumber, TYPE

    FROM @tbl myTable

    WHERE ReferenceNumber NOT LIKE 'TR%'

    AND

    UID

    IN

    (

    SELECT UID

    FROM @tbl myTable

    WHERE TYPE = 1

    UNION

    SELECT UID

    FROM @tbl myTable

    WHERE ReferenceNumber

    NOT IN (SELECT ReferenceNumber FROM @tbl myTable WHERE TYPE = 1)

    )

    ORDER BY UID

    SELECT UID, ReferenceNumber, TYPE

    FROM @tbl myTable

    WHERE ReferenceNumber NOT LIKE 'TR%'

    ORDER BY UID

    -- Edit: maybe there is a data constellation that requires an EXISTS check:

    SELECT UID, ReferenceNumber, TYPE

    FROM #tbl myTable

    WHERE ReferenceNumber NOT LIKE 'TR%'

    AND EXISTS (SELECT 1 FROM #tbl myTable2 WHERE TYPE = 1 AND myTable.uid = myTable2.uid)

    ORDER BY UID



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Do you have an execution plan?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • lmu92 (3/31/2010)


    I'm not sure if I'm totally wrong, but isn't that WHERE condition identical to WHERE ReferenceNumber NOT LIKE 'TR%' ?

    Reason:

    With your first subquery you include all UIDs with Type = 1 and with your second subquery you add all remaining UIDs.

    [/code]

    At the beginning, I have the same thought.

    but if Column "ReferenceNumber" duplicate on type=0 and type=1, then the result will only returm type=1.

    For example,

    ID,UID,ReferenceNumber,Type

    1,B57B7BDF-2D13-4F00-AF97-000016834168,TR052950,0

    2,EA82B85C-7993-4AC7-9221-000018FCAF44,TR052950,1

    3,5749DF81-FC62-4CA8-9AFF-0000AF35F77E,AS053069,1

    4,50C32C91-A0CA-4E21-9C2C-0002888315F8,AS053069,0

    5,380728E0-A1F8-46C1-9D06-0007924C50AF,SD068972,1

    The result will only show ID=2,3,5. not all.

  • GilaMonster (3/31/2010)


    Please post complete table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Have you considered normalising that table?

    CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[myTable]

    (

    [UID] ASC

    )

    CREATE UNIQUE NONCLUSTERED INDEX [IX_ReferenceNumber] ON [dbo].[myTable]

    (

    [ReferenceNumber] ASC,

    [Type] ASC

    )

    yes, this is a normalisied table.

    By the way, Type has only two value 0 and 1

  • Try:

    SELECT T.UID, T.ReferenceNumber, T.Type

    FROM myTable T

    WHERE T.ReferenceNumber NOT LIKE 'TR%'

    AND

    (

    T.Type = 1

    OR NOT EXISTS

    (

    SELECT *

    FROM myTable T1

    WHERE T1.ReferenceNumber = T.ReferenceNumber

    AND T1.Type = 1

    )

    )

    ORDER BY UID

  • Judy-363345 (4/1/2010)


    GilaMonster (3/31/2010)


    Please post complete table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Have you considered normalising that table?

    CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[myTable]

    (

    [UID] ASC

    )

    CREATE UNIQUE NONCLUSTERED INDEX [IX_ReferenceNumber] ON [dbo].[myTable]

    (

    [ReferenceNumber] ASC,

    [Type] ASC

    )

    yes, this is a normalisied table.

    By the way, Type has only two value 0 and 1

    Execution plan please?

    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
  • Ken McKelvey (4/1/2010)


    Try:

    SELECT T.UID, T.ReferenceNumber, T.Type

    FROM myTable T

    WHERE T.ReferenceNumber NOT LIKE 'TR%'

    AND

    (

    T.Type = 1

    OR NOT EXISTS

    (

    SELECT *

    FROM myTable T1

    WHERE T1.ReferenceNumber = T.ReferenceNumber

    AND T1.Type = 1

    )

    )

    ORDER BY UID

    Compared to my original query, The run time is close. but the logical read is much smaller than mine.

    Your logical read is 198509. mine is 461382

  • Did you try the solution I posted earlier?

    SELECT UID, ReferenceNumber, TYPE

    FROM #tbl myTable

    WHERE ReferenceNumber NOT LIKE 'TR%'

    AND EXISTS (SELECT 1 FROM #tbl myTable2 WHERE TYPE = 1 AND myTable.uid = myTable2.uid)

    ORDER BY UID



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • How does this do?

    SELECT UID, ReferenceNumber, Type

    FROM myTable

    WHERE ReferenceNumber NOT LIKE 'TR%'

    AND Type = 1

    UNION ALL

    SELECT T.UID, T.ReferenceNumber, T.Type

    FROM myTable T

    WHERE T.ReferenceNumber NOT LIKE 'TR%'

    AND T.Type <> 1

    AND NOT EXISTS

    (

    SELECT *

    FROM myTable T1

    WHERE T1.ReferenceNumber = T.ReferenceNumber

    AND T1.Type = 1

    )

Viewing 15 posts - 1 through 15 (of 25 total)

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