Agent Job never ending
-
Hello community,
I try is to calculate the cost of goods per warehouse and per product by using the FIFO method, per warehouse per product and store the results in a table.
I have a table called ITEMTRANSACTIONS, which has the transactions for all the products and for all the warehouses, and another table called PRODUCTS with the transactions for some of the products.
PRODUCTS table structure:
I have created a job called FIFO_MAIN, which has the following steps:
- Step 1 to 6: Updating some columns in the ITEMTRANSACTIONS table.
- Step 7: Update the data in the PRODUCTS table (using MERGE), using the ITEMTRANSACTIONS table as the source.
- Step 8: Delete the data in the RESULTS table.
- Step 9: Calculate values using data from the PRODUCTS table and enter results in the RESULTS table (using INSERT INTO).
- Step 10: Update the data type of some columns in the RESULTS table.
- Step 11: Update FIFO price column in the PRODUCTS table (using UPDATE TABLE SET COLUMN).
In step 9 I use WITH where I create the tables:
- sample_data where I pull the data from the table. I use the WHERE clause to only take the rows related to the MAIN warehouse.
- Table_1 (which pulls the data from sample_data), Table_2 and Table_3, which use data each from the previous one to calculate some columns.
- WITH clause closes and becomes INSERT INTO (columns of the RESULTS table) and SELECT (columns of Table_3) + 2 calculated columns which are a simple sum and a multiplication.
The code of step 9 is the following:
SET QUOTED_IDENTIFIER ON;
BEGIN TRY
BEGIN TRANSACTION;
--Calculate FIFO cost and Insert into RESULTS table
WITH
sample_data AS ( --pull data from table PRODUCTS
SELECT [GXDATE], [TIMESPL], [ITID], [WHCODE] AS GXCODE, [GXITEMCODE], [TRANSACTION_TYPE], [GXAQTY], [GXPRICE] AS NPRICE,
CASE WHEN ([TRANSACTION_TYPE] = 'Sale' OR [TRANSACTION_TYPE] = 'Export') THEN 'OUT'
WHEN ([TRANSACTION_TYPE] = 'Start Inventory' OR [TRANSACTION_TYPE] = 'Purchase' OR [TRANSACTION_TYPE] = 'Import') THEN 'IN'
ELSE '' END AS TRA
FROM [PRODUCTS]
WHERE [PRODUCTS].[WHCODE] IN ('MAIN','SECOND') –warehouses we want
),
setup AS ( --create sorter column for IN and for OUT transactions
SELECT [GXDATE], [TIMESPL], [ITID], [GXCODE], [GXITEMCODE], [TRANSACTION_TYPE], TRA, [GXAQTY], [NPRICE],
COALESCE(
[sorter1]
,0)
+ CASE WHEN (TRA <> 'OUT') THEN 0.5 ELSE [GXAQTY] END
AS sorter
FROM sample_data AS main
CROSS APPLY (
SELECT
SUM([GXAQTY])
AS sorter1
FROM sample_data AS sub
WHERE
sub.[GXCODE] = main.[GXCODE]
AND
sub.GXITEMCODE = main.GXITEMCODE --Per product
AND sub.[TRA] = main.[TRA]
AND sub.[TIMESPL] < main.[TIMESPL]
) AS A
),
net_inventory AS ( --first calculations about inventory and prices (cost for each output)
SELECT s.*,
COALESCE([NETIN1],0) AS NETIN,
COALESCE([NETINB4s],0) AS NETINB4,
(CASE WHEN [ITID$2] IS NOT NULL THEN [ITID$2] ELSE [ITID$1] END) AS ITID$,
(CASE WHEN [OUTID2] IS NOT NULL THEN [OUTID2] ELSE [OUTID] END) AS OUTID,
(CASE WHEN [IDID2] IS NOT NULL THEN [IDID2] ELSE [IDID] END) AS IDID,
COALESCE(
(CASE WHEN [next_TRA1] IS NOT NULL THEN [next_TRA1] ELSE [next_TRA2] END)
,'IN') AS next_TRA,
(CASE WHEN [next_QTY1] IS NOT NULL THEN [next_QTY1] ELSE [next_QTY2] END) AS next_QTY
FROM setup AS s
CROSS APPLY (
SELECT TOP 1 SUM(CASE WHEN ss.[TRA] = 'OUT' THEN -ss.[GXAQTY] ELSE ss.[GXAQTY] END) AS NETINB4s
FROM setup AS ss
WHERE
ss.[GXCODE] = s.GXCODE –Per warehouse
AND
ss.GXITEMCODE = s.GXITEMCODE –Per product
AND ss.[sorter] <= s.[sorter]
AND ss.[TIMESPL] <> s.[TIMESPL]
) AS B
CROSS APPLY (
SELECT TOP 1 (CASE WHEN ss2.[TRA] = 'OUT' THEN CONVERT(DATETIME, ss2.TIMESPL, 126) END) AS OUTID
FROM setup AS ss2
WHERE
ss2.GXCODE = s.GXCODE AND
ss2.GXITEMCODE = s.GXITEMCODE AND
ss2.sorter >= s.sorter
AND ss2.[TIMESPL] > s.TIMESPL
ORDER BY ss2.GXDATE, ss2.TIMESPL
) AS C
OUTER APPLY (
SELECT TOP 1 (CASE WHEN REP2.[TRA] = 'OUT' THEN CONVERT(DATETIME, REP2.TIMESPL, 126) END) AS OUTID2
FROM setup AS REP2
WHERE
REP2.[TRA] = 'OUT' AND
REP2.GXCODE = s.GXCODE AND
REP2.GXITEMCODE = s.GXITEMCODE AND
REP2.sorter >= s.sorter
AND REP2.TIMESPL <> s.TIMESPL
ORDER BY
REP2.TIMESPL
) AS REP
CROSS APPLY (
SELECT TOP 1 (CASE WHEN ss2.[TRA] = 'OUT' THEN CONVERT(NVARCHAR(36), ss2.ITID, 126) END) AS IDID
FROM setup AS ss2
WHERE
ss2.GXCODE = s.GXCODE AND
ss2.GXITEMCODE = s.GXITEMCODE AND
ss2.sorter >= s.sorter
AND ss2.TIMESPL > s.TIMESPL
ORDER BY
ss2.TIMESPL
) AS CID
OUTER APPLY (
SELECT TOP 1 (CASE WHEN REP2.[TRA] = 'OUT' THEN CONVERT(NVARCHAR(36), REP2.ITID, 126) END) AS IDID2
FROM setup AS REP2
WHERE
REP2.[TRA] = 'OUT' AND
REP2.GXCODE = s.GXCODE AND
REP2.GXITEMCODE = s.GXITEMCODE AND
REP2.sorter >= s.sorter
AND REP2.TIMESPL <> s.TIMESPL
ORDER BY
REP2.TIMESPL
) AS REPID
CROSS APPLY (
SELECT TOP 1
SUM(CASE WHEN n.[TRA] = 'OUT' THEN -n.[GXAQTY] ELSE n.[GXAQTY] END) AS NETIN1
FROM setup AS n
WHERE
n.[GXCODE] = s.GXCODE
AND
n.GXITEMCODE = s.GXITEMCODE
AND n.sorter <= s.sorter
) AS D
CROSS APPLY (
SELECT TOP 1
(CASE WHEN (ii.[NPRICE] IS NOT NULL AND ii.[NPRICE]<>0 AND ii.TRA<>'OUT') THEN CONVERT(DATETIME, ii.[TIMESPL], 126) END) AS ITID$1
FROM setup AS ii
WHERE
ii.[GXCODE] = s.[GXCODE] AND
ii.GXITEMCODE = s.GXITEMCODE AND
ii.sorter <= s.sorter
AND ii.TIMESPL <= s.TIMESPL
ORDER BY
ii.TIMESPL DESC
) AS E
CROSS APPLY (
SELECT TOP 1
(CASE WHEN (ii2.[NPRICE] IS NOT NULL AND ii2.[NPRICE]<>0 AND ii2.TRA<>'OUT') THEN CONVERT(DATETIME, ii2.[TIMESPL], 126) END) AS ITID$2
FROM setup AS ii2
WHERE
ii2.[NPRICE] IS NOT NULL AND
ii2.[NPRICE] <> 0 AND
ii2.TRA <> 'OUT' AND
ii2.[GXCODE] = s.[GXCODE] AND
ii2.GXITEMCODE = s.GXITEMCODE AND
ii2.sorter <= s.sorter
AND ii2.TIMESPL <= s.TIMESPL
ORDER BY
ii2.TIMESPL DESC
) AS E2
CROSS APPLY (
SELECT TOP 1 L.TRA AS next_TRA1
FROM setup AS L
WHERE
L.[GXCODE] = s.[GXCODE]
AND
L.GXITEMCODE = s.GXITEMCODE
AND L.sorter >= s.sorter
AND L.TIMESPL <> s.TIMESPL
ORDER BY L.sorter,-- L.GXDATE,
L.TIMESPL
) AS ntra
CROSS APPLY (
SELECT TOP 1 L2.TRA AS next_TRA2
FROM setup AS L2
WHERE
L2.[GXCODE] = s.[GXCODE]
AND
L2.GXITEMCODE = s.GXITEMCODE
AND L2.sorter >= s.sorter
AND L2.TIMESPL >= s.TIMESPL
ORDER BY L2.sorter,
L2.TIMESPL
) AS ntra2
CROSS APPLY (
SELECT TOP 1 Q.[GXAQTY] AS next_QTY1
FROM setup AS Q
WHERE
Q.[GXCODE] = s.[GXCODE]
AND
Q.GXITEMCODE = s.GXITEMCODE
AND Q.sorter >= s.sorter
AND Q.TIMESPL <> s.TIMESPL
ORDER BY Q.sorter,
Q.TIMESPL
) AS nQ
CROSS APPLY (
SELECT TOP 1 Q2.[GXAQTY] AS next_QTY2
FROM setup AS Q2
WHERE
Q2.[GXCODE] = s.[GXCODE]
AND
Q2.GXITEMCODE = s.GXITEMCODE
AND Q2.sorter >= s.sorter
AND Q2.[TIMESPL] >= s.TIMESPL
ORDER BY Q2.sorter,
Q2.TIMESPL
) AS nQ2
),
rebalancer AS ( --last calculations match QTYs with Cost (last_in$)
SELECT s.*,
CASE
WHEN [TRA] = 'IN' AND [next_TRA] = 'IN' THEN [GXAQTY]
WHEN [TRA] = 'IN' AND [next_TRA] = 'OUT' THEN [next_QTY]-[NETINB4]
WHEN [TRA] = 'OUT' AND [next_TRA] = 'IN' THEN [NETIN]
WHEN [TRA] = 'OUT' AND [next_TRA] = 'OUT' THEN [next_QTY]
END AS QTY2,
(CASE WHEN [NPRICE2] IS NOT NULL THEN [NPRICE2] ELSE [NPRICE1] END) AS last_in$
FROM net_inventory AS s
CROSS APPLY (
SELECT TOP 1
(CASE WHEN (ii.[NPRICE] IS NOT NULL AND ii.[NPRICE]<>0 AND ii.TRA<>'OUT') THEN ii.[NPRICE] END) AS NPRICE1
FROM net_inventory AS ii
WHERE
ii.[GXCODE] = s.[GXCODE] AND
ii.GXITEMCODE = s.GXITEMCODE AND
ii.sorter <= s.sorter
AND ii.TIMESPL <= s.TIMESPL
ORDER BY
ii.TIMESPL DESC
) AS E
CROSS APPLY (
SELECT TOP 1
(CASE WHEN (ii2.[NPRICE] IS NOT NULL AND ii2.[NPRICE]<>0 AND ii2.TRA<>'OUT') THEN ii2.[NPRICE] END) AS NPRICE2
FROM net_inventory AS ii2
WHERE
ii2.[NPRICE] IS NOT NULL AND
ii2.[NPRICE] <> 0 AND
ii2.TRA <> 'OUT' AND
ii2.[GXCODE] = s.[GXCODE] AND
ii2.GXITEMCODE = s.GXITEMCODE AND
ii2.sorter <= s.sorter
ORDER BY
ii2.TIMESPL DESC
) AS E2
)
INSERT INTO RESULTS (GXCODE, GXITEMCODE, OUTID, IDID, FINALCOUNT, VALUE, UNIT$, TRATYPE) –Insert results into RESULTS table
SELECT s.GXCODE, s.GXITEMCODE, s.OUTID, s.IDID,
SUM(s.QTY2) AS FINALCOUNT,
SUM(s.QTY2*s.last_in$) AS VALUE,
CASE WHEN SUM(s.QTY2) <> 0 THEN SUM(s.QTY2*s.last_in$)/SUM(s.QTY2) ELSE 0 END AS UNIT$,
CASE WHEN TRATYPE1 IS NOT NULL THEN TRATYPE1 END AS TRATYPE –only for OUT transactions so we know if it is sale or export
FROM rebalancer AS s
CROSS APPLY (
SELECT TOP 1 TRTE.TRANSACTION_TYPE AS TRATYPE1
FROM rebalancer AS TRTE
WHERE
TRTE.TIMESPL = s.OUTID -- match transactions unique values
) AS TRATP1
GROUP BY s.GXCODE, s.GXITEMCODE, s.OUTID, s.IDID, TRATYPE1
ORDER BY 1, CASE WHEN s.OUTID IS NULL THEN 2 ELSE 1 END,
s.OUTID;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
ROLLBACK
END CATCHI run the FIFO_MAIN job at night using daily schedule. Each night it takes a total of 2 hours and 36 minutes at most to complete and the results were stored as expected in the RESULTS table.
The issue is:
I had to create another job (FIFO_SECOND) which also pulls data from the PRODUCTS table, but this time, for the SECOND warehouse. All steps were the same, except for step 9, where in sample_data in the WHERE clause WAREHOUSE=SECOND instead of WAREHOUSE=MAIN. This time, I use the RESULTS_SECOND table for the output results. The first time the FIFO_SECOND job ran, when it reached step 9, it got stuck. It took many hours appearing "in progress" in the history of the job until I forced it to stop.
What I tried:
I tried to clear the cache and run the FIFO_SECOND job again, but it still got stuck at step 9. Also, I ran the 2 jobs separately, but I still had the same result.
In the FIFO_MAIN job, which has no issues, I tried to replace the MAIN warehouse with the SECOND one in the WHERE clause, since this is the only difference and it got stuck again at step 9. When I replaced it again as it was at the beginning, the job ran normally again.
It looks like it has to do with the change in step 9. However, by making changes to any other step, eg in 2 where I update a column of the table ITEMTRANSACTIONS I changed the calculation method and yet the job never stuck to step 2.
What could be the reason of this never-ending job?
Is there a chance that having a different source for the table sample_data in these 2 jobs causing an issue? and / or because I am using the same source table (PRODUCTS)?
Any help or hint will be very much appreciated. Thank you in advance!
-
This question was also posted to microsoft.public.sqlserver.programming. For convenience I repeat my answer here:
All sorts of reasons:
1) Long-running query plan.
2) Infinite loop.
3) Blocking.
There may be more, but those are the ones that come to mind immeidately..
Since I don't know your tables or your code, I can't say what it might
be, but you could use my beta_lockinfo to investigate whether there is
any blocking. It will also show you the current statement. Running it repeatedly, can also some indication of the progress.
http://www.sommarskog.se/sqlutil/beta_lockinfo.html
If the job is running a loop of some sort, you may also have help of
Lee Tudor's sp_sqltrace, which is also on my web site:
http://www.sommarskog.se/sqlutil/sqltrace.html
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
-
I'll also add....
- Might need an update on statistics.
- Different or missing indexes.
- Skewed data.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply