Hello,
I have a procedure stored in my application which takes a very very long time to execute.
It deals with the relations of compositon of a product (a computer is made up of a screen, a keyboard and electronic components).
The subject is about component/ sub component relations (article or product breakdown) also called non hierachical relationships.
The products have a code composed of 22 characters (ARTICLE_CODE) but the links in the breakdown between articles are builted on the first 12 characters (ROOT_ARTICLE_CODE).
So in the relationship table we can find an article (V_ROOT_PART_NUMBER_CODE) associated with several articles (N_ARTICLE_FATHER_ID) because they share the 12 first characters
Example
N_ARTICLE_ID;N_FATHER_ID;N_DATASOURCE_ID;N_SOURCE_ID;V_ROOT_PART_NUMBER_CODE;V_NODE_LABEL;B_ACTIVE;V_FATHER_ROOT_PART_NUMBER_CODE;V_FATHER_NODE_LABEL;B_FATHER_ACTIVE
1005;20637;2;14;5M4K85638000;5M4K85638000 - TEST7;1;TK18J1066002;TK18J1066002 - TEST1;1
1005;20638;2;14;5M4K85638000;5M4K85638000 - TEST7;1;A2HG31T67000;A2HG31T67000 - TEST2;1
1005;20639;2;14;5M4K85638000;5M4K85638000 - TEST7;1;A2HG31T67002;A2HG31T67002 - TEST3;1
1005;23946;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD231000;T5D8FD231000 - TEST4;1
1005;24034;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1
1005;109862;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1
1005;109876;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1
1005;109877;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1
9560;706;2;12;5F4RE5135200;5F4RE5135200 - TEST8;1;5F4RE5145002;5F4RE5145002 - TEST6;1
9550;706;2;12;5F4RE5128202;5F4RE5128202 - TEST9;1;5F4RE5145002;5F4RE5145002 - TEST6;1
14049;706;2;12;5F4RE5126200;5F4RE5126200 - TEST10;1;5F4RE5145002;5F4RE5145002 - TEST6;1
14049;707;2;12;5F4RE5126200;5F4RE5126200 - TEST10;1;5F4RE5145002;5F4RE5145002 - TEST6;1
9550;707;2;-6;5F4RE5128202;5F4RE5128202 - TEST9;1;5F4RE5145002;5F4RE5145002 - TEST6;1
9560;707;2;-6;5F4RE5135200;5F4RE5135200 - TEST8;1;5F4RE5145002;5F4RE5145002 - TEST6;1
9554;707;2;-6;5F4RE5132202;5F4RE5132202 - TEST11;1;5F4RE5145002;5F4RE5145002 - TEST6;1
705;707;2;-6;5F4RE5128202;5F4RE5128202 - TEST12;1;5F4RE5145002;5F4RE5145002 - TEST6;1
18405;707;2;-6;5F4RE5145220;5F4RE5145220 - TEST13;1;5F4RE5145002;5F4RE5145002 - TEST6;1
But sometimes links are missing between identifiers (I have already an existing link between two articles. I have just added an article (new N_ARTICLE_ID) common on the first 12 characters with one of the previous elements.
I must thus create a new link father/ child because of this common element.
I can create several links if the article is linked with seral articles at a component level (the same keyboard can be used in several models of computers)
I make the same procedure for the father (by adding the links with existing child for an article common at 12 characters).
The problem is that it is a very very long procedure.
I have 75000 articles and it is necessary at least 48H for me to manage them.
Could you help me to optimize the code of this stored procedure?
Thank you by advance
Code Snippet
CREATE TABLE [ARTICLE] (
[N_ARTICLE_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[V_CODE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[V_PART_NUMBER_LABEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[B_ACTIVE] [bit] NULL ,
CONSTRAINT [PK_ARTICLE] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_ARTICLE_CODE_UNIQUE] UNIQUE NONCLUSTERED
(
[V_CODE]
) ON [PRIMARY]
GO
CREATE TABLE [ARTICLE_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL ,
[N_ARTICLE_FATHER_ID] [bigint] NOT NULL ,
[N_DATASOURCE_ID] [bigint] NOT NULL ,
[N_SOURCE_ID] [bigint] NOT NULL ,
[V_ROOT_CODE] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[V_NODE_LABEL] [char] (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[B_ACTIVE] [bit] NULL ,
[V_FATHER_ROOT_CODE] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[V_FATHER_NODE_LABEL] [char] (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[B_FATHER_ACTIVE] [bit] NULL ,
CONSTRAINT [PK_ARTICLE_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID],
[N_ARTICLE_FATHER_ID],
[N_DATASOURCE_ID],
[N_SOURCE_ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ARTICLE_BREAKDOWN_ARTICLE] FOREIGN KEY
(
[N_ARTICLE_ID]
) REFERENCES [ARTICLE] (
[N_ARTICLE_ID]
),
CONSTRAINT [FK_ARTICLE_BREAKDOWN_ARTICLE1] FOREIGN KEY
(
[N_ARTICLE_FATHER_ID]
) REFERENCES [ARTICLE] (
[N_ARTICLE_ID]
),
CONSTRAINT [FK_ARTICLE_BREAKDOWN_SOURCE] FOREIGN KEY
(
[N_SOURCE_ID]
) REFERENCES [SOURCE] (
[N_SOURCE_ID]
)
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Code Snippet
CREATE PROCEDURE [dbo].[LOAD_ARTICLE_BREAKDOWN_PACKAGE]
@.ARTICLE_ID AS BIGINT= NULL,@.ARTICLE_FATHER_ID AS BIGINT= NULL, @.DATASOURCE AS BIGINT= NULL, @.SOURCE AS BIGINT= NULL
AS
BEGIN
SET NOCOUNT ON;
If @.ARTICLE_ID IS NOT NULL AND @.ARTICLE_FATHER_ID IS NOT NULL AND @.DATASOURCE IS NOT NULL AND @.SOURCE IS NOT NULL
BEGIN
/*Table pour mettre tous les pn fils
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]
END*/
CREATE TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL
)
ALTER TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] WITH NOCHECK ADD
CONSTRAINT [PK_ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY]
/*Table pour mettre tous les pn père
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]
END*/
CREATE TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL
)
ALTER TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] WITH NOCHECK ADD
CONSTRAINT [PK_ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY]
Declare @.LABEL1 as CHAR(50)
Declare @.SPARE1 as BIT
Declare @.LABEL2 as CHAR(50)
Declare @.SPARE2 as BIT
Declare @.ARTICLEC as BIGINT
Declare @.ARTICLEF as BIGINT
/*information sur le premier PN*/
SET @.LABEL1 = (SELECT TOP 1 CASE WHEN PNLABEL1.V_PART_NUMBER_LABEL IS NULL THEN '' ELSE PNLABEL1.V_PART_NUMBER_LABEL END FROM ARTICLE PNLABEL1
WHERE PNLABEL1.N_ARTICLE_ID IN (SELECT PNLABEL2.N_ARTICLE_ID FROM ARTICLE PNLABEL2 WHERE LEFT(PNLABEL2.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID)) order by PNLABEL1.V_PART_NUMBER_LABEL desc)
SET @.SPARE1 = (SELECT TOP 1 PNSPARE.B_ACTIVE FROM ARTICLE PNSPARE
WHERE PNSPARE.N_ARTICLE_ID in (SELECT PNSPARE1.N_ARTICLE_ID FROM ARTICLE PNSPARE1 WHERE (LEFT(PNSPARE1.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID))))
/*information sur le second PN*/
SET @.LABEL2 = (SELECT TOP 1 CASE WHEN PNLABEL1.V_PART_NUMBER_LABEL IS NULL THEN '' ELSE PNLABEL1.V_PART_NUMBER_LABEL END FROM ARTICLE PNLABEL1
WHERE PNLABEL1.N_ARTICLE_ID IN (SELECT PNLABEL2.N_ARTICLE_ID FROM ARTICLE PNLABEL2 WHERE LEFT(PNLABEL2.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID)) order by PNLABEL1.V_PART_NUMBER_LABEL desc)
SET @.SPARE2 = (SELECT TOP 1 PNSPARE.B_ACTIVE FROM ARTICLE PNSPARE
WHERE PNSPARE.N_ARTICLE_ID in (SELECT PNSPARE1.N_ARTICLE_ID FROM ARTICLE PNSPARE1 WHERE (LEFT(PNSPARE1.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID))))
/*recherche les articles communs a 12 dans la première boucle pour le fils*/
/*recherche les articles communs a 12 dans la seconde boucle pour le père*/
DELETE FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_SOURCE_ID=-6) AND (N_ARTICLE_ID = @.ARTICLE_ID) AND (N_ARTICLE_FATHER_ID = @.ARTICLE_FATHER_ID)
IF (NOT EXISTS(SELECT * FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_SOURCE_ID=@.SOURCE) AND (N_ARTICLE_ID = @.ARTICLE_ID) AND (N_ARTICLE_FATHER_ID = @.ARTICLE_FATHER_ID)))
BEGIN
INSERT INTO ARTICLE_BREAKDOWN
(N_ARTICLE_ID, N_ARTICLE_FATHER_ID, N_DATASOURCE_ID, N_SOURCE_ID, V_ROOT_CODE, V_NODE_LABEL,
B_ACTIVE, V_FATHER_ROOT_CODE, V_FATHER_NODE_LABEL, B_FATHER_ACTIVE)
SELECT PN1.N_ARTICLE_ID, PN2.N_ARTICLE_ID AS FATHER_ID, @.DATASOURCE AS DATASOURCE, @.SOURCE AS SOURCE, left(PN1.V_CODE,12),
left(PN1.V_CODE,12) + ' - ' + @.LABEL1, @.SPARE1, left(PN2.V_CODE,12) AS FATHER_CODE,
left(PN2.V_CODE,12) + ' - ' + @.LABEL2 AS FATHER_LABEL, @.SPARE2 AS FATHER_SPARE
FROM ARTICLE PN1 CROSS JOIN
ARTICLE PN2
WHERE (PN2.N_ARTICLE_ID = @.ARTICLE_FATHER_ID) AND (PN1.N_ARTICLE_ID = @.ARTICLE_ID);
END
INSERT INTO #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN
SELECT PNLIST.N_ARTICLE_ID FROM ARTICLE PNLIST
WHERE (LEFT(PNLIST.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_ID))
WHILE EXISTS(SELECT * FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN)
BEGIN
SET @.ARTICLEC = (SELECT TOP 1 N_ARTICLE_ID FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN)
INSERT INTO #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN
SELECT PNLIST.N_ARTICLE_ID FROM ARTICLE PNLIST
WHERE (LEFT(PNLIST.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@.ARTICLE_FATHER_ID))
WHILE EXISTS(SELECT * FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN)
BEGIN
SET @.ARTICLEF = (SELECT TOP 1 N_ARTICLE_ID FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN)
IF (NOT EXISTS(SELECT * FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@.DATASOURCE) AND (N_ARTICLE_ID = @.ARTICLEC) AND (N_ARTICLE_FATHER_ID = @.ARTICLEF)))
BEGIN
INSERT INTO ARTICLE_BREAKDOWN
(N_ARTICLE_ID, N_ARTICLE_FATHER_ID, N_DATASOURCE_ID, N_SOURCE_ID, V_ROOT_CODE, V_NODE_LABEL,
B_ACTIVE, V_FATHER_ROOT_CODE, V_FATHER_NODE_LABEL, B_FATHER_ACTIVE)
SELECT PN1.N_ARTICLE_ID, PN2.N_ARTICLE_ID AS FATHER_ID, @.DATASOURCE AS DATASOURCE, -6 AS SOURCE, left(PN1.V_CODE,12),
left(PN1.V_CODE,12) + ' - ' + @.LABEL1, @.SPARE1, left(PN2.V_CODE,12) AS FATHER_CODE,
left(PN2.V_CODE,12) + ' - ' + @.LABEL2 AS FATHER_LABEL, @.SPARE2 AS FATHER_SPARE
FROM ARTICLE PN1 CROSS JOIN
ARTICLE PN2
WHERE (PN2.N_ARTICLE_ID = @.ARTICLEF) AND (PN1.N_ARTICLE_ID = @.ARTICLEC);
END
DELETE FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN WHERE N_ARTICLE_ID = @.ARTICLEF
END
DELETE FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN WHERE N_ARTICLE_ID = @.ARTICLEC
END
END
/*if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]
END
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]
END*/
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Comment ca va Rabiwan?
Bon, je ne suis pas sur de pouvoir t'aider facilement. Continuons ca en english.
I can try to give a couple clues.
You use quite a few sub-queries. They are slow
You use quite a few DELETE statement, they are slow
Indexing the temp tables may or may not help, it takes time to index them and it is not sure that query plan will use these indexes.
I guess you should have a second look at the entire solution and see if you could have any use of recursive CTE's
Philippe
|||Bonjour philippe,
ca va, ca va merci mais mieux quand ce problème sera arrang.
ok for the sub queries I will find a solution for that with an denormalization of the column V_CODE for have a new column at 12 characters.
For the delete I can set a new field in my tempory table and do an update when the data is processing.
The update is less consuming?
I use SQL SERVER 2000 SP4, So I think there is not CTE.
And for finish the index is only on primary key. But when I delete an element in the tempory table, the index is reload?
Rabbiwan
No comments:
Post a Comment