x

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.

more ▼

asked Mar 30, 2010 at 01:56 AM in Default

avatar image

Schappo
11 1 1 2

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

Mar 30, 2010 at 02:23 AM Schappo
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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?

more ▼

answered Mar 31, 2010 at 06:45 AM

avatar image

DamirP
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2037
x162
x73
x25

asked: Mar 30, 2010 at 01:56 AM

Seen: 1687 times

Last Updated: Mar 30, 2010 at 06:39 PM

Copyright 2018 Redgate Software. Privacy Policy