SQL Server Performance Forum – Threads Archive
SQL Query Tuning
I am running the following queries Step 1select company_id
from company
where upper_company_name like ‘%BBG%’
– Takes 2 Sec
output is one row and the value is 50024985 Step 2
select sales_order_id
from sales_order_party
where company_id in (50024985)
– Takes less than 1 Sec Step 3
select sales_order_id
from sales_order_party
where company_id in
(select company_id
from company
where upper_company_name like ‘%BBG%’)
– Takes more than 40 Sec. Can someone explain why this is happening ? I am totally confused. The index used for upper company name search has the column in the middle of the index, but still the index is being used. -Nags
EXISTS is generally fast performing than IN clause. Try running the query like
select sales_order_id
from sales_order_party
where exists
(select * from company
where upper_company_name like ‘%BBG%’
and company.company_id = sales_order_party.company_id )
As far as index is concerned, if you really think the index use is benefecial try forcing the index using hint and see if there is any performance improvement. HTH. Gaurav
In this particular scenario I cannot afford to have a co-related query. The sales_order_party table is very huge and by having a co-related query I would be scanning the entire sales_order_party table. The subquery was a way of reducing the results that i would be processing. My question is why is the execution plan different. Why does the query take so long to execute when used as a sub-query but when used as independent queries it is run within seconds ? I did run by using the exists clause to check the speed of the query. It runs very slow and is almost the same like the subquery. -Nags
One more issue I forgot to mention, we cannot use hints within our application. Only tuning that is possible is indexes and re-writing the queries. -Nags
your problem is mostly like due to the difficulty of estimating the row from a query involving LIKE with a leading wildcard.
it doesn’t matter what the actual row count, what matters is the estimated row count, which i am guessing is pretty high, when the estimated row count for the company portion is applied to the entire join, SQL Server is expecting a large number of rows, hence it employs a join suitable for handling large row counts (ie, one that is not efficient for small row counts) if you could use hints and can guarantee that not many rows will come out of the company table, the following should work:
select s.sales_order_id
from company c
inner loop join sales_order_party s ON s.company_id = c.company_id
where c.upper_company_name like ‘%BBG%’ given that you can not use hints, one way to override the row estimates that SQL Server generates is: select sales_order_id
from sales_order_party
where company_id in
(select TOP xx company_id
from company
where upper_company_name like ‘%BBG%’) where xx is some small number (less than 120) that you are certain will be the max rows out of the company table
]]>