Can you suggest an alternative to an IN statement?

20 pts.
Tags:
SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Ooops, left out one point. The IN statement that I’d be interested in replacing is contained within the main select clause. The purpose of the IN statement is to count up the number of products contained within the category.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Sonk55
    Hi Can you post the structure of the tables and their indexes ? Are the tables analyzed recently ? Can you post an explain plan of this statement ? Regards, sonk55
    0 pointsBadges:
    report
  • RolandT
    If you put your job in debug mode (STRDBG) before running the SQL statement, you should get additional messages some of which often suggest additional keys that would help with performance. If it suggests an addition access path, just create a new logical file. You don't even have to use the new logical in your querry. SQL will find it and use it if it would help. Similar messages are available using the SQL statements from within ISeries Navigator, actually these can be easier to work with.
    0 pointsBadges:
    report
  • CrushNspin
    May be you can use Exists clause instead of IN like where exists( select taxSub.tax_id from taxonomy taxSub where taxSub.tax_id = tpa.taxonomy_id start with taxSub.tax_id = tax.tax_id connect by parent = prior taxSub.tax_id ) See if the logic would still be correct. I think it should be coz you are trying to check if the taxonomy Id is in the tax_id and its children. Hope that helps.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following