I'm running a query which contains an IN statement and even though the cost of the query is low, the performance is not ideal.
Here is the problem:
We categorize products by associating the items to branches on a taxonomy tree. Customers are able to browse products by category and in this case, a customer is browsing our online warehouse. The design of the application states that each category will display the total number of products available at that category and below.
The solution I have is to collect the children taxonomy branches through a connect by statement within an IN statement. The query itself uses a count(*) to determine the number of products associated with all child categories and "current" category.
I believe the IN statement is killing my performance, but I can't seem to find a better solution.
Here is a sample query, where @taxID = current category id:
select tax.tax_id, tax.tax_name, tax.sequence,
(select count(*) count
from taxonomy_product_a tpa, store_product_a spa, product p
where tpa.taxonomy_id IN (select taxSub.tax_id from taxonomy taxSub start with taxSub.tax_id = tax.tax_id connect by parent = prior taxSub.tax_id) and tpa.content_id = spa.content_id and
spa.store_id = 0 and
spa.status = 'live' and
tpa.product_id = p.product_id and
p.clearance_ind is null and
p.outofstock_ind is null) productCount
from taxonomy tax
where tax.parent = '@taxID'
I hope I've explained this issue clearly enough for a response. Thanks.