June 13, 2007 at 1:54 pm
Hello
On the following auto referencing Table containing hierarchical documents
CREATE
TABLE T_DEVPART_DPT (
DPT_ID bigint NOT NULL PRIMARY KEY,
DEV_ID bigint NULL,
DPT_PID bigint NULL,
DPT_ORD int NULL,
DPT_STYPE
DPT_FAMILLE VARCHAR(16),
DPT_SFAMILLE VARCHAR(16) NULL,
DPT_REF VARCHAR(24) NULL,
DPT_LIBELLE D_LIBELLE_ARTICLE NULL,
DPT_QTE DECIMAL(15,4) NULL,
DPT_PUA1 DECIMAL(15,4) NULL,
DPT_PUA2 DECIMAL(15,4) NULL,
DPT_PUV1 DECIMAL(15,4) NULL,
DPT_PUVF DECIMAL(15,4) NULL,
)
I have to run aggregate function SUM on PUx, not only for a view but by storing the values physically
in a recursive update i.e. to store the sum(QTE * PUx) of all the children of each items having children who already them are even calculated
I can do that with cursors in recursive sp but it is not very fast
have some tracks to improve that
Thanks ( excuse me for my English )
October 17, 2007 at 4:11 am
Two questions:
1) Have you considered using Common Table Expressions?
2) Why would you need to store calculated values?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
October 17, 2007 at 5:23 am
I guess CTE is best suited for recursive logic...
Also, please provide us with some sample data and the output that you require, without which we're just working blindly....
--Ramesh
October 26, 2007 at 5:48 am
Hi
CTE's do have performance implications. If your CTE is going to return huge volume of data then performance can come down.
"Keep Trying"
October 26, 2007 at 5:50 am
I assume you have an alternative in mind that would perform better...?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
October 26, 2007 at 5:54 am
Recursive Queries in SQL:1999 and SQL Server 2005
With nice tests ...
CTE Performance
http://www.sqlservercentral.com/articles/2926/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 26, 2007 at 6:11 am
Regarding this article:
http://www.sqlservercentral.com/articles/2926/
...none of the files are available, so it's not possible to know any DDL (especially how the tables are indexed).
While the duration of query execution is one of the criteria used to analyze the performance of DML, it would IMHO be more appropriate to compare the number of reads, writes and the stress on the CPU in order to get a more complete overview of the possibilities.
Nonetheless, both articles are pretty informative, so thanks for pointing them out!
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
October 27, 2007 at 5:33 am
regarding the unavailable files .... if you encounter invalid urls, contact the webmaster. The site has been restyled and suffers some issues of this kind.:ermm:
I've just contaced the webmaster. Normaly SSC is very responsive to this kind of issues.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 28, 2007 at 7:56 am
Thanks!
And I agree, Steve (& Co.) are doing a great job keeping the community active and productive.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply