Performance issue with SQL Server query

This is my query:

SELECT ID, [type], naam, adresl1, adresl2, tel, fax, email, contactpersoon
FROM
    ------------------------ START --------------------------
    (SELECT av.personID as [id], 'P' as [type], 
            av.firstname + ' ' + av.lastname as 'naam', 
            av.straat as 'adresl1',
            c.zipCode + ' ' + c.City as 'adresl2',
            av.phone as 'tel',
            '' as fax,
            av.Email as 'email',
            '' as 'website', '' as 'contactpersoon',
            coalesce(a1.aliasTitle,'') + '|' + coalesce(a1.aliasTitle2,'') + '|' + coalesce(1.aliasTitle3,'') + '|' + 
            coalesce(a2.aliasTitle,'') + '|' + coalesce(a2.aliasTitle2,'') + '|' + coalesce(a2.aliasTitle3,'') + '|' + 
            coalesce(f.FunctionTitle,'') + '|' + coalesce(r.Raad,'') + '|' + coalesce(rci.RedCrossInstitutionName,'') + '|' + 
            coalesce(av.firstname,'') + ' ' + coalesce(av.lastname,'') + '|' + 
            coalesce(av.lastname,'') + ' ' + coalesce(av.firstname,'') AS 'wie',
            coalesce(a1.aliasTitle,'') + '|' + coalesce(a1.aliasTitle2,'') + '|' + coalesce(a1.aliasTitle3,'') AS 'waar',
            coalesce(echelon.Street,'') + '|' + coalesce(echelon.zipcode,'') + '|' + 
            coalesce(echelon.City,'') + '|' + coalesce(echelon.RedCrossEntityName,'') AS 'waar_E'
    FROM RVW_vwAdresboekVrijwilligers av
    LEFT JOIN City c ON av.CityID = c.CityID
    LEFT JOIN AliasPerson ap ON av.PersonID = ap.PersonID
    LEFT JOIN Alias a1 ON ap.AliasID = a1.AliasID
    LEFT JOIN FunctionPerson fp ON av.PersonID = fp.PersonID
    LEFT JOIN 
         (SELECT RedCrossEntity.RedCrossEntityID, RedCrossEntity.RedCrossEntityName, 
                 RedCrossEntity.street, City.zipcode, City.city
          FROM RedCrossEntity
          LEFT JOIN City ON RedCrossEntity.CityID = City.CityID
         ) AS echelon ON echelon.RedCrossEntityID = fp.RedCrossEntityID
    LEFT JOIN [Function] f ON f.FunctionID = fp.FunctionID
    LEFT JOIN AliasFunction af ON af.FunctionID = f.FunctionID
    LEFT JOIN Alias a2 ON a2.AliasID = af.AliasID
    LEFT JOIN FunctionRaad fr ON fr.FunctionID = f.FunctionID
    LEFT JOIN Raad r ON r.RaadID = fr.RaadID
    LEFT JOIN RedCrossInstitution rci ON rci.RedCrossInstitutionID = fp.RedCrossInstitutionID
    WHERE 
        f.functionid IN (SELECT functionid FROM FunctionResponsibility 
                         WHERE Responsibility开发者_开发问答TypeId = 4)
------------------------- END -----------------------
 ) data     
WHERE 
     (wie LIKE '%jos%' OR waar LIKE '+++++'  )
ORDER BY 
     [type]

The section between -START- and -END- is working fine and executes in about 3 seconds. But when I add the WHERE (wie LIKE '%jos%' OR waar LIKE '+++++' ) it takes 30 seconds to run.

I have tried this too:

.......
LEFT JOIN RedCrossInstitution rci ON rci.RedCrossInstitutionID = fp.RedCrossInstitutionID   
WHERE 
     1=1 
     AND (a1.aliasTitle LIKE '%jos%'
        OR a1.aliasTitle2 LIKE '%jos%'
        OR a1.aliasTitle3 LIKE '%jos%'
        OR a1.aliasTitle LIKE '+++++'
        OR a1.aliasTitle2 LIKE '+++++'
        OR a1.aliasTitle3 LIKE '+++++'
        OR a2.aliasTitle LIKE '%jos%'
        OR a2.aliasTitle2 LIKE '%jos%'
        OR a2.aliasTitle3 LIKE '%jos%'
        OR f.FunctionTitle LIKE '%jos%'
        OR r.Raad LIKE '%jos%'
        OR rci.RedCrossInstitutionName LIKE '%jos%'
        OR (av.firstname + ' ' + av.lastname LIKE '%jos%')
        OR (av.lastname + ' ' + av.firstname LIKE '%jos%')
          )
      AND f.functionid IN (SELECT functionid FROM FunctionResponsibility 
                           WHERE ResponsibilityTypeId = 4)

