SQL : Optimizing query to have few IO operations

Question

How can i rewrite the below query to return results with few IO operation and less response time

select 
    *,
   (select Product_Name from InventoryMaster J2 where J1.ParentItem=J2.ItemId) as ParentName,
   (select Description from InventoryMaster J2 where J1.ParentItem=J2.ItemId) as ParentDesc
from
   InventoryMaster J1
where
   Flag like '%N%'

The InventoryMaster table has a parent Item column where it will store another records ItemId (for parent-child relatioship) for some records

Answer 1

where
   Flag like '%N%'

The above like makes it difficult for the database to answer this query efficiently. It has to look at every value of the "Flag" column and check for an "N" within the string.

Could this be changed to just Flag = 'N'?

If the answer is no, then the database is designed incorrectly. You should store one thing in a column, not multiple. Search for "database normalization".

You should consider re-writing the query as a join between the three tables rather than using the sub-select in the query list as you have.

Answer 2

Why don't you use a JOIN between the child and parent? Like this:

SELECT
  J1.*,
  J2.Product_Name AS ParentName,
  J2.Description AS ParentDesc
FROM InventoryMaster AS J1
LEFT JOIN InventoryMaster AS J2 ON J2.ItemId = J1.ParentItem
WHERE J1.Flag LIKE '%N%'

Answer 3

This might be a bit lighter on the database

select 
    J1.*,
    J2.Product_Name as ParentName,
    J2.Description as ParentDesc
from
   InventoryMaster J1
left join InventoryMaster J2 on J1. ParentItem = J2.ItemID
where
   Flag like '%N%'

But the big killer is still the like clause. Suggest you rework that to have a single field that stores the N flag.

Answer 4

Also, don't use SELECT *. You may be bringing back columns you aren't using - or someone may add a massive TEXT column in the future, which your application won't be expecting/using, and that will reduce the performance of your query further.

I think the change to use a JOIN could be as important in improving performance as the LIKE '%N%' issue - in your original syntax SQL is having to make a lot of correlated queries to get the Parent Product_Name and Description (unless the optimiser is smart enough to spot that ...)

In fact the LIKE issue may be mute - even if the Flag column is indexed I can't see SQL using it [for an EQUALS test] if the only values are Y and N and are evenly spread (if Flag = N is a very small proportion of the rows [i.e. highly selective] then the optimiser may use the index [by checking the Statistics for the index]). So if SQL has to do a full table scan anyway to solve Flag = 'N' then its not a lot more work to do Flag LIKE '%N%'

Performance will then be helped further by having an index on ParentItem and another on ItemID (which may be your Primary key anyway)

(My knowledge is only with MS SQL Server)