how to improve this transaction

Hello,

I have four different transactions such as below and I do one insert and one update in each transaction and it seem it is slow and creates deadlock with the user interface.

These transactions are performed against the tables that users are accessing with another user interface. I have following two questions:

1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did not put index since only two distinct values. Should I put indexes on these fields?

2. Can I make this transaction let user interface do its task in case accessing the same rows, I can start transaction again but I do not want users get disturbed?

.

BEGIN TRANSACTION pTrans

BEGIN

INSERT INTO T1

(fields)

SELECT(fields)

FROMT2 INNER JOIN View1 ON T2.TrID = View1.MyTableID

WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)

UPDATET2

SETTextField2 = 'Ok',TextField2Date=@.MyRunDateTime

FROMT2

WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)

IF @.@.ERROR <> 0

BEGIN

rollback transaction pTrans

return(-1)

END

ELSE

BEGIN

commit transaction pTrans

END

END

i think youre just maintaing the data on T2

based from the inserted values on T1.

how about just doing the insert on T1

and placing the update codes on

an "after insert trigger" in t1.

a transaction may not be necessary in this case

the previous design will cause a lot of deadlock in the system

|||

Jim,

You should consider defining indexes on fields listed in "where" clause.
If the problem persists after that, then more detailed examination will be needed.

Indeed, you can invoke asynchronous calls to SQL Server. You can use separate thread to handle the calls to SQL, or you can use built-in support for asynchronous SQL calls. For example, look at SqlCommand.BeginExecuteNonQuery Method at MSDN.