Timeout Expired - VB-SLQ Server - Return Identity

[RollingMoose]

We're occasionally receiving a -2147217871 Timeout expired message when running a VB application that inserts and updates a SQL Server database. Most times the application runs and performs the inserts and updates in less than 5 seconds. But every now and then the updates and inserts don't occur and we get the timeout expired message. I added a message to an errorhandler to return the procedure or function involved when the message is generated. This is the stored procedure that is being called, it's returning an identity value to be used in other procedures in the application. The VB in the application closes the connection after this procedure is called using a command object.
-------------------------------
CREATE PROCEDURE [dbo].[msp_Form900]
@LTA varchar(10),
@Year varchar(10),
@Recd datetime,
@Type varchar(10),
@CWOPA varchar(10),
@Comm varchar(10),
@ident int OUTPUT
AS
DECLARE @ver tinyint,
@accept char(1),
@house char(1),
SET NOCOUNT ON
SET @accept='N'
SET @house='Y'
IF (SELECT Max(Version) FROM [900_Forms] WHERE LTA_ID=@LTA AND Tax_Year=@Year) IS NULL
BEGIN
SET @ver=1
END
ELSE
BEGIN
SELECT @ver=(SELECT Max(Version) FROM [900_Forms] WHERE LTA_ID=@LTA AND Tax_Year=@Year)
SET @ver=@ver+1
END
INSERT INTO [900_Forms] (Tax_Year, LTA_ID,Date_Received,LTA_Type,Accept_Computed,Version,In_House,CWOPA_ID,Comments)
VALUES(@Year,@LTA,@Recd,@Type,@accept,@ver,@house,@CWOPA,@Comm)
SET @ident=@@identity
GO
-----------------------------
Any suggestions are appreciated!!

 

[gmmastros]

Looking at this stored procedure, I don't notice anything that should cause this, under most conditions, to take even 5 seconds. There is room for improvement. The way you get your @Ver variable could be better.

CREATE PROCEDURE [dbo].[msp_Form900]
@LTA varchar(10),
@Year varchar(10),
@Recd datetime,
@Type varchar(10),
@CWOPA varchar(10),
@Comm varchar(10),
@ident int OUTPUT
AS
DECLARE @ver tinyint,
@accept char(1),    
@house char(1),        
SET NOCOUNT ON
SET @accept='N'
SET @house='Y'
SET @Ver = 1 + IsNull(SELECT Max(Version) FROM [900_Forms] WHERE LTA_ID=@LTA AND Tax_Year=@Year, 0)

INSERT INTO [900_Forms] (Tax_Year, LTA_ID,Date_Received,LTA_Type,Accept_Computed,Version,In_House,CWOPA_ID,Comments)
VALUES(@Year,@LTA,@Recd,@Type,@accept,@ver,@house,@CWOPA,@Comm)
SET @ident=@@identity
GO

Even still, the way you had it shoudl be very fast unless you have millions upon millions of records.

I suspect the 'slowness' is caused by indexes. Indexes are great because they can improve the performance of selects ALOT. If you have too many indexes on a table, it will slow performance of insert, update, and delete. Those 3 actions cause the index to get updated, which can account for the performance problems.

I recommend you look at the indexes and determine if you can remove any.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

When that happens again... can you go to QA, select troubled database then run this:

create table #tmp
(	spid smallint,
	dbid smallint,
	ObjId int,
	IndId smallint,
	Type nchar(4),
	Resource nchar(16),
	Mode nvarchar(8),
	Status nvarchar(5)
)
insert into #tmp exec sp_lock
select * from #tmp where dbid=db_id()
drop table #tmp

... and post results here?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]