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

END

What's the schemas of the 3 tables T1,T2 and View1? I tried a test script using your statements, the query went fine and no deadlock occurred.

Q1. Do not put index on low selective column (as you said only 2 distinct values), because it dosen't make sense. Here are some guidelines for choosing columns to index from "Programming a Microsoft? SQL Server? 2000 Database":

Indexes are useful, but they consume disk space and incur overhead and maintenance costs. Consider the following facts and guidelines about indexes:

When you modify data on an indexed column, SQL Server updates the associated indexes.
|||

Thanks for your help, can you let me know how I can make this transaction low isolation level? I am quite new in the transaction isolation level.

|||

Simply use the SET TRANSACTION ISOLATION LEVEL command to set the session option, you can refer to this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp