Need to improve Complex query performance

Transact-SQL
2

It would be a big help if you posted:

  1. The DML/CREATE TABLE statements for the tables involved
  2. The query and/or view definitions involved
  3. Some sample data (INSERT statements) and the expected results

If I'm reading your post correctly, you have non-unique varchar data JOINed to other varchar columns? You certainly can use indexes on non-integer columns, assuming they're not >900 bytes.

It would also help if you could post the query plan assuming it's not too large. We'd need the XML of the plan, not the image of it. It's likely your 10M row table is being scanned with a nested loop somewhere if it's really very slow.

1 Like