asked on

Creating date based transactions

I have a table (DEPRECIATION) which has a start date, end date, number of months, cost and I need to create transactions for each item in this table that range from the start date to the end date, limited by number of months. I am current using a cross apply to a function which does the calculations and returns a table. This is extremely slow. I have 144000+ rows in the main table and left the process running overnight and it had still not completed. I need a solution that would be much faster if possible. Any help?

Here is one insert statement
INSERT INTO [DBO].[DEPRTRANSACTIONS]
([STOREID]
,[ITEMID]
,[DEPRDATE]
,[BORT]
,[DEPRAMOUNT]
,[RBV]
,[ACCUM]
,[WOAMT])
SELECT
       d.LocationID
      , D.ITEMID
      , SL.SLDATE
      , 'T'
      , SL.DEPRAMOUNT
      , SL.RBV
      , SL.ACCUM
      , SL.WOAMT
FROM DEPRECIATION D
CROSS APPLY FNC_CALCSL(D.COSTBASISTAX , D.STARTDATE, @ASOFDATE, D.WRITEOFFDATETAX, D.TAXLIFE) SL
WHERE D.TAXMETHOD = 'S' AND D.STARTDATE IS NOT NULL

and here is the function called

CREATE FUNCTION [dbo].[FNC_CALCSL] (@COST NUMERIC(8,2), @BEGSLDATE DATETIME, @ENDSLDATE DATETIME, @WODATE DATETIME, @NUMMONTHS INT)
RETURNS @SLTABLE TABLE (SLDATE DATETIME, DEPRAMOUNT NUMERIC(8,2), RBV NUMERIC(8,2), ACCUM NUMERIC(8,2), WOAMT NUMERIC(8,2))

AS
BEGIN

DECLARE @CURDATE DATETIME
DECLARE @CURMONTH DATETIME
DECLARE @MDEPR NUMERIC(8,2)
DECLARE @MDEPRUN NUMERIC(8,5)
DECLARE @RBV NUMERIC(8,2)
DECLARE @ACCUM NUMERIC(8,2)
DECLARE @WOAMT NUMERIC(8,2)
DECLARE @THISMONTH INT
DECLARE @DIFFAMT NUMERIC(18,5)

SET @CURDATE = @BEGSLDATE
SET @MDEPR = @COST / @NUMMONTHS

SELECT @MDEPRUN = ROUND(@COST / @NUMMONTHS, 5)
SET @DIFFAMT = @COST - (@MDEPRUN * @NUMMONTHS)
IF (@MDEPR * @NUMMONTHS) < @COST
      SET @MDEPR = @MDEPR + .01
SET @THISMONTH = 1
SET @RBV = @COST
SET @ACCUM = 0

IF @WODATE IS NOT NULL AND @WODATE < @ENDSLDATE
      SET @ENDSLDATE = @WODATE

WHILE @THISMONTH <= @NUMMONTHS
      BEGIN
            IF DATEPART(YY, @CURDATE) = DATEPART(YY, @WODATE) AND DATEPART(MM, @CURDATE) = DATEPART(MM, @WODATE)
                  BEGIN
                        SET @WOAMT = @RBV
                        SET @RBV = 0
                        SET @ACCUM = @COST - @WOAMT
                        SET @THISMONTH = @NUMMONTHS
                        SET @MDEPR = 0
                  END
            ELSE
                  BEGIN
                        SET @RBV = @RBV - @MDEPR
                        IF @RBV < 0
                              BEGIN
                                    SET @MDEPR = @MDEPR + @RBV
                                    SET @RBV = 0
                                    SET @ACCUM = @COST
                                    SET @WOAMT = 0
                              END
                        ELSE
                              BEGIN
                                    SET @ACCUM = @ACCUM + @MDEPR
                                    SET @WOAMT = 0
                              END
                  END
            INSERT INTO @SLTABLE VALUES (DATEADD(D, -1, DATEADD(M, DATEDIFF(M, 0, @CURDATE) + 1, 0)), @MDEPR, @RBV, @ACCUM, @WOAMT)
            SET @THISMONTH = @THISMONTH + 1
            SET @CURMONTH = DATEADD(MM, 1, @CURDATE)
            SET @CURDATE = @CURMONTH
      END

RETURN

END

Vitor Montalvão

Do you have any trigger in the DEPRTRANSACTIONS table?
What are the indexes created in DEPRECIATION table?

lburnsinmagnolia

ASKER

No triggers. The only index on the DEPRECIATION table is the primary key (ITEMID).

Vitor Montalvão

The only index on the DEPRECIATION table is the primary key (ITEMID).

It might not be enough since it will perform a clustered index scan. Your filter is WHERE D.TAXMETHOD = 'S' AND D.STARTDATE IS NOT NULL so at least create an index on those fields:

CREATE INDEX idxName ON DEPRECIATION (TAXMETHOD,STARTDATE)

Open in new window

After that run again the script and see if the performance improved.

lburnsinmagnolia

ASKER

Created the index. That didn't help.

Vitor Montalvão

Created the index. That didn't help.

Humm, seems to me to fast for you know that helped or not.
Did you stop the current process, created the index and the re start the script?

lburnsinmagnolia

ASKER

Yes, I did that, but in testing I think I found out that is not the problem. I have another function that tries to get the location of each item and that is the function that is taking too long. Here is what I'd like to do to test. The locating is in a function, but I'd like to get it in a sub query. I am having a problem with that.

What I want to do is by selecting the rowS in DEPRECIATION, if there is a row in TRANSFER table, I can return a column from TRANSFER, otherwise I need the LOCATIONID from DEPRECIATION. Here is what I have in a test case. But I can't figure out how to get the LOCATIONID from DEPRECIATION into the result.

SELECT TT.CURRENTLOC
FROM DEPRECIATION D
      cross apply (SELECT TOP 1
            CASE DIRECTION
                  WHEN 'I' THEN T.STOREID
                  ELSE T.LocationID
                  END AS CURRENTLOC
                         FROM TRANSFER T WHERE T.ItemID = D.ItemID AND T.TRANSFERTIME < '11/30/2016 23:59:59'
                        ORDER BY TRANSFERTIME DESC) TT
WHERE D.ITEMID = 101000002

Since the item I am looking at is not in TRANSFER, I get no results. In that case, I'd like to return the LOCATIONID from DEPRECIATION table for the item.

Thanks.

Larry

Vitor Montalvão

Ok, then this is another query.
I can see that you're using an ORDER BY clause and that's is really bad for a query performance. You can always improve an ORDER BY operation by creation an index with the same columns. In your case:

CREATE NONCLUSTERED INDEX IX_TransferTime
ON TRANSFER (TRANSFERTIME  DESC);

Open in new window

lburnsinmagnolia

ASKER

Ok,

I can create the index. But what about my problem of getting the correct storeid from either the transfer table or depreciation table. If there is no transfer record for an item, i want to use the location from the depreciation table and that query doesn't work if there isn't a transfer record.

Thanks

Vitor Montalvão

But what about my problem of getting the correct storeid from either the transfer table or depreciation table.

I didn't realize you have another problem besides the performance issue.
Can we take care first of the performance and then check the other issue?

lburnsinmagnolia

ASKER

I figured out the performance issue was calling the function in a cross apply. I moved the function into a sub query cross apply and the performance is now ok.

What I have now, is if there are no transfer records from the sub-query, I need to rettuen a column from another table

here is what I am looking at.

select itemid,
tt.currentloc
from depreciation d
cross apply ((
SELECT TOP 1
CASE DIRECTION
WHEN 'I' THEN T.STOREID
WHEN 'O' THEN T.LOCATIONID
END AS CURRENTLOC
FROM TRANSFER T
WHERE T.TRANSFERTIME <= '09/30/16'
AND T.ITEMID = 1009312
ORDER BY TRANSFERTIME DESC) tt

I need a return row from the cross apply on every call as I will be using the "CURRENTLOC" column it returns in subsequent cross apply statements. I would want the LOCATIONID from the depreciation table to be the fallback column.

Thanks.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Not very clear , but try this..

SELECT 
	  p.itemid,
      p.CURRENTLOC,
	  d.locationid 
FROM depreciation d
CROSS APPLY 
(
	   SELECT TOP 1 CASE DIRECTION   WHEN 'I' THEN T.STOREID          
									 WHEN 'O' THEN T.LOCATIONID
									 END AS CURRENTLOC
       FROM TRANSFER T
	   WHERE T.TRANSFERTIME <= '09/30/16' AND  T.ITEMID = 1009312	   
	   ORDER BY TRANSFERTIME DESC
)p

Open in new window

lburnsinmagnolia

ASKER

Ok,

I figured out the problem. The CROSS APPLY would not return a row if there was no TRANSFER matching the criteria. But I had to have a location for the subsequent sub queries. By changing to OUTER APPLY, if there were no TRANSFER matches, I got back the NULL value. Here is the full INSERT/SELECT I ended up with.

INSERT INTO [dbo].[deprtemp]
([itemid]
,[storeid]
,[modelid]
,[serial_number]
,[statusid]
,[statusdesc]
,[method]
,[disposed]
,[Cost]
,[CostBasis]
,[Purchased]
,[StartDate]
,[PMo]
,[PYr]
,[MonthlyDepr]
,[YTDDepr]
,[CurPeriodWO]
,[YtdWO]
,[TOTWO]
,[RBV]
,[Accumdepr]
,[WOMo]
,[WOYr])
SELECT D.ITEMID
, ISNULL(TT.CURRENTLOC, D.LOCATIONID)
, I.MODELID
, I.SERIAL_NUMBER
, S.STATUSID
, (SELECT DESCRIPTION FROM STATUS WHERE StatusID = S.STATUSID)
, @CALCMETHOD
, CASE @CALCMETHOD
WHEN 'B' THEN D.WRITEOFFDATEBOOK
ELSE D.WRITEOFFDATETAX
END
, I.COST
, CASE @CALCMETHOD
WHEN 'B' THEN D.COSTBASISBOOK
ELSE D.COSTBASISTAX
END
, I.PURCHASED
, D.STARTDATE
, DATEPART(MM, D.STARTDATE)
, DATEPART(YY, D.STARTDATE)
, ISNULL(GMODEPR.MONTHLYDEPR, 0) --(SELECT MONTHLYDEPR FROM DBO.FNC_GETMTDDEPR(D.ITEMID, D.LOCATIONID, @ASOFDATE, @CALCMETHOD , @USINGCORP))
, ISNULL(GYTDDEPR.YTDDEPR, 0) --(SELECT YTDDEPR FROM DBO.FNC_GETYTDDEPR(D.ITEMID, D.LOCATIONID, @ASOFDATE, @CALCMETHOD , @USINGCORP))
, ISNULL(GCURPERWOAMT.WOAmt, 0) --(SELECT CURPERIODWO FROM DBO.FNC_GETCURPERWODEPR(D.ITEMID, D.LOCATIONID, @ASOFDATE, @CALCMETHOD , @USINGCORP))
, ISNULL(GYTDDEPR.YTDWO, 0) --(SELECT YTDWO FROM DBO.FNC_GETYTDWODEPR(D.ITEMID, D.LOCATIONID, @ASOFDATE, @CALCMETHOD , @USINGCORP))
, ISNULL(GTOTWO.TOTWO, 0) --(SELECT TOTWO FROM DBO.FNC_GETTOTWODEPR(D.ITEMID, D.LOCATIONID, @ASOFDATE, @CALCMETHOD , @USINGCORP))
, ISNULL(GRBV.RBV, D.COSTBASISBOOK) --(SELECT RBV FROM DBO.FNC_GETRBVDEPR(D.ITEMID, D.LOCATIONID, @ASOFDATE, @CALCMETHOD , @USINGCORP))
, ISNULL(GRBV.Accum, 0) --(SELECT ACCUM FROM DBO.FNC_GETACCUMDEPR(D.ITEMID, D.LOCATIONID, @ASOFDATE, @CALCMETHOD , @USINGCORP))
, CASE @CALCMETHOD
WHEN 'B' THEN
CASE WHEN D.WRITEOFFDATEBOOK IS NULL THEN 0
ELSE DATEPART(MM, D.WRITEOFFDATEBOOK)
END
WHEN 'T' THEN
CASE WHEN D.WRITEOFFDATETAX IS NULL THEN 0
ELSE DATEPART(MM, D.WRITEOFFDATETAX)
END
END
, CASE @CALCMETHOD
WHEN 'B' THEN
CASE WHEN D.WRITEOFFDATEBOOK IS NULL THEN 0
ELSE DATEPART(YY, D.WRITEOFFDATEBOOK)
END
WHEN 'T' THEN
CASE WHEN D.WRITEOFFDATETAX IS NULL THEN 0
ELSE DATEPART(YY, D.WRITEOFFDATETAX)
END
END
FROM DEPRECIATION D
OUTER APPLY ((SELECT TOP 1
CASE DIRECTION
WHEN 'I' THEN T.STOREID
WHEN 'O' THEN T.LOCATIONID
END AS CURRENTLOC
FROM TRANSFER T
WHERE T.TRANSFERTIME <= @ASOFDATE
AND D.ITEMID = T.ITEMID
ORDER BY TRANSFERTIME DESC)) TT
OUTER APPLY (SELECT SUM(COALESCE(DT2.DEPRAMOUNT, 0)) AS MONTHLYDEPR
FROM DEPRTRANSACTIONS DT2
WHERE DT2.ITEMID = D.ITEMID
AND DT2.STOREID = ISNULL(TT.CURRENTLOC, D.LOCATIONID)
AND DT2.DEPRDATE = @ASOFDATE
AND DT2.BORT = @CALCMETHOD
) GMODEPR
OUTER APPLY (SELECT SUM(DT2.DEPRAMOUNT) AS YTDDEPR, SUM(DT2.WOAMT) AS YTDWO
FROM DEPRTRANSACTIONS DT2
WHERE DT2.ITEMID = D.ITEMID
AND DT2.STOREID = ISNULL(TT.CURRENTLOC, D.LOCATIONID)
AND DT2.DEPRDATE BETWEEN @BOYDATE AND @ASOFDATE
AND DT2.BORT = @CALCMETHOD) GYTDDEPR
OUTER APPLY (SELECT TOP 1 DT2.RBV, DT2.Accum
FROM DEPRTRANSACTIONS DT2
WHERE DT2.ITEMID = D.ItemID
AND DT2.STOREID = ISNULL(TT.CURRENTLOC, D.LOCATIONID)
AND DT2.DEPRDATE <= @ASOFDATE
AND DT2.BORT = @CALCMETHOD
ORDER BY DT2.DEPRDATE DESC) GRBV
OUTER APPLY (SELECT TOP 1 DT2.WOAMT
FROM DEPRTRANSACTIONS DT2
WHERE DT2.ITEMID = D.ITEMID
AND DT2.STOREID = ISNULL(TT.CURRENTLOC, D.LOCATIONID)
AND DT2.DEPRDATE = @ASOFDATE
AND DT2.BORT = @CALCMETHOD) GCURPERWOAMT
OUTER APPLY (SELECT SUM(DT2.WOAMT) AS TOTWO
FROM DEPRTRANSACTIONS DT2
WHERE DT2.ITEMID = D.ITEMID
AND DT2.STOREID = ISNULL(TT.CURRENTLOC, D.LOCATIONID)
AND DT2.DEPRDATE <= @ASOFDATE
AND DT2.BORT = @CALCMETHOD) GTOTWO

JOIN INVENTORY I ON
I.ITEMID = D.ITEMID
AND I.STOREID = ISNULL(TT.CURRENTLOC, D.LOCATIONID)
JOIN STATUS S ON
S.STATUSID = I.STATUSID
WHERE D.STARTDATE IS NOT NULL

As you can see, I used the ISNULL function on the sub query to make it switch to the locationid in depreciation.

Thanks for all the help.

Larry

Pawan Kumar

Cross APPLY works like INNER JOIN

AND OUTER apply will consider NULL records also like FULL Outer join...

Vitor Montalvão

Larry, any feedback for us?