TSEQUAL & TIMESTAMP Lock too long the DB - SQL2000
Hi, I have a big question about this Function. Since I work in this company (two months ago) as DBA, I had to find the cause of the locks in the database. During this job, I found a query wich use TSEQUAL and takes too long to execute the process. The query is as follows: UPDATE XXXXXXXX SET XXXXXXXNumber = 431802 WHERE companyowner = '00000000' AND TypeDocument = 'TR' AND Series = '001' AND tsequal(Timestamp, 0x0000000011f13619) Before this work, I never used TSEQUAL, so i don't know if this sentence could takes lower time and how can I improve this. It would be really nice from you if you could explain me how this function works. Thanks a lot for your help! I really appreciate it.
I support the suggestion from Oleg (comment on TRADs answer) and that of sqlnubi about a possible index. Please also be aware that using a function in a `WHERE` clause or join will most likely make SQL Server unable to use your indexes as a function call negates SARGability. You will effectively force SQL Server to perform a table scan/clustered index scan for the table you are wanting to update. If the table you are updating is not very large, then this may not be a problem, but I suspect that this is exactly what you are experiencing as a problem. The index suggestion from sqlnubi may still not be of much help if the cardinality of the columns is not high enough. If the `WHERE` clause filters down to about 1% of the table contents, then it may be used by SQL Server to satisfy your query (and you use = instead of TSEQEUL), above that it may do a table scan anyway.
tsequal just does a comparison of the timestamp to another. (time stamp equal) As for the long running query, it could be a number of things. To many indexes to have to update with the update statement, not the right index to support the where clause, millions of rows that it has to search through and compare by. You have lots of options to try to optimize this. If you could provide more information as to the size of the table and your index structure we could make some great recommendations.