dcsimg
Results 1 to 4 of 4

Thread: Index Opinion

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,322

    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.
    Slow as hell.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,322

    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.
    Slow as hell.

  4. #4

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,322

    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.

    Name:  Clipboard01.jpg
Views: 24
Size:  12.0 KB

    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.
    Slow as hell.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width