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!
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?
Is the testDB and Live DB on same server could it be that parallelization is working in one of the sub querys?
answered Mar 31, 2010 at 06:45 AM