Index Opinion

[sapator]

Select email,showdate,ShowTime,EmailSend,RetryStatus
form EOrderReminderConsent
WhereDATEDIFF(mi,getdate(),ShowTime) <=  45 and DATEDIFF(mi,getdate(),ShowTime) >=0
and EmailSend = 0  and RetryStatus  < 300

  update EOrderReminderConsent 
  set [RetryStatus] = [RetryStatus] + 1 
  where BookingId = 'WKMZLQ9'
  and Email ='avhill@ge.com.gr'

INSERT INTO [dbo].[EOrderReminderConsent]

           ([BookingId]
         ,[BookingNumber]
           ,[ShowTime]
           ,[Email])
     VALUES
           (@BookingId
		   ,@BookingNumber
           ,@ShowTime
           ,@Email)

Table:

/****** Object:  Table [dbo].[EOrderReminderConsent]    Script Date: 28/2/2018 5:52:51 μμ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[EOrderReminderConsent](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BookingId] [nvarchar](50) NOT NULL,
	[BookingNumber] [int] NULL CONSTRAINT [DF_EOrderReminderConsent_BookingNumber]  DEFAULT ((0)),
	[ShowTime] [datetime] NOT NULL,
	[Email] [nvarchar](150) NULL,
	[EmailSend] [smallint] NULL CONSTRAINT [DF_EOrderReminderConsent_EmailSend]  DEFAULT ((0)),
	[RetryStatus] [smallint] NULL CONSTRAINT [DF_EOrderReminderConsent_RetryStatus]  DEFAULT ((0)),
 CONSTRAINT [PK_EOrderReminderConsent] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I have a table as above.
Id is not used but is included in index for uniqueness.

I will be using a ShowTime in order do get the emails.
I am using the below indexes:

UNIQUE CLUSTERED on Showtime and id

/****** Object:  Index [CL_EOrderReminderConsent]    Script Date: 28/2/2018 5:58:33 μμ ******/
CREATE UNIQUE CLUSTERED INDEX [CL_EOrderReminderConsent] ON [dbo].[EOrderReminderConsent]
(
	[ShowTime] ASC,
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Non Clustered on ID

USE [VSAPI]
GO

/****** Object:  Index [PK_EOrderReminderConsent]    Script Date: 28/2/2018 6:00:11 μμ ******/
ALTER TABLE [dbo].[EOrderReminderConsent] ADD  CONSTRAINT [PK_EOrderReminderConsent] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

I'm not sure about this one but I was thinking I need an index on email:

USE [VSAPI]
GO

/****** Object:  Index [NonClusteredIndex-20180228-174416]    Script Date: 28/2/2018 6:01:34 μμ ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180228-174416] ON [dbo].[EOrderReminderConsent]
(
	[Email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The table uses ShowTime , ShowTime is not unique and is summed to large groups. So we may have
Showtime email
2017-1-1 12:12 1@1.com
2017-1-1 12:12 2@2.com
2017-1-1 12:12 3@3.com
2017-1-1 15:12 1@1.com
2017-1-1 15:12 5@1.com
2017-1-1 15:12 6@1.com

Email is mostly unique(mostly but may appear again in a new showtime)

Are the above indexes seem OK?

More concerned about the email index.
Since the selects are used without an email where (but updates us it). Should I use it in an induced column?
If so, how would I go and use an index key (I need one), since I have used the session on another index.
Can I re used the session index on the email index(included column)?
Or should the email be used on the clustered index. But on that index(the main select) it is not on the where clause and a cluster index do not allow me included columns.

Any thought?

Thanks.

[techgnome]

If you're not using ID, ever, then it shouldn't be in the table, let alone part of any index. Ideally you should have a natural PKey on the table... a set of fields that make the row unique. It's not always possible. So you introduce surrogate keys, such as ID. But that's when you need to uniquely identify specific rows. It's not clear here if that's the intent or need. If you're going to be constantly dealing with sets of rows (which could be 1 or more rows) a PKey may not make sense.

The way I approach indexes is to look at what's used in joins and where clauses...

ShowTime
EmailSend
RetryStatus
BookingId
Email

EmailSend looks like a boolean and wouldn't benefit... RetryStatus doesn't look like it would have any real benefit either being a counter...

based on all that... I'd do this:
4 indexes:
non clustered index on BookingID
non clustered index on Email
non clustered index on ShowTime
covering index, clustered on ShowTime and Email

And create them in that order.

-tg

[sapator]

Hi.
I've read that email may be a very volatile column to index and may cause fragmentation.
Also I am only updating by email, never do a select.
Maybe I should avoid an index there?
Thanks.

[sapator]

So I have not used an email index but I have used a non cluster index on bookingNumber.
Now when I do this:

  Select email,ShowTime,EmailSend,RetryStatus--,DATEDIFF(mi,getdate(),ShowTime),DATEADD(MINUTE, 45, GETDATE()) 
  from EOrderReminderConsent
Where ShowTime <= DATEADD(MINUTE, 45, GETDATE()) AND
ShowTime >= GETDATE() and Email = 0 and RetryStatus < 2

I get an index seek, so it's fine.

When i do this:

  update EOrderReminderConsent 
  set [RetryStatus] = 1
  where BookingNumber = 1231231

I get a cluster index update and a cluster index scan.
I'm not sure if that is the expected behavior.I also used "and emal="xxx" but with o witout the email the execution plan is the same.



Please note that I only have a couple of rows in the table right now.