Post reply

Query optimizer : bad plan for simple select

  • March 4, 2020 at 7:39 am

    #3730776

    Hello Everyone,

    I have issue with query optimizer in MSSQL 2017 (CU19,Developer edition,DB CompatibilityLevel=140).

    When I want to select data from child table (joined by unique index) I get wrong EstimatedNumberOfRows and in real world scenario with much more tables/joins/data it leads to very long execution times with ugly plan. Mentioned index is filtered againts NULL values.

    Scenario with data sample: Each Child must have only and exactly one Root (check constraint). Each Root can have maximaly one Child (unique index).

    Code and actual query plan screenshot is below. Any ideas?

    Thanks in advance

    Vojta

    -- CREATE STRUCTURE (part 1)
    CREATE TABLE RootTable
    (
    Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    SomeColumn INT NOT NULL
    )

    CREATE TABLE ChildTable
    (
    Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    RootId1 UNIQUEIDENTIFIER NULL,
    RootId2 UNIQUEIDENTIFIER NULL,
    SomeValue INT NOT NULL
    )

    GO
    SET NOCOUNT ON
    -- NEED SOME DATA
    DECLARE @id UNIQUEIDENTIFIER
    DECLARE @i INT = 0
    WHILE @i < 1000000 --milion rows
    BEGIN

    SELECT @id = NEWID();

    INSERT INTO RootTable(Id,SomeColumn)
    SELECT @id, @i%100000 --aprox 10 row for each value

    IF(@i%2=0)
    INSERT INTO ChildTable (Id, RootId1, RootId2, SomeValue) VALUES (NEWID(), @id, NULL, @i);
    ELSE
    INSERT INTO ChildTable (Id, RootId1, RootId2, SomeValue) VALUES (NEWID(), NULL, @id, @i);

    SELECT @i = @i +1
    END
    SET NOCOUNT OFF

    GO
    -- CREATE STRUCTURE (part 2)
    CREATE INDEX IDX_SomeColumn ON RootTable(SomeColumn)
    CREATE UNIQUE INDEX IDX_RootId1 ON ChildTable(RootId1) WHERE RootId1 IS NOT NULL
    CREATE UNIQUE INDEX IDX_RootId2 ON ChildTable(RootId2) WHERE RootId2 IS NOT NULL
    ALTER TABLE ChildTable WITH CHECK ADD CONSTRAINT OnlyOneRootIdIsFilled CHECK ((RootId1 IS NOT NULL AND RootId2 IS NULL OR RootId2 IS NOT NULL AND RootId1 IS NULL))
    ALTER TABLE ChildTable CHECK CONSTRAINT OnlyOneRootIdIsFilled

    GO
    -- FRESH STATS
    UPDATE STATISTICS RootTable WITH FULLSCAN
    UPDATE STATISTICS ChildTable WITH FULLSCAN

    GO
    -- JUST FOR SURE
    DBCC FREEPROCCACHE

    GO
    -- FINALY READ DATA
    SELECT *
    FROM RootTable t
    LEFT JOIN ChildTable c1 ON c1.RootId1=t.Id
    LEFT JOIN ChildTable c2 ON c2.RootId2=t.Id
    WHERE T.SomeColumn=4641
    Attachments:
    You must be logged in to view attached files.

  • March 4, 2020 at 7:43 am

    #3730779

    EDIT : MSSQL2019 Developer acts the same 🙁

  • March 4, 2020 at 11:43 am

    #3730813

    Nice blogpost about this is here : https://sqlperformance.com/2013/04/t-sql-queries/optimizer-limitations-with-filtered-indexes

    ...Filtered unique indexes do not provide uniqueness information to the optimizer...

    I have no idea if this kind of limitation will change in future. Anybody does?

     

  • March 25, 2020 at 12:29 pm

    #3736854

    wrote:

    Tip for when you 'walk the second range', take the stories.id from your first query (i.e. add it to the result), and then in the second query add a criteria stories.id > {prev_max}. This is more efficient than OFFSET clauses.

    This is the second post in a row of absolute gibberish (and you only have 2 posts at this point).  Either you've posted on the wrong thread  or you're fishing to setup for spam.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 4 (of 4 total)

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