I'm trying to improve the performance of a specific query that is automatically generated by Entity Framework. I have run the query through SSMS, and it has suggested creating two missing indexes. The table in question:
CREATE TABLE [dbo].[PackageEvents]
(
[EventID] [int] NOT NULL IDENTITY(1, 1),
[PackageID] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Notes] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsSynchronized] [bit] NOT NULL CONSTRAINT [DF_AmazonPackageEvents_IsSynchronized]
DEFAULT ((0)),
[LastSyncDate] [datetime] NULL,
[Version] [timestamp] NOT NULL
)
SSMS has suggested the following two indexes:
CREATE NONCLUSTERED INDEX [IX_IsSynchronized] ON [dbo].[PackageEvents]
([IsSynchronized]) INCLUDE ([PackageID])
CREATE NONCLUSTERED INDEX [IX_Covering] ON [dbo].[PackageEvents] ([PackageID])
INCLUDE ([EventDate], [EventDescription], [EventID], [IsSynchronized], [LastSyncDate],
[Notes], [UserName], [Version])
I'm not posting the query I'm optimizing for because it is horrendous (generated by Entity Framework) and is almost unreadable. In general the query is looking for any rows where IsSynchronized = 0 and returning those rows.
Is there a way to combine these two indexes into one index that would provide the same or better performance benefit? Is this question impossible to answer without the exact query?
EDIT: The only existing index is the clustered index on the primary key, EventID.
I searched through the generated Entity Framework query. The following where clause occurs 4 times, but always in the same form:
WHERE EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AmazonPackageEvents] AS [Extent46]
WHERE ([Project30].[PackageID] = [Extent46].[PackageID]) AND
(0 = [Extent46].[IsSynchronized])
)
Which seems to boil down to
WHERE PackageID=@PackageID AND IsSynchronized=0
SELECT PE.PackageID FROM dbo.PackageEvents PE WHERE PE.IsSynchronized = 1
SELECT PE.EventDate FROM dbo.PackageEvents PE WHERE PE.IsSynchronized = 1 and PE.PackageId = 'x'` but I don't know whether your going to be able to always know the available IsSynchronized values for idx (IsSyn, PkgId). Suppose since it's binary, could UNION ALL the 1 & 0 version