Optimising Query with Like in the Select

Question

question

Schappo asked Mar 30, '10

Optimising Query with Like in the Select

I have a view which is as follows

SELECT dbo.taCompany.comRecID, dbo.taCompany.corRecID, dbo.taCompany.CompanyName, dbo.taCompany.Phone, dbo.taCompany.Fax, dbo.taCompany.Email, dbo.taCompany.WebSite, dbo.taCorporation.CorporationName, dbo.taCompany.Active, dbo.Address_View.Address, dbo.Address_View.Suburb, dbo.Address_View.Postcode, dbo.Address_View.State, dbo.Address_View.suRecID, dbo.Address_View.Street, dbo.taCompany.CreateDate, dbo.taCompany.ModifDate, dbo.taCompany.ReadOnly FROM dbo.taCorporation RIGHT OUTER JOIN dbo.taCompany ON dbo.taCorporation.corRecID = dbo.taCompany.corRecID LEFT OUTER JOIN dbo.Address_View ON dbo.GetCompanyAddRecID(dbo.taCompany.comRecID) = dbo.Address_View.AddRecID WHERE (dbo.taCompany.ReadOnly = 0)

Whien I run the following query on the above view, it runs in less then one second

SELECT * FROM Company_View WHERE active = 1 and companyName like '%lj%'

But if I run the following query on the same view with different parameters it takes 33 Seconds

SELECT * FROM Company_View WHERE active = 1 and companyName like '%hooker%'

How can I reduce the time of the second query.

Things I have done: -ran DE Tuning advisor, -looked for missing indexes, -tried to analyse Execution plan. There is a Hash Match(left Outer Join) Cost: 47% in the execution plan on taAddress.addRecID. -I have rebuilt and reorganised indexes on all tables.

I am told by users this query used to run under 2 Second, I'm not sure what else I can do any help would be greatly appreciated!

Thanks Shane.

sql-server-2005 select view like

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Schappo commented · Mar 30, 2010 at 02:23 AM

I have just run the second query on our test database wich is a older vesion of the live database basically the same and the query ran in less than 2 seconds? The execution Plan on the test DB vs the Live DB looks very different (missing the Hash Match I spoke about above.) There are also a few new indexes in the live DB. Shane

0 ·

Answer 1 · 2010-03-31T06:45:31Z

DamirP answered Mar 31, '10

If you can try to remove the dbo.GetCompanyAddRecID function even if you need to join another table to get that ID. (Try to avoid functions in querys they slow it down and mess up execution plans) Leading wildcard characters does not allow the query optimizer to use indexes to optimize the search. Did you clear the execution plan when you created the indexes?

checkpoint            
            
DBCC DROPCLEANBUFFERS             
DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'Live DB')            
DBCC FLUSHPROCINDB (@intDBID)

Is the testDB and Live DB on same server could it be that parallelization is working in one of the sub querys?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question