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.

• Mohamed abdelrahman: you are right in the part of using in over like and here is screen shots as prove on concept for others  
• Mohamed abdelrahman: another part you could use #TempTables you mean creating temp table with same structure ??
• Phrancis: @Mohamedabdelrahman yes, if modifying the database is not an option. I've had to use this technique a few times, and while it may seem crude it has helped me improve execution significantly in some cases where the source tables didn't have the indexes I needed.
• Phrancis: Just make sure to copy the bare minimum of the data you need to temp tables, otherwise you will replace one problem with another.
• Mohamed abdelrahman: what if i need all columns ?