Process streamlining ideas

Question

Process streamlining ideas

philcart

SSC-Forever

Points: 47794
More actions
February 14, 2006 at 7:54 pm

#93268

We have some data in a table on our Progress database that stores data by year and utilises repeating fields to represent months within the year. The table will always have approx. 650 rows per year and currently holds 12 years worth of data.
When we transfer this data to SQL Server the data shows up in a single field that has the values seperated by semi-colons.
I've got a stored procedure that splits the field up so that we have a record for each month. This process has been running fine for quite a few months and takes approx 5-6 mins to run. Now we want to run the process more often and need to make it run faster.
Hopefully someone has some improvement ideas, finds a glaring mistake, or has a better idea to streamline the process.
The following is the code for the source and destination tables, some sample data and the T-SQL statement to split the fields.
Note: The code below creates temp tables, but for the production process the tables are all permanently located in the same database.
-- BEGIN CODE --
SET NOCOUNT ON
SET DATEFORMAT dmy
-- nums table for split
CREATE TABLE #Nums (
[n] [int] NOT NULL ,
CONSTRAINT [PK_Nums] PRIMARY KEY CLUSTERED
(
[n]
)
)
DECLARE @n int
SET @n = 1
WHILE @n < 20
BEGIN
INSERT INTO #Nums VALUES ( @N )
SET @N = @n + 1
END
-- SOURCE TABLE
-- *** NOTE: Structure cannot be altered
CREATE TABLE #advbrok_year (
[AdvId] [int] NOT NULL
, [advyear] [int] NOT NULL
, [brokearn] [varchar] (384) NULL
, [brokord] [varchar] (384) NULL
, [brokcom] [varchar] (384) NULL
, [conscli] [varchar] (384) NULL
, [consord] [varchar] (384) NULL
, [conscom] [varchar] (384) NULL
, [countcli] [varchar] (384) NULL
, [countord] [varchar] (384) NULL
, [countcom] [varchar] (384) NULL
, [misccli] [varchar] (384) NULL
, [miscord] [varchar] (384) NULL
, [misccom] [varchar] (384) NULL
CONSTRAINT [PK_advbrok_Adv_Year] PRIMARY KEY NONCLUSTERED
(
[AdvId],
[advyear]
)
)
-- DESTINATION
-- *** NOTE: Structure CAN be altered if necessary
CREATE TABLE #advbrok_mth (
[AdvId] [int] NOT NULL
, [advmth] [datetime] NOT NULL
, [brokearn] [decimal](18, 4) NULL
, [brokord] [decimal](18, 4) NULL
, [brokcom] [decimal](18, 4) NULL
, [conscli] [decimal](18, 4) NULL
, [consord] [decimal](18, 4) NULL
, [conscom] [decimal](18, 4) NULL
, [countcli] [decimal](18, 4) NULL
, [countord] [decimal](18, 4) NULL
, [countcom] [decimal](18, 4) NULL
, [misccli] [decimal](18, 4) NULL
, [miscord] [decimal](18, 4) NULL
, [misccom] [decimal](18, 4) NULL
CONSTRAINT [PK_advbrok_Adv_Mth] PRIMARY KEY NONCLUSTERED
(
[AdvId],
[advmth]
)
)
-- LOAD SAMPLE SOURCE DATA
INSERT INTO #advbrok_year
VALUES (
1, 2006, '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0'
, '227443266.14;460116268.56;0;0;0;0;0;0;0;0;0;0', '227443266.14;460116268.56;0;0;0;0;0;0;0;0;0;0'
, '227443266.14;460136811.36;0;0;0;0;0;0;0;0;0;0', '940;655;0;0;0;0;0;0;0;0;0;0'
, '940;655;0;0;0;0;0;0;0;0;0;0', '940;657;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0'
, '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0'
)
INSERT INTO #advbrok_year
VALUES (
2, 2006, '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0'
, '97800;900;0;0;0;0;0;0;0;0;0;0', '97800;900;0;0;0;0;0;0;0;0;0;0', '97800;900;0;0;0;0;0;0;0;0;0;0'
, '3;1;0;0;0;0;0;0;0;0;0;0', '3;1;0;0;0;0;0;0;0;0;0;0', '3;1;0;0;0;0;0;0;0;0;0;0'
, '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0'
)
INSERT INTO #advbrok_year
VALUES (
3, 2006, '2984.1;2391.74;0;0;0;0;0;0;0;0;0;0', '2984.1;2391.74;0;0;0;0;0;0;0;0;0;0'
, '2984.1;2391.74;0;0;0;0;0;0;0;0;0;0', '298410;282782.2;0;0;0;0;0;0;0;0;0;0'
, '298410;282782.2;0;0;0;0;0;0;0;0;0;0', '298410;282782.2;0;0;0;0;0;0;0;0;0;0'
, '2;4;0;0;0;0;0;0;0;0;0;0', '2;4;0;0;0;0;0;0;0;0;0;0', '2;4;0;0;0;0;0;0;0;0;0;0'
, '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0'
)
INSERT INTO #advbrok_year
VALUES (
8, 2006, '75626.02;42851.16;0;0;0;0;0;0;0;0;0;0', '75626.02;42851.16;0;0;0;0;0;0;0;0;0;0'
, '75626.02;42851.16;0;0;0;0;0;0;0;0;0;0', '11324253.84;6056374.73;0;0;0;0;0;0;0;0;0;0'
, '10851838.31;6056374.73;0;0;0;0;0;0;0;0;0;0', '9540527.48;5855593;0;0;0;0;0;0;0;0;0;0'
, '340;218;0;0;0;0;0;0;0;0;0;0', '331;218;0;0;0;0;0;0;0;0;0;0', '326;213;0;0;0;0;0;0;0;0;0;0'
, '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0'
)
INSERT INTO #advbrok_year
VALUES (
11, 2006, '22283.16;14372.38;0;0;0;0;0;0;0;0;0;0', '22283.16;14372.38;0;0;0;0;0;0;0;0;0;0'
, '22283.16;14372.38;0;0;0;0;0;0;0;0;0;0', '3383944.33;2141315.57;0;0;0;0;0;0;0;0;0;0'
, '3383944.33;2141315.57;0;0;0;0;0;0;0;0;0;0', '3383944.33;2141315.57;0;0;0;0;0;0;0;0;0;0'
, '149;68;0;0;0;0;0;0;0;0;0;0', '149;68;0;0;0;0;0;0;0;0;0;0', '149;68;0;0;0;0;0;0;0;0;0;0'
, '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0', '0;0;0;0;0;0;0;0;0;0;0;0'
)
SELECT * FROM #advbrok_year
-- split advbrok into seperate records for each month
INSERT INTO #advbrok_mth (
[AdvID]
, [advmth]
, [brokearn]
, [brokord]
, [brokcom]
, [conscli]
, [consord]
, [conscom]
, [countcli]
, [countord]
, [countcom]
, [misccli]
, [miscord]
, [misccom]
)
SELECT
[brokearn].[AdvID]
, [brokearn].[advmth]
, [brokearn].[brokearn]
, [brokord].[brokord]
, [brokcom].[brokcom]
, [conscli].[conscli]
, [consord].[consord]
, [conscom].[conscom]
, [countcli].[countcli]
, [countord].[countord]
, [countcom].[countcom]
, [misccli].[misccli]
, [miscord].[miscord]
, [misccom].[misccom]
FROM (
--split brokearn
SELECT DISTINCT
[AdvID]
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([brokearn], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([brokearn], num1.n,
CHARINDEX(';', [brokearn] + ';', num1.n) - num1.n) as decimal(18,4)) AS [brokearn]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([brokearn])
AND SUBSTRING(';' + [brokearn], num1.n, 1) = ';'
) as brokearn
INNER JOIN (
--split brokord
SELECT DISTINCT
AdvID
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([brokord], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([brokord], num1.n,
CHARINDEX(';', [brokord] + ';', num1.n) - num1.n) as decimal(18,4)) AS [brokord]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([brokord])
AND SUBSTRING(';' + [brokord], num1.n, 1) = ';'
) as brokord
ON brokearn.AdvID = brokord.AdvID
AND brokearn.AdvMth = brokord.AdvMth
INNER JOIN (
--split brokcom
SELECT DISTINCT
AdvID
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([brokcom], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([brokcom], num1.n,
CHARINDEX(';', [brokcom] + ';', num1.n) - num1.n) as decimal(18,4)) AS [brokcom]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([brokcom])
AND SUBSTRING(';' + [brokcom], num1.n, 1) = ';'
) as brokcom
ON brokearn.AdvID = brokcom.AdvID
AND brokearn.AdvMth = brokcom.AdvMth
INNER JOIN (
--split conscli
SELECT DISTINCT
AdvID
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([conscli], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([conscli], num1.n,
CHARINDEX(';', [conscli] + ';', num1.n) - num1.n) as decimal(18,4)) AS [conscli]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([conscli])
AND SUBSTRING(';' + [conscli], num1.n, 1) = ';'
) as conscli
ON brokearn.AdvID = conscli.AdvID
AND brokearn.AdvMth = conscli.AdvMth
INNER JOIN (
--split consord
SELECT DISTINCT
AdvID
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([consord], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([consord], num1.n,
CHARINDEX(';', [consord] + ';', num1.n) - num1.n) as decimal(18,4)) AS [consord]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([consord])
AND SUBSTRING(';' + [consord], num1.n, 1) = ';'
) as consord
ON brokearn.AdvID = consord.AdvID
AND brokearn.AdvMth = consord.AdvMth
INNER JOIN (
--split conscom
SELECT DISTINCT
AdvID
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([conscom], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([conscom], num1.n,
CHARINDEX(';', [conscom] + ';', num1.n) - num1.n) as decimal(18,4)) AS [conscom]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([conscom])
AND SUBSTRING(';' + [conscom], num1.n, 1) = ';'
) as conscom
ON brokearn.AdvID = conscom.AdvID
AND brokearn.AdvMth = conscom.AdvMth
INNER JOIN (
--split countcli
SELECT DISTINCT
AdvID
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([countcli], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([countcli], num1.n,
CHARINDEX(';', [countcli] + ';', num1.n) - num1.n) as decimal(18,4)) AS [countcli]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([countcli])
AND SUBSTRING(';' + [countcli], num1.n, 1) = ';'
) as countcli
ON brokearn.AdvID = countcli.AdvID
AND brokearn.AdvMth = countcli.AdvMth
INNER JOIN (
--split countord
SELECT DISTINCT
AdvID
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([countord], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([countord], num1.n,
CHARINDEX(';', [countord] + ';', num1.n) - num1.n) as decimal(18,4)) AS [countord]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([countord])
AND SUBSTRING(';' + [countord], num1.n, 1) = ';'
) as countord
ON brokearn.AdvID = countord.AdvID
AND brokearn.AdvMth = countord.AdvMth
INNER JOIN (
--split countcom
SELECT DISTINCT
AdvID
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([countcom], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([countcom], num1.n,
CHARINDEX(';', [countcom] + ';', num1.n) - num1.n) as decimal(18,4)) AS [countcom]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([countcom])
AND SUBSTRING(';' + [countcom], num1.n, 1) = ';'
) as countcom
ON brokearn.AdvID = countcom.AdvID
AND brokearn.AdvMth = countcom.AdvMth
INNER JOIN (
--split misccli
SELECT DISTINCT
AdvID
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([misccli], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([misccli], num1.n,
CHARINDEX(';', [misccli] + ';', num1.n) - num1.n) as decimal(18,4)) AS [misccli]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([misccli])
AND SUBSTRING(';' + [misccli], num1.n, 1) = ';'
) as misccli
ON brokearn.AdvID = misccli.AdvID
AND brokearn.AdvMth = misccli.AdvMth
INNER JOIN (
--split miscord
SELECT DISTINCT
AdvID
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([miscord], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([miscord], num1.n,
CHARINDEX(';', [miscord] + ';', num1.n) - num1.n) as decimal(18,4)) AS [miscord]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([miscord])
AND SUBSTRING(';' + [miscord], num1.n, 1) = ';'
) as miscord
ON brokearn.AdvID = miscord.AdvID
AND brokearn.AdvMth = miscord.AdvMth
INNER JOIN (
--split misccom
SELECT DISTINCT
AdvID
, CAST(CAST([advyear] as varchar(4))
+ RIGHT('00' + CAST((num1.n -
LEN(REPLACE(LEFT([misccom], num1.n), ';', '')) + 1) as varchar(4)), 2)
+ '01' as datetime) as AdvMth
, CAST(SUBSTRING([misccom], num1.n,
CHARINDEX(';', [misccom] + ';', num1.n) - num1.n) as decimal(18,4)) AS [misccom]
FROM #advbrok_year
INNER JOIN #Nums num1
ON num1.n <= LEN([misccom])
AND SUBSTRING(';' + [misccom], num1.n, 1) = ';'
) as misccom
ON brokearn.AdvID = misccom.AdvID
AND brokearn.AdvMth = misccom.AdvMth
-- show results
SELECT * FROM #advbrok_mth
-- Remove Temp tables
DROP TABLE #advbrok_mth
DROP TABLE #advbrok_year
DROP TABLE #Nums
-- END CODE --
--------------------
Colt 45 - the original point and click interface

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jesper-244176 SSCertifiable Points: 7082 More actions · Answer 1

You need more numbers in your #Nums table

Apart from that, I think I would start by unpivoting data. Then I'd split it to find the data I need. Finally, I'd bring it back on pivot form, if I had to (I haven't carried out this step in the code below).

The code below might be faster, because it's simpler (at least in my opinion ), and doesn't use as many string manipulations. On the other hand, I use a couple of extra temp tables (which should be indexed, by the way)...

The code is as follows (create all tables as in your code, then run the code below):

-- Unpivot data

create table #advbrok

(

[AdvId] [int] NOT NULL,

[AdvYear] [int] NOT NULL,

Type varchar(20),

Value varchar(384)

)

insert into #advbrok (AdvId, AdvYear, Type, Value) select AdvId, AdvYear, 'brokearn', brokearn from #advbrok_year

insert into #advbrok (AdvId, AdvYear, Type, Value) select AdvId, AdvYear, 'brokcom', brokcom from #advbrok_year

insert into #advbrok (AdvId, AdvYear, Type, Value) select AdvId, AdvYear, 'conscli', conscli from #advbrok_year

insert into #advbrok (AdvId, AdvYear, Type, Value) select AdvId, AdvYear, 'consord', consord from #advbrok_year

insert into #advbrok (AdvId, AdvYear, Type, Value) select AdvId, AdvYear, 'conscom', conscom from #advbrok_year

insert into #advbrok (AdvId, AdvYear, Type, Value) select AdvId, AdvYear, 'countcli', countcli from #advbrok_year

insert into #advbrok (AdvId, AdvYear, Type, Value) select AdvId, AdvYear, 'countord', countord from #advbrok_year

insert into #advbrok (AdvId, AdvYear, Type, Value) select AdvId, AdvYear, 'countcom', countcom from #advbrok_year

insert into #advbrok (AdvId, AdvYear, Type, Value) select AdvId, AdvYear, 'misccli', misccli from #advbrok_year

insert into #advbrok (AdvId, AdvYear, Type, Value) select AdvId, AdvYear, 'miscord', miscord from #advbrok_year

insert into #advbrok (AdvId, AdvYear, Type, Value) select AdvId, AdvYear, 'misccom', misccom from #advbrok_year

-- Split data:

create table #advbrok2

(

Id int identity(1, 1),

[AdvId] [int] NOT NULL,

[AdvYear] [int] NOT NULL,

Type varchar(20),

Value decimal(18, 4)

)

insert into #advbrok2 (AdvId, AdvYear, Type, Value)

select AdvId, AdvYear, Type, cast(substring(Value, n, charindex(';', Value + ';', n) - n) as decimal(18, 4))

from #advbrok cross join #Nums

where

n - 1 <= len(Value)

and (substring(Value, n - 1, 1) = ';' or n = 1)

order by AdvId, AdvYear, Type, n

-- Final select:

select dateadd(m, a.Id - dt.minid, dateadd(yy, a.advYear - 1900, '1900')) as Date,

a.AdvId,

a.Type,

a.Value

from #advbrok2 a

inner join

(

select AdvId, AdvYear, Type, min(Id) as minid

from #advbrok2

group by AdvId, AdvYear, Type

)

dt

on a.AdvId = dt.AdvId and a.AdvYear = dt.AdvYear and a.Type = dt.Type

order by a.AdvId, a.AdvYear, dateadd(m, a.Id - dt.minid, dateadd(yy, a.advYear - 1900, '1900')), a.Type