0

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 !

  • Can you provide query examples? – Nic Nov 30 '16 at 15:13
  • I think you need to post the definition of your materialized view before we can further discuss why an index is used or not.. – jyao Nov 30 '16 at 23:29
1

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.