May 18, 2011 at 7:41 am
Hi,
my table-valued function takes too long to execute so I can not call it from trigger after insert. But I need it excecute after insert to one of tables to fill other table.
What options do I have? I read about using SISS to pass parameters to.
So is it Trigger>invoke JOB > SISS pacakge? or Trigger > Proc > function
or any other suggestions how to make it work on "backround"
With regards
May 18, 2011 at 7:58 am
What is the TVF doing? Can you provide some DDL and sample data so we can see what you are talking about? Also, can you tell show uswhat your expected results are?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2011 at 8:06 am
It could be that the trigger is the issue too. Can you just move the code into the stored procedure that does the insert?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 18, 2011 at 11:34 pm
My bad 🙂 Point taken.
May 19, 2011 at 12:47 am
Well, to explain a bit the situation I must say that I am not professional SQL admin/dev etc. It is what has come along by implementing production ERP system. Overall the system is good and open as you can see I can play around with it 🙂 So I appreciate a lot that you, guys, help somebody like me wasting your time to trivia questions.
to subject: the main goal of my actions is to fill production table from several tables and views from which planning dep. will get Excel report for daily use. First it started as a view but when I needed to add scalar function to get purchase order numbers as sequence, it slowed down (what is obvious) so that Excel VB gaved timeout. So I decided to make it as a permanent table. Problem is that this table must be updated after purchase orders are made. So I decided to go this way: trigger for calling table function doing insert. Maybe I have approached the whole thing from wrong end. Anyway, as there is lot of tables involved I will post trigger,functions and exec plan. Hope this helps , if not , I appreciate your time taken.
Trigger:
GO
/****** Object: Trigger [dbo].[USR_TI_Optimization_table_fromDocRel] Script Date: 05/19/2011 09:45:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[USR_TI_Optimization_table_fromDocRel] ON [dbo].[DocumentRelationships]
AFTER INSERT
AS
BEGIN
DECLARE @SrcDocumentId AS UNIQUEIDENTIFIER
DECLARE @DestDocumentId AS UNIQUEIDENTIFIER
DECLARE @LotNr AS INT
SET NOCOUNT ON
--Test of DocumRelat_trigger
IF ( SELECT SrcDocumentType
FROM inserted
) <> 3
RETURN
IF ( SELECT DestDocumentType
FROM inserted
) <> 4
RETURN
SELECT @SrcDocumentId = SrcDocumentId
FROM inserted
SELECT @LotNr = Numero
FROM dbo.Optimizaciones
WHERE DocumentId = @SrcDocumentId
-- delete if exists
DELETE FROM dbo.USR_ProductionSchedule
WHERE dbo.USR_ProductionSchedule.ProductionLot = @LotNr
--insert new values
INSERT INTO dbo.USR_ProductionSchedule
([ProductionLot]
,[ProductionSet]
,[PaidDate]
,[OrderNumber]
,[ShippingArea]
,[Windows]
,[UserName]
,[ScheduledShopEntryDate]
,[LotDescription]
,[LotType]
,[GlassPurchaseOrder]
,[USR_ProductionNotes1]
,[USR_ProductionNotes2]
,[ModelSystem]
,[SubmodelColor]
,[Frames]
,[Sashes]
,[Mullions]
,[Rebates]
,[Glasses]
,[Squares]
,[GlassArea]
,[ProviderName]
,[EstimatedReceptionDate])
SELECT [ProductionLot]
,[ProductionSet]
,[PaidDate]
,[OrderNumber]
,[ShippingArea]
,[Windows]
,[UserName]
,[ScheduledShopEntryDate]
,[LotDescription]
,[LotType]
,[GlassPurchaseOrder]
,[USR_ProductionNotes1]
,[USR_ProductionNotes2]
,[ModelSystem]
,[SubmodelColor]
,[Frames]
,[Sashes]
,[Mullions]
,[Rebates]
,[Glasses]
,[Squares]
,[GlassArea]
,[ProviderName]
,[EstimatedReceptionDate]
FROM dbo.USR_GetProdLotData(@LotNr)
--GROUP BY PROD.Numero, PROD.Version, Monedas.ISO4217, pf.NumeroPedido
END
TVF
GO
/****** Object: UserDefinedFunction [dbo].[USR_GetProdLotData] Script Date: 05/19/2011 09:46:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <12.05.2011>
-- Description:<For getting lot information for table USR_GetProdLotData >
-- =============================================
CREATE FUNCTION [dbo].[USR_GetProdLotData] (@LotNr int )
RETURNS TABLE
AS RETURN
(SELECT TOP (100) PERCENT ESMP.ProductionLot, ESMP.ProductionSet, dbo.PAF.FechaPagado AS PaidDate, dbo.PAF.NumeroPedido AS OrderNumber,
SA.AreaCode AS ShippingArea, CPSM.Frames AS Windows, STF.EmployeeName AS UserName, OP.ScheduledDate AS ScheduledShopEntryDate,
OP.Descripcion AS LotDescription, OP.Tipo AS LotType, dbo.UW_GetGlassOrders(ESMP.Numero, ESMP.Version) AS GlassPurchaseOrder,
dbo.PAF.USR_ProductionNotes1, dbo.PAF.USR_ProductionNotes2, AD.ModelSystem, AD.SubmodelColor, CPSM.Frames, CPSM.Sashes,
CPSM.Mullions, CPSM.Rebates, CPSM.Glasses, CPSM.Squares, ISNULL(PGG_1.Area, 0) AS GlassArea, PD.ProviderName,
PD.EstimatedReceptionDate
FROM dbo.EstadoSubModelosPAF AS ESMP INNER JOIN
dbo.Optimizaciones AS OP ON ESMP.ProductionLot = OP.Numero INNER JOIN
dbo.ContenidoPAFSubModels AS CPSM ON ESMP.Version = CPSM.Version AND ESMP.Numero = CPSM.Numero AND ESMP.Orden = CPSM.Orden AND
ESMP.SubModel = CPSM.SubModel INNER JOIN
dbo.PAF ON ESMP.Numero = dbo.PAF.Numero AND ESMP.Version = dbo.PAF.Version LEFT OUTER JOIN
dbo.ShippingAreas AS SA ON dbo.PAF.ShipToAreaCode = SA.AreaCode INNER JOIN
dbo.vwCMSStaffCodeName AS STF ON dbo.PAF.UserCode = STF.EmployeeCode LEFT OUTER JOIN
(SELECT DISTINCT
MN.ProductionSet AS ProductionLot, MN.FromNumber AS Number, MN.FromVersion AS Version, PSD.Number AS PurchaseOrder,
dbo.Proveedores.Nombre AS ProviderName, dbo.Purchases.EstimatedReceptionDate
FROM dbo.MaterialNeeds AS MN INNER JOIN
dbo.PurchasesSubDetail AS PSD ON MN.GUID = PSD.MaterialNeedId INNER JOIN
dbo.Numeraciones AS NR ON PSD.Numeration = NR.id INNER JOIN
dbo.Purchases ON PSD.Number = dbo.Purchases.Number AND PSD.Numeration = dbo.Purchases.Numeration INNER JOIN
dbo.Proveedores ON dbo.Purchases.ProviderCode = dbo.Proveedores.CodigoProveedor
WHERE (MN.MaterialType = 4) AND (MN.HoleId <> - 1) AND (NR.Purchase = 1) AND (NR.DocumentType = 2)) AS PD ON
ESMP.Numero = PD.Number AND ESMP.Version = PD.Version AND ESMP.ProductionLot = PD.ProductionLot LEFT OUTER JOIN
(SELECT DISTINCT ProductionLot, ProductionSet, SubmodelColor, ModelSystem
FROM dbo.vwLotSubmodels
WHERE (SubmodelColor <> '') AND (ModelSystem <> '')) AS AD ON ESMP.ProductionLot = AD.ProductionLot AND
ESMP.ProductionSet = AD.ProductionSet LEFT OUTER JOIN
(SELECT PGSW.Number, PGSW.Version, PGSW.Position, PGSW.SubModel, SUM(PGG.Width * PGG.Height) / 1000000 AS Area
FROM dbo.ProdGenericGlasses AS PGG INNER JOIN
dbo.ProdGenericSubWindows AS PGSW ON PGG.GenericSubWindowId = PGSW.RowId
WHERE (PGG.Composite = 2)
GROUP BY PGSW.Number, PGSW.Version, PGSW.Position, PGSW.SubModel) AS PGG_1 ON ESMP.Version = PGG_1.Version AND
ESMP.Numero = PGG_1.Number AND ESMP.Orden = PGG_1.Position AND ESMP.SubModel = PGG_1.SubModel
Where ESMP.ProductionLot = @LotNr
Order By ESMP.ProductionLot
);
Scalar:
GO
/****** Object: UserDefinedFunction [dbo].[UW_GetGlassOrders] Script Date: 05/19/2011 09:37:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[UW_GetGlassOrders] (@docNumero int, @docVersion int) RETURNS nvarchar(250)
AS
BEGIN
DECLARE @Desccription nvarchar(250)
SET @Desccription = NULL
SELECT @Desccription = ISNULL(@Desccription + ',', '') + CAST(dbo.Purchases.Number as nvarchar(20))
FROM dbo.MaterialNeeds AS MN INNER JOIN
dbo.PurchasesSubDetail AS PSD ON MN.GUID = PSD.MaterialNeedId INNER JOIN
dbo.Numeraciones AS NR ON PSD.Numeration = NR.id INNER JOIN
dbo.Purchases ON PSD.Number = dbo.Purchases.Number AND PSD.Numeration = dbo.Purchases.Numeration INNER JOIN
dbo.Proveedores ON dbo.Purchases.ProviderCode = dbo.Proveedores.CodigoProveedor
WHERE (MN.MaterialType = 4) AND (MN.HoleId <> - 1) AND (NR.Purchase = 1) AND (NR.DocumentType = 2)
GROUP BY dbo.Purchases.Number, MN.FromNumber, MN.FromVersion
HAVING (MN.FromNumber = @docNumero) AND (MN.FromVersion = @docVersion)
RETURN(@Desccription)
END
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply