I've written a query for a SRSS report that is supposed to retrieve a list of items from maximo.
It includes how many items are in stock, if they have a received and/or issue date (and if so what is the latest date) and the unit cost at that time.
Although it returns what I want, it runs very slowly, to the point that when it is used in the report it never seems to load. 5000 rows takes 21 seconds (and there are already > 100k transactions in maximo!). How can I returning the unit costs to make it quicker? It runs quickly without the unit cost lines.
Query
select top 5000 item.commoditygroup, item.itemnum, item.description, item.commodity, inventory.location, inventory.siteid,
(select sum(curbal) from invbalances where item.itemnum = invbalances.itemnum group by itemnum) CurrentBalance,
(select max(Convert(varchar(10), matrectrans.transdate, 103)) from matrectrans
where item.itemnum = matrectrans.itemnum and matrectrans.issuetype='RECEIPT') ReceivedDate,
(Select m1.unitcost from matrectrans m1
where m1.transdate = (select max(m2.transdate)from matrectrans m2 where item.itemnum = m2.itemnum and m2.issuetype='RECEIPT' and linecost <> 0)) RecievedUnitCost,
(select max(Convert(varchar(10), matusetrans.transdate, 103)) from matusetrans
where item.itemnum = matusetrans.itemnum and matusetrans.issuetype='ISSUE') IssueDate,
(Select m3.unitcost from matusetrans m3
where m3.transdate = (select max(m4.transdate)from matusetrans m4 where item.itemnum = m4.itemnum and m4.issuetype='ISSUE' and linecost <> 0)) IssuedUnitCost
from item, inventory
where item.itemnum = inventory.itemnum
Thanks