SQL query get latest from multiple rows - too slow

Question

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

It would help if you posted the structure of the tables involved, how they link together, and what you want the query to do. — Anthony Grist, Apr 04 '14 at 09:16
Please can you also add an image of the execution plan. I suspect this is because you don't have sufficient indexes setup — sarin, Apr 04 '14 at 09:17

score 1 · Answer 1 · answered Apr 04 '14 at 10:05

There's a lot of work to do for the dbms with all those sub-queries per item. So don't expect this to be fast. I suppose you already made sure the tables have the appropriate indexes.

So the only chance I see to speed this up is to make the dbms find those 5000 records as quick as possible. As it is, you don't use order by, so you simply get 5000 random inventories. For one item in the result list you may get all inventories or just some of them, it's all random.

Knowing so, you can select 5000 random inventories and only when having found these, join with item:

select 
  item.commoditygroup, 
  item.itemnum, 
  item.description, 
  item.commodity, 
  inv5000.location, 
  inv5000.siteid, 
  ...
from (select top 5000 itemnum, location, siteid, from inventory) inv5000
inner join item on item.itemnum = inv5000.itemnum;

Maybe this changes something. Maybe not. Good luck!

score 1 · Answer 2 · answered Apr 07 '14 at 16:07

Thanks everyone - I ended up with this query which works much quicker (it gets 10000 records in 2 seconds)

select distinct inventory.itemnum, item.commodity, item.commoditygroup, inventory.siteid, inventory.location, inventory.itemsetid, 
item
.description as itemDesc, inventory.issueunit as iUnit,


(select max(actualdate) from matusetrans 

where issuetype in (select value from synonymdomain where domainid = 'ISSUETYP' and maxvalue = 'ISSUE') 

and siteid=inventory.siteid and storeloc=inventory.location and itemsetid=inventory.itemsetid and itemnum=inventory.itemnum) as useTransDate,


(select max(actualdate) from matrectrans 

where issuetype in (select value from synonymdomain where domainid = 'ISSUETYP' and maxvalue = 'RECEIPT')

and siteid=inventory.siteid and tostoreloc=inventory.location and itemsetid=inventory.itemsetid and itemnum=inventory.itemnum 

) as recTransDate 
from

inventory inner join item on inventory.itemsetid = item.itemsetid and inventory.itemnum = item.itemnum 
where

item.itemtype in ( select value from synonymdomain where domainid = 'ITEMTYPE' and maxvalue in ('ITEM') ) 

and inventory.status not in ( select value from synonymdomain where domainid='ITEMSTATUS' and maxvalue in ('OBSOLETE') )

score 0 · Answer 3 · edited May 23 '17 at 12:05

For your unitcost sub-selects, use matrectransid and matusetransid instead. Those ID fields are indexed and will make your query faster:

(Select m1.unitcost from matrectrans m1 where m1.matrectransid =
(select max(m2.matrectransid) from matrectrans m2 
where item.itemnum = m2.itemnum and m2.issuetype='RECEIPT' and linecost <> 0))
ReceivedUnitCost

(Select m3.unitcost from matusetrans m3 where m3.matusetransid = 
(select max(m4.matusetransid) from matusetrans m4 
where item.itemnum = m4.itemnum and m4.issuetype='ISSUE' and linecost <> 0)) 
IssuedUnitCost

In my database, greater ID value correlates to a more recent transdate. You can verify yourself using the Lag Function:

This is for Oracle, but SQL Server has a LAG function as well.

select matusetransid, 
to_char(transdate, 'DD-MON-YYYY HH24:MI:SS.ss') transdate,  
LAG (to_char(transdate, 'DD-MON-YYYY HH24:MI:SS.ss'),1) 
over (order by transdate desc) as prev_transdate, 
(case when transdate > LAG (transdate,1) over (ORDER BY matusetransid desc) 
then 1 else 0 end) as test 
from matusetrans
where 4 = 1
order by matusetransid desc;

If the above query returns any rows that means some data is not in order (interpolated).

Just a side note on your result set: you are selecting inventory.location and inventory.siteid, but your sub-selects do not match on those fields. If your itemnum belongs to only one siteid and location (storeroom), the current query should be fine. Otherwise, your results may not be accurate.

Thanks very much for your reply.. I worked through it and have an answer — user3494666, Apr 07 '14 at 16:03

SQL query get latest from multiple rows - too slow

3 Answers3