none
Why is the FK parent table read

    Question

  • You cannot vote on your own post                            
    0
                    

    SQL Server 2012 Enterprise. I have two tables with a FK relationship. The referencing column in the child table is NOT NULL. The FK is trusted. I create a view that joins the two tables with an INNER JOIN on the foreign key column and no filter. When I SELECT COUNT(*) from the view, it returns the count of rows in the child table (as expected), but looking at actual execution plan, it reads the parent table to do so. I see no reason to read from the parent table. The trusted FK on a NOT NULL column in the child table means that for every row in the child table there HAS to be a corresponding row in the parent table, so reading the parent table should be unnecessary. If I change the view's join to an OUTER JOIN, the parent table is not read with a SELECT COUNT(*) from the view. Is this a shortcoming in the optimizer (not really a bug), or am I missing something? Here's an example:

    USE tempdb;
    GO

    IF OBJECT_ID('dbo.vChild') IS NOT NULL DROP VIEW dbo.vChild;
    IF OBJECT_ID('dbo.Child') IS NOT NULL DROP TABLE dbo.Child;
    IF OBJECT_ID('dbo.Parent') IS NOT NULL DROP TABLE dbo.Parent;

    CREATE TABLE dbo.Parent (PKey int NOT NULL PRIMARY KEY,
     SomeCol varchar(10) NOT NULL);
    GO

    CREATE TABLE dbo.Child (PKey int NOT NULL,
     SeqNum int NOT NULL,
     CONSTRAINT ChildPK PRIMARY KEY (PKey, SeqNum));


    ALTER TABLE dbo.Child WITH CHECK ADD CONSTRAINT FK_Parent_Child FOREIGN KEY(PKey)
     REFERENCES dbo.Parent (PKey);
    GO

    select is_not_trusted, * from sys.foreign_keys
     WHERE [name] = 'FK_Parent_Child';
    GO

    CREATE VIEW dbo.vChild AS
    SELECT C.PKey, C.SeqNum, P.SomeCol
     FROM dbo.Child AS C
     JOIN dbo.Parent AS P
      ON C.PKey = P.PKey
    GO

    SELECT COUNT(*) FROM dbo.vChild;

    Thanks, Vern


    Vern Rabe

    Monday, January 20, 2014 9:22 PM

Answers

All replies

  • The View dont save Data so to calculate the Count(*) SQL Server execute the Query that build the view.

    So try to use the Index on the View like (Index1) and :

    Select Count(Index1) From yourView

    Monday, January 20, 2014 9:52 PM
  • I was unable to repro that on:

    Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

    The plan I got was:

    StmtText

    --------------------------------------------------------------------------------------

      |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))

           |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))

                |--Clustered Index Scan(OBJECT:([master].[dbo].[Child].[ChildPK] AS [C]))

    David



    David http://blogs.msdn.com/b/dbrowne/

    Monday, January 20, 2014 10:15 PM
  • I think you found it, David. I running Microsoft SQL Server 2012 - 11.0.2332.0 (X64) and I get this plan:

      |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
           |--Stream Aggregate(DEFINE:([Expr1007]=Count(*)))
                |--Nested Loops(Inner Join, OUTER REFERENCES:([C].[PKey]))
                     |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Child].[ChildPK] AS [C]))
                     |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Parent].[PK__Parent__5E190D18925BAE8C] AS [P]), SEEK:([P].[PKey]=[tempdb].[dbo].[Child].[PKey] as [C].[PKey]) ORDERED FORWARD)

    Apparently an RTM bug fixed in SP1. Time to upgrade.


    Vern Rabe

    Monday, January 20, 2014 10:46 PM
  • I got the same plan as OP

    Product Version is 11.0.3368.0 (x 64)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, January 20, 2014 10:47 PM
    Moderator
  • I got the same plan as you and I am running SP1 and CU4. I've installed CU4 because of another bug

    Confusing error message

    Here is my plan as a text:

    StmtText
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
           |--Stream Aggregate(DEFINE:([Expr1007]=Count(*)))
                |--Nested Loops(Inner Join, OUTER REFERENCES:([C].[PKey]))
                     |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Child].[ChildPK] AS [C]))
                     |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Parent].[PK__Parent__5E190D18084EC2A6] AS [P]), SEEK:([P].[PKey]=[tempdb].[dbo].[Child].[PKey] as [C].[PKey]) ORDERED FORWARD)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Monday, January 20, 2014 10:51 PM
    Moderator
  • Just to make sure we're on the same page, what is TempDB collation?

    SQL_Latin1_General_CP1_CI_AS


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, January 20, 2014 11:10 PM
    Moderator
  • SQL_Latin1_General_CP1_CI_AS

    Vern Rabe

    Monday, January 20, 2014 11:20 PM
  • I get the same results (nested loop) in Microsoft SQL Server 2014 (CTP2) - 12.0.1524.0 (X64)

    Vern Rabe

    Monday, January 20, 2014 11:28 PM
  • Yes me too.  And it does choose that plan even when it's materially more expensive than just scanning dbo.Child's PK. 

    You should open a Connect item for comment.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, January 21, 2014 3:56 PM