Using CTE doughts

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Using CTE doughts

Using CTE doughts

Rate Topic

Display Mode

Topic Options

Author

Message

luissantos

Posted Sunday, October 14, 2012 3:59 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 10:34 AM
Points: 244, Visits: 480

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

Post #1372542

CELKO

Posted Sunday, October 14, 2012 10:00 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1372555

ChrisM@home

Posted Monday, October 15, 2012 1:32 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 921, Visits: 3,743

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.

^{Low-hanging fruit picker and defender of the moggies}

For better assistance in answering your questions, please read this.

Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Post #1372579

« Prev Topic | Next Topic »

Permissions