April 26, 2015 at 11:40 pm
I am having a table with around 60000 rows.
When updating, it takes long time to finish.
My table design is as below.
CREATE TABLE [dbo].[Entry_Jangad_Lot](
[Srno] [bigint] IDENTITY(1,1) NOT NULL,
[Kapan] [nvarchar](50) NOT NULL,
[Lotno] [int] NOT NULL,
[Quality] [nvarchar](50) NOT NULL,
[Pcs] [int] NOT NULL,
[Caret] [decimal](18, 2) NOT NULL,
[JangadNo] [int] NOT NULL,
[IssuedTo] [nvarchar](50) NULL,
[IssuedDate] [nvarchar](50) NULL,
[Status] [nvarchar](50) NULL,
[ReceivedPcs] [int] NULL,
[ReceivedCaret] [decimal](18, 2) NULL,
[ReceivedDate] [nvarchar](50) NULL,
[ReceivedType] [nvarchar](50) NULL,
[Position] [nvarchar](50) NULL,
[Process] [nvarchar](50) NULL,
[MergedStatus] [nvarchar](50) NULL,
[MergedToLotno] [int] NULL,
CONSTRAINT [PK_Entry_Jangad_Lot_1] PRIMARY KEY CLUSTERED
(
[Srno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I am using following Procedure to update a table record.
CREATE procedure [dbo].[sp_add_update_lot_receive]
(
@mode nvarchar(50),
@Kapan nvarchar(50),
@Lotno int,
@ReceivedPcs int,
@ReceivedCaret decimal(18,2),
@ReceivedDate nvarchar(50),
@ReceivedType nvarchar(50)
)
as
if @mode='ADD'
begin
declare @srno int
select @srno=srno from Entry_Jangad_Lot where Kapan=@Kapan
and Lotno=@Lotno
and Position='CURRENT'
update Entry_Jangad_Lot set
ReceivedPcs=@ReceivedPcs,
ReceivedCaret=@ReceivedCaret,
ReceivedDate=@ReceivedDate,
ReceivedType=@ReceivedType,
Status='RECEIVED'
where srno=@srno
--where Kapan=@Kapan
--and Lotno=@Lotno
--and Position='CURRENT'
if @ReceivedType='POLISH'
begin
update Entry_Jangad_Lot set
Process='POLISH'
where srno=@srno
--where Kapan=@Kapan
--and Lotno=@Lotno
--and Position='CURRENT'
end
else
begin
if @ReceivedType='TABLE'
begin
update Entry_Jangad_Lot set
Process='TABLE'
where srno=@srno
--where Kapan=@Kapan
--and Lotno=@Lotno
--and Position='CURRENT'
end
else
begin
update Entry_Jangad_Lot set
Process='NOTPOLISH'
where srno=@srno
--where Kapan=@Kapan
--and Lotno=@Lotno
--and Position='CURRENT'
end
end
end
GO
April 26, 2015 at 11:52 pm
Are you updating those 60K rows one by one?
If so than it's expected to be slow. You have to run a set-based update query.
Igor Micev,My blog: www.igormicev.com
April 26, 2015 at 11:54 pm
No
I am not updating all rows.
I am just updating a single row if where criteria matches with column 'kapan' and 'lotno'.
April 27, 2015 at 12:03 am
Ok,
If you create an index on Lotno,Kapan and Position it should improve the selection in the where condition.
where Lotno=@Lotno
and Kapan=@Kapan
and Position='CURRENT'
Ensure Lotno is the first key column in the index:
create non-clustered index IX_Entry_Jangad_Lot_index1 on Entry_Jangad_Lot(Lotno,Kapan,Position)
Igor Micev,My blog: www.igormicev.com
April 27, 2015 at 12:11 am
As an aside, storing dates as nvarchar(50) is generally considered a really bad idea.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 27, 2015 at 1:33 am
Still no improvements.
April 27, 2015 at 1:37 am
Igor Micev (4/27/2015)
Ok,If you create an index on Lotno,Kapan and Position it should improve the selection in the where condition.
where Lotno=@Lotno
and Kapan=@Kapan
and Position='CURRENT'
Ensure Lotno is the first key column in the index:
create non-clustered index IX_Entry_Jangad_Lot_index1 on Entry_Jangad_Lot(Lotno,Kapan,Position)
No improvements after this also.
April 27, 2015 at 1:57 am
hemal_301080 (4/27/2015)
Igor Micev (4/27/2015)
Ok,If you create an index on Lotno,Kapan and Position it should improve the selection in the where condition.
where Lotno=@Lotno
and Kapan=@Kapan
and Position='CURRENT'
Ensure Lotno is the first key column in the index:
create non-clustered index IX_Entry_Jangad_Lot_index1 on Entry_Jangad_Lot(Lotno,Kapan,Position)No improvements after this also.
write the query properly first:
CREATE procedure [dbo].[sp_add_update_lot_receive]
(
@mode nvarchar(50),
@Kapan nvarchar(50),
@Lotno int,
@ReceivedPcs int,
@ReceivedCaret decimal(18,2),
@ReceivedDate nvarchar(50),
@ReceivedType nvarchar(50)
)
AS
IF @mode = 'ADD' -- shouldn't this be 'UPDATE'?
UPDATE Entry_Jangad_Lot SET
ReceivedPcs = @ReceivedPcs,
ReceivedCaret = @ReceivedCaret,
ReceivedDate = @ReceivedDate,
ReceivedType = @ReceivedType,
[Status] = 'RECEIVED',
Process = CASE WHEN @ReceivedType = 'POLISH' THEN 'POLISH' WHEN @ReceivedType = 'TABLE' THEN 'TABLE' ELSE 'NOTPOLISH' END
WHERE Kapan = @Kapan
AND Lotno = @Lotno
AND Position = 'CURRENT'
GO
Check the performance, if it's still unacceptably slow then post the actual execution plan.
As Phil has already pointed out, your datatypes are awful. Is there anything you can do about them?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 27, 2015 at 3:25 am
You need to look to what the execution plan is telling you for how it's going to resolve this query. Your indexes are not being used or the statistics are out of date possibly.
One concern I have is that you have a number of different UPDATE statements all within a single procedure. When this procedure is called, regardless of the parameters passed to it, all these statements will be compiled based on the parameters passed. This may be causing some of the plans to be created based on bad data. I would suggest treating an IF/ELSE construct like this as a wrapper procedure. Have each of your UPDATE statements in a different procedure. That way, each one gets its own unique plan when it gets called, not all at once.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2015 at 4:32 am
ChrisM@Work (4/27/2015)
hemal_301080 (4/27/2015)
Igor Micev (4/27/2015)
Ok,If you create an index on Lotno,Kapan and Position it should improve the selection in the where condition.
where Lotno=@Lotno
and Kapan=@Kapan
and Position='CURRENT'
Ensure Lotno is the first key column in the index:
create non-clustered index IX_Entry_Jangad_Lot_index1 on Entry_Jangad_Lot(Lotno,Kapan,Position)No improvements after this also.
write the query properly first:
CREATE procedure [dbo].[sp_add_update_lot_receive](
@mode nvarchar(50),
@Kapan nvarchar(50),
@Lotno int,
@ReceivedPcs int,
@ReceivedCaret decimal(18,2),
@ReceivedDate nvarchar(50),
@ReceivedType nvarchar(50)
)
AS
IF @mode = 'ADD' -- shouldn't this be 'UPDATE'?
UPDATE Entry_Jangad_Lot SET
ReceivedPcs = @ReceivedPcs,
ReceivedCaret = @ReceivedCaret,
ReceivedDate = @ReceivedDate,
ReceivedType = @ReceivedType,
[Status] = 'RECEIVED',
Process = CASE WHEN @ReceivedType = 'POLISH' THEN 'POLISH' WHEN @ReceivedType = 'TABLE' THEN 'TABLE' ELSE 'NOTPOLISH' END
WHERE Kapan = @Kapan
AND Lotno = @Lotno
AND Position = 'CURRENT'
GO
Check the performance, if it's still unacceptably slow then post the actual execution plan.
As Phil has already pointed out, your datatypes are awful. Is there anything you can do about them?
Agreed with you and I shortened the procedure.
April 27, 2015 at 4:33 am
Grant Fritchey (4/27/2015)
You need to look to what the execution plan is telling you for how it's going to resolve this query. Your indexes are not being used or the statistics are out of date possibly.One concern I have is that you have a number of different UPDATE statements all within a single procedure. When this procedure is called, regardless of the parameters passed to it, all these statements will be compiled based on the parameters passed. This may be causing some of the plans to be created based on bad data. I would suggest treating an IF/ELSE construct like this as a wrapper procedure. Have each of your UPDATE statements in a different procedure. That way, each one gets its own unique plan when it gets called, not all at once.
Yes you are correct and I have shortened the query as Chris said.
April 27, 2015 at 8:18 am
MANY things could be at fault here:
1) triggers doing bad stuff
2) constraints doing bad stuff
3) blocking by other activity (I go with this by default for a 60K row tale without any other information)
I note you never actually said how long each update was taking nor how long you expect it to take. What is the hardware? Is it virtualized?
Assuming one update is running for multiple seconds at least you can use sp_whoisactive (awesome freebie from sqlblog.com) to determine what is happening during execution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 27, 2015 at 1:45 pm
You can use the NOLOCK hint for srno without worries because u're reading an identity value.
Igor Micev,My blog: www.igormicev.com
April 27, 2015 at 2:35 pm
Igor Micev (4/27/2015)
You can use the NOLOCK hint for srno without worries because u're reading an identity value.
Can you please explain that statement Igor??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 27, 2015 at 4:03 pm
TheSQLGuru (4/27/2015)
Igor Micev (4/27/2015)
You can use the NOLOCK hint for srno without worries because u're reading an identity value.Can you please explain that statement Igor??
Yeah. I'm unclear on it too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply