Please help in Optimizing this Query

Transact-SQL
1

Dear All,

Please see the below query as it is working too slow. Its a simply query. I am also attaching the respective estimated execution plan for your reference. Please help me out.

select
distinct
[Location Code],[Item No_],
(Select [Description] from [Kamla Retail Limited$Item]
where [No_]=A.[Item No_]) as [Description],
[Item Category Code],[Product Group Code],
isnull((Select sum(Quantity) from [Kamla Retail Limited$Item Ledger Entry]
where [Location Code]=A.[Location Code] and [Item No_]=A.[Item No_]
and [Posting Date]<='08/24/2015'),0) as [Linked_Department_Stock]
,
isnull((Select sum(Quantity) from [Kamla Retail Limited$Item Ledger Entry]
where [Location Code]=A.[Location Code] and [Posting Date]<='08/24/2015'
and [Item No_] in (Select [Item No_] from [Kamla Retail Limited$Linked Item]
where [Linked Item No_]=A.[Item No_]
))
,0) as [Department Stock]
from [Kamla Retail Limited$Item Ledger Entry] as A
where [Posting Date]<='08/24/2015' and [Location Code] not like 'IT%'
and [Product Group Code] in ('WBX') and [Location Code] in ('ECHD')

2

Reformatting:

SELECT DISTINCT [Location Code]
      ,[Item No_]
      ,(
            SELECT [Description]
            FROM [Kamla Retail Limited$Item]
            WHERE [No_] = A.[Item No_]
            ) AS [Description]
      ,[Item Category Code]
      ,[Product Group Code]
      ,isnull((
                  SELECT sum(Quantity)
                  FROM [Kamla Retail Limited$Item Ledger Entry]
                  WHERE [Location Code] = A.[Location Code]
                        AND [Item No_] = A.[Item No_]
                        AND [Posting Date] <= '08/24/2015'
                  ), 0) AS [Linked_Department_Stock]
      ,isnull((
                  SELECT sum(Quantity)
                  FROM [Kamla Retail Limited$Item Ledger Entry]
                  WHERE [Location Code] = A.[Location Code]
                        AND [Posting Date] <= '08/24/2015'
                        AND [Item No_] IN (
                              SELECT [Item No_]
                              FROM [Kamla Retail Limited$Linked Item]
                              WHERE [Linked Item No_] = A.[Item No_]
                              )
                  ), 0) AS [Department Stock]
FROM [Kamla Retail Limited$Item Ledger Entry] AS A
WHERE [Posting Date] <= '08/24/2015'
      AND [Location Code] NOT LIKE 'IT%'
      AND [Product Group Code] IN ('WBX')
      AND [Location Code] IN ('ECHD')

It is easy to see problems here.

  1. Rewrite your query to use joins instead of the cascading selects

  2. Ensure that the columns used in your WHERE conditions (which will become ON conditions in JOINS once you rewrite your query) are indexed.

  3. Do not write:

    [Posting Date] <= '08/24/2015'

Which is locale-specific. Instead, write:

[Posting Date] <= '20150824'
1 Like
3

Dear Sir,

Thank you so much for your valuable feedback. I have rewrite the query using Group by and removed first cascading select to compute sum of quantity as it is now calculated simply as sum(quantity) using Group by Clause. It is working fine now.

Regards
Yogesh Sharma

4

AND [Product Group Code] IN ('WBX')
AND [Location Code] IN ('ECHD')

Change the above to

AND [Product Group Code] = ('WBX')
AND [Location Code] = ('ECHD')

and also i would use the exists clause