sql server - Optimized query for a subquery in sql -

i made query inventory of products follows:

select      b.productid, c.productname,     (select           case              when sum(qty) null                 0                 else sum(qty)           end                 invoicedetails                 productid = b.productid) sold,      (select           case              when sum(qtyreceive) null                 0                 else sum(qtyreceive)           end                 purchaseorderdetails                 productid = b.productid) stocks,      ((select             case                when sum(qtyreceive) null                   0                   else sum(qtyreceive)             end                     purchaseorderdetails                     productid = b.productid) -        (select             case                when sum(qty) null                   0                   else sum(qty)             end                     invoicedetails                     productid = b.productid)) remainingstock        invoicedetails   right join      purchaseorderdetails b on a.productid = b.productid   inner join      products c on b.productid = c.productid   group      b.productid, c.productname   

this query returns data want, , runs fine in desktop, when deploy application runs query on lower specs laptop, slow , causes laptop hang. need on how optimize query or maybe change make more efficient... in advance

this data of invoicedetails table

data purchaseorderdetails table

data products table

so i've taken out subqueries in select, don't think these necessary @ all. i've moved around joins , given better aliases tables;

select      b.productid,      c.productname,     isnull(sum(id.qty),0) sold,      isnull(sum(pod.qtyreceive),0) stocks,     isnull(sum(pod.qtyreceive),0) - isnull(sum(id.qty),0) remainingstock   purchaseorderdetails pod inner join products pr      on pr.productid = pod.productid   left join invoicedetails id      on id.productid = pod.productid  group     pod.productid, pr.productname   

you joining 2 tables don't need subqueries in select @ all. i've wrapped sum in isnull ensure there no null errors.

i'd suggest using set statistics time,io on @ beginning of code (with off command @ end). copy of text 'messages' tab statisticsparser.com. both queries , compare, check total cpu time , logical reads, want these both lower better performance. i'm betting logical reads drop new query.

edit

ok, i've put new query based upon sample data. i've used fields need query it's simpler example.

sample data

create table #invoicedetails (productid int, qty int) insert #invoicedetails (productid,qty) values (3,50),(1,0),(2,1),(1,12),(2,1),(3,1),(1,1),(2,1),(1,1),(2,1)  create table #purchaseorderdetails (productid int, qty int) insert #purchaseorderdetails (productid, qty) values (1,100),(2,20),(4,10),(1,12),(5,12),(4,12),(3,12),(2,20),(3,20),(4,20),(5,20)  create table #products (productid int, productname varchar(20)) insert #products (productid, productname) values (1,'sample product'),(2,'dye ink cyan'),(3,'test product 1'),(4,'test product 2'),(5,'test product 3'),(1004,'testing product') 

for this, here output of original query

productid   productname     sold    stocks  remainingstock 1           sample product  14      112     98 2           dye ink cyan    4       40      36 3           test product 1  51      32      -19 4           test product 2  0       42      42 5           test product 3  0       32      32 

this re-written query i've used. note, there no subqueries within select statement, they're within joins should be. see we're aggregating in subqueries don't need in outer query too.

select      pod.productid,      pr.productname,     isnull(id.qty,0) sold,      isnull(pod.qty,0) stocks,     isnull(pod.qty,0) - isnull(id.qty,0) remainingstock   #products pr inner join (select productid, sum(qty) qty #purchaseorderdetails group productid) pod     on pr.productid = pod.productid   left join (select productid, sum(qty) qty #invoicedetails group productid) id      on id.productid = pr.productid  

and new output

productid   productname     sold    stocks  remainingstock 1           sample product  14      112     98 2           dye ink cyan    4       40      36 3           test product 1  51      32      -19 4           test product 2  0       42      42 5           test product 3  0       32      32 

which matches original query.

i'd suggest trying query on machines , seeing performs better, try statistics time,io command mentioned previously.