Inheritance and performance in database [closed]

Posted on

Question :

I have 3 table linked by an “inheritance” with Entity Framework.
Sadly, I have poor performance with those query because I can’t make any good index.
Here is the tables :

CREATE TABLE [dbo].[PlannedElements](
    [IdPlannedElement] [int] IDENTITY(1,1) NOT NULL,
    [ActualDate] [datetime] NULL,
    [Order] [int] NOT NULL,
    [PlannedDate] [datetime] NULL,
    [TimeSpan] [time](7) NULL,
    [IsCompleted] [bit] NOT NULL,
    [PlanningLineIdPlanningLine] [int] NULL,
    [AddressIdAddress] [int] NULL,
    [CrewIdCrew] [int] NULL,
    [PlannedElementIdPlannedElement] [int] NULL,
    [SuppressionDate] [datetime] NULL,
    [LastUpdateDate] [datetime] NULL,
    [IsActualDateEstimated] [bit] NOT NULL,
    [TrackerIdTracker] [int] NULL,
    [HospitalRoom] [nvarchar](4000) NULL,
 CONSTRAINT [PK_PlannedElements] PRIMARY KEY CLUSTERED 
(
    [IdPlannedElement] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[PlannedElements_PlannedFleetElement](
    [IdPlannedElement] [int] NOT NULL,
    [TypeOfFleetElement] [int] NOT NULL,
 CONSTRAINT [PK_PlannedElements_PlannedFleetEl] PRIMARY KEY CLUSTERED 
(
    [IdPlannedElement] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[PlannedElements_PlannedBusinessFleetElement](
    [IdPlannedElement] [int] NOT NULL,
    [TypeOfBusinessFleetElement] [int] NOT NULL,
    [JourneyIdJourney] [int] NULL,
    [IsCertified] [bit] NOT NULL,
 CONSTRAINT [PK_PlannedElements_PlannedBusines] PRIMARY KEY CLUSTERED 
(
    [IdPlannedElement] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I would need an index on JourneyIdJourney, AddressIdAddress but when I use a materialized view SQL Server is never using it;
Do you have any recommendations ?

Thanks !

Answer :

Paul White explains in his answer to the question below amongst other things that automatic indexed view matching won’t even be considered if a low enough plan is found first.

What factors go into an Indexed View’s Clustered Index being selected?

also
Consider having a look at what statistics you have in each table
and keeping them updated

and
when using indexed views WITH(NOEXPAND) query hint

From Query Hints (Transact SQL) on MSDN:

The indexed view is not expanded only if the view is directly
referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH
(NOEXPAND, INDEX( index_value [ ,…n ] ) ) is specified.

Leave a Reply

Your email address will not be published. Required fields are marked *