But that was even slower.

Does anyone see a way to speed this up?

Please remove the IN and replace it with join as mentioned below. In this way we can avoid the looping which is happening due to IN

SELECT ID, [type], naam, adresl1, adresl2, tel, fax, email, contactpersoon
        FROM(
    ------------------------ START --------------------------
            SELECT av.personID as [id], 'P' as [type], 
                av.firstname + ' ' + av.lastname as 'naam', 
                av.straat as 'adresl1',
                c.zipCode + ' ' + c.City as 'adresl2',
                av.phone as 'tel',
                '' as fax,
                av.Email as 'email',
                '' as 'website', '' as 'contactpersoon',
                coalesce(a1.aliasTitle,'') + '|' + coalesce(a1.aliasTitle2,'') + '|' + coalesce(a1.aliasTitle3,'') + '|' + 
                    coalesce(a2.aliasTitle,'') + '|' + coalesce(a2.aliasTitle2,'') + '|' + coalesce(a2.aliasTitle3,'') + '|' + 
                    coalesce(f.FunctionTitle,'') + '|' + coalesce(r.Raad,'') + '|' + coalesce(rci.RedCrossInstitutionName,'') + '|' + 
                    coalesce(av.firstname,'') + ' ' + coalesce(av.lastname,'') + '|' + 
                    coalesce(av.lastname,'') + ' ' + coalesce(av.firstname,'') AS 'wie',
                coalesce(a1.aliasTitle,'') + '|' + coalesce(a1.aliasTitle2,'') + '|' + coalesce(a1.aliasTitle3,'') AS 'waar',
                coalesce(echelon.Street,'') + '|' + coalesce(echelon.zipcode,'') + '|' + 
                    coalesce(echelon.City,'') + '|' + coalesce(echelon.RedCrossEntityName,'') AS 'waar_E'
            FROM RVW_vwAdresboekVrijwilligers av
            LEFT JOIN City c ON av.CityID = c.CityID
            LEFT JOIN AliasPerson ap ON av.PersonID = ap.PersonID
            LEFT JOIN Alias a1 ON ap.AliasID = a1.AliasID
            LEFT JOIN FunctionPerson fp ON av.PersonID = fp.PersonID
            LEFT JOIN 
                (
                    SELECT RedCrossEntity.RedCrossEntityID, RedCrossEntity.RedCrossEntityName, RedCrossEntity.street, City.zipcode, City.city
                    FROM RedCrossEntity
                    LEFT JOIN City ON RedCrossEntity.CityID = City.CityID
                ) as echelon ON echelon.RedCrossEntityID = fp.RedCrossEntityID

            LEFT JOIN [Function] f ON f.FunctionID = fp.FunctionID
            LEFT JOIN AliasFunction af ON af.FunctionID = f.FunctionID
            LEFT JOIN Alias a2 ON a2.AliasID = af.AliasID
            LEFT JOIN FunctionRaad fr ON fr.FunctionID = f.FunctionID
            LEFT JOIN Raad r ON r.RaadID = fr.RaadID
            LEFT JOIN RedCrossInstitution rci ON rci.RedCrossInstitutionID = fp.RedCrossInstitutionID
            INNER Join FunctionResponsibility FRes on FRes.functionid = f.functionid
            WHERE FRes.ResponsibilityTypeId = 4
------------------------- END -----------------------
            ) data      
        WHERE (wie LIKE '%jos%' OR waar LIKE '+++++'  )
        ORDER BY [type]

The problem is that you're doing a wildcard search, looking for a specific string contained anywhere inside the field you're searching on. If this is performance-critical and there's no way to make your search more specific, consider using Full-Text Search:

Comparison of LIKE to Full-Text Search

In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.

Note that Full-Text Search is a separate component of SQL Server that is installed and administered on its own, so you will have to evaluate for yourself if that overhead is worth the possible performance gain. See Full-Text Search (SQL Server) for more information.

This article from MSDN talks about performance when the LIKE predicate of a WHERE clause starts with the wildcard character, %.

SQL server will struggle to use indexes on the wie and waar columns when you use LIKE operators in your query.

More information here: http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx

EDIT: If you are trying to implement a search feature, then you could look into Full Text Search