Need to improve Complex query performance

Transact-SQL
1

I have a complex query(view) and the driving table has 10 Million records and also it is joined with some other tables(3 tables).The Join condition/Where Clause columns are varchar type and non of the column is unique and integer So i can't able to use index to speed up the query..

How to speed up the query where the most of the columns are holding character data.

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
3

Thanks for quick response.

All tables fields are of type varchar(max).Sorry I can't share the table/query... But in query we are using more Like operators for wild card search. will it cause poor performance.

4

Yes, performance will be poor. The only option I can think that might improve it is full-text indexing:

https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-2017

1 Like
5

I will try..

Thank you very much.

6

Robert,

Can we refer the full text index functionality in view ?

7

Should be able to, I can't think of a reason why not. I haven't done it though.