2
\$\begingroup\$

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?

\$\endgroup\$
3
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ you are right in the part of using in over like and here is screen shots as prove on concept for others ![in.png](postimg.org/image/b4e7mi3j3) ![like.png](postimg.org/image/kdgdwmcf3) \$\endgroup\$ – Mohamed abdelrahman Jan 24 '17 at 7:52
  • \$\begingroup\$ another part you could use #TempTables you mean creating temp table with same structure ?? \$\endgroup\$ – Mohamed abdelrahman Jan 24 '17 at 7:55
  • \$\begingroup\$ @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. \$\endgroup\$ – Phrancis Jan 24 '17 at 8:09
  • \$\begingroup\$ Just make sure to copy the bare minimum of the data you need to temp tables, otherwise you will replace one problem with another. \$\endgroup\$ – Phrancis Jan 24 '17 at 8:22
  • \$\begingroup\$ what if i need all columns ? \$\endgroup\$ – Mohamed abdelrahman Jan 24 '17 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.