q; 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)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>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?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh