After noticing that editing POIB (Purchase Order Entry detail lines) in the grid can be incredibly slow at times, I decided to take a peek at what happens on the SQL server when saving a row.
Imagine this setup:
PO Purchase Order Entry form is open.
Header area is filled out and saved.
Two detail lines exist.
The 'Units' field in the detail area is highlighted for the first row.
Here is the action I take:
Type in a new quantity of units.
Tap the SAVE ICON.
Here is a summary of what is recorded on the server:
exec vspPOItemPOCDClosedCheck
exec vspPOItemLinesExist
exec bspHQMatlNotesGet
(set app role and setActiveLoginType stuff)
exec uspPOIBManufacturer
update POIB SET...
select top 5000 ..... from POIB
exec vspPOItemLinesExist
exec bspHQMatlNotesGet
exec uspPOIBManufacturer
exec vspDDCountRowsWithParams
exec vspDDCountRowsWithParams
exec vspPOItemLinesExist
exec bspHQMatlNotesGet
(set app role and setActiveLoginType stuff)
The procedure which took so long was #7, the TOP 5000 ...... from POIB. That stored procedure took 38 seconds to complete this time. It seems to take between 1/2 a second, and 40 seconds. :-(
When running this test multiple times, my custom procedure (lines 5 & 10) seems to average around 0.210 seconds. Sometimes it's only called once.
Here's a formatted version of that TOP 5000 code. Note that COMMENTING OUT the bottom OUTER APPLY makes this run consistently at 2 seconds. UNCOMMENTING IT makes it run at 38 seconds, consistently.
SELECT TOP 50000
[POIB].[POItem] AS [POItem],
POIB.Description AS [POIBDescription],
[POIB].[MatlGroup] AS [MatlGroup],
[POIB].[PhaseGroup] AS [PhaseGroup],
[POIB].[EMGroup] AS [EMGroup],
[HQCO].[TaxGroup] AS [Dft Sales TaxGrp],
[POIB].[TaxGroup] AS [TaxGroup],
[POIB].[TaxRate] AS [TaxRate],
[POIB].[GSTRate] AS [GSTRate],
[POIB].[SupplierGroup] AS [SupplierGroup],
[POIB].[OrigTax] AS [OrigTax],
[POIB].[RequisitionNum] AS [RequisitionNum],
[JCJM].[JobStatus] AS [JobStatus],
[JCJM].[LockPhases] AS [LockPhases],
[HQMT].[PurchaseUM] AS [PurchaseUM],
[HQMT].[Taxable] AS [Taxable],
ISNULL(POIT.OrigUnits, 0) AS [Original Units],
ISNULL(POIT.CurUnits, 0) AS [Current Units],
ISNULL(POIT.RecvdUnits, 0) AS [Received Units],
ISNULL(POIT.BOUnits, 0) AS [Backordered Units],
ISNULL(POIT.TotalUnits, 0) AS [Total Units],
ISNULL(POIT.InvUnits, 0) AS [Invoiced Units],
ISNULL(POIT.RemUnits, 0) AS [Remaining Units],
ISNULL(POIT.OrigUnitCost, 0) AS [Original Unit Cost],
[POIT].[OrigECM] AS [Original ECM],
ISNULL(POIT.CurUnitCost, 0) AS [Current Unit Cost],
[POIT].[CurECM] AS [Current ECM],
ISNULL(POIT.OrigCost, 0) AS [Original Total Cost],
ISNULL(POIT.CurCost, 0) AS [Current Total Cost],
ISNULL(POIT.RecvdCost, 0) AS [Received Total Cost],
ISNULL(POIT.BOCost, 0) AS [Backordered Total Cost],
ISNULL(POIT.TotalCost, 0) AS [Total Cost],
ISNULL(POIT.InvCost, 0) AS [Invoiced Total Cost],
ISNULL(POIT.RemCost, 0) AS [Remaining Total Cost],
ISNULL(POIT.OrigTax, 0) AS [Original Tax],
ISNULL(POIT.CurTax, 0) AS [Current Tax],
ISNULL(POIT.TotalTax, 0) AS [Total Tax],
ISNULL(POIT.InvTax, 0) AS [Invoiced Tax],
ISNULL(POIT.RemTax, 0) AS [Remaining Tax],
ISNULL(EMEM.PostCostToComp, '') AS [PostCostToComp],
'EQ#: ' + CONVERT(varchar(10), ISNULL(POIB.Equip, '')) + ' - ' + ISNULL(EMEM.Description, '') AS [EQ Desc],
'Component: ' + CONVERT(varchar(10), ISNULL(e2.ComponentTypeCode, '')) + ' - ' + CONVERT(varchar(30), ISNULL(POIB.Component, '')) + ' - ' + CONVERT(varchar(30), ISNULL(e2.Description, '')) AS [EQ Comp],
CASE
WHEN EXISTS (SELECT
1
FROM SMWorkCompleted
WHERE POCo = POIT.POCo
AND PONumber = POIT.PO
AND POItem = POIT.POItem) THEN 'Y'
ELSE 'N'
END AS [WorkCompletedUsingPOItem],
[EMCO].[WOCostCodeChg] AS [WOCostCodeChg],
[SMWorkOrderScope].[Status] AS [Status],
[SMCostType].[TaxableYN] AS [TaxableYN],
--[e].[AvailableBudget] AS [AvailableBudget],
--[e].[ThisBatch] AS [ThisBatch],
--[e].[RemainingBudget] AS [RemainingBudget],
[POIB].[udStage2] AS [udStage2],
[POIB].[udBuilding2] AS [udBuilding2],
[POIB].[udFloor2] AS [udFloor2],
[POIB].[udSystem2] AS [udSystem2],
[POIB].[udModifiedBy] AS [udModifiedBy],
[POIB].[udModifiedOn] AS [udModifiedOn],
[POIB].[udManufacturer] AS [udManufacturer],
[POIB].[udMfgPartNum] AS [udMfgPartNum],
[POIB].[udUseMultiplier] AS [udUseMultiplier],
[POIB].[PostToCo] AS [PostToCo],
[POIB].[BatchTransType] AS [BatchTransType],
CONVERT(varchar(3), [POIB].[ItemType]) AS [ItemType],
[POIB].[JCCo] AS [JCCo],
[POIB].[Job] AS [Job],
JCJM.Description AS [Job Description],
[POIB].[INCo] AS [INCo],
[POIB].[Loc] AS [Loc],
INLM.Description AS [Location Description],
[POIB].[EMCo] AS [EMCo],
[POIB].[Equip] AS [Equip],
EMEM.Description AS [Equipment Description],
[POIB].[CompType] AS [CompType],
[POIB].[Component] AS [Component],
[POIB].[WO] AS [WO],
EMWH.Description AS [WO Description],
[POIB].[WOItem] AS [WOItem],
EMWI.Description AS [WO Item Description],
[POIB].[CostCode] AS [CostCode],
EMCC.Description AS [EM Cost Code Description],
[POIB].[EMCType] AS [EMCType],
[POIB].[SMCo] AS [SMCo],
[POIB].[SMWorkOrder] AS [SMWorkOrder],
[POIB].[SMScope] AS [SMScope],
[POIB].[SMCostType] AS [SMCostType],
SMCostType.Description AS [SMCTDesc],
[POIB].[Material] AS [Material],
[POIB].[Phase] AS [Phase],
JCPM.Description AS [Phase Description],
[POIB].[JCCType] AS [JCCType],
[POIB].[VendMatId] AS [VendMatId],
[INMT].[OnHand] AS [On Hand Total],
[POIB].[SMPhase] AS [SMPhase],
[POIB].[SMJCCostType] AS [SMJCCostType],
[INMT].[OnOrder] AS [On Order Total],
[POIB].[RecvYN] AS [RecvYN],
[POIB].[Description] AS [Description],
[POIB].[GLCo] AS [GLCo],
[POIB].[GLAcct] AS [GLAcct],
GLAC.Description AS [GLAcct Desc],
[POIB].[ReqDate] AS [ReqDate],
[POIB].[PayCategory] AS [PayCategory],
[POIB].[PayType] AS [PayType],
[POIB].[UM] AS [UM],
[POIB].[OrigUnits] AS [OrigUnits],
[POIB].[OrigUnitCost] AS [OrigUnitCost],
[POIB].[OrigECM] AS [OrigECM],
[POIB].[OrigCost] AS [OrigCost],
CONVERT(varchar(3), [POIB].[TaxType]) AS [TaxType],
[POIB].[TaxCode] AS [TaxCode],
[POIB].[Supplier] AS [Supplier],
APVM.Name AS [Supplier Name],
[POIB].[Notes] AS [Notes],
(SELECT
MAX(ActualDate)
FROM EMCD WITH (NOLOCK)
WHERE POIB.PostToCo = EMCD.EMCo
AND POIB.Equip = EMCD.Equipment
AND POIB.Material = EMCD.Material)
AS [Part Last Used Date],
[EMCO].[MatlLastUsedYN] AS [MatlLastUsedYN],
[JCCO].[GLCostOveride] AS [JCCO GLOverride],
[INCO].[OverrideGL] AS [INCO GLOverride],
[EMCO].[GLOverride] AS [EMCO GLOverride],
[SMWorkOrderScope].[JCCo] AS [SMWorkOrderScopeSM WO JCCo],
[SMWorkOrderScope].[Job] AS [SMWorkOrderScopeSM WO Job],
[POIB].[SMPhaseGroup] AS [SMPhaseGroup]
FROM POIB
LEFT OUTER JOIN POHB WITH (NOLOCK)
ON POIB.Co = POHB.Co
AND POIB.Mth = POHB.Mth
AND POIB.BatchId = POHB.BatchId
AND POIB.BatchSeq = POHB.BatchSeq
LEFT OUTER JOIN POIT WITH (NOLOCK)
ON POIB.Co = POIT.POCo
AND POIB.POItem = POIT.POItem
AND POHB.PO = POIT.PO
LEFT OUTER JOIN GLAC WITH (NOLOCK)
ON GLAC.GLCo = POIB.GLCo
AND GLAC.GLAcct = POIB.GLAcct
LEFT OUTER JOIN JCJM WITH (NOLOCK)
ON JCJM.JCCo = POIB.PostToCo
AND JCJM.Job = POIB.Job
LEFT OUTER JOIN JCPM WITH (NOLOCK)
ON JCPM.PhaseGroup = POIB.PhaseGroup
AND JCPM.Phase = POIB.Phase
LEFT OUTER JOIN EMWH WITH (NOLOCK)
ON EMWH.EMCo = POIB.PostToCo
AND EMWH.WorkOrder = POIB.WO
LEFT OUTER JOIN EMWI WITH (NOLOCK)
ON EMWI.EMCo = POIB.PostToCo
AND EMWI.WorkOrder = POIB.WO
AND EMWI.WOItem = POIB.WOItem
LEFT OUTER JOIN EMCC WITH (NOLOCK)
ON EMCC.EMGroup = POIB.EMGroup
AND EMCC.CostCode = POIB.CostCode
LEFT OUTER JOIN INLM WITH (NOLOCK)
ON INLM.INCo = POIB.PostToCo
AND INLM.Loc = POIB.Loc
LEFT OUTER JOIN EMEM WITH (NOLOCK)
ON EMEM.EMCo = POIB.PostToCo
AND EMEM.Equipment = POIB.Equip
LEFT JOIN EMCO WITH (NOLOCK)
ON POIB.PostToCo = EMCO.EMCo
LEFT JOIN HQMT WITH (NOLOCK)
ON HQMT.MatlGroup = POIB.MatlGroup
AND HQMT.Material = POIB.Material
LEFT OUTER JOIN EMEM e2 WITH (NOLOCK)
ON e2.EMCo = POIB.PostToCo
AND e2.Equipment = POIB.Component
LEFT OUTER JOIN HQCO WITH (NOLOCK)
ON POIB.PostToCo = HQCO.HQCo
LEFT JOIN INMT WITH (NOLOCK)
ON POIB.PostToCo = INMT.INCo
AND POIB.Loc = INMT.Loc
AND POIB.MatlGroup = INMT.MatlGroup
AND POIB.Material = INMT.Material
LEFT OUTER JOIN APVM WITH (NOLOCK)
ON POIB.SupplierGroup = APVM.VendorGroup
AND POIB.Supplier = APVM.Vendor
LEFT JOIN JCCO WITH (NOLOCK)
ON POIB.PostToCo = JCCO.JCCo
LEFT JOIN INCO WITH (NOLOCK)
ON POIB.PostToCo = INCO.INCo
LEFT JOIN dbo.SMWorkOrderScope
ON SMWorkOrderScope.SMCo = POIB.SMCo
AND SMWorkOrderScope.WorkOrder = POIB.SMWorkOrder
AND SMWorkOrderScope.Scope = POIB.SMScope
LEFT JOIN dbo.SMCostType
ON SMCostType.SMCo = POIB.SMCo
AND SMCostType.SMCostType = POIB.SMCostType
-- OUTER APPLY SELECT TOP 50000
[POIB].[POItem] AS [POItem],
POIB.Description AS [POIBDescription],
[POIB].[MatlGroup] AS [MatlGroup],
[POIB].[PhaseGroup] AS [PhaseGroup],
[POIB].[EMGroup] AS [EMGroup],
[HQCO].[TaxGroup] AS [Dft Sales TaxGrp],
[POIB].[TaxGroup] AS [TaxGroup],
[POIB].[TaxRate] AS [TaxRate],
[POIB].[GSTRate] AS [GSTRate],
[POIB].[SupplierGroup] AS [SupplierGroup],
[POIB].[OrigTax] AS [OrigTax],
[POIB].[RequisitionNum] AS [RequisitionNum],
[JCJM].[JobStatus] AS [JobStatus],
[JCJM].[LockPhases] AS [LockPhases],
[HQMT].[PurchaseUM] AS [PurchaseUM],
[HQMT].[Taxable] AS [Taxable],
ISNULL(POIT.OrigUnits, 0) AS [Original Units],
ISNULL(POIT.CurUnits, 0) AS [Current Units],
ISNULL(POIT.RecvdUnits, 0) AS [Received Units],
ISNULL(POIT.BOUnits, 0) AS [Backordered Units],
ISNULL(POIT.TotalUnits, 0) AS [Total Units],
ISNULL(POIT.InvUnits, 0) AS [Invoiced Units],
ISNULL(POIT.RemUnits, 0) AS [Remaining Units],
ISNULL(POIT.OrigUnitCost, 0) AS [Original Unit Cost],
[POIT].[OrigECM] AS [Original ECM],
ISNULL(POIT.CurUnitCost, 0) AS [Current Unit Cost],
[POIT].[CurECM] AS [Current ECM],
ISNULL(POIT.OrigCost, 0) AS [Original Total Cost],
ISNULL(POIT.CurCost, 0) AS [Current Total Cost],
ISNULL(POIT.RecvdCost, 0) AS [Received Total Cost],
ISNULL(POIT.BOCost, 0) AS [Backordered Total Cost],
ISNULL(POIT.TotalCost, 0) AS [Total Cost],
ISNULL(POIT.InvCost, 0) AS [Invoiced Total Cost],
ISNULL(POIT.RemCost, 0) AS [Remaining Total Cost],
ISNULL(POIT.OrigTax, 0) AS [Original Tax],
ISNULL(POIT.CurTax, 0) AS [Current Tax],
ISNULL(POIT.TotalTax, 0) AS [Total Tax],
ISNULL(POIT.InvTax, 0) AS [Invoiced Tax],
ISNULL(POIT.RemTax, 0) AS [Remaining Tax],
ISNULL(EMEM.PostCostToComp, '') AS [PostCostToComp],
'EQ#: ' + CONVERT(varchar(10), ISNULL(POIB.Equip, '')) + ' - ' + ISNULL(EMEM.Description, '') AS [EQ Desc],
'Component: ' + CONVERT(varchar(10), ISNULL(e2.ComponentTypeCode, '')) + ' - ' + CONVERT(varchar(30), ISNULL(POIB.Component, '')) + ' - ' + CONVERT(varchar(30), ISNULL(e2.Description, '')) AS [EQ Comp],
CASE
WHEN EXISTS (SELECT
1
FROM SMWorkCompleted
WHERE POCo = POIT.POCo
AND PONumber = POIT.PO
AND POItem = POIT.POItem) THEN 'Y'
ELSE 'N'
END AS [WorkCompletedUsingPOItem],
[EMCO].[WOCostCodeChg] AS [WOCostCodeChg],
[SMWorkOrderScope].[Status] AS [Status],
[SMCostType].[TaxableYN] AS [TaxableYN],
--[e].[AvailableBudget] AS [AvailableBudget],
--[e].[ThisBatch] AS [ThisBatch],
--[e].[RemainingBudget] AS [RemainingBudget],
[POIB].[udStage2] AS [udStage2],
[POIB].[udBuilding2] AS [udBuilding2],
[POIB].[udFloor2] AS [udFloor2],
[POIB].[udSystem2] AS [udSystem2],
[POIB].[udModifiedBy] AS [udModifiedBy],
[POIB].[udModifiedOn] AS [udModifiedOn],
[POIB].[udManufacturer] AS [udManufacturer],
[POIB].[udMfgPartNum] AS [udMfgPartNum],
[POIB].[udUseMultiplier] AS [udUseMultiplier],
[POIB].[PostToCo] AS [PostToCo],
[POIB].[BatchTransType] AS [BatchTransType],
CONVERT(varchar(3), [POIB].[ItemType]) AS [ItemType],
[POIB].[JCCo] AS [JCCo],
[POIB].[Job] AS [Job],
JCJM.Description AS [Job Description],
[POIB].[INCo] AS [INCo],
[POIB].[Loc] AS [Loc],
INLM.Description AS [Location Description],
[POIB].[EMCo] AS [EMCo],
[POIB].[Equip] AS [Equip],
EMEM.Description AS [Equipment Description],
[POIB].[CompType] AS [CompType],
[POIB].[Component] AS [Component],
[POIB].[WO] AS [WO],
EMWH.Description AS [WO Description],
[POIB].[WOItem] AS [WOItem],
EMWI.Description AS [WO Item Description],
[POIB].[CostCode] AS [CostCode],
EMCC.Description AS [EM Cost Code Description],
[POIB].[EMCType] AS [EMCType],
[POIB].[SMCo] AS [SMCo],
[POIB].[SMWorkOrder] AS [SMWorkOrder],
[POIB].[SMScope] AS [SMScope],
[POIB].[SMCostType] AS [SMCostType],
SMCostType.Description AS [SMCTDesc],
[POIB].[Material] AS [Material],
[POIB].[Phase] AS [Phase],
JCPM.Description AS [Phase Description],
[POIB].[JCCType] AS [JCCType],
[POIB].[VendMatId] AS [VendMatId],
[INMT].[OnHand] AS [On Hand Total],
[POIB].[SMPhase] AS [SMPhase],
[POIB].[SMJCCostType] AS [SMJCCostType],
[INMT].[OnOrder] AS [On Order Total],
[POIB].[RecvYN] AS [RecvYN],
[POIB].[Description] AS [Description],
[POIB].[GLCo] AS [GLCo],
[POIB].[GLAcct] AS [GLAcct],
GLAC.Description AS [GLAcct Desc],
[POIB].[ReqDate] AS [ReqDate],
[POIB].[PayCategory] AS [PayCategory],
[POIB].[PayType] AS [PayType],
[POIB].[UM] AS [UM],
[POIB].[OrigUnits] AS [OrigUnits],
[POIB].[OrigUnitCost] AS [OrigUnitCost],
[POIB].[OrigECM] AS [OrigECM],
[POIB].[OrigCost] AS [OrigCost],
CONVERT(varchar(3), [POIB].[TaxType]) AS [TaxType],
[POIB].[TaxCode] AS [TaxCode],
[POIB].[Supplier] AS [Supplier],
APVM.Name AS [Supplier Name],
[POIB].[Notes] AS [Notes],
(SELECT
MAX(ActualDate)
FROM EMCD WITH (NOLOCK)
WHERE POIB.PostToCo = EMCD.EMCo
AND POIB.Equip = EMCD.Equipment
AND POIB.Material = EMCD.Material)
AS [Part Last Used Date],
[EMCO].[MatlLastUsedYN] AS [MatlLastUsedYN],
[JCCO].[GLCostOveride] AS [JCCO GLOverride],
[INCO].[OverrideGL] AS [INCO GLOverride],
[EMCO].[GLOverride] AS [EMCO GLOverride],
[SMWorkOrderScope].[JCCo] AS [SMWorkOrderScopeSM WO JCCo],
[SMWorkOrderScope].[Job] AS [SMWorkOrderScopeSM WO Job],
[POIB].[SMPhaseGroup] AS [SMPhaseGroup]
FROM POIB
LEFT OUTER JOIN POHB WITH (NOLOCK)
ON POIB.Co = POHB.Co
AND POIB.Mth = POHB.Mth
AND POIB.BatchId = POHB.BatchId
AND POIB.BatchSeq = POHB.BatchSeq
LEFT OUTER JOIN POIT WITH (NOLOCK)
ON POIB.Co = POIT.POCo
AND POIB.POItem = POIT.POItem
AND POHB.PO = POIT.PO
LEFT OUTER JOIN GLAC WITH (NOLOCK)
ON GLAC.GLCo = POIB.GLCo
AND GLAC.GLAcct = POIB.GLAcct
LEFT OUTER JOIN JCJM WITH (NOLOCK)
ON JCJM.JCCo = POIB.PostToCo
AND JCJM.Job = POIB.Job
LEFT OUTER JOIN JCPM WITH (NOLOCK)
ON JCPM.PhaseGroup = POIB.PhaseGroup
AND JCPM.Phase = POIB.Phase
LEFT OUTER JOIN EMWH WITH (NOLOCK)
ON EMWH.EMCo = POIB.PostToCo
AND EMWH.WorkOrder = POIB.WO
LEFT OUTER JOIN EMWI WITH (NOLOCK)
ON EMWI.EMCo = POIB.PostToCo
AND EMWI.WorkOrder = POIB.WO
AND EMWI.WOItem = POIB.WOItem
LEFT OUTER JOIN EMCC WITH (NOLOCK)
ON EMCC.EMGroup = POIB.EMGroup
AND EMCC.CostCode = POIB.CostCode
LEFT OUTER JOIN INLM WITH (NOLOCK)
ON INLM.INCo = POIB.PostToCo
AND INLM.Loc = POIB.Loc
LEFT OUTER JOIN EMEM WITH (NOLOCK)
ON EMEM.EMCo = POIB.PostToCo
AND EMEM.Equipment = POIB.Equip
LEFT JOIN EMCO WITH (NOLOCK)
ON POIB.PostToCo = EMCO.EMCo
LEFT JOIN HQMT WITH (NOLOCK)
ON HQMT.MatlGroup = POIB.MatlGroup
AND HQMT.Material = POIB.Material
LEFT OUTER JOIN EMEM e2 WITH (NOLOCK)
ON e2.EMCo = POIB.PostToCo
AND e2.Equipment = POIB.Component
LEFT OUTER JOIN HQCO WITH (NOLOCK)
ON POIB.PostToCo = HQCO.HQCo
LEFT JOIN INMT WITH (NOLOCK)
ON POIB.PostToCo = INMT.INCo
AND POIB.Loc = INMT.Loc
AND POIB.MatlGroup = INMT.MatlGroup
AND POIB.Material = INMT.Material
LEFT OUTER JOIN APVM WITH (NOLOCK)
ON POIB.SupplierGroup = APVM.VendorGroup
AND POIB.Supplier = APVM.Vendor
LEFT JOIN JCCO WITH (NOLOCK)
ON POIB.PostToCo = JCCO.JCCo
LEFT JOIN INCO WITH (NOLOCK)
ON POIB.PostToCo = INCO.INCo
LEFT JOIN dbo.SMWorkOrderScope
ON SMWorkOrderScope.SMCo = POIB.SMCo
AND SMWorkOrderScope.WorkOrder = POIB.SMWorkOrder
AND SMWorkOrderScope.Scope = POIB.SMScope
LEFT JOIN dbo.SMCostType
ON SMCostType.SMCo = POIB.SMCo
AND SMCostType.SMCostType = POIB.SMCostType
-- OUTER APPLY dbo.vfPOGetItemEstimates(POIB.Co, POIB.Mth, POIB.BatchId, POIB.BatchSeq, POIB.POItem, NULL, NULL, 0) e
WHERE POIB.Mth = '2021/08/01'
AND POIB.BatchId = 724
AND POIB.Co = 5
AND POIB.BatchSeq = 1
ORDER BY [POIB].[POItem] e
WHERE POIB.Mth = '2021/08/01'
AND POIB.BatchId = 724
AND POIB.Co = 5
AND POIB.BatchSeq = 1
ORDER BY [POIB].[POItem]
On further testing of just SELECT * FROM dbo.vfPOGetItemEstimates(5, '2021-08-01', 724, 1, 2, NULL, NULL, 0) with 1 or 2 as the 'Item' argument (2 in the above code), it takes about 1/2 second each time.... So, why is it taking so long when part of the bigger statement?
If I trim down the large SELECT statement to ONLY fields from POIB and vfPOGetItemEstimates, then the whole statement runs in one second.
So, I can get this large query to run quick (under 2 seconds) if I either COMMENT OUT THE JOINS, or COMMENT OUT THE OUTER APPLY. But with both in place as normal it takes 38 seconds.
I'm not enough of a SQL GURU to know WHY though. Viewpoint, could you please take a closer look at why this is happening? "Little" slowdowns like this are what kill the user experience in VP.
This same ~40 second load time sometimes happens when opening an existing PO into PO Purchase Order Entry.
Nathan Sutherland
| Company | A-1 Plumbing & Emergency Rooter |
| Job Title / Role | IT Admin |
| I need it... | Yesterday...Come on already |
Dear Viewpoint Suggestion Box contributor;
We at Viewpoint sincerely thank you for your contribution to Suggestion Box on how we can improve Viewpoint products. While we can’t do everything at once, we rely upon your feedback to help guide the prioritization of our product improvements, and Suggestion Box is a critical tool for us to understand and prioritize our customers’ needs.
Viewpoint reviews Suggestion Box regularly for all of our products and updates statuses, adds comments, and performs various house-keeping (including deleting) as needed to ensure that Suggestion Box is maintained as a productive environment for product enhancements requests.
© 2023 Trimble Inc. All Rights Reserved. Viewpoint®, Vista™, Spectrum®, ProContractor™, Jobpac Connect™, Viewpoint Team™, Viewpoint Analytics™, Viewpoint Field View™, Viewpoint Estimating™, Viewpoint For Projects™, Viewpoint HR Management™, Viewpoint Field Management™, Viewpoint Financial Controls™, Vista Field Service™, Spectrum Service Tech™, ViewpointOne™, ProjectSight® and Trimble Construction One™ are trademarks or registered trademarks of Trimble Inc. or its affiliates in the United States and other countries. Other names and brands may be claimed as the property of others.