problem queryplan xml template query

Hi,
I use a xsd schema to load XML with a complex structure from a database for
using it in an ASP webpage. Somehow the database uses quite a long time to
make a query plan for the template. The second time the template is used the
database responses quickly, also for other data (using other selection
criteria).
The queryplan is lost when there are no calls for some time, or when a small
change is made to de database stucture, so the next time the ASP page is
called users receive a timeout error.
I have checked all the relevant indexes from the tables that are used for
creating the XML.
Is there any way to influence the speed/persistance of the queryplan that is
created for a xml template query?
Any help will be appreceated,
Albert JanI don't think there is. What is happening is that the first time the query
is compiled and then cached. If you don't run it for a while, the query plan
will be purged from the cache and the query will be recompiled. The only way
to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
stored proc and call the stored proc. You can use the SQL Profiler to see
what the query is that is being generated.
Best regards
Michael
"Albert Jan" <awonnink@.hotmail.com> wrote in message
news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
> Hi,
> I use a xsd schema to load XML with a complex structure from a database
> for
> using it in an ASP webpage. Somehow the database uses quite a long time to
> make a query plan for the template. The second time the template is used
> the
> database responses quickly, also for other data (using other selection
> criteria).
> The queryplan is lost when there are no calls for some time, or when a
> small
> change is made to de database stucture, so the next time the ASP page is
> called users receive a timeout error.
> I have checked all the relevant indexes from the tables that are used for
> creating the XML.
> Is there any way to influence the speed/persistance of the queryplan that
> is
> created for a xml template query?
> Any help will be appreceated,
> Albert Jan
>|||Hi Michael,
I had hoped I woudn't have to redesign the solution, because I like the
technique using the template query. But maybe I'll just have to.
Thank you for your answer.
Albert Jan
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:emXkJcUOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> I don't think there is. What is happening is that the first time the query
> is compiled and then cached. If you don't run it for a while, the query
plan
> will be purged from the cache and the query will be recompiled. The only
way
> to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
> stored proc and call the stored proc. You can use the SQL Profiler to see
> what the query is that is being generated.
> Best regards
> Michael
> "Albert Jan" <awonnink@.hotmail.com> wrote in message
> news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
to
for
that
>
>

problem queryplan xml template query

Hi,
I use a xsd schema to load XML with a complex structure from a database for
using it in an ASP webpage. Somehow the database uses quite a long time to
make a query plan for the template. The second time the template is used the
database responses quickly, also for other data (using other selection
criteria).
The queryplan is lost when there are no calls for some time, or when a small
change is made to de database stucture, so the next time the ASP page is
called users receive a timeout error.
I have checked all the relevant indexes from the tables that are used for
creating the XML.
Is there any way to influence the speed/persistance of the queryplan that is
created for a xml template query?
Any help will be appreceated,
Albert Jan
I don't think there is. What is happening is that the first time the query
is compiled and then cached. If you don't run it for a while, the query plan
will be purged from the cache and the query will be recompiled. The only way
to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
stored proc and call the stored proc. You can use the SQL Profiler to see
what the query is that is being generated.
Best regards
Michael
"Albert Jan" <awonnink@.hotmail.com> wrote in message
news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
> Hi,
> I use a xsd schema to load XML with a complex structure from a database
> for
> using it in an ASP webpage. Somehow the database uses quite a long time to
> make a query plan for the template. The second time the template is used
> the
> database responses quickly, also for other data (using other selection
> criteria).
> The queryplan is lost when there are no calls for some time, or when a
> small
> change is made to de database stucture, so the next time the ASP page is
> called users receive a timeout error.
> I have checked all the relevant indexes from the tables that are used for
> creating the XML.
> Is there any way to influence the speed/persistance of the queryplan that
> is
> created for a xml template query?
> Any help will be appreceated,
> Albert Jan
>
|||Hi Michael,
I had hoped I woudn't have to redesign the solution, because I like the
technique using the template query. But maybe I'll just have to.
Thank you for your answer.
Albert Jan
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:emXkJcUOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> I don't think there is. What is happening is that the first time the query
> is compiled and then cached. If you don't run it for a while, the query
plan
> will be purged from the cache and the query will be recompiled. The only
way[vbcol=seagreen]
> to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
> stored proc and call the stored proc. You can use the SQL Profiler to see
> what the query is that is being generated.
> Best regards
> Michael
> "Albert Jan" <awonnink@.hotmail.com> wrote in message
> news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
for[vbcol=seagreen]
that
>
>
sql

Problem on Sync Access database

Dear all,

I have a software written in C#, using RDA to sync access database from desktop. But it is tricky that some time during "Pull" data from SQL Server Agent, my PDA tell me "A request to send data to the computer running IIS has failed. For more information, see HRESULT".

This also happening when I try to unplug the ethernet cable from my desktop however the push and pull actions did not required to go through internet.

Here are part of my codes
m_intdb = new SqlCeRemoteDataAccess("http://desk01:1024", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\inttemp.mdb; Jet OLEDB:System Database=C:\\Documents and Settings\\user\\Application Data\\Microsoft\\Access\\System.MDW");

m_intdb.InternetUrl = "http://desk01:1024";
m_intdb.LocalConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\inttemp.mdb; Jet OLEDB:System Database=C:\\Documents and Settings\\user\\Application Data\\Microsoft\\Access\\System.MDW"";
m_intdb.Pull("pulltest", "SELECT * from pullTest", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\inttemp.mdb; Jet OLEDB:System Database=C:\\Documents and Settings\\user\\Application Data\\Microsoft\\Access\\System.MDW", RdaTrackOption.TrackingOff, "Err_PullTest");

and the error message look like
A request to send data to the computer running IIS has failed. For more information, see HRESULT.

Please help me solve this problem

Best Regards
Tony Lam

Hi Tony,

We have see this problem of coonection error on pulling out the network cable. It dosent happen all the time, but sometimes. We are working on this and this would be fixed in RTW. In meanwhile, if you simply restart the PC (with the ethernet cable still pulled out) and try to sync it should work.

About the first problem you mentioned, connection error while pulling (i understading is in this situation, yiu are nit unplugging the ethernet cable), it would help if you could provide some more information.

Thanks

Manish

|||Dear Manish,

Thanks for your reply, here is the step to procedure the first problem
1. start workstation and pocket pc
2. start pocket pc application
(pull and push functions automatically start after in time interval after cradled
try and catch statement place between each pull and push function,
when an error catched, display it on a dataview in report format
)
3. remove ethernet cable from workstation network adapter
4. when push or pull function is called, the problem produced.

and I have make a temporality solution to create a loopback device for this situation. It might help developer when facing same problem, since I am using non-english version of windows, some wordings might not exactly the same with what you can see. So, just get the meaning.

Here steps to create a "loopback device" (For winxp)
1. gocontrol panel
2. click on iconadd new hardware
3. click next until see question on hardware connected to machine, chooseyes
4. selectadd new hardware from the hardware list
5. choose option withi will pick up hardware myself
6. selectnetwork adpater from the hardware list
7. here you see two lists, choosemicrosoft from left list, andmicrosoft loop back adapter from right list and click next to complete the setup.

Best Regards
Tony Lam
|||

Hi Tony,

Thanks for the details. We will look into this

Regards

Manish

Problem on Sync Access database

Dear all,

I have a software written in C#, using RDA to sync access database from desktop. But it is tricky that some time during "Pull" data from SQL Server Agent, my PDA tell me "A request to send data to the computer running IIS has failed. For more information, see HRESULT".

This also happening when I try to unplug the ethernet cable from my desktop however the push and pull actions did not required to go through internet.

Here are part of my codes
m_intdb = new SqlCeRemoteDataAccess("http://desk01:1024", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\inttemp.mdb; Jet OLEDB:System Database=C:\\Documents and Settings\\user\\Application Data\\Microsoft\\Access\\System.MDW");

m_intdb.InternetUrl = "http://desk01:1024";
m_intdb.LocalConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\inttemp.mdb; Jet

OLEDB:System Database=C:\\Documents and Settings\\user\\Application

Data\\Microsoft\\Access\\System.MDW"";
m_intdb.Pull("pulltest", "SELECT * from pullTest", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\inttemp.mdb; Jet

OLEDB:System Database=C:\\Documents and Settings\\user\\Application

Data\\Microsoft\\Access\\System.MDW", RdaTrackOption.TrackingOff, "Err_PullTest");

and the error message look like
A request to send data to the computer running IIS has failed. For more information, see HRESULT.

Please help me solve this problem

Best Regards
Tony Lam

Hi Tony,

We have see this problem of coonection error on pulling out the network cable. It dosent happen all the time, but sometimes. We are working on this and this would be fixed in RTW. In meanwhile, if you simply restart the PC (with the ethernet cable still pulled out) and try to sync it should work.

About the first problem you mentioned, connection error while pulling (i understading is in this situation, yiu are nit unplugging the ethernet cable), it would help if you could provide some more information.

Thanks

Manish

|||Dear Manish,

Thanks for your reply, here is the step to procedure the first problem
1. start workstation and pocket pc
2. start pocket pc application
(pull and push functions automatically start after in time interval after cradled
try and catch statement place between each pull and push function,
when an error catched, display it on a dataview in report format
)
3. remove ethernet cable from workstation network adapter
4. when push or pull function is called, the problem produced.

and I have make a temporality solution to create a loopback device for this situation. It might help developer when facing same problem, since I am using non-english version of windows, some wordings might not exactly the same with what you can see. So, just get the meaning.

Here steps to create a "loopback device" (For winxp)
1. go control panel
2. click on icon add new hardware
3. click next until see question on hardware connected to machine, choose yes
4. select add new hardware from the hardware list
5. choose option with i will pick up hardware myself
6. select network adpater from the hardware list
7. here you see two lists, choose microsoft from left list, and microsoft loop back adapter from right list and click next to complete the setup.

Best Regards
Tony Lam|||

Hi Tony,

Thanks for the details. We will look into this

Regards

Manish

Problem on Sync Access database

Dear all,

I have a software written in C#, using RDA to sync access database from desktop. But it is tricky that some time during "Pull" data from SQL Server Agent, my PDA tell me "A request to send data to the computer running IIS has failed. For more information, see HRESULT".

This also happening when I try to unplug the ethernet cable from my desktop however the push and pull actions did not required to go through internet.

Here are part of my codes
m_intdb = new SqlCeRemoteDataAccess("http://desk01:1024", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\inttemp.mdb; Jet OLEDB:System Database=C:\\Documents and Settings\\user\\Application Data\\Microsoft\\Access\\System.MDW");

m_intdb.InternetUrl = "http://desk01:1024";
m_intdb.LocalConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\inttemp.mdb; Jet

OLEDB:System Database=C:\\Documents and Settings\\user\\Application

Data\\Microsoft\\Access\\System.MDW"";
m_intdb.Pull("pulltest", "SELECT * from pullTest", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\inttemp.mdb; Jet

OLEDB:System Database=C:\\Documents and Settings\\user\\Application

Data\\Microsoft\\Access\\System.MDW", RdaTrackOption.TrackingOff, "Err_PullTest");

and the error message look like
A request to send data to the computer running IIS has failed. For more information, see HRESULT.

Please help me solve this problem

Best Regards
Tony Lam

Hi Tony,

We have see this problem of coonection error on pulling out the network cable. It dosent happen all the time, but sometimes. We are working on this and this would be fixed in RTW. In meanwhile, if you simply restart the PC (with the ethernet cable still pulled out) and try to sync it should work.

About the first problem you mentioned, connection error while pulling (i understading is in this situation, yiu are nit unplugging the ethernet cable), it would help if you could provide some more information.

Thanks

Manish

|||Dear Manish,

Thanks for your reply, here is the step to procedure the first problem
1. start workstation and pocket pc
2. start pocket pc application
(pull and push functions automatically start after in time interval after cradled
try and catch statement place between each pull and push function,
when an error catched, display it on a dataview in report format
)
3. remove ethernet cable from workstation network adapter
4. when push or pull function is called, the problem produced.

and I have make a temporality solution to create a loopback device for this situation. It might help developer when facing same problem, since I am using non-english version of windows, some wordings might not exactly the same with what you can see. So, just get the meaning.

Here steps to create a "loopback device" (For winxp)
1. go control panel
2. click on icon add new hardware
3. click next until see question on hardware connected to machine, choose yes
4. select add new hardware from the hardware list
5. choose option with i will pick up hardware myself
6. select network adpater from the hardware list
7. here you see two lists, choose microsoft from left list, and microsoft loop back adapter from right list and click next to complete the setup.

Best Regards
Tony Lam|||

Hi Tony,

Thanks for the details. We will look into this

Regards

Manish

Problem on parallelism

after running query at first time working all processes

but later 2-3 sec. working only one

SQL 2005

Hewlett Packard DL580 (16 processes)

What is ideas?

Hi Vladmir,

Could you please expand on what you are trying to do and what you are experiencing?

thanks

Jag

|||It's possible that you're seeing the initial disk reads for the query being done in parallel, and then the remainder of the execution carrying on in a single thread, but that's about the best guess I can give from the current information. Not all queries will be executed in multiple parallel threads - there are certain criteria and requirements used by the query optimizer to decide on a degree of parallelism.|||

Thanks for replies!

I have a bank's system (Diasoft 5NT)

It works with SQL Server 2005 (64 bit version). If I run stored procedure in QA - OK! All processes work.

It executing on multiple EC (executing context)

But if I run in programm (Diasoft 5NT) - after few second work only one thread.

P.S. Connect to database over BDE (Borland Database Engine).

|||

davidbrit2 wrote:

It's possible that you're seeing the initial disk reads for the query being done in parallel, and then the remainder of the execution carrying on in a single thread, but that's about the best guess I can give from the current information. Not all queries will be executed in multiple parallel threads - there are certain criteria and requirements used by the query optimizer to decide on a degree of parallelism.

Yes! I think.

But the same query in one case are executing in multiple parallel threads , in other case in single.

|||

Thanks for all!

This problem have been resolved when I turn on "Auto Create Statistic" and "Auto Update Statistic"!

This option had been turned off because we planning run update statistic at night time.

Problem of optimization in complex stored procedure

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

Problem of optimization in complex stored procedure

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

Problem Month Granularity

Hello,

I suppose that my problem is something basic but it's the first time i'm confronted with this.

I want to link a measure group to a time dimension on a Month granularity.

My time dimension is a server time dimension, the inventory data comes on a monthly base, but with a normal datetime field (always the last day of the month). Linking this on a date granularity is no problem, but has no sense. When i set the granularity to Month i'm getting this error.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: ASW_Monthly_Stock, Column: ASW_STOCK_DATE, Value: 31/08/2006. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Month of Dimension: Calendar Time from Database: SdwCubes, Cube: c_Sales, Measure Group: ASW Monthly Stock, Partition: ASW Monthly Stock, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'ASW Monthly Stock' partition of the 'ASW Monthly Stock' measure group for the 'c_Sales' cube from the SdwCubes database.

I've seen that when setting the granularity in the Define Relationship window i'm getting a warning 'If you Select a non-key ....', but when i go into the 'Advanced...' button i can link more attributes.

But what function do i have to put on my ASW_STOCK_DATE to link it to the Month attribute of the time dimension ? The T-Sql function Month only returns the Month-number, and not the year ...

So ... if anyone could get me a bit furter on this ... greatly appreciated.

Please check that releationship between measure group and dimension columns is correct, most likely you still have days as a measure group column. To do this go to the Dimension Usage Tab, click on ... near the time dimension name, you'll get window "define relationship".|||

Hi,

Does your table AS_Monthly_Stock stock, store data for the last day of the month.

It seems like the server time dimension has the "month key" as the first day of the month. This can be the cause of your issue.

The easiet option is to change your fact table to record data on the first day of the month.

CAST(CAST(year(getdate())asvarchar)+'-'+CAST(month(getdate())asvarchar)+'-'+'01'asdatetime)

Punita

Problem Month Granularity

Hello,

I suppose that my problem is something basic but it's the first time i'm confronted with this.

I want to link a measure group to a time dimension on a Month granularity.

My time dimension is a server time dimension, the inventory data comes on a monthly base, but with a normal datetime field (always the last day of the month). Linking this on a date granularity is no problem, but has no sense. When i set the granularity to Month i'm getting this error.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: ASW_Monthly_Stock, Column: ASW_STOCK_DATE, Value: 31/08/2006. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Month of Dimension: Calendar Time from Database: SdwCubes, Cube: c_Sales, Measure Group: ASW Monthly Stock, Partition: ASW Monthly Stock, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'ASW Monthly Stock' partition of the 'ASW Monthly Stock' measure group for the 'c_Sales' cube from the SdwCubes database.

I've seen that when setting the granularity in the Define Relationship window i'm getting a warning 'If you Select a non-key ....', but when i go into the 'Advanced...' button i can link more attributes.

But what function do i have to put on my ASW_STOCK_DATE to link it to the Month attribute of the time dimension ? The T-Sql function Month only returns the Month-number, and not the year ...

So ... if anyone could get me a bit furter on this ... greatly appreciated.

Please check that releationship between measure group and dimension columns is correct, most likely you still have days as a measure group column. To do this go to the Dimension Usage Tab, click on ... near the time dimension name, you'll get window "define relationship".|||

Hi,

Does your table AS_Monthly_Stock stock, store data for the last day of the month.

It seems like the server time dimension has the "month key" as the first day of the month. This can be the cause of your issue.

The easiet option is to change your fact table to record data on the first day of the month.

CAST(CAST(year(getdate())asvarchar)+'-'+CAST(month(getdate())asvarchar)+'-'+'01'asdatetime)

Punita

Problem installing SQL server 2005 express

When I try to install SQL Server 2005 Express I find that after a short time it discontinues and say's "Installation not completed etc" on looking at the logs it say.s "Could not find IIS" do I need XP Pro to be able to install SQL Server

Thanks in advance

Hi Teriel,

If you're installing a SQL Server 2005 Express with advanced service, it will try to install the Reporting Service to the machine, which requires IIS.

In this case, you can try to install the SQL Server 2005 Express Edition instead.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

Hi Kevin,

Many thanks for your kind assistance, the version I had problems with had "X86" after it's name, I downloaded and installed a copy with just "SQL Server 2005 Express Edition" and it has installed with no problems, thank you again.

Teriel

Problem installing desktop SQL 7 on Windows XP

I'm trying to install SQL 7 Desktop on Windows XP. When setup gives the
'starting service' message for the second time it stalls for ~1 minute then
returns with "Cannot initialize server".

I have noticed at least 20 other members (no exagerration!) of this forum report the same issue.

Does anyone out there know what is the solution when this happens.

Your help would be very much appreciated!do it have to do with your installing it under the windows authentication or sql server authentication?|||Originally posted by lorddog
do it have to do with your installing it under the windows authentication or sql server authentication?

No.
Its fixed now!
For me the solution was to stop everything that was already running (including antivirus, firewalls - all that stuff in the bottom right corner of your screen). I suppose that the "bottom-right-corner" stuff is implemented as "services" in XP, and these items must somehow get in the way of the installer when it tries to start the SQL service. Anyway, its fixed now! Hope this helps the rest who have (and will in the future) come up against this problem.

PS: I did not have the problem on my old win 98 system (which does not do "services"), and so the installation was able to complete without this issue arising.

Problem inserting time stamp 24:00:00.000 into a datetime column

Hi,
Problem inserting time stamp 24:00:00.000 to an SQL 2K database. App
code(EJB-SQL JDBC) constantly receives reading and it calls add procedure to
insert reading datetime. So, if reading comes exactly at midnight, the inser
t
fails.
Any help is really appreciated.
thanksHi
Midnight is 00:00:00.000!
John
"Ajay" <Ajay@.discussions.microsoft.com> wrote in message
news:E8F82952-F6C0-45D7-AED2-245D745A6273@.microsoft.com...
> Hi,
> Problem inserting time stamp 24:00:00.000 to an SQL 2K database. App
> code(EJB-SQL JDBC) constantly receives reading and it calls add procedure
> to
> insert reading datetime. So, if reading comes exactly at midnight, the
> insert
> fails.
> Any help is really appreciated.
> thanks
>