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.