Problem SQL statement

  • Hi all

    Ive been trying to tune up the following statement with no major performance increase. The big problem is the %value% like lookup as you will see below. Any ideas on how this can be improved?

    organisation = 90618 rows

    organisation_name = 131255 rows

    organisation_role = 90648 rows

    organisation_relationship = 50115 rows

    SELECT DISTINCT A.org_id, A.org_national_id, LN.orgname_name, TD.orgname_name as trading_name, P.orgrelationship_parent_org_id, '' as org_roles

    FROM Organisation AS A

    LEFT OUTER JOIN Organisation_Name AS B ON B.orgname_org_id=A.org_id

    -- get legal name

    LEFT OUTER JOIN Organisation_Name AS LN ON LN.orgname_org_id=A.org_id

    AND LN.orgname_nametype_id=1

    AND LN.orgname_current_ind = 1

    -- get trade name

    LEFT OUTER JOIN Organisation_Name AS TD ON TD.orgname_org_id=A.org_id

    AND TD.orgname_nametype_id=2

    AND TD.orgname_current_ind = 1

    LEFT OUTER JOIN Organisation_Relationship AS P ON A.org_id=P.orgrelationship_child_org_id AND P.orgrelationship_orgrelationtype_id = 2

    LEFT OUTER JOIN Organisation_Role AS D ON A.org_id=D.orgrole_org_id


    -- locate all orgs where there is a loose name assoc as below

    B.orgname_name LIKE '%electric%'

    AND D.orgrole_roletype_id = 2

    ORDER BY LN.orgname_name

    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Leading wildcard always sucks! Does the query run ok if you just use a trailing wildcard? I know that doesnt meet your goal, but would confirm/deny we're looking in the right place.

    Assuming it is, the trick becomes how to do better. One idea would be an indexed view on just the pkey and that column, at least when it table scans you'll have a lot more rows per page. A better idea would be to change the way the data is populated to include an ID that goes to a lookup table. Even if you can't modify the app to do it itself you could probably write a backend process that would go through every x mins/days to "guess" what type it should be.


  • The problem with the leading % is that SQL Server is unable to use an index since the first part of the string could be anything. As a result, a full table scan has to be carried out on the organisation_name table. As Andy suggested, if you can get rid of the leading wildcard you have a chance at getting an index, provided there is one on that column.

    Another possibility may be looking at full text indexing for the character column. It'll allow you to search for the word like a wildcard, but it's optimized for these types of searches. It also handles basic forms of words as well. There are issues to consider with full text indexing, such as the fact that catalogs have to be maintained "manually" and the additional resources (hard drive space, a little extra processing power) required to maintain and use full text indexes.

    K. Brian Kelley

    K. Brian Kelley

  • Agree with Brian. Just my 2 cents

    Steve Jones

  • thanks guys, i ran some tests with full-text and the performance has improved 200%, but as we are going live in 2 weeks we will hold off for the time being. The %% is a real problem and apart from full-text I cant find another work around, so full-text it is!

    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Thanks for the followup. Let is know if it works in production.

    Steve Jones

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply