|
|
Posted by Hugo Kornelis on 11/30/06 21:40
On 29 Nov 2006 01:39:15 -0800, Mohd Al Junaibi wrote:
>Thanks Ed,
>
>
>> We need to see these calculations.
>
>They are 9 calculations. Too large to post. So I'll post one of them
>(in this case @StrReceipts):
(snip)
Hi Mohd,
The most straightforward conversion to a setbased solution would be to
replace each variable in the final INSERT statement with a subquery that
is easily adopted from these calculations, like this:
INSERT INTO invsum
(STORELOC, RECEIPTS, RETURNS, TRANSFERIN, WRITEON,
ISSUES, TRANSFEROUT, WRITEOFF, OPENBAL, CALCLOSEBAL,
INVENTORYVAL, OPENBALDATE, CLOSEBALDATE )
SELECT inv.Location, &#&#&#&#&#&#&#
-- Calculation for Receipts below
(SELECT sum(LOADEDCOST)
FROM Matrectrans A , item B
WHERE issuetype = 'RECEIPT'
AND Tostoreloc = inv.Location
AND issue = 'N'
AND transdate > @startDate
AND transdate <= @endDate
AND A.itemnum = B.itemnum
AND ( B.in9 <> 'I'
OR B.in9 IS NULL )
AND ( A.gldebitacct NOT LIKE '%249001'
OR A.gldebitacct IS NULL )
AND ( A.gldebitacct NOT LIKE '%249002'
OR A.gldebitacct IS NULL )
AND ( A.glcreditacct NOT LIKE '%249001'
OR A.glcreditacct IS NULL )
AND ( A.glcreditacct NOT LIKE '%249002'
OR A.glcreditacct IS NULL ) )
-- Calculation for Returns below
(SELECT ....)
-- Calculation for TransferIn below
(SELECT ....)
(etc)
-- Calculation for CloseBalDate below
(SELECT ....)
FROM Item AS i, Inventory AS inv, Companies AS cmp
WHERE i.itemnum = inv.itemnum
AND ( i.in9 <> 'I' OR i.in9 IS NULL )
AND inv.location = cmp.company
AND inv.location <> 'WHHQ'
AND cmp.registration2 NOT IN
('MAIN','DISPOSAL','SURPLUS','PROJECT','COMPLETED')
GROUP BY inv.location;
However, this is only the start. You'll robably see some performance
gain, but not much. The real fun starts when you start comparing the
subqueries for the various calculations. Chances are that many have
elements in common - and in that case, you can get a tremendous
performance gain by moving the common elements from the subqueries to
the outer query.
Unfortunately, since you chose not to post the other calculations, I
can't offer any more specific advice that this.
--
Hugo Kornelis, SQL Server MVP
[Back to original message]
|