how to improve sql performance in below case

Question

Resource 128 GB RAM, 6 TB space, Sql server 2014 enterprise.

Problem: Our DB has few tables including 5 'read only tables' of 200GB(approx) each. Each of these 5 tables have 5 columns (all columns(varchar 100)). Each col have repeated strings or values. Two columns (col3 and col4) have approx 3500 unique strings or values that are repeating.

Our only query is 'select col1......(all columns) from table x where col1 like 'searchstring%' or col2 like 'searchstring%' (only two col are queried for OR clause) as these are the only match criteria.

Presently query is taking 1hour to 1.5hrs to return results with present index. I am wondering if there is an efficient way to get result within 5 to 10 minutes. Thanks

Can you post your query? and the create script of the table? — Felix Pamittan, Apr 23 '15 at 04:17
assumed that your OR condition would be in small brackets (), e.g. Where (col1 like 'searchstring%' Or col2 like 'searchstring%'). and use With (Nolock), e.g. from table x With (Nolock) — Mihir Shah, Apr 23 '15 at 04:34
Try changing your "like" to "contains", see if it helps: http://stackoverflow.com/questions/7510646/like-vs-contains-on-sql-server — User2012384, Apr 23 '15 at 04:35
Look your query in execution plan to find out the expensive part — sqluser, Apr 23 '15 at 04:36
1. These are readonly tables so would it be helpful to use NOLOCK (tried with no luck). — Pradhan Sai, Apr 24 '15 at 00:58

score 1 · Answer 1 · answered Apr 23 '15 at 04:42

Go in SQL Server Managment Studio and look at the execution plan for the query. This will show you any recommended index(s) as well as the rate limiting steps (say io). Make sure you have no cardinality mismatching and on large tables make sure that you are keeping your statistics up to date.

score 0 · Answer 2 · answered Apr 23 '15 at 04:33

It all rather depends on your database but I suggest trying splitting the query in 2 to remove the OR.

I also recommend investigating functional indexes, you might be able to index on the first 10 character for instance and then the query may be faster.

Try everything, you never know what will work.

how to improve sql performance in below case

2 Answers2