June 7, 2018 at 9:24 am
I am working on taking data out of an ERP system and into a data warehouse. One of the unfortunate facts of life with this ERP system is that the links from the Transaction lines to the sources of the transaction lines are all held in a single field delimited with semicolons. There are 3 fields involved in this mapping
The Transaction Line Table (GL_Transline) has 2 Fields incolved in it -
GLL_Source - a GUID that links to a table called GL_Source
GLL_SourceRef- A Semicolon Delimited field that can contain anywhere from 0 - 5 values
The GL Source table has a table called GLS_SourceRefMapping that is a semicolon delimited field that determines what the values in the GLL_Sourceref table are related to .
An Example of A GLS_SourceRefmapping Field is
ItemID;Date;
Then The GLL_Sourceref field would have the item ID and Date connected to the transaction line connected to it
I tried using the query below so I could get the values all split out and then was thinking of doing a pivot afterwards to make a column for each value of GLLMAP with the corresponding Value from GLLitem as the value for that row.
SELECT [GLT].[GLT_TransNbr] [GLL].[GLL_ID] ,mapSplit.Item GLLmap ,mapSplit.ItemNumber GLLmapnumber ,gllsourcesplit.Item gllitem ,gllsourcesplit.ItemNumber Item1FROM [GL_TRans] AS [GLT]
JOIN [GL_Transline] AS [GLL] ON [GLL].[GLL_TransID] = [GLT].[GLT_TransID]
JOIN GL_Source GLS ON GLS.GLS_ID = GLL.GLL_Source
cross apply DelimitedSplit8K(GLS.GLS_SourceRefMap, ';') mapSplit
Cross Apply DelimitedSplit8K(GLL_SourceRef, ';') gllsourcesplit
WHERE mapSplit.ItemNumber = gllsourcesplit.ItemNumber
When I run just the query without the Cross Applied DelimitedSplit8K , It takes about 24 Seconds to complete (5,483,050 Rows)
Once I bring the Cross Applied DelimitedSplit8Ks into the equation the time jumps up to about 2:00 (12,167,728 Rows )
I have not yet gotten to the part where I try to pivot this, as the database I am working on is a small portion of the total data (~ 1/24th ) and I was wondering if there was a better way performance-wise and general approach wise to tackle this.
June 7, 2018 at 9:29 am
Have you considered breaking this into chunks & using temp tables to store the expanded data? Might help things.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 7, 2018 at 9:31 am
Considering you're using SQL Server 2016, have you considered using STRING_SPLIT? Provided you don't need to retain the ordinal position. Not to say thar Jeff's solution is bad (it's awesome) but you might find better performance with Microsoft's in-built solution.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 7, 2018 at 9:34 am
Thom A - Thursday, June 7, 2018 9:31 AMConsidering you're using SQL Server 2016, have you considered using STRING_SPLIT? Provided you don't need to retain the ordinal position.
I do need to retain the ordinal position, because I need to be able to ensure that if Item1 from mapSplit is ItemID, it ends up on the same row as the itemId data from the gllsourcesplit.
June 7, 2018 at 9:44 am
I'm not sure you want to CROSS APPLY the 2nd use of the splitter function. I may have misunderstood your requirement, but I came away from reading it with thinking that you would join values from two separate splitter executions, perhaps each within a CTE, and then using the ItemNumber and values from each, JOIN the two splits together in some fashion. The reason your execution time is exploding is because the 2nd CROSS APPLY is creating all of it's rows for every row in the first CROSS APPLY, and I'm pretty sure you don't need (or want) to do that...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 7, 2018 at 9:44 am
Have you tried with the DelimitedSplit8K_Lead?
http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/
June 7, 2018 at 10:56 am
Steven.Grzybowski - Thursday, June 7, 2018 9:24 AMI am working on taking data out of an ERP system and into a data warehouse. One of the unfortunate facts of life with this ERP system is that the links from the Transaction lines to the sources of the transaction lines are all held in a single field delimited with semicolons. There are 3 fields involved in this mapping
The Transaction Line Table (GL_Transline) has 2 Fields incolved in it -
GLL_Source - a GUID that links to a table called GL_Source
GLL_SourceRef- A Semicolon Delimited field that can contain anywhere from 0 - 5 valuesThe GL Source table has a table called GLS_SourceRefMapping that is a semicolon delimited field that determines what the values in the GLL_Sourceref table are related to .
An Example of A GLS_SourceRefmapping Field isItemID;Date;
Then The GLL_Sourceref field would have the item ID and Date connected to the transaction line connected to it
I tried using the query below so I could get the values all split out and then was thinking of doing a pivot afterwards to make a column for each value of GLLMAP with the corresponding Value from GLLitem as the value for that row.
SELECT [GLT].[GLT_TransNbr] [GLL].[GLL_ID] ,mapSplit.Item GLLmap ,mapSplit.ItemNumber GLLmapnumber ,gllsourcesplit.Item gllitem ,gllsourcesplit.ItemNumber Item1FROM [GL_TRans] AS [GLT]
JOIN [GL_Transline] AS [GLL] ON [GLL].[GLL_TransID] = [GLT].[GLT_TransID]
JOIN GL_Source GLS ON GLS.GLS_ID = GLL.GLL_Source
cross apply DelimitedSplit8K(GLS.GLS_SourceRefMap, ';') mapSplit
Cross Apply DelimitedSplit8K(GLL_SourceRef, ';') gllsourcesplit
WHERE mapSplit.ItemNumber = gllsourcesplit.ItemNumber
When I run just the query without the Cross Applied DelimitedSplit8K , It takes about 24 Seconds to complete (5,483,050 Rows)
Once I bring the Cross Applied DelimitedSplit8Ks into the equation the time jumps up to about 2:00 (12,167,728 Rows )
I have not yet gotten to the part where I try to pivot this, as the database I am working on is a small portion of the total data (~ 1/24th ) and I was wondering if there was a better way performance-wise and general approach wise to tackle this.
Can you please post the table's DDL (create) script and some accurate sample data as an insert statement?
😎
June 7, 2018 at 12:42 pm
Eirikur Eiriksson - Thursday, June 7, 2018 10:56 AMSteven.Grzybowski - Thursday, June 7, 2018 9:24 AMI am working on taking data out of an ERP system and into a data warehouse. One of the unfortunate facts of life with this ERP system is that the links from the Transaction lines to the sources of the transaction lines are all held in a single field delimited with semicolons. There are 3 fields involved in this mapping
The Transaction Line Table (GL_Transline) has 2 Fields incolved in it -
GLL_Source - a GUID that links to a table called GL_Source
GLL_SourceRef- A Semicolon Delimited field that can contain anywhere from 0 - 5 valuesThe GL Source table has a table called GLS_SourceRefMapping that is a semicolon delimited field that determines what the values in the GLL_Sourceref table are related to .
An Example of A GLS_SourceRefmapping Field isItemID;Date;
Then The GLL_Sourceref field would have the item ID and Date connected to the transaction line connected to it
I tried using the query below so I could get the values all split out and then was thinking of doing a pivot afterwards to make a column for each value of GLLMAP with the corresponding Value from GLLitem as the value for that row.
SELECT [GLT].[GLT_TransNbr] [GLL].[GLL_ID] ,mapSplit.Item GLLmap ,mapSplit.ItemNumber GLLmapnumber ,gllsourcesplit.Item gllitem ,gllsourcesplit.ItemNumber Item1FROM [GL_TRans] AS [GLT]
JOIN [GL_Transline] AS [GLL] ON [GLL].[GLL_TransID] = [GLT].[GLT_TransID]
JOIN GL_Source GLS ON GLS.GLS_ID = GLL.GLL_Source
cross apply DelimitedSplit8K(GLS.GLS_SourceRefMap, ';') mapSplit
Cross Apply DelimitedSplit8K(GLL_SourceRef, ';') gllsourcesplit
WHERE mapSplit.ItemNumber = gllsourcesplit.ItemNumber
When I run just the query without the Cross Applied DelimitedSplit8K , It takes about 24 Seconds to complete (5,483,050 Rows)
Once I bring the Cross Applied DelimitedSplit8Ks into the equation the time jumps up to about 2:00 (12,167,728 Rows )
I have not yet gotten to the part where I try to pivot this, as the database I am working on is a small portion of the total data (~ 1/24th ) and I was wondering if there was a better way performance-wise and general approach wise to tackle this.Can you please post the table's DDL (create) script and some accurate sample data as an insert statement?
😎
I am working on putting together Sample data for an insert statement, but here are the Table Creation statements
/****** Object: Table [dbo].[GL_Trans] Script Date: 6/7/2018 1:53:03 PM ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
CREATE TABLE [dbo].[GL_Trans]( [GLT_TransID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [GLT_TransNbr] [nvarchar](25) NULL, [GLT_TransDate] [datetime] NOT NULL, [GLT_EntryDate] [datetime] NOT NULL, [GLT_Source] [uniqueidentifier] NOT NULL, [GLT_SourceRef] [nvarchar](255) NULL, [GLT_Journal] [nvarchar](50) NULL, [GLT_ReversalID] [uniqueidentifier] NULL, [GLT_PostedDate] [datetime] NULL, [GLT_Comments] [nvarchar](255) NULL, [GLT_RecTransID] [uniqueidentifier] NULL, [GLT_Source40] [nvarchar](255) NULL, [GLT_SourceRef40] [nvarchar](255) NULL, [timestamp] [timestamp] NULL, [GLT_UserDef1] [nvarchar](255) NULL, [GLT_UserDef2] [nvarchar](255) NULL, [GLT_UserDef3] [nvarchar](255) NULL, [GLT_UserDef4] [nvarchar](255) NULL, [GLT_UserDef5] [nvarchar](255) NULL, [GLT_CurrencyType] [nvarchar](50) NULL, [GLT_CurrencyRate] [float] NOT NULL, [GLT_EmployeeID] [nvarchar](20) NULL, [GLT_PRJG_RecordID] [uniqueidentifier] NULL, CONSTRAINT [PK_GL_Trans] PRIMARY KEY NONCLUSTERED ( [GLT_TransID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO
ALTER TABLE [dbo].[GL_Trans] ADD CONSTRAINT [DF_GLT_TransID] DEFAULT (newid()) FOR [GLT_TransID]GO
ALTER TABLE [dbo].[GL_Trans] ADD CONSTRAINT [DF_GLT_TransDate] DEFAULT (getdate()) FOR [GLT_TransDate]GO
ALTER TABLE [dbo].[GL_Trans] ADD CONSTRAINT [DF_GLT_EntryDate] DEFAULT (getdate()) FOR [GLT_EntryDate]GO
ALTER TABLE [dbo].[GL_Trans] ADD CONSTRAINT [DF__GL_Trans__GLT_Cu__1DFC4B8A] DEFAULT (1) FOR [GLT_CurrencyRate]GO
ALTER TABLE [dbo].[GL_Trans] WITH CHECK ADD CONSTRAINT [FK_GL_Trans_CURRENCY_TYPE] FOREIGN KEY([GLT_CurrencyType])REFERENCES [dbo].[CURRENCY_TYPE] ([CurrencyType])GO
ALTER TABLE [dbo].[GL_Trans] CHECK CONSTRAINT [FK_GL_Trans_CURRENCY_TYPE]GO
ALTER TABLE [dbo].[GL_Trans] WITH CHECK ADD CONSTRAINT [FK_GL_Trans_GL_RecTrans] FOREIGN KEY([GLT_RecTransID])REFERENCES [dbo].[GL_RecTrans] ([RGLT_TransID])GO
ALTER TABLE [dbo].[GL_Trans] CHECK CONSTRAINT [FK_GL_Trans_GL_RecTrans]GO
ALTER TABLE [dbo].[GL_Trans] WITH CHECK ADD CONSTRAINT [FK_GL_Trans_GL_Source] FOREIGN KEY([GLT_Source])REFERENCES [dbo].[GL_Source] ([GLS_ID])GO
ALTER TABLE [dbo].[GL_Trans] CHECK CONSTRAINT [FK_GL_Trans_GL_Source]GO
ALTER TABLE [dbo].[GL_Trans] WITH CHECK ADD CONSTRAINT [FK_GL_Trans_GL_Trans] FOREIGN KEY([GLT_ReversalID])REFERENCES [dbo].[GL_Trans] ([GLT_TransID])GO
ALTER TABLE [dbo].[GL_Trans] CHECK CONSTRAINT [FK_GL_Trans_GL_Trans]GO
ALTER TABLE [dbo].[GL_Trans] WITH CHECK ADD CONSTRAINT [FK_GLT_ProjectGroup] FOREIGN KEY([GLT_PRJG_RecordID])REFERENCES [dbo].[ProjectGroup] ([PRJG_RecordID])GO
ALTER TABLE [dbo].[GL_Trans] CHECK CONSTRAINT [FK_GLT_ProjectGroup]GO
GO
/****** Object: Table [dbo].[GL_TransLine] Script Date: 6/7/2018 1:54:18 PM ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
CREATE TABLE [dbo].[GL_TransLine]( [GLL_ID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [GLL_TransID] [uniqueidentifier] NOT NULL, [GLL_CurrencyType] [nvarchar](50) NULL, [GLL_Account] [nvarchar](25) NOT NULL, [GLL_Amount] [money] NOT NULL, [GLL_TransAmount] [money] NULL, [GLL_Source] [uniqueidentifier] NOT NULL, [GLL_SourceRef] [nvarchar](255) NULL, [GLL_Comments] [nvarchar](255) NULL, [GLL_ItemID] [nvarchar](100) NULL, [GLL_ItemQty] [money] NULL, [GLL_CustomerID] [nvarchar](10) NULL, [GLL_VendorID] [nvarchar](10) NULL, [GLL_ShipToID] [int] NULL, [GLL_RepName] [nvarchar](10) NULL, [GLL_Line] [int] NOT NULL, [GLL_VATCodeID] [nvarchar](10) NULL, [GLL_ITR_ID] [uniqueidentifier] NULL, [GLL_SalesmanID] [nvarchar](10) NULL, [GLL_WorkOrderID] [nvarchar](25) NULL, [GLL_PRJG_RecordID] [uniqueidentifier] NULL, [GLL_Territory] [nvarchar](30) NULL, [timestamp] [timestamp] NULL, [GLL_UserDef1] [nvarchar](255) NULL, [GLL_UserDef2] [nvarchar](255) NULL, [GLL_UserDef3] [nvarchar](255) NULL, [GLL_UserDef4] [nvarchar](255) NULL, [GLL_UserDef5] [nvarchar](255) NULL, [GLL_GLA_ID] [uniqueidentifier] NULL, [GLL_CSA_RecordID] [uniqueidentifier] NULL, CONSTRAINT [PK_GL_TransLine] PRIMARY KEY NONCLUSTERED ( [GLL_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO
ALTER TABLE [dbo].[GL_TransLine] ADD CONSTRAINT [DF_GLL_ID] DEFAULT (newid()) FOR [GLL_ID]GO
ALTER TABLE [dbo].[GL_TransLine] ADD CONSTRAINT [DF_GLL_Amount] DEFAULT (0) FOR [GLL_Amount]GO
ALTER TABLE [dbo].[GL_TransLine] WITH CHECK ADD CONSTRAINT [FK_GL_TransLine_CURRENCY_TYPE] FOREIGN KEY([GLL_CurrencyType])REFERENCES [dbo].[CURRENCY_TYPE] ([CurrencyType])GO
ALTER TABLE [dbo].[GL_TransLine] CHECK CONSTRAINT [FK_GL_TransLine_CURRENCY_TYPE]GO
ALTER TABLE [dbo].[GL_TransLine] WITH CHECK ADD CONSTRAINT [FK_GL_TransLine_IMA] FOREIGN KEY([GLL_ItemID])REFERENCES [dbo].[Item] ([IMA_ItemID])GO
ALTER TABLE [dbo].[GL_TransLine] CHECK CONSTRAINT [FK_GL_TransLine_IMA]GO
ALTER TABLE [dbo].[GL_TransLine] WITH CHECK ADD CONSTRAINT [FK_GL_TransLine_VEN] FOREIGN KEY([GLL_VendorID])REFERENCES [dbo].[VEN] ([VEN_VendorID])GO
ALTER TABLE [dbo].[GL_TransLine] CHECK CONSTRAINT [FK_GL_TransLine_VEN]GO
ALTER TABLE [dbo].[GL_TransLine] WITH CHECK ADD CONSTRAINT [FK_GLL_Account] FOREIGN KEY([GLL_Account])REFERENCES [dbo].[COA] ([COA_AcctNbr])GO
ALTER TABLE [dbo].[GL_TransLine] CHECK CONSTRAINT [FK_GLL_Account]GO
ALTER TABLE [dbo].[GL_TransLine] WITH CHECK ADD CONSTRAINT [FK_GLL_GLA_ID_GLA_ID] FOREIGN KEY([GLL_GLA_ID])REFERENCES [dbo].[GLAllocate] ([GLA_ID])GO
ALTER TABLE [dbo].[GL_TransLine] CHECK CONSTRAINT [FK_GLL_GLA_ID_GLA_ID]GO
ALTER TABLE [dbo].[GL_TransLine] WITH CHECK ADD CONSTRAINT [FK_GLL_TransID] FOREIGN KEY([GLL_TransID])REFERENCES [dbo].[GL_Trans] ([GLT_TransID])GO
ALTER TABLE [dbo].[GL_TransLine] CHECK CONSTRAINT [FK_GLL_TransID]GO
/****** Object: Table [dbo].[GL_Source] Script Date: 6/7/2018 1:54:39 PM ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
CREATE TABLE [dbo].[GL_Source]( [GLS_ID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [GLS_Name] [nvarchar](50) NOT NULL, [GLS_SourceRefMap] [nvarchar](255) NOT NULL, [GLS_Cycle] [nvarchar](255) NULL, [GLS_Object] [nvarchar](255) NULL, [GLS_OpenOption] [int] NULL, [GLS_Parameters] [nvarchar](255) NULL, [GLS_Header] [bit] NOT NULL, [timestamp] [timestamp] NULL, [GLS_UserDef1] [nvarchar](255) NULL, [GLS_UserDef2] [nvarchar](255) NULL, [GLS_UserDef3] [nvarchar](255) NULL, [GLS_UserDef4] [nvarchar](255) NULL, [GLS_UserDef5] [nvarchar](255) NULL, [GLS_Journal] [nvarchar](50) NOT NULL, CONSTRAINT [PK_GL_Source] PRIMARY KEY NONCLUSTERED ( [GLS_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO
ALTER TABLE [dbo].[GL_Source] ADD CONSTRAINT [DF_GLS_ID] DEFAULT (newid()) FOR [GLS_ID]GO
ALTER TABLE [dbo].[GL_Source] ADD CONSTRAINT [DF_GLS_Header] DEFAULT (0) FOR [GLS_Header]GO
ALTER TABLE [dbo].[GL_Source] ADD CONSTRAINT [DF_GLS_Journal] DEFAULT ('General') FOR [GLS_Journal]GO
June 7, 2018 at 1:08 pm
If someone is interested on code that is actually readable and without errors.
CREATE TABLE [dbo].[GL_Source]
(
[GLS_ID] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL CONSTRAINT [DF_GLS_ID] DEFAULT (NEWID()),
[GLS_Name] [NVARCHAR](50) NOT NULL,
[GLS_SourceRefMap] [NVARCHAR](255) NOT NULL,
[GLS_Cycle] [NVARCHAR](255) NULL,
[GLS_Object] [NVARCHAR](255) NULL,
[GLS_OpenOption] [INT] NULL,
[GLS_Parameters] [NVARCHAR](255) NULL,
[GLS_Header] [BIT] NOT NULL CONSTRAINT [DF_GLS_Header] DEFAULT (0),
[timestamp] [TIMESTAMP] NULL,
[GLS_UserDef1] [NVARCHAR](255) NULL,
[GLS_UserDef2] [NVARCHAR](255) NULL,
[GLS_UserDef3] [NVARCHAR](255) NULL,
[GLS_UserDef4] [NVARCHAR](255) NULL,
[GLS_UserDef5] [NVARCHAR](255) NULL,
[GLS_Journal] [NVARCHAR](50) NOT NULL CONSTRAINT [DF_GLS_Journal] DEFAULT ('General'),
CONSTRAINT [PK_GL_Source]
PRIMARY KEY NONCLUSTERED ([GLS_ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
) ;
GO
CREATE TABLE [dbo].[GL_Trans]
(
[GLT_TransID] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL CONSTRAINT [DF_GLT_TransID] DEFAULT (NEWID()),
[GLT_TransNbr] [NVARCHAR](25) NULL,
[GLT_TransDate] [DATETIME] NOT NULL CONSTRAINT [DF_GLT_TransDate] DEFAULT (GETDATE()),
[GLT_EntryDate] [DATETIME] NOT NULL CONSTRAINT [DF_GLT_EntryDate] DEFAULT (GETDATE()),
[GLT_Source] [UNIQUEIDENTIFIER] NOT NULL,
[GLT_SourceRef] [NVARCHAR](255) NULL,
[GLT_Journal] [NVARCHAR](50) NULL,
[GLT_ReversalID] [UNIQUEIDENTIFIER] NULL,
[GLT_PostedDate] [DATETIME] NULL,
[GLT_Comments] [NVARCHAR](255) NULL,
[GLT_RecTransID] [UNIQUEIDENTIFIER] NULL,
[GLT_Source40] [NVARCHAR](255) NULL,
[GLT_SourceRef40] [NVARCHAR](255) NULL,
[timestamp] [TIMESTAMP] NULL,
[GLT_UserDef1] [NVARCHAR](255) NULL,
[GLT_UserDef2] [NVARCHAR](255) NULL,
[GLT_UserDef3] [NVARCHAR](255) NULL,
[GLT_UserDef4] [NVARCHAR](255) NULL,
[GLT_UserDef5] [NVARCHAR](255) NULL,
[GLT_CurrencyType] [NVARCHAR](50) NULL,
[GLT_CurrencyRate] [FLOAT] NOT NULL DEFAULT (1),
[GLT_EmployeeID] [NVARCHAR](20) NULL,
[GLT_PRJG_RecordID] [UNIQUEIDENTIFIER] NULL,
CONSTRAINT [PK_GL_Trans]
PRIMARY KEY NONCLUSTERED ([GLT_TransID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
) ;
GO
--ALTER TABLE [dbo].[GL_Trans] WITH CHECK
--ADD CONSTRAINT [FK_GL_Trans_CURRENCY_TYPE] FOREIGN KEY ([GLT_CurrencyType]) REFERENCES [dbo].[CURRENCY_TYPE] ([CurrencyType]);
--GO
--ALTER TABLE [dbo].[GL_Trans] WITH CHECK
--ADD CONSTRAINT [FK_GL_Trans_GL_RecTrans] FOREIGN KEY ([GLT_RecTransID]) REFERENCES [dbo].[GL_RecTrans] ([RGLT_TransID]);
--GO
ALTER TABLE [dbo].[GL_Trans] WITH CHECK
ADD CONSTRAINT [FK_GL_Trans_GL_Source] FOREIGN KEY ([GLT_Source]) REFERENCES [dbo].[GL_Source] ([GLS_ID]);
GO
ALTER TABLE [dbo].[GL_Trans] WITH CHECK
ADD CONSTRAINT [FK_GL_Trans_GL_Trans] FOREIGN KEY ([GLT_ReversalID]) REFERENCES [dbo].[GL_Trans] ([GLT_TransID]);
GO
--ALTER TABLE [dbo].[GL_Trans] WITH CHECK
--ADD CONSTRAINT [FK_GLT_ProjectGroup] FOREIGN KEY ([GLT_PRJG_RecordID]) REFERENCES [dbo].[ProjectGroup] ([PRJG_RecordID]);
--GO
CREATE TABLE [dbo].[GL_TransLine]
(
[GLL_ID] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL CONSTRAINT [DF_GLL_ID] DEFAULT (NEWID()),
[GLL_TransID] [UNIQUEIDENTIFIER] NOT NULL,
[GLL_CurrencyType] [NVARCHAR](50) NULL,
[GLL_Account] [NVARCHAR](25) NOT NULL,
[GLL_Amount] [MONEY] NOT NULL CONSTRAINT [DF_GLL_Amount] DEFAULT (0),
[GLL_TransAmount] [MONEY] NULL,
[GLL_Source] [UNIQUEIDENTIFIER] NOT NULL,
[GLL_SourceRef] [NVARCHAR](255) NULL,
[GLL_Comments] [NVARCHAR](255) NULL,
[GLL_ItemID] [NVARCHAR](100) NULL,
[GLL_ItemQty] [MONEY] NULL,
[GLL_CustomerID] [NVARCHAR](10) NULL,
[GLL_VendorID] [NVARCHAR](10) NULL,
[GLL_ShipToID] [INT] NULL,
[GLL_RepName] [NVARCHAR](10) NULL,
[GLL_Line] [INT] NOT NULL,
[GLL_VATCodeID] [NVARCHAR](10) NULL,
[GLL_ITR_ID] [UNIQUEIDENTIFIER] NULL,
[GLL_SalesmanID] [NVARCHAR](10) NULL,
[GLL_WorkOrderID] [NVARCHAR](25) NULL,
[GLL_PRJG_RecordID] [UNIQUEIDENTIFIER] NULL,
[GLL_Territory] [NVARCHAR](30) NULL,
[timestamp] [TIMESTAMP] NULL,
[GLL_UserDef1] [NVARCHAR](255) NULL,
[GLL_UserDef2] [NVARCHAR](255) NULL,
[GLL_UserDef3] [NVARCHAR](255) NULL,
[GLL_UserDef4] [NVARCHAR](255) NULL,
[GLL_UserDef5] [NVARCHAR](255) NULL,
[GLL_GLA_ID] [UNIQUEIDENTIFIER] NULL,
[GLL_CSA_RecordID] [UNIQUEIDENTIFIER] NULL,
CONSTRAINT [PK_GL_TransLine]
PRIMARY KEY NONCLUSTERED ([GLL_ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
);
GO
--ALTER TABLE [dbo].[GL_TransLine] WITH CHECK
--ADD CONSTRAINT [FK_GL_TransLine_CURRENCY_TYPE] FOREIGN KEY ([GLL_CurrencyType]) REFERENCES [dbo].[CURRENCY_TYPE] ([CurrencyType]);
--GO
--ALTER TABLE [dbo].[GL_TransLine] WITH CHECK
--ADD CONSTRAINT [FK_GL_TransLine_IMA] FOREIGN KEY ([GLL_ItemID]) REFERENCES [dbo].[Item] ([IMA_ItemID]);
--GO
--ALTER TABLE [dbo].[GL_TransLine] WITH CHECK
--ADD CONSTRAINT [FK_GL_TransLine_VEN] FOREIGN KEY ([GLL_VendorID]) REFERENCES [dbo].[VEN] ([VEN_VendorID]);
--GO
--ALTER TABLE [dbo].[GL_TransLine] WITH CHECK
--ADD CONSTRAINT [FK_GLL_Account] FOREIGN KEY ([GLL_Account]) REFERENCES [dbo].[COA] ([COA_AcctNbr]);
--GO
--ALTER TABLE [dbo].[GL_TransLine] WITH CHECK
--ADD CONSTRAINT [FK_GLL_GLA_ID_GLA_ID] FOREIGN KEY ([GLL_GLA_ID]) REFERENCES [dbo].[GLAllocate] ([GLA_ID]);
--GO
ALTER TABLE [dbo].[GL_TransLine] WITH CHECK
ADD CONSTRAINT [FK_GLL_TransID] FOREIGN KEY ([GLL_TransID]) REFERENCES [dbo].[GL_Trans] ([GLT_TransID]);
GO
June 7, 2018 at 2:24 pm
Can you also tell us how many rows each table has.
From what you said before I would think GL_Transline is the big table, but GL_Source I would be inclined to think the volumes would be quite low.
If this is indeed the case you may be better off spiting out GL_Source into a temp table first, and then joining this one on the the results of the split on the big one.
Also and as your tables have a rowversion/timestamp field you should be able to do a initial bulk operation and deltas after it easily - unless a significant number of records are updated retrospectively.
June 14, 2018 at 11:38 am
After splitting things out into temp tables before attempting to do the big calculations, I discovered that the data in the semi-colon delimited fields was not actually usable, as a large percentage of the data was inconsistent, and it turns out that the fields were not updated during some ERP upgrades, so I ended up just taking a completely different approach with a larger number of fact tables.
June 14, 2018 at 9:38 pm
Steven.Grzybowski - Thursday, June 14, 2018 11:38 AMAfter splitting things out into temp tables before attempting to do the big calculations, I discovered that the data in the semi-colon delimited fields was not actually usable, as a large percentage of the data was inconsistent, and it turns out that the fields were not updated during some ERP upgrades, so I ended up just taking a completely different approach with a larger number of fact tables.
What approach was that?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2018 at 5:03 am
Try to separate GLL together with its CROSS APPLY and GLS together with its CROSS APLY into different subqueries (in form of either der. I've tables or CTEs) and join them by GLS.GLS_ID = GLL.GLL_Source and GLS.ItemNumber = GLL.ItemNumber
_____________
Code for TallyGenerator
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply