Hello Sir

I just want to view the Transaction of the 3 tables at first the result of query display fast but when it data inserted increase takes too long to display and shows a message that says timeout expired...

How could i simplify and speedup this query to display data very fast..

The tblitems has a primarykey ITEMID
tblitemledger has no primary key as well as the tblwarehousing but they have a automatic number pkeyno identity auto
the sample database looks like this




TBLITEMS(5000 records)
=========================================================================================================
ITEMID |DESCRIPTION |CONVERSION |BIGUNIT |SMALLUNIT |SECTION |COSTBIG |COSTSMALL |BULKPRICE |SELLINGPRICE
100 A 10 pck pcs 2 50 5 55 7
200 A 10 pck pcs 2 50 5 55 7
300 A 10 pck pcs 2 50 5 55 7
400 A 10 pck pcs 2 50 5 55 7
500 A 10 pck pcs 2 50 5 55 7

TBLWAREHOUSING(10000 Records doubled the tblitems)
ITEMID|DEPTCODE
================
100 DEPTA
200 DEPTA
300 DEPTA
400 DEPTA
500 DEPTA
100 DEPTB
200 DEPTB
300 DEPTB
400 DEPTB
500 DEPTB

TBLITEMLEDGER(20 0000 of records)
================
ITEMID|QTYIN|QTYOUT|TOTSALESCOST|TOTSALESPRICE TRANSDATE
100 1 0 5 5 01/01/2012
100 1 0 5 5 01/01/2012
100 1 0 5 5 02/02/2012
200 1 0 5 5 02/02/2012
200 1 0 5 5 03/03/2012
200 1 0 5 5 03/04/2012
200 1 0 5 5 04/01/2012
300 1 0 5 5 04/01/2012
300 1 0 5 5 05/01/2012
300 1 0 5 5 05/01/2012
400 1 0 5 5 06/01/2012
400 1 0 5 5 06/01/2012
100 1 0 5 5 07/06/2012
100 1 0 5 5 07/06/2012
200 1 0 5 5 07/06/2012

I Just made a sample data that looks like on my records, when i execute the query it takes too long that result for timeout in my application and in sql server 2005 IDE. executing and processing too long that cause me to stop the execution
Hope it helps you figure out my concern.


select i.itemid,i.description,
isnull(i.conversion,1)as conversion,
isnull(i.bigunit,'PCS')AS BigUnit,
isnull(i.itemsection,0)as Itemsection,
isnull(i.smallunit,'PCS')as SmallUnit,
isnull(isnull(i.costsmall,0),0) as costsmall,isnull(isnull(i.costBig,0),0) as costBig,
isnull(isnull(i.bulkprice,0),0) as bulkPrice,isnull(isnull(i.sellingprice,0),0) as sellingprice,
[QTYIN] = ISNULL((select SUM(QTYIN*CONVERSION) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' and TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0'),
[QTYOUT] = ISNULL((select SUM(QTYOUT*CONVERSION) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' and TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0'),
[BALANCE]=ISNULL((select SUM(QTYIN*CONVERSION)-SUM(QTYOUT*CONVERSION) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' AND TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0')
[PurchaseCost]=ISNULL((select SUM(ISNULL(TotPurchaseCost,0)) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' AND TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0'),
[SalesCost]=ISNULL((select SUM(ISNULL(TotSalesCost,0)) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' AND TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0'),
[SalesPrice]=ISNULL((select SUM(ISNULL(TotSalesPrice,0)) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' AND TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0')
FROM tblitems i inner join tblwarehousing w on i.itemid=w.itemid where w.deptcode='GROCERY' order by I.DESCRIPTION


I really appreciate all your help here..

Thank you in advance.

First - that doesn't even Parse correctly.
Second - You have a Field in TBLITEMS called 'Description' - that is a reserved word so shouldn't really be used, if you can't change it to something like 'Desc' then it needs to be surrounded by square brackets in your TSQL - [Description]
Third - Your query includes a reference to a Field 'TotPurchaseCost' in TBLITEMLEDGER, but you don't show that in your table design - does it exist?
And Fourth - assuming that 'TotPurchaseCost' DOES exist in TBLITEMLEDGER then try this:-

SELECT i.itemid,i.[description], ISNULL(i.conversion,1)As conversion, ISNULL(i.bigunit,'PCS')As BigUnit, ISNULL(i.itemsection,0)As Itemsection, ISNULL(i.smallunit,'PCS')As SmallUnit, ISNULL(i.costsmall,0) As costsmall, ISNULL(i.costBig,0) As costBig, ISNULL(i.bulkprice,0) As bulkPrice, ISNULL(i.sellingprice,0) As sellingprice, ISNULL(A.TotalIN,0) As TotalIN, ISNULL(A.TotalOUT,0) As TotalOUT, ISNULL(A.Balance,0) As Balance, ISNULL(A.PurchaseCost,0) As PurchaseCost, ISNULL(A.SalesCost,0) As SalesCost, ISNULL(A.SalesPrice,0) As SalesPrice FROM tblitems i INNER JOIN (SELECT w.deptcode, L.itemid,  SUM(L.QTYIN * S.CONVERSION) As TotalIN, SUM(L.QTYOUT * S.CONVERSION) As TotalOUT, ISNULL(SUM(L.QTYIN * S.CONVERSION), 0) - ISNULL(SUM(L.QTYOUT * S.CONVERSION),0) As Balance, SUM(ISNULL(TotPurchaseCost,0)) As PurchaseCost,  SUM(ISNULL(TotSalesCost,0)) As SalesCost, SUM(ISNULL(TotSalesPrice,0)) As SalesPrice FROM TBLITEMLEDGER L INNER JOIN tblitems S ON  L.itemid = S.itemid INNER JOIN tblwarehousing w on L.itemid = w.itemid WHERE (L.TRANSDATE<='7/6/2012') GROUP BY  w.deptcode, L.itemid HAVING  (w.deptcode='GROCERY') )A ON  i.itemid =  A.itemid ORDER BY i.[description]

Depending on what Date Format you are using, and whether or not you intend passing the date in to the Procedure as a Parameter, you may need to put an explicit conversion into the 'WHERE' clause to make it work :-
WHERE (L.TRANSDATE<=CONVERT(DATETIME, @DateParam , 102))




____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Thank you very very  much sir Geofs..

Sorry i did not included some other table but it exist on table.

May i request again to help me out how to include the one column

that looks like this

ITEMID     BEGSOH    IN       OUT       BAL CONVERSION|BIGUNIT|SMALLUNIT|ITEMSECTION|COSTSMALL|COSTBIG|BULKPRICE|SELLINGPRICE|PURCHASECOST|SALESCOST|SALESPRICE
1001          5               5        2        8                                                                                                                          
1002          10             3        2        11                                                                                                                          
1003          25           15       10       30                                                                                                                          


This is the same with my post but i just want to add the Beginning Stock on hand.

the value of BEGSoh is beyond the selected date range

it looks like


select [DEPTCODE]=a.DEPTCODE,
[ITEMID] = a.itemid,
[ITEMDESC] = isnull(i.description,'UNIDENTIFIED'),
[CONVERSION] =isnull(i.conversion,1),
[BEG_SOH] = ISNULL((select SUM(QTYIN*CONVERSION)-SUM(QTYOUT*CONVERSION) FROM TBLITEMLEDGER WHERE  DEPTCODE=a.DEPTCODE and (transdate<'7/1/2012') and  itemid = A.ITEMID),'0'),
[DEBIT] = ISNULL((select SUM(QTYIN*CONVERSION) FROM TBLITEMLEDGER WHERE  DEPTCODE=a.DEPTCODE and (transdate>='7/1/2012' AND TRANSDATE<='7/31/2012') and  itemid = A.ITEMID),'0'),
[CREDIT] = ISNULL((select SUM(QTYOUT*CONVERSION) FROM TBLITEMLEDGER WHERE  DEPTCODE=a.DEPTCODE and (transdate>='7/1/2012' AND TRANSDATE<='7/31/2012') and itemid = A.ITEMID),'0'),
[SOH]=ISNULL((select SUM(QTYIN*CONVERSION)-SUM(QTYOUT*CONVERSION) FROM TBLITEMLEDGER WHERE  DEPTCODE=a.DEPTCODE and (transdate>='7/1/2012' AND TRANSDATE<='7/31/2012') and itemid = A.ITEMID),'0'),
[PurchaseCost]=ISNULL((select SUM(ISNULL(TotPurchaseCost,0)) FROM TBLITEMLEDGER WHERE  DEPTCODE=a.DEPTCODE and (transdate>='7/1/2012' AND TRANSDATE<='7/31/2012')  and itemid = A.ITEMID),'0'),
[SalesCost]=ISNULL((select SUM(ISNULL(TotSalesCost,0))  FROM TBLITEMLEDGER WHERE  DEPTCODE=a.DEPTCODE and (transdate>='7/1/2012' AND TRANSDATE<='7/31/2012') and itemid = A.ITEMID),'0'),
[SalesPrice]=ISNULL((select SUM(ISNULL(TotSalesPrice,0))  FROM TBLITEMLEDGER WHERE   DEPTCODE=a.DEPTCODE and (transdate>='7/1/2012' AND TRANSDATE<='7/31/2012') and itemid = A.ITEMID),'0')
FROM tblwarehousing A left outer join tblitems i on a.itemid=i.itemid
WHERE A.DEPTCODE='GROCERY'
  order by [ITEMDESC]



balance=(begsoh+debit)-credit
Please check me help it out.


Thank you sir in advance





[Edited by VYX on 01-08-2012 at 03:15 PM GMT]

This is a bit more complicated, as it is always possible that you could have items currently in stock that had no beginning stock, and items that had a beginning stock level but for which you now have no stock.
So what we need to do is get a recordset for your beginning stock and another recordset for your ending stock, then make a full join of the 2 recordsets so that you get all items from which you can then select into a final recordset. Because ITEMID and ITEMDESC may be NULL in the beginning stock recordset (A) we need to check for this and return that value from the ending stock Recordset (B) where that is the case, replacing a possible NULL in the B Recordset with your defaults where necessary.
So it would look like this:-

SELECT ISNULL(A.itemid, B.itemid) As ITEMID, ISNULL(A.[description], ISNULL(B.[description],'UNIDENTIFIED')) As ITEMDESC, ISNULL(A.conversion, ISNULL(B.conversion,1)) As conversion, ISNULL(A.deptcode, ISNULL(B.deptcode, 'GROCERY')) As DEPTCODE, ISNULL(A.BEG_SOH, 0) As BEG_SOH, ISNULL(B.DEBIT, 0) As DEBIT, ISNULL(B.CREDIT, 0) As CREDIT, ISNULL(B.SOH, 0) As SOH, ISNULL(B.PurchaseCost, 0) As PurchaseCost, ISNULL(B.SalesCost, 0) As SalesCost, ISNULL(B.SalesPrice, 0) As SalesPrice FROM (SELECT w.deptcode, L.itemid, ISNULL(i.[description],'UNIDENTIFIED') As ITEMDESC, ISNULL(i.conversion,1) As conversion, ISNULL(SUM(L.QTYIN *  ISNULL(i.conversion,1)), 0) - ISNULL(SUM(L.QTYOUT * ISNULL(i.conversion,1)),0) As BEG_SOH FROM TBLITEMLEDGER L INNER JOIN tblitems i ON  L.itemid = i.itemid INNER JOIN tblwarehousing w on L.itemid = w.itemid WHERE (L.TRANSDATE < '7/1/2012') GROUP BY  w.deptcode, L.itemid HAVING  (w.deptcode='GROCERY') )A   FULL OUTER JOIN (SELECT w.deptcode, L.itemid, ISNULL(i.[description],'UNIDENTIFIED') As ITEMDESC, ISNULL(i.conversion,1) As conversion,   SUM(L.QTYIN *  ISNULL(i.conversion,1)) As DEBIT, SUM(L.QTYOUT *  ISNULL(i.conversion,1)) As CREDIT, ISNULL(SUM(L.QTYIN * ISNULL(i.conversion,1)), 0) - ISNULL(SUM(L.QTYOUT *  ISNULL(i.conversion,1)),0) As SOH, SUM(ISNULL(TotPurchaseCost,0)) As PurchaseCost,  SUM(ISNULL(TotSalesCost,0)) As SalesCost, SUM(ISNULL(TotSalesPrice,0)) As SalesPrice FROM TBLITEMLEDGER L INNER JOIN tblitems i ON  L.itemid = i.itemid INNER JOIN tblwarehousing w on L.itemid = w.itemid WHERE (L.TRANSDATE >= '7/1/2012') AND  (L.TRANSDATE < '8/1/2012') GROUP BY  w.deptcode, L.itemid HAVING  (w.deptcode='GROCERY') )B ON  A.itemid =  B.itemid ORDER BY ITEMDESC

This should work for you - but as I do not have your tables I cannot test it. However, if you look at how the resulting recordset has been built from the 2 inner recordsets you should be able to see the theory behind what is going on. The effect of this is that you are only making 2 calls to the database tables before making the final select, whereas your method is making 8.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.