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!