@scott, we also had a consultant look at our system troubleshooting this - looked at everything we could possibly think of in SQL and found nothing to explain the problem. Every other SSRS report is lightning fast. We believe it is either an Epicor bug (something is very un-optimal with the report) or our data is bad in some way. I just tried unchecking “exclude items with 0 qty” but that didn’t help me… I’m going to try experimenting with other permutations of the parameters
This problem gets more interesting. Support said to do a Conversion Workbench - User Run #1420. UpdateTrailingSpaces.
This solved the problem in one company. No changes in the other company.
I was told to run this periodically if we are uploading data.
Still chasing the other company’s problem.
Scott
Check the case (upper vs lower) of the plant and warehouse ID’s. We had mixed cases(Mfgsys vs mfgsys, and Mfg vs mfg), and that was throwing off some processes.
Calvin
Could you decipher if I am covered or not?
–Our Plants have an numerical ID in the Plant file. 1,2 etc. Epicor consultants didn’t see a problem of continuing our logic when we moved into Epicor.
–All Warehouse IDs use CAPs in the Warehouse File. In Warehouse the Plant is numerical.
–The Epicor tech helping me did mention case sensitivity, but he made a comment of “our version does not have that problem”. 600.18
–I did notice something off. We never used the mfgsys plant. Right from the start it was ignored and removed from anyone’s views. We used the numerical IDs. SO mfgsys does not have any setup. No Defaults.
I have been able to narrow my problem down to a single Part Number making it fail – report works perfectly fine when I exclude that Part.
I tried running the trailing spaces conversion, Refresh Part Allocations, and Refresh PartBin QOH, and nothing’s working yet.
Check for other “odd characters” Like an “em dash” in stead of regular one.
– That’s an em-dash
- And that’s a regular dash
Also, check for Non-breaking spaces (ASCII 160) I don’t think the Trim functions remove those. So first replace them with regular spaces with
Replace(Fileds!PartDesc.Value,CHR(160),CHR(32))
@John_Mitchell, I am wondering if my issue is a similar performance problem due to # of lots. I narrowed my problem down to a single Part Number, and, surprise, it is the Part in our system with the most Lots (~55,000), PartBins (~1,000), and PartTran records (~350,000).
My issue is not tied to lots. One company uses lots. The problem in that company was corrected by running the fix listed above. The problem in the other company remains. I tried class, and each class ran consistently longer when counting how many parts in the class. We did find special characters in part tran file coming from manually entered Parts on PO receiving transactions. We sent up a copy of the database, but that has never been successful before.
Yeah, I am grasping at straws at this point… Like I said, my problem Part is our most “active” part in the system, but I tested our next few most “active” parts and there was no significant performance issue with them. That makes me a little more hopeful that the problem is with our data and not an unfixable flaw with the Stock Status itself.
Another interesting point is that the bottleneck does not seem to be SSRS whatsoever. Whatever happens before SSRS is “sent the print job” seems to be the culprit. If I look at System Monitor, I see the task take 7 hours, but in the SSRS logs it only takes a few seconds.
Are there any parts that used to be OTF (On The Fly) but recent changed to being added to the part table? If so, when SSR runs it might be getting confused by parttran records that exist for a given part number, trying to reconcile non-inventory transactions for that part, with the inventory related transactions.
Also, if the UOM used when the part was OTF, doesn’t match the UOM used after it was added to the part table, could cause problems.
Make a BAQ
- Linking Part to PartTran (Company = company, and PartNum = PartNum)
- With a table criteria on parttran on TranDate, to limit it to the same as the date you’re entering on SSR form.
- Output columns
PartTran.PartNum, andCOUNT(PartTran.TranNum)AS TranCount - Order By Calculated_TranCount (Descending)
This will give you a feeling for how many partran records the SSR process needs to work though.
Do any parts jump out with unexpectedly high tran counts?
Thank you for the suggestions. I had looked at PartTran, and my problem part does have the most, but I ran SSR for all the other parts in this last and had no performance issues. They each take minutes to run, as opposed to 7+ hours.
We don’t allow OTF parts, but that is a good thought.
We did have some PartTran data with erroneous future dates, and I thought that might have been the culprit, but we also have other parts with bad future dates too that are not making SSR fail.
So you’ve narrowed it down to a particular part(s) ??
Does the offending part run okay if it is just todays date used?
Still okay if you go back: 1 week, 1 month, 6 months, 1 year, etc …?
And just how far back are you trying to run it for?
Calvin
The count on transactions did not raise any flags. There are only 96,000 total records for this site in our life in Epicor.
The other questions did cause me to look at data closer.
In PartTran I do have:
special characters (%)
transactions without a PartTran.UM.
transactions where parts have different UM (I did use the process to change UM).
OTF entries that did use an existing part num.
Calvin
There are two different users in this conversation.
Tom has identified a part.
I have not.
Scott
@scott - You’ve not identified any specific parts, or you’ve determined that it doesn’t matter which part?
Does SSR run in an acceptable time, if you select just one part (preferably a new-ish part with few part transactions, and not lot or serial controlled)?
How about if you select all parts, but use todays date? And try it with the “Exclude Part with zero QOH” option selected.
FWIW - My test environment has 165,776 parttran records and SSR runs in about 15 - 25 seconds (15 with Excludeing zero QOH, and 25 when un-excluded).
Calvin
I have not identified a part that is causing the problem with the SSR.
- The SSR does not run in an acceptable time. We use “Exclude Part with zero QOH”, and let it run with the default “As of” showing today’s date. This takes 1 hour in one site. The other sites are acceptable.
- In my notes I recorded that at one time in this game, I received output in normal time when I unchecked the “Exclude Part with zero QOH” for this site. I cannot duplicate this test now.
- I have tried reports run on each individual Class, and all of them run considerably longer than the < 1 minute for a full site SSR pre upgrade. 10 minutes to 38 minutes. This correlates with the number of parts.
- Part Tran for this site has less than 100K records, but I don’t understand the logic the report uses. I don’t know where it starts pulling data to count.
- Total PartTran transactions across 2 companies with 14 total sites is 1.67M. This was not a problem under previous version.
Scott
SSR determines the Qty you had on hand on the specified date. It does this by taking the current QOH (from the PartBin records), and subtracts out all the transactions between now, and the specified date.
Do you have access to the Epicor Upgrades Portal (https://upgradeservices.epicor.com/) ?? There is a “ERP Analyzer” tool you can download and run on the App server. It will then do a bunch of checks on the DB, looking for any inconsistencies.
It’ll generate a report (which you have to view online), with a section called “Health Checks”, that will list some things that aren’t as expected, like:
@TomAlexander Take the part that is running the slowest and open a SQL Profiler session with the report running. You will be able to see the exact query that is being run and can see if any of your data isn’t matching the field type.
We reported PRB0205773 where parts with a large amount of lots would take about 10 minutes to make a single lot cost adjustment. The bug was fixed a few releases ago and I have a feeling this is the exact same issue but good luck getting Epicor to accept this one.
Here is the sql it seems to be stuck on:
SELECT TOP (@p__linq__18)
[Project1].[SysTime] AS [SysTime],
[Project1].[TranQty] AS [TranQty],
[Project1].[TranClass] AS [TranClass],
[Project1].[TranType] AS [TranType],
[Project1].[ExtCost] AS [ExtCost],
[Project1].[JobNum] AS [JobNum],
[Project1].[PackSlip] AS [PackSlip],
[Project1].[PONum] AS [PONum],
[Project1].[PartNum] AS [PartNum],
[Project1].[AfterBurUnitCost] AS [AfterBurUnitCost],
[Project1].[AfterLbrUnitCost] AS [AfterLbrUnitCost],
[Project1].[AfterMtlBurUnitCost] AS [AfterMtlBurUnitCost],
[Project1].[AfterMtlUnitCost] AS [AfterMtlUnitCost],
[Project1].[AfterSubUnitCost] AS [AfterSubUnitCost],
[Project1].[BurUnitCost] AS [BurUnitCost],
[Project1].[LbrUnitCost] AS [LbrUnitCost],
[Project1].[SubUnitCost] AS [SubUnitCost],
[Project1].[MtlBurUnitCost] AS [MtlBurUnitCost],
[Project1].[MtlUnitCost] AS [MtlUnitCost],
[Project1].[MtlMtlUnitCost] AS [MtlMtlUnitCost],
[Project1].[MtlBurdenUnitCost] AS [MtlBurdenUnitCost],
[Project1].[MtlSubUnitCost] AS [MtlSubUnitCost],
[Project1].[MtlLabUnitCost] AS [MtlLabUnitCost],
[Project1].[LotNum] AS [LotNum],
[Project1].[RMANum] AS [RMANum],
[Project1].[TranNum] AS [TranNum],
[Project1].[SysDate] AS [SysDate],
[Project1].[POLine] AS [POLine],
[Project1].[PORelNum] AS [PORelNum],
[Project1].[PackLine] AS [PackLine]
FROM ( SELECT [Project1].[SysDate] AS [SysDate], [Project1].[SysTime] AS [SysTime], [Project1].[TranNum] AS [TranNum], [Project1].[PartNum] AS [PartNum], [Project1].[TranClass] AS [TranClass], [Project1].[TranType] AS [TranType], [Project1].[TranDate] AS [TranDate], [Project1].[TranQty] AS [TranQty], [Project1].[MtlUnitCost] AS [MtlUnitCost], [Project1].[LbrUnitCost] AS [LbrUnitCost], [Project1].[BurUnitCost] AS [BurUnitCost], [Project1].[SubUnitCost] AS [SubUnitCost], [Project1].[MtlBurUnitCost] AS [MtlBurUnitCost], [Project1].[ExtCost] AS [ExtCost], [Project1].[JobNum] AS [JobNum], [Project1].[PackLine] AS [PackLine], [Project1].[PONum] AS [PONum], [Project1].[POLine] AS [POLine], [Project1].[PORelNum] AS [PORelNum], [Project1].[PackSlip] AS [PackSlip], [Project1].[LotNum] AS [LotNum], [Project1].[RMANum] AS [RMANum], [Project1].[MtlMtlUnitCost] AS [MtlMtlUnitCost], [Project1].[MtlLabUnitCost] AS [MtlLabUnitCost], [Project1].[MtlSubUnitCost] AS [MtlSubUnitCost], [Project1].[MtlBurdenUnitCost] AS [MtlBurdenUnitCost], [Project1].[AfterBurUnitCost] AS [AfterBurUnitCost], [Project1].[AfterLbrUnitCost] AS [AfterLbrUnitCost], [Project1].[AfterMtlBurUnitCost] AS [AfterMtlBurUnitCost], [Project1].[AfterMtlUnitCost] AS [AfterMtlUnitCost], [Project1].[AfterSubUnitCost] AS [AfterSubUnitCost], row_number() OVER (ORDER BY [Project1].[TranDate] DESC, [Project1].[SysDate] DESC, [Project1].[SysTime] DESC, [Project1].[TranNum] DESC) AS [row_number]
FROM ( SELECT
[Extent1].[SysDate] AS [SysDate],
[Extent1].[SysTime] AS [SysTime],
[Extent1].[TranNum] AS [TranNum],
[Extent1].[PartNum] AS [PartNum],
[Extent1].[TranClass] AS [TranClass],
[Extent1].[TranType] AS [TranType],
[Extent1].[TranDate] AS [TranDate],
[Extent1].[TranQty] AS [TranQty],
[Extent1].[MtlUnitCost] AS [MtlUnitCost],
[Extent1].[LbrUnitCost] AS [LbrUnitCost],
[Extent1].[BurUnitCost] AS [BurUnitCost],
[Extent1].[SubUnitCost] AS [SubUnitCost],
[Extent1].[MtlBurUnitCost] AS [MtlBurUnitCost],
[Extent1].[ExtCost] AS [ExtCost],
[Extent1].[JobNum] AS [JobNum],
[Extent1].[PackLine] AS [PackLine],
[Extent1].[PONum] AS [PONum],
[Extent1].[POLine] AS [POLine],
[Extent1].[PORelNum] AS [PORelNum],
[Extent1].[PackSlip] AS [PackSlip],
[Extent1].[LotNum] AS [LotNum],
[Extent1].[RMANum] AS [RMANum],
[Extent1].[MtlMtlUnitCost] AS [MtlMtlUnitCost],
[Extent1].[MtlLabUnitCost] AS [MtlLabUnitCost],
[Extent1].[MtlSubUnitCost] AS [MtlSubUnitCost],
[Extent1].[MtlBurdenUnitCost] AS [MtlBurdenUnitCost],
[Extent1].[AfterBurUnitCost] AS [AfterBurUnitCost],
[Extent1].[AfterLbrUnitCost] AS [AfterLbrUnitCost],
[Extent1].[AfterMtlBurUnitCost] AS [AfterMtlBurUnitCost],
[Extent1].[AfterMtlUnitCost] AS [AfterMtlUnitCost],
[Extent1].[AfterSubUnitCost] AS [AfterSubUnitCost]
FROM [Erp].[PartTran] AS [Extent1]
INNER JOIN [Erp].[Plant] AS [Extent2] ON ([Extent1].[Company] = [Extent2].[Company]) AND ([Extent1].[Plant] = [Extent2].[Plant])
WHERE ([Extent1].[Company] = @p__linq__0) AND ((N'T' = @p__linq__1) OR (N'O' = @p__linq__2) OR ([Extent1].[CostID] = @p__linq__3) OR ((N'' = [Extent1].[CostID]) AND ([Extent2].[PlantCostID] = @p__linq__4))) AND ([Extent1].[PartNum] = @p__linq__5) AND (((@p__linq__6 = 1) AND ([Extent1].[TranDate] >= @p__linq__7) AND ([Extent1].[TranDate] <= @p__linq__8)) OR ((@p__linq__9 <> 1) AND ([Extent1].[TranDate] <= @p__linq__10))) AND (([Extent1].[SysDate] < @p__linq__11) OR (([Extent1].[SysDate] = @p__linq__12) AND ([Extent1].[SysTime] < @p__linq__13))) AND ([Extent1].[InventoryTrans] = @p__linq__14) AND ([Extent1].[GLTrans] = @p__linq__15) AND ([Extent1].[CostMethod] = @p__linq__16) AND (1 <> [Extent1].[TranRefType]) AND (3 <> [Extent1].[TranRefType])
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > @p__linq__17
ORDER BY [Project1].[TranDate] DESC, [Project1].[SysDate] DESC, [Project1].[SysTime] DESC, [Project1].[TranNum] DESC