February 8, 2010 at 1:36 am
CREATE TABLE [dbo].[EDocs](
[ReviewID] [int] NOT NULL,
[DocID] [nvarchar](50) NOT NULL,
[PageDocID] [nvarchar](50) NOT NULL,
Copied bit null,
Comp bit null,
DateTime1 datetime null,
Audit int null,
CONSTRAINT [PK_EDocsOrEmail_Review_Page_Soft] PRIMARY KEY CLUSTERED
(
[ReviewID] ASC,
[DocID] ASC,
[PageDocID] 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
CREATE NONCLUSTERED INDEX [IndexForBate] ON [dbo].[EDocsOrEmail_Review_Page_Soft]
(
[DocID] ASC,
[ReviewID] 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 = OFF) ON [PRIMARY]
declare @Copied bit,@Comp bit,@DateTime datetime,@Audit int
set @Copied ='TRUE'
set @Comp ='TRUE'
set @DateTime =getdate()
set @Audit=1
following query works to slow ....................
Update Edocs
set Copied = , Comp = , DateTime1 = , Audit =
where ReviewID = 1 and DocID = 'true';
...........to improve performance i have created following index.....................
CREATE NONCLUSTERED INDEX [IndexForBate] ON [dbo].[EDocs]
([DocID] ASC,
[ReviewID] 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 = OFF) ON [PRIMARY]....................still slow.................
have any quick suggestion to improve this update statement..........i hv to update million record this ways through variables
February 8, 2010 at 1:38 am
hi please make note
EDocsOrEmail_Review_Page_Soft = edocs are the same table
please replace all reference relatd to them.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply