Allocated Batch Query

[former_member4247]

I wrote a quet (see below) to find all batches that have been allocated to a Production order.

PROBLEM: The query takes a LONG time to run, from 8-10 minutes. I am sure it has something to do with the tables I am using. Any ideas how to speed it up? I want to use this query for a report but i cannot have ot take 10 minutes each tome we want to view or print a report.

SELECT DISTINCT T0.BatchNum, T0.ItemCode, T1.U_X_PackWt as 'Package Weight', T0.Quantity AS 'Allocated Qty'
FROM            OIBT AS T1 LEFT OUTER JOIN
                         IBT1 AS T0 ON T1.BatchNum = T0.BatchNum
WHERE        (T0.BaseNum = '39725') and T0.ItemCode not like 'B%'
order by T0.ItemCode

[neilos]

Have you tried to see it if is still slow in returning results if you don't query OIBT table?
So only

SELECT T0.[ItemCode], T0.[BatchNum], T0.[Quantity] FROM IBT1 T0 
WHERE        (T0.[BaseNum] = '39725') and T0.[ItemCode] not like 'B%'
order by T0.ItemCode

As I have tried it and it returns results in quick time (with altered where clause), but joining OIBT table to the query does cause a very noticeable (45 secs - 1+ minute) delay in returning even a small set of results results.
If your query is expected to have a large return, then I would hazard a guess it is a fundamental SAP bottleneck somehow, and not how the query is constructed.