-
Feb 28th, 2018, 11:09 AM
#1
Index Opinion
Code:
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
Code:
update EOrderReminderConsent
set [RetryStatus] = [RetryStatus] + 1
where BookingId = 'WKMZLQ9'
and Email ='avhill@ge.com.gr'
Code:
INSERT INTO [dbo].[EOrderReminderConsent]
([BookingId]
,[BookingNumber]
,[ShowTime]
,[Email])
VALUES
(@BookingId
,@BookingNumber
,@ShowTime
,@Email)
Table:
Code:
/****** 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
Code:
/****** 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
Code:
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:
Code:
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.
Last edited by sapator; Mar 1st, 2018 at 03:42 AM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Feb 28th, 2018, 01:54 PM
#2
Re: Index Opinion
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
-
Mar 1st, 2018, 03:43 AM
#3
Re: Index Opinion
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 1st, 2018, 10:59 AM
#4
Re: Index Opinion
So I have not used an email index but I have used a non cluster index on bookingNumber.
Now when I do this:
Code:
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:
Code:
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.
Last edited by sapator; Mar 1st, 2018 at 11:03 AM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|