Improving specific SQL Server Query Performance -

i have following query being executed in sql server 2008 r2 several times day on multiple client databases.

update    propane.recordkey (tablock) set              lookupkey = system.param_recordkey.lookupkey         propane.recordkey inner join                    system.param_recordkey on                    propane.recordkey.indexid = system.param_recordkey.indexid                   , propane.recordkey.recordnumber = system.param_recordkey.recordnumber                                          , propane.recordkey.lookupkey <> system.param_recordkey.lookupkey     (system.param_recordkey.parameterkeyid = @key) 

i looking suggestion on how make query run possible. having delays can last few seconds minute or more.

the performance not consistent, cases many records affected running relatively , other cases many fewer affected records running quite slowly. on test machine performance acceptable (i.e few seconds 10,000+ records), there significant delays occurring on of our clients.

this extreme variation unable account for, combined admittedly limited knowledge of how tweak query performance, makes me reluctant experiment alterations may not show benefit , might make query run worse.

the table being updated, recordkey, read (hundreds thousands of times day) , updated (a few times per day).

recordkey's primary index indexid , recordnumber. param_recordkey has key fields of parameterkeyid, indexid, , recordnumber. i.e. filtering , joining on primary key fields of both tables. these fields ints.

recordkey have hundreds of thousands few million records in it. param_recordkey have records being updated. typically param_recordkey contain few thousand records, though 10's of thousands of records common , 100,000 or more possible.

the lookupkey field in both tables varchar(27). in cases text stored in field have length between 5-10 characters. lengths of 10-15 characters common , lengths on 20 rare. in vast majority of cases values in respective lookupkeys same, i.e. few records updated. in rare cases of records updated.

one question have is: better performance if remove check propane.recordkey.lookupkey <> system.param_recordkey.lookupkey? i.e. if update records , not different lookupkeys. expectation time update records greater updating have changed, i'm uncertain true.

any advice on how improve query appreciated.

---

april 9th 2013, new info.

after removing (tablock) , testing changes @ client's site, results mixed. in cases query executed faster, in cases slower, , in many cases there no significant change in how long took process records.

since making change did not result significant , repeatable improvement in performance, i'm leaving question open , marked unanswered. still interested in assistance problem.

the table hint tablock take exclusive lock on table being updated. such locks not compatible other locks, shared locks, , impact performance. if query requires exclusive table lock have wait until no other locks taken. remove hint , let database engine decide locking strategy - it's quite @ it. performance on development machine acceptable because (probably) person accessing table , such no other locks taken