May 17, 2015 at 11:10 am
Dears,
i am stuck with an issue on our database servers, we are doing synchronization services that updates some rows in different tables, i am doing a bulk update for updating rows and bulk insert for inserting rows each are called separability.
1st table WeightExercise have around 50K Row
2nd table WeightSet 90K row
There is a FK in WeightSet to WeightExercise
here is the scripts for the tables
CREATE TABLE [dbo].[FY_WeightSet](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SetID] [varchar](15) NOT NULL,
[Weight] [float] NULL,
[Repetitions] [int] NULL,
[Duration] [nvarchar](10) NULL,
[RestTime] [int] NULL,
[WeightExerciseID] [varchar](15) NULL,
[IsCompleted] [bit] NULL,
[IsDeleted] [bit] NULL,
CONSTRAINT [PK_FY_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_FY_WeightSet] UNIQUE NONCLUSTERED
(
[SetID] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[FY_WeightSet] ADD CONSTRAINT [DF_FY_WeightSet_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]
GO
Building my update script at once and sending it to SQL server. however after tracing the process it taking 3458 ms for each batch to update and 8 seconds for the whole process, please let me know if someone could help, really appreciated, i will attach the trace log too.
May 21, 2015 at 2:24 am
Any help would be appreciated, i can provide more information if necessary, however what i have noticed even when i do select query it takes 3 seconds for 161k row is this normal ?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply