October 14, 2012 at 3:59 pm
Hello comunity
I have build this current CTE query to have a drilldown information to know from the customer order, which the separation order and finally the invoice all by REF (article), my code is:
;WITH Drilldown AS
(
--initialization
SELECT nmdos, ndos,bi.ref, bistamp, rdata
FROM bi (NOLOCK)
WHERE bi.ndos = 1 AND YEAR(bi.rdata) = 2012--ReportsTo IS NULL
),
myOS AS
--recursive execution
(SELECT bii.nmdos,bii.ndos, bii.ref , bii.rdata,[bii].obistamp, bii.bistamp
FROM bi bii (nolock) where bii.ndos <> 1 AND YEAR(bii.rdata) = 2012
),
myFT AS
(SELECT f.nmdoc, f.fno, f.ref, ft.fdata , f.bistamp
FROM fi f (nolock) INNER JOIN ft (nolock) ON
f.ftstamp = ft.ftstamp
where f.ndoc = 1 AND year(ft.fdata) >= 2012
)
SELECT Drilldown.nmdos, Drilldown.ndos,DrillDown.rdata, Drilldown.ref,
MyOs.nmdos, MyOS.ndos,MyOs.rdata,MyOs.ref,
MyFT.nmdoc, MyFT.fno, MyFT.fdata, MyFT.ref
FROM Drilldown INNER JOIN myOS ON
Drilldown.bistamp = MyOS.obistamp
INNER join myFT ON myFT.bistamp = myOS.bistamp
ORDER BY DrillDown.rdata asc
I want to know if is correct to use WHERE clause on each CTE to filter the information ?
Also, for persons with more experience, i would like to know if the construction of my CTE is correct or someone could suggest me some improvements for better performance.
Many thanks,
Luis Santos
October 15, 2012 at 1:32 am
luissantos (10/14/2012)
I want to know if is correct to use WHERE clause on each CTE to filter the information ?Also, for persons with more experience, i would like to know if the construction of my CTE is correct or someone could suggest me some improvements for better performance.
It's absolutely fine. I wouldn't have used CTE's for this query because none of the main reasons for using CTE's are there. Here's your query without CTE's;
SELECT
bi.nmdos, bi.ndos, bi.rdata, bi.ref,
bii.nmdos, bii.ndos, bii.rdata, bii.ref,
f.nmdoc, f.fno, ft.fdata, f.ref
FROM bi bi (NOLOCK)
INNER JOIN bi bii (NOLOCK)
ON bii.bistamp = bi.obistamp
AND bii.ndos <> 1
AND YEAR(bii.rdata) = 2012
INNER JOIN fi f (nolock)
ON f.bistamp = bi.bistamp
AND f.ndoc = 1
INNER JOIN ft (nolock)
ON ft.ftstamp = f.ftstamp
AND year(ft.fdata) >= 2012
WHERE bi.ndos = 1
AND YEAR(bi.rdata) = 2012--ReportsTo IS NULL
ORDER BY bi.rdata asc
Ordinary CTE's are used to:
Make code more readable by redistributing complexity
Allow filtering on an aggregate in a subquery
Refer to the same subquery more than once in a main query
CTE's don't usually impact on performance unless they are nested to several level deep, when cardinality changes and sort order changes (caused by e.g. aggregates and joins) between levels can confuse the optimiser resulting in suboptimal plans.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply