ScottPletcher (12/12/2013)
1) Maybe you can combine the three separate UPDATEs into one, as shown below, avoiding repeated joins of the same tables.2) Depending on the row counts, you might consider creating a filtered index on IBC.ProcessedFlag, including at least InstanceId and SiteId (if they are the clustering keys, of course you won't need to explicitly INCLUDE them, although it doesn't hurt anything to do so).
3) Beyond just this query, if you most often access the listed tables by InstanceId and SiteId, then if possible cluster the tables that way. That should speed up all your processing.
I don't know enough about the data distributions to know whether it should be ( InstanceId, SiteId ) or ( SiteId, InstanceId ), but you can determine that. If the third column is consistently specified in the WHERE, you could specify it in the clus key as well if you want; that is: ( key1, key2, LocalBuildingId | LocalEmployeeId | NoteCategoryCode ), although that lengthens the key so much that it must be weighed carefully.
Update IBC
SET IBC.ProcessedFlag = 1,
IBC.ReturnCode = '2001',
IBC.EmailSentFlag = 0,
IBC.ProcessedDate = GETDATE()
FROM InBound_BuildingNote IBC WITH(NOLOCK)
LEFT JOIN Building B WITH(NOLOCK) ON B.InstanceId = IBC.InstanceId
AND B.SiteId = IBC.SiteId
AND B.LocalBuildingId = IBC.LocalBuildingId
LEFT JOIN Employee E WITH(NOLOCK) ON E.InstanceId = IBC.InstanceId
AND E.SiteId = IBC.SiteId
AND E.LocalEmployeeId = IBC.LocalEmployeeId
LEFT JOIN NoteCategory NC ON NC.NoteCategoryCode = IBC.NoteCategoryCode
AND NC.InstanceId = IBC.InstanceId
AND NC.SiteId = IBC.SiteId
WHERE (B.LocalBuildingId IS NULL OR E.LocalEmployeeId IS NULL OR NC.NoteCategoryCode IS NULL)
AND IBC.ProcessedFlag = 0
AND IBC.InstanceId = @InstanceID
AND IBC.SiteId = @SiteID
Thanks Scott for your suggestion, I will combime multiple update statements into a single update statement..
Below is my table script to review for index point of view if any changes is required:
/****** Object: Table [dbo].[Employee] Script Date: 12/13/2013 12:05:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[InstanceId] [nchar](3) NOT NULL,
[SiteId] [nchar](3) NOT NULL,
[LocalEmployeeId] [nvarchar](20) NOT NULL,
[LocalEmployeeNumber] [nvarchar](20) NOT NULL,
[OfficeId] [int] NOT NULL,
[Prefix] [nvarchar](10) NULL,
[FirstName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DisplayName] [nvarchar](125) NULL,
[WorkPhoneNumber] [nvarchar](25) NULL,
[CellPhoneNumber] [nvarchar](25) NULL,
[EmailAddress] [nvarchar](75) NULL,
[SupervisorId] [int] NULL,
[PersonTypeId] [int] NULL,
[DeviceID] [nvarchar](100) NULL,
[LocalUnionID] [nvarchar](20) NOT NULL,
[OT1RateMultiple] [decimal](2, 1) NOT NULL,
[OT2RateMultiple] [decimal](2, 1) NULL,
[OT3RateMultiple] [decimal](2, 1) NULL,
[OldTravelRate] [decimal](6, 4) NULL,
[CurrentTravelRate] [decimal](6, 4) NULL,
[CurrentRateStartDate] [datetime] NULL,
[TemporaryRateCode] [nchar](3) NULL,
[StatusFlag] [bit] NOT NULL,
[DateAdded] [datetime] NOT NULL,
[AddedBy] [nvarchar](75) NOT NULL,
[DateChanged] [datetime] NULL,
[ChangedBy] [nvarchar](75) NULL,
[LocalDateChanged] [datetime] NOT NULL,
[LocalChangedBy] [nvarchar](75) NOT NULL,
[SyncTimeStamp] [timestamp] NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [uq1_Employee] UNIQUE NONCLUSTERED
(
[InstanceId] ASC,
[SiteId] ASC,
[LocalEmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_EmployeeOfficeId] FOREIGN KEY([OfficeId])
REFERENCES [dbo].[Office] ([OfficeId])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_EmployeeOfficeId]
GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_EmployeeSupervisorId] FOREIGN KEY([SupervisorId])
REFERENCES [dbo].[Supervisor] ([SupervisorId])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_EmployeeSupervisorId]
GO
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/