I ran the same query on the same workstation against two different databases that yielded completely different execution plans. The structure and indexes are the same. The only difference is the data. The strange thing is the data in “database1” is much less than the data in “database2” and the performance is worse (database2 takes ½ the time as database1). You’ll notice an obvious design flaw in the ItemProperties table and we plan on changing the structure of this poorly designed table, however before that takes place I’m looking for optimization ideas. Here is the info for both databases.
Database1: [Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=HyVv5-PE-
Database2: [Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=rJrzjbDVW