August 6, 2009 at 3:33 am
Hi,
I'm using a middle ware tool to update 500 records in sql server 2005 using stored procedure.
I'm not commiting any individual rows becuase if there in any problem with my updates I should roll back the the entier updates.
And finally after sucessful updates of 500 records I'm doing a commit.
But it is taking long time and the tables are getting locked.
For 20 records it takes 3 mins to updates which has 8 coulumns.
Regards,
Abdul
August 6, 2009 at 4:36 am
Can you post at least the query? Almost impossible to give useful advice on performance without seeing code. Also the exec plan, table definitions and index definitions. Please take a look at http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2009 at 5:26 am
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE *******
@BU_Code varchar(10),
@vendor_Code varchar(10),
@vendor_Tpid varchar(40),
@vendor_Name nvarchar(250),
@acc_Code varchar(40),
@vendor_EmailId varchar(250),
@vendor_ContactName varchar(250),
@vendor_ContactPhone varchar(250)
AS
declare @error_code int
set @error_code = 0
BEGIN TRAN
Update table1
set BUCode = @BU_Code,
AccountCode = @vendor_Code,
AccountName = @vendor_Name,
ContactName = @vendor_ContactName,
ContactPhone = @vendor_ContactPhone,
emailid = @vendor_EmailId
where BUCode = @BU_Code and AccountCode = @vendor_Code
if @@error 0
begin
ROLLBACK TRANSACTION
RETURN(1)
end
Update table2
set BUCode = @BU_Code,
ID = @vendor_Code,
UserName = @acc_Code,
TPID = @vendor_Tpid
where BUCode = @BU_Code and ID = @vendor_Code
if @@error 0
begin
ROLLBACK TRANSACTION
RETURN(1)
end
if @error_code = 0
begin
((((( COMMIT TRANSACTION))))))))
RETURN(0)
end
else
begin
ROLLBACK TRANSACTION
RETURN(2)
end
The above is the stored procedure I'm using. If I include the commit statement all the records are updated in less an a min. But if I dont give a commit and try to do a mass commit (After updating all the records) through my middle ware it takes ages.
4 fields are indexed in table1 and 1 field is indexed in table2.
August 6, 2009 at 5:42 am
Did you read the article that I references?
I don't see table definitions, I don't see index definitions and there's no execution plan.
Also, have you checked to see if the proc's getting blocked? Look at sys.dm_exec_requests and check for blocking processes or high wait times
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2009 at 3:46 am
Hi Gail,
Please find below the details
CREATE TABLE table1(
[TPType] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [****] DEFAULT ('vvvvv'),
[BUCode] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[AccountCode] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
[AccountName] [varchar](250) COLLATE Latin1_General_CI_AS NOT NULL,
[emailId] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContactName] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContactPhone] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Integrated] [bit] NULL CONSTRAINT [****] DEFAULT ((0)),
[AllowInvoice] [bit] NULL CONSTRAINT [*****] DEFAULT ((0)),
[EnabledDate] [datetime] NOT NULL CONSTRAINT [******] DEFAULT (getdate()),
[isActive] [bit] NOT NULL CONSTRAINT [************] DEFAULT ((1)),
[RA_Active] [bit] NULL CONSTRAINT [*****] DEFAULT ((0)),
CONSTRAINT [*****] PRIMARY KEY CLUSTERED
(
[TPType] ASC,
[BUCode] ASC,
[AccountCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_AccountCode] ON table1
(
[AccountCode] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_BUCodeOnly] ON table1
(
[BUCode] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_TPDetails] ON table1
(
[BUCode] ASC,
[AccountCode] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
ALTER TABLE table1 ADD CONSTRAINT [*****] PRIMARY KEY CLUSTERED
(
[TPType] ASC,
[BUCode] ASC,
[AccountCode] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE TABLE table2(
[SystemID] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [*****] DEFAULT ('****'),
[BUCode] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[ID] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
[UserName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Password] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TPID] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[https] [bit] NOT NULL CONSTRAINT [*****] DEFAULT (0),
CONSTRAINT [****] PRIMARY KEY CLUSTERED
(
[SystemID] ASC,
[BUCode] ASC,
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE table2 ADD CONSTRAINT [****] PRIMARY KEY CLUSTERED
(
[SystemID] ASC,
[BUCode] ASC,
[ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
-------------------
Sorry Gail i could not share the execution plan.
Could you please advise what is the problem.
August 7, 2009 at 4:10 am
Hi Gail,
The thing I noted is
If I include the commit statement as part of the Stored procedure there is no issue at all.
But then the commit will be done for every record update. Which I dont want.
But when I dont use a commit statement and try to call a commit once all updates are done (i.e 250 records) the query is very slow
August 7, 2009 at 4:39 am
Is there blocking, are there waits? If so, what's the wait type?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2009 at 5:42 am
Hi Gail,
Sorry for asking this. I'm not a DBA but a middle ware engineer. Please let me know how I can find it and shall give the information to you.
Thanks
August 7, 2009 at 6:03 am
GilaMonster (8/6/2009)
Also, have you checked to see if the proc's getting blocked? Look at sys.dm_exec_requests and check for blocking processes or high wait times
Is there a DBA there who can help with this?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2009 at 10:54 am
what is your Isolation level ?
try reading Snapshot isolation level and try it.
it will might help you out.
Musab
http://www.sqlhelpline.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply