Q:

How to make my search for multiple columns procedure more efficient?

I have a stored procedure that searches across multiple columns in a combined table.
It works, however, it takes 15 seconds to search for a value in that combined table. The table takes 9 seconds to load so I'm not sure, maybe it's because my table too big?
So I'm just wondering if there's a way to make this query runs faster.
This is my stored procedure:
create procedure LRMWEB_Search

  @input nvarchar(1500)

AS

  SET NOCOUNT ON;

  SELECT tr.ResourceID , 
         tr.ProjectFile, 
         tr.ResourceFile, 
         tr.ResourceName, 
         trt.Culture, 
         trt.TranslatedFlag, 
         trt.TranslatedValue, 
         tr.Comments, 
         tr.IsApproved
  FROM       tblResourcesTranslated_NEW trt
  INNER JOIN tblResources_NEW           tr  ON trt.ResourceID = tr.ResourceID
  where tr.ResourceID       like '%'+ @input + '%'
     OR tr.ProjectFile      like '%'+ @input + '%'
     OR tr.ResourceFile     like '%'+ @input + '%'
     OR tr.ResourceName     like '%'+ @input + '%'
     OR tr.ResourceValue    like '%'+ @input + '%'
     OR tr.Comments         like '%'+ @input + '%'
     OR trt.Uid             like '%'+ @input + '%'
     OR trt.TranslatedValue like '%'+ @input + '%'
  ;

A:

Any use of like precludes an index, unless it has a fixed prefix with a wildcard suffix, such as where foo like 'bar%'. Your like expressions (e.g., '%xxx%' ) do not meet that requirement.
As a result, while the join criteria may well have a covering index, nothing else does and so a table scan of the join tables is required.
In a nutshell, there is no way to fix performance outside of either

rethinking what you're doing, or
using something like a full text search

