Is there any way of speeding up Queries that use LIKE clauses ? 1

[Connatic]

I have a query (stored proc) that uses a LIKE statement on a Varchar column and it takes 6 seconds to run. As this is returning a results set to an application, this performance is unnacceptable.

Query :

SELECT m.m_mat_id, m.m_clt_code, m.m_mat_num, m.m_clt_name, 
       m_mat_desc, t.at_desc, a.add_type_id, a.add_id 
FROM matters m, addresstypes t, addresses a, mat_addr_lnk l WITH (NOLOCK) 
WHERE (m.m_clt_name LIKE '%' + @Variable + '%')
and m.m_mat_id = l.mat_id 
and a.add_id = l.add_id 
and a.add_type_id = t.at_type_id 
ORDER BY m.m_clt_code, m.m_mat_num ASC

The @Variable is obviously passed in to the stored proc!

If i take out the Wildcard character that comes before the variable it only takes about 1 second to run (which is fine), but i would really rather not do this, as users WILL Want to be able to do partial matches on both sides of the entered word.

e.g. entering 'Brown' would return ( for the Client Name Column)

1 Brown Associates
2 Brown & Jones
3 Mithell Brown Group
4 Davis, Brown & Partners

instead of

1 Brown Associates
2 Brown & Jones

So is thier any other way to speed up this type of query ??

Any help will be greatly appricated as i have spent all day looking at this and got nowhere !!!

Regards
Richard

 

[Connatic]

Thanks all for replying,

Sorry but got home on friday, and was so sunny, have spent almost the whole weekend out in the sun, normally accompanied by a cool beer.

Anyway, to get back to your questions:

The tables do have FK constraints, and the FKs are joined on PKs !

FK'S - mat_id & Add_id in LINK table
- Ad_type_id in Address table

Have looked at the execution plan and it uses indexes & there is No Table Scan.

Non Clustered Scan (on the Like Statement)points to Merge Join/Inner join on Mat_id = Mat_id. This Non Clustered Index Scan Takes up 92 % of the Query Cost!

Have Come in this morning and realised (i inherited these tables from someone else) That the LINK table doesn't have it's own Primary Key, but a composite Primary Key based on the Two foriegn Keys - Mat_id & Add_id, will this have any effect ??

Also, have just tried using the Index Tuning Wizard & it recommended creating A Covering Index on the Matters Table as NoCoolHandle said before.

It told me that this should give a 59% performance benefit for my Query, so i followed it's suggestion and the query STILL takes 6 Seconds !!!

Has changed the Execution Plan doing this but thats about it !!!

WITH (NOLOCK) - Was just something i was trying out, haven't seen any benefit from using it yet though !!

Query Plan Looks Like (after creating Covering Index)--

Select (Cost 0%)
Sort on Clt_code & Mat_num (Cost 0%)
Hash Match / Inner Join (on ad_type_id / cost 1%)
Clustered Index Scan - On PKAddressTypes (cost 1%)
Merge Join / Inner Join (on add_id / cost 1%)
Clustered Index Scan - On PKAddress (cost 4%)
Sort on Add_id (Cost 1%)
Merge Join / Inner Join (on matt_id / cost 8%)
Clustered Index Scan - On PKLink (cost 2%)
Index Scan - on Covering Index for LIKE statement (same covering index as NoCoolHandles) (Cost 82%)

SO, thats where i am up to, hope that this is more than enough information to be going on with, as it has taken long enough to write this !!

Cheers
Richard

 

[Connatic]

Aha, Full Execution Plan

|--Sort(ORDER BY[m].[m_clt_code] ASC, [m].[m_mat_num] ASC))

|--Hash Match(Inner Join, HASH[t].[at_type_id])=([a].[add_type_id]), RESIDUAL[t].[at_type_id]=[a].[add_type_id]))

|--Clustered Index Scan(OBJECT[dstest].[dbo].[AddressTypes].[PK_AddressTypes] AS [t]))

|--Merge Join(Inner Join, MERGE[a].[add_id])=([l].[add_id]), RESIDUAL[a].[add_id]=[l].[add_id]))

|--Clustered Index Scan(OBJECT[dstest].[dbo].[addresses].[PK_address] AS [a]), ORDERED FORWARD)

|--Sort(ORDER BY[l].[add_id] ASC))

|--Merge Join(Inner Join, MERGE[m].[m_mat_id])=([l].[mat_id]), RESIDUAL[m].[m_mat_id]=[l].[mat_id]))

|--Index Scan(OBJECT[dstest].[dbo].[matters].[matters5] AS [m]), WHERElike([m].[m_clt_name], '%brown%', NULL)) ORDERED FORWARD)

|--Clustered Index Scan(OBJECT[dstest].[dbo].[mat_addr_lnk].[PK_mat_addr_lnk] AS [l]), ORDERED FORWARD)

Hope this is usefull

Richard

 

[donutman]

With 20% of the cost outside of the covered index scan that in itself accounts for 1.2 seconds. The entire query should execute in less than that. I repeat my previous post: YOUR SERVER IS SUSPECT. Do any of the developers have the SQL engine on their desktop? Copy the tables over and run it locally.
-Karl

 

[Connatic]

OK Karl,

Will attempt to copy the tables, and run the query elsewhere, as Nothing else has worked.

I may be able to do this today or it may be tommorow, but will post to tell you how i have got on !!

Regards
Richard

 

[Connatic]

Right Karl - Mister DonutMan, you were right, or at least partially.

I Scripted all my tables and copied them & data to a new server. Initially my script took about 12 seconds.

Then i ran the 'Index Tuning wizard' (could do with understanding how that works a little more) and it created an indexed View for me which has speeded up the query to approx 1 second (cracking).

When i run the 'Wizard' on my Intiall database it doesn't give creating the indexed view as an option though - wierd!

So i think that i need to rebuild my initiall database!

Any way thanks all who contributed thier tuppence worth !!
and cheers Karl !


Richard!

 

[Siggy19]

One other thing to try...

Have a two stage query... in the first part do the LIKE with the trailing % only. If that does not return any results, then try the query with the initial %.

This will be slower for cases where the initial % is needed and will not show 'Smith and Brown' at all if you are looking for 'Brown' and 'Brown and Smith' is found, but these may be worthwhile tradeoffs.

 

[donutman]

I think Siggy is providing a good idea. The user should be presented with a checkbox that determines whether a search will be a "starts with" or "contains". The default should be the "starts with". The users will learn to use both and because one will take longer they will be motivated to use the faster.
-Karl

 

[Connatic]

Way ahead of you guy's, have already put that functionality in place just in case i couldn't get the Query time down !!

-Richard