Map groups to parent products using nested subqueries

Question

I have the following tables

productstbl
===========================================
SegmentCode CategoryId ParentId
============================================

-----------------------------------------------------
categoriestbl

==================
CategoryId  name
====================

---------------------------------------------------------

Groupstbl

=====================
GroupId name
=====================

---------------------------------------------------------
CategoriesGroupsInfo

=======================
id  CategoryId  GroupId
=======================

---------------------------------------------------------

the logic goes like this: I want to map groups to parent products (root node which have parentid = 0) and the subproducts of that parent will inherit theses groups by default.

The levels of products are unlimited, for example:

1   Basic foods (root node )
    1.1 Baked goods
        1.1.1 American breads
              1.1.1.1 Adobe bread
    1.2 Breads 

2 Prepared foods(root node )
    2.1 Appetizers
    2.2 Condiments
    2.3 Confectionery

Note that I know the root node of any product through first char of segmentcode.

For example, root node of 1.1.1.1 Adobe bread will have segment code 1 which is Basic foods.

Groups are only applied for root nodes and to know the subproducts have to match first number of segment code.

Here is my query it do the work but the execution plan is a headache.

 select * from productstbl
    where substring(SegmentCode,1,1) 
    like ( select substring(SegmentCode,1,1) 
           from categoriestbl where CatID in(
           select CatID from CategoriesGroupsInfo where GroupID = 1)) 

Is there any alternative query?

Answer 1

I think the formatting of your code could use some improvements :

select * 
from productstbl 
where substring(SegmentCode,1,1) like 
( 
    select substring(SegmentCode,1,1)      
    from categoriestbl 
    where CatID in
    ( 
        select CatID 
        from CategoriesGroupsInfo    
        where GroupID = 1
    )
) 

---

Selecting every column can be expensive; I would recommend to optimize the query to select only the columns you need.

You could also potentially improve the performance (though not necessarily the execution plan) by creating a stored procedure, so the SQL engine wouldn't have to recalculate the execution plan on each call. This also make it much easier to call it from application code.

---

As far as improving the execution plan, you could modify the database to add indexing to SegmentCode, or add some columns to contain the individual segment codes (if that is feasible).

Short of changing the database, you could use #TempTables and add indexes on the column(s). That would increase I/O but may improve the execution plan.

On another note, you could most likely change like to in since you are comparing a single character with another. like is mostly used for string comparisons using wildcards, which are very expensive and you are not doing.