January 20, 2002 at 10:10 pm
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
WHERE
-- 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
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
January 21, 2002 at 5:48 am
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.
Andy
January 21, 2002 at 10:51 am
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
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 21, 2002 at 5:04 pm
January 23, 2002 at 12:59 am
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
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
January 23, 2002 at 12:05 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply