Should I use an index on this?

21

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.

22

If you will lookup by Bookingnumber then, yes, add an index on Bookingnumber.

23

I didn't say "never". I said every table should be individually analyzed to determine that specific table's best clustering key. That is, there should never be a "default" clustering key of identity. But I've seen it done, all the time, on literally ALL tables. It's often the first column added to a table.

I can't imagine how the SELECT would prevent an insert from happening. I've been a full-time DBA for 30 years and I'm not familiar with that one. I suppose one can construct a scenario where if you combined the SELECT with some type of modification in the same transaction you could create a deadlock, but that's easy enough to correct with proper coding technique.

24

Hi.
I was mistakenly using an index on bookingId and not booking number.
So now I get an index seek on the update :slight_smile:

25

Yes but if i use an email.
So:

 update EOrderReminderConsent 
  set [RetryStatus] = 1
  where BookingNumber = 1231231
and email="123@123.com"

I get an index scan.

So I should still avoid the email index?
Thanks

26

I am thinking of another scenario.

I'm thinking I do not actually need the email to update as I can get the id on the first select.
So I can do a an update on the where id=X and since it is unique I can get an index seek.
Is that optimal?
Thanks.

27

Yes. Specify the clus key column(s) whenever possible to do so, for any statement.

28

Hello.
Another question if you may.

I inserted a datetime column (rebuild the indexes)
so I try something like this:

  Select bookingNumber
  from EOrderReminderConsent
 where ShowTime <= DATEADD(MINUTE, 45, GETDATE())

I get an index scan, even if I use where ShowTime <= DATEADD(MINUTE, 45, GETDATE()) and BookingDate > getdate() -1 , I get an index scan.

The new index is nonclustered on BookingDate. Is this correct in theory? the index.
Why do I get issues? I have rebuilded the index and the execution plans in case there where any caching issues.Is the data volume plays a role?

This is educational as we decided to remove the column and the index but I did not expect to see index scans.
I also tried to use the index inside the clustered index (id,showtime,BookingDate) but still index scan.

Thanks.

29

If the index is on BookingDate, then ShowTime would not apply to that index.

30

Hi.

I have one index non clustered on BookingDate
also i have a UNIQUE CLUSTERED INDEX on showtime and id .
Using or not using BookingDate on where, should not make a difference, should it?
Thanks.

31

You're getting an index "scan" because it's reading more than one row, but there should still be a SEEK predicate of the max time.

32

Should be seek but is using a scan?
Or you mean something else.

Again this is out of curiosity as we are no longer use the BookingDate and now everything works fine.

So is the index I have set (before I remove the BookingDate column) correct, given the specific Select Statement?
If they seem OK then maybe I was doing something wrong that I can no longer reproduce.
But I just need to know if the indexes seemed fine.

Thanks.

33

A seek followed by a (forward) scan, which is what you want to see, that the SEEK predicate is working.

34

Hi.
I have inserted the datetime for test(dtm) and I do not get a cluster seek with the following query:

  Select bookingNumber
  from EOrderReminderConsent
 where  ShowTime <= DATEADD(MINUTE, 145, GETDATE()) 

 and dtm >= '20180113' or dtm is null

Clipboard01
Clipboard01.jpg644×689 248 KB

Can this happen if the data is a few lines and there is no need for index paging? In my case the test env had 82 lines

Here are the table and indexes:

Table:

CREATE TABLE [dbo].[EOrderReminderConsent](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BookingId] [nvarchar](50) NOT NULL,
	[BookingNumber] [int] NOT NULL CONSTRAINT [DF_EOrderReminderConsent_BookingNumber]  DEFAULT ((0)),
	[ShowTime] [datetime] NOT NULL,
	[Email] [nvarchar](150) NOT NULL,
	[EmailSend] [bit] NOT NULL CONSTRAINT [DF_EOrderReminderConsent_EmailSend]  DEFAULT ((0)),
	[RetryStatus] [smallint] NULL CONSTRAINT [DF_EOrderReminderConsent_RetryStatus]  DEFAULT ((0)),
	[dtm] [datetime] NULL,
 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]

Indexes:

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]

---

CREATE NONCLUSTERED INDEX [NCL_BookingNumber] ON [dbo].[EOrderReminderConsent]
(
	[BookingNumber] 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]

---
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]

--- The test dtm index
CREATE NONCLUSTERED INDEX [dtmNonClusteredIndex-20180315-110758] ON [dbo].[EOrderReminderConsent]
(
	[dtm] 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]
35

Hello again.
From what I see is, if I include columns that are used in an index (id,showtime,bookingNumber) i get an index scan. If I use columns that are not used in an index (for example email,bookingid) I get an index seek.

36

You didn't properly restrict the OR condition, presuming you want the date restriction to always apply:

WHERE ShowTime <= DATEADD(MINUTE, 145, GETDATE())
AND ( dtm >= '20180113' OR dtm IS NULL )

37

Hi.

These are just tests. I did not restrict as I wanted to see at the time if more data will force an index seek.
But the question is about the correct index structure. If you can comment on that, would be appreciated.
Thanks.

38

Properly restricting the conditions on dtm should allow an index seek, that is the point.

39

OK.
So we can say that the previous "OR" was not sargable so it did not allow correct indexing?
Thanks.

40

It's sargable. It's just that ANDs occur before ORs. So writing this:

where ShowTime <= DATEADD(MINUTE, 145, GETDATE())
and dtm >= '20180113' or dtm is null

Is the same as:
( ShowTime <= DATEADD(MINUTE, 145, GETDATE()) and dtm >= '20180113')
OR dtm is null

That is, if "dtm IS NULL", that row is selected, regardless of the ShowTime value. Therefore, SQL had to scan the table to find all possible rows.

When you code it the other way, SQL knows the ShowTime must always match, so can do a seek for it first.

1 Like