Submit Your Suggestions for Vista

Ideaspace topics with the most votes have been moved into the Suggestion Box. If there is a topic missing, please re-enter. Our product team reviews the Suggestion Box items regularly and will provide updates as to status and incorporation into upcoming releases.

Speed up saving detail rows in PO Purchase Order Entry

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:

  1. exec vspPOItemPOCDClosedCheck

  2. exec vspPOItemLinesExist

  3. exec bspHQMatlNotesGet

  4. (set app role and setActiveLoginType stuff)

  5. exec uspPOIBManufacturer

  6. update POIB SET...

  7. select top 5000 ..... from POIB

  8. exec vspPOItemLinesExist

  9. exec bspHQMatlNotesGet

  10. exec uspPOIBManufacturer

  11. exec vspDDCountRowsWithParams

  12. exec vspDDCountRowsWithParams

  13. exec vspPOItemLinesExist

  14. exec bspHQMatlNotesGet

  15. (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
  • Aug 9 2021
Company A-1 Plumbing & Emergency Rooter
Job Title / Role IT Admin
I need it... Yesterday...Come on already
  • Attach files

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.

1515 SE Water Avenue, Suite 300, Portland, OR 97214 |  800.333.3197  | Contact Us | Terms of Use | Privacy | Support Policies

© 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.