1

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 ResponsibilityTypeId = 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?

Improve this question

4 Answers 4

Reset to default
3

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]
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thanks, I've been looking at this for hours. Your solution worked! Never thought it would be the IN
1

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.

Improve this answer

1 Comment

thanks for your reply, unfortunatly I am using a 2005 server and I don't own it. Alse, not using the starting '%' seems to take the same amount of time to run
0

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

Improve this answer

Comments

0

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

Improve this answer

6 Comments

@Simen S - are there any other improvements possible for a situation like this, where you may need the LIKE operator?
It's not just the LIKE operator, it's the worst case of starting the condition with '%', which will always cause a full table scan. It's impossible for the database to use an index for this condition, because you haven't provided the beginning of the column value.
@Brett: If you're using SQL Server 2008 Full Text Search might be an option (as I have specified above)
@joel C: I just tried running both of the examples above using LIKE 'jos%' (without the starting '%') and it still took the same amount of time
@gen does the field you're searching on have an index? If it's not indexed, it will be a full table scan either way.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.