CTE, I have a dream...

If they (CTE) were parameterized, I would stop working user functions
for use in only one place.
/*
* CTE, I have a dream...
*/
With TopRatedEmployees
(
@.DepartmentId int
)
RETURNS TABLE
AS
(
SELECT TOP 10 EmployeeId,
EmployeeName,
Rank
FROM Employees
WHERE DepartmentId = @.DepartmentId
ORDER BY Rank DESC
)
With EmployeesCount
(
@.DepartmentId int
)
RETURNS int
AS
(
SELECT COUNT(*)
FROM Employees
WHERE DepartmentId = @.DepartmentId
)
SELECT Departments.Id,
Departments.Name,
dbo.EmployeesCount(Department.Id),
TopRatedEmployees1.EmployeeId,
TopRatedEmployees1.EmployeeName,
FROM Departments
CROSS APPLY
dbo.TopRatedEmployees(Department.Id) AS TopRatedEmployees1
ORDER BY Departments.Name;
GO<guercheLE@.gmail.com> wrote in message
news:1151617460.266574.183380@.j72g2000cwa.googlegroups.com...
> If they (CTE) were parameterized, I would stop working user functions
> for use in only one place.
> /*
> * CTE, I have a dream...
> */
> With TopRatedEmployees
> (
> @.DepartmentId int
> )
> RETURNS TABLE
> AS
> (
> SELECT TOP 10 EmployeeId,
> EmployeeName,
> Rank
> FROM Employees
> WHERE DepartmentId = @.DepartmentId
> ORDER BY Rank DESC
> )
> With EmployeesCount
> (
> @.DepartmentId int
> )
> RETURNS int
> AS
> (
> SELECT COUNT(*)
> FROM Employees
> WHERE DepartmentId = @.DepartmentId
> )
> SELECT Departments.Id,
> Departments.Name,
> dbo.EmployeesCount(Department.Id),
> TopRatedEmployees1.EmployeeId,
> TopRatedEmployees1.EmployeeName,
> FROM Departments
> CROSS APPLY
> dbo.TopRatedEmployees(Department.Id) AS TopRatedEmployees1
> ORDER BY Departments.Name;
> GO
>
Assuming I've understood your pseudo-code correctly you can do it like this:
SELECT D.DepartmentId,
D.Name,
D.DepartmentId,
TopRatedEmployees.EmployeeId,
TopRatedEmployees.EmployeeName
FROM Departments AS D
CROSS APPLY
(
SELECT TOP 10 EmployeeId,
EmployeeName,
Rank
FROM Employees
WHERE DepartmentId = D.DepartmentId
ORDER BY Rank DESC
) AS TopRatedEmployees
CROSS APPLY
(
SELECT D.DepartmentId, COUNT(*) AS cnt
FROM Employees
WHERE DepartmentId = D.DepartmentId
) AS EmployeesCount
ORDER BY D.Name;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

CTE XML Output

I have a organizational table with location->Department->group. Location has many departments and department has many groups. I have only one table to reprasent the above

Id, Name, Parent

Parent is the foreign key tieing back to Id.

Is there any way to use CTE and output an xml like the following?

<Location id="1">
<Name>Location name</Name>
<Departments>
<Department id="2"><Name>Department1</Name></Department>
<Department id="3"><Name>Department2</Name></Department>
</Departments>
</Location>

Thanks,
-keasv

With a fixed hierarchy depth you should be able to use nested selects

select t1.id as "@.id",
t1.Name,
(select t2.id as "@.id",
t2.Name,
(select t3.id as "@.id",
t3.Name
from mytable t3
where t3.parent=t2.id
for xml path('Group'),root('Groups'),type)
from mytable t2
where t2.parent=t1.id
for xml path('Department'),root('Departments'),type)
from mytable t1
where t1.parent is null
for xml path('Location'),type

|||

If the nesting level is not known apriori, you can write a recursive SQL UDF. See the FOR XML Whitepaper at http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/default.aspx?pull=/library/en-us/dnsql90/html/forxml2k5.asp.

Best regards

Michael

|||

SQL Server 2005 has a maximum limit of 32 recursively nested function invocations. If your parts hierarchy exceeds the limit, you will need to use the old approach of getting the XML in flat form and applying an XSLT style sheet to create the hierarchy.

Its mentioned here at http://msdn2.microsoft.com/en-us/library/ms345137.aspx

So can anyone suggest any alternate method here?

CTE XML Output

I have a organizational table with location->Department->group. Location has many departments and department has many groups. I have only one table to reprasent the above

Id, Name, Parent

Parent is the foreign key tieing back to Id.

Is there any way to use CTE and output an xml like the following?

<Location id="1">
<Name>Location name</Name>
<Departments>
<Department id="2"><Name>Department1</Name></Department>
<Department id="3"><Name>Department2</Name></Department>
</Departments>
</Location>

Thanks,
-keasv

With a fixed hierarchy depth you should be able to use nested selects

select t1.id as "@.id",
t1.Name,
(select t2.id as "@.id",
t2.Name,
(select t3.id as "@.id",
t3.Name
from mytable t3
where t3.parent=t2.id
for xml path('Group'),root('Groups'),type)
from mytable t2
where t2.parent=t1.id
for xml path('Department'),root('Departments'),type)
from mytable t1
where t1.parent is null
for xml path('Location'),type

|||

If the nesting level is not known apriori, you can write a recursive SQL UDF. See the FOR XML Whitepaper at http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/default.aspx?pull=/library/en-us/dnsql90/html/forxml2k5.asp.

Best regards

Michael

|||

SQL Server 2005 has a maximum limit of 32 recursively nested function invocations. If your parts hierarchy exceeds the limit, you will need to use the old approach of getting the XML in flat form and applying an XSLT style sheet to create the hierarchy.

Its mentioned here at http://msdn2.microsoft.com/en-us/library/ms345137.aspx

So can anyone suggest any alternate method here?

CTE with multiple update statements?

HI Gurus

I have written one CTE (common table expression) and trying to use same CTE with three seperate UPDATE statements which gives me error saying "Invalid Object name" (it works fine when I try to use with 1 update statement (any one from three update statements)

---

Isnt it possible that I can use 1 CTE with mutiple update statements?

waiting for your reply....

A CTE can be used multiple times in a single statement but not in multiple statements. You would need a table variable or temporary table if you will have multiple references.

More CTE documentation in BOL here: http://msdn2.microsoft.com/en-us/library/ms175972.aspx

|||thanx for your reply

CTE vs. Table Variable (Paging)

Hello Experts.
I'm trying to find the pros / cons of using CTEs (Common Table Expressions)
vs. Table Variables for 'Paging' through data.
Here is the scanario:
A. Table Variable Example:
Select Query returns 100k posible matching rows, I store the matching key
column along with an identity column in a table varable (i.e. DECLARE @.tmp
TABLE (rowid int identity, userid int) ), then I left join users to userid i
n
@.tmp where @.tmp.rowid between N and N.
B. CTE Example:
WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT * from
CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
What I noticed so far is:
CTE compares ROW_NUMBER() expression to the values of the BETWEEN expression
and returns only matching rows,
While Table Variables are first saving all userids in memory, then going
back for a table scan for a match for the BETWEEN expression.
I would like to know if anyone here has a definitive answer on why and in
what scanario can CTEs be faster then Table Variables and vice versa.
Thank you in advance for all your help.
- Eyal Zinder.>> I would like to know if anyone here has a definitive answer on why and in
In general, unless you have an environment where every factor that affects
performance is controlled, comparisons are moot. Also, there could be
several other ways one could achieve similar results. There could be even
different approaches for paging rows using a CTE or table variable as well.
So simply asking which construct/structure is "faster" doesn't mean much.
Your posted examples are not very clear either. Please post a sample table
structure, a few sample data as insert statements and legible code snippets
that demostrates your paging attempts.
Anith|||There are far better options than table variables.
http://www.aspfaq.com/2120
I have not played with CTEs in this case.
"Eyal" <ezinder@.yahoo.com> wrote in message
news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
> Hello Experts.
> I'm trying to find the pros / cons of using CTEs (Common Table
> Expressions)
> vs. Table Variables for 'Paging' through data.
> Here is the scanario:
> A. Table Variable Example:
> Select Query returns 100k posible matching rows, I store the matching key
> column along with an identity column in a table varable (i.e. DECLARE @.tmp
> TABLE (rowid int identity, userid int) ), then I left join users to userid
> in
> @.tmp where @.tmp.rowid between N and N.
> B. CTE Example:
> WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT *
> from
> CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
> What I noticed so far is:
> CTE compares ROW_NUMBER() expression to the values of the BETWEEN
> expression
> and returns only matching rows,
> While Table Variables are first saving all userids in memory, then going
> back for a table scan for a match for the BETWEEN expression.
>
> I would like to know if anyone here has a definitive answer on why and in
> what scanario can CTEs be faster then Table Variables and vice versa.
> Thank you in advance for all your help.
> - Eyal Zinder.
>
>|||".. unless you have an environment where every factor that affects
performance is controlled, comparisons are moot.. "
The database environment I work with currently exceeds 500,000 hits per
second.
I am very much concerned with the smallest difference in performance.
As for my examples, here is a detailed view:
/* TABLE VARIABLE EXAMPLE: */
SET NOCOUNT ON
DECLARE @.tmp TABLE (rowid int identity, userid int)
INSERT INTO @.tmp (userid)
SELECT userid
FROM users (nolock)
WHERE userStatus = @.N1
ORDER BY userLastLoginDate
SELECT u.*
FROM @.tmp t
LEFT JOIN users u (nolock)
ON u.userid = t.userid
WHERE t.rowid BETWEEN @.N2 and @.N3
ORDER BY t.rowid
/* CTE EXAMPLE */
SET NOCOUNT ON
WITH tmp AS
(
SELECT userid, ROW_NUMBER() OVER (ORDER BY u.userLastLoginDate) AS rowid
FROM users (nolock)
WHERE userStatus = @.N1
)
SELECT u.*
FROM tmp t
LEFT JOIN users u (nolock)
ON u.userid = t.userid
WHERE t.rowid BETWEEN @.N2 and @.N3
ORDER BY t.rowid
Again, I am NOT looking for new / better ways to page through data or any
Cursor based paging. I am looking for what pros / cons does CTE offer over
the above solution (variable table example). I am interested to know how CT
E
works and how it differs from the above example.
Thank you again for all your help and prompt reply.
Eyal Zinder.
"Anith Sen" wrote:

> In general, unless you have an environment where every factor that affects
> performance is controlled, comparisons are moot. Also, there could be
> several other ways one could achieve similar results. There could be even
> different approaches for paging rows using a CTE or table variable as well
.
> So simply asking which construct/structure is "faster" doesn't mean much.
> Your posted examples are not very clear either. Please post a sample table
> structure, a few sample data as insert statements and legible code snippet
s
> that demostrates your paging attempts.
> --
> Anith
>
>|||Hi Eyal,
Make sure you keep it server side and only pass back the page the user wants
to the client, that will save significanly on resources especially the
network.
I, personally, don't tend to use CTE for paging because it does the whole
query first and the way I design schema I only need to join for those rows
on my page to get the 'meta' data - basically, I search on surrogate keys
where possible.
Personally I'd be inclided to use the ROWNUMBER() method and pump the output
into a table variable and join that table variable out to the base tables to
get my 'meta' as described above.
The reason is simple, it cuts down on IO.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
> Hello Experts.
> I'm trying to find the pros / cons of using CTEs (Common Table
> Expressions)
> vs. Table Variables for 'Paging' through data.
> Here is the scanario:
> A. Table Variable Example:
> Select Query returns 100k posible matching rows, I store the matching key
> column along with an identity column in a table varable (i.e. DECLARE @.tmp
> TABLE (rowid int identity, userid int) ), then I left join users to userid
> in
> @.tmp where @.tmp.rowid between N and N.
> B. CTE Example:
> WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT *
> from
> CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
> What I noticed so far is:
> CTE compares ROW_NUMBER() expression to the values of the BETWEEN
> expression
> and returns only matching rows,
> While Table Variables are first saving all userids in memory, then going
> back for a table scan for a match for the BETWEEN expression.
>
> I would like to know if anyone here has a definitive answer on why and in
> what scanario can CTEs be faster then Table Variables and vice versa.
> Thank you in advance for all your help.
> - Eyal Zinder.
>
>|||>> I am looking for what pros / cons does CTE offer over the above solution
There is no empirical evidence that suggests one approach is always better
than the other. In your specific situation, you should evaluate and compare
the query plans and execution times and decide which one performs better.
You can think of CTE as a temporary resultset/virtual table that lasts only
for the duration of the query. The primary benefits of a CTE include
generation of recursive queries, allowance of multiple references in the
same query and overall simplicity ( many complex queries can be simplified
with a well written CTE )
In your example, CTE offers nothing additional to the overall paging
functionality of the code. In other words, you can avoid the CTE altogether
and use a derived table to achieve similar results. In a small sample I
tested, the plans with a derived table and with a CTE were mostly similar
and provided similar performance.
In general, the "paging" methods are the SQL are an extension of a class of
queries called Quota queries in relational literature. Quota queries sort
the rows based on some explicit sequence of values in a column and then
identify the top/bottom subset (quota). You might want to research on that
if you'd like some background on such formulations.
There are several different approaches to this problem and Aaron's website
offers some of the best SQL 2000 methods that are frequently posted in this
newsgroup.
Anith|||Aaron,
Thank you. But the scale of this site does not allow for middle-tier paging
.
"Aaron Bertrand [SQL Server MVP]" wrote:

> There are far better options than table variables.
> http://www.aspfaq.com/2120
> I have not played with CTEs in this case.
>
>
> "Eyal" <ezinder@.yahoo.com> wrote in message
> news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
>
>|||Tony,
Could you provide an example of using Meta Data for such a scanario?
"Tony Rogerson" wrote:

> Hi Eyal,
> Make sure you keep it server side and only pass back the page the user wan
ts
> to the client, that will save significanly on resources especially the
> network.
> I, personally, don't tend to use CTE for paging because it does the whole
> query first and the way I design schema I only need to join for those rows
> on my page to get the 'meta' data - basically, I search on surrogate keys
> where possible.
> Personally I'd be inclided to use the ROWNUMBER() method and pump the outp
ut
> into a table variable and join that table variable out to the base tables
to
> get my 'meta' as described above.
> The reason is simple, it cuts down on IO.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Eyal" <ezinder@.yahoo.com> wrote in message
> news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
>
>|||The code below runs on my site http://sqlserverfaq.com and performs the
listing and searching of Articles.
You will see I use a temporary table with mostly id's in there and then at
the very end join only for those rows I'm throwing back to the client.
Tony.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[ukug3_selGetKBArticles]
@.max_pages int output,
@.required_page int = 1,
@.rows_per_page int = 2,
@.FileType varchar(10) = '',
@.idEvents int = 0,
@.SearchKeywords varchar(200) = '',
@.OpType char(1) = 'F',
@.member_group_id int = NULL,
@.is_member_group_restrict char(1) = 'N'
as
begin
set nocount on
create table #results (
idrow int not null identity,
idKBArticle int not null,
Rank int not null,
Characterization varchar(500) not null default( '' )
)
declare @.from_row int
declare @.to_row int
set @.from_row = ( (@.required_page-1) * @.rows_per_page ) + 1
set @.to_row = @.from_row + ( @.rows_per_page - 1 )
declare @.sql nvarchar(4000)
if @.SearchKeywords > ''
begin
DECLARE @.user_search_text varchar(300)
SET @.user_search_text = @.SearchKeywords
SET @.OpType = 'C' -- Done for performance
SET @.SearchKeywords = dbo.fn_search_cleanse( @.SearchKeywords,
'AND' ) -- Gets rid of noise words and adds 'AND'
IF @.SearchKeywords = '' -- Bad search, give it another chance and
use freetext instead.
BEGIN
SET @.OpType = 'F'
SET @.SearchKeywords = @.user_search_text
END
-- If restricting to a member group then add the additional search
clause for that group
IF @.is_member_group_restrict = 'Y'
BEGIN
SELECT @.SearchKeywords = @.SearchKeywords + ' ' + search_clause
FROM member_group
WHERE id = @.member_group_id
END
SET @.sql = 'SELECT TOP 50 *
FROM (
SELECT DISTINCT
kba.idKBArticle,
[Rank],
Characterization
FROM ( SELECT DISTINCT TOP 50 [FileName],
[Rank],
Characterization
FROM OPENQUERY( lsIndexServer,
''SELECT FileName, Rank, Characterization
FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE
WHEN @.OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END +
'( '' +
@.SearchKeywords + '' )'' )
WHERE LEFT( Characterization, 12 ) <>
''vti_encoding''
) AS qry
INNER JOIN KBArticle kba ON kba.ArticleFileName =
qry.[FileName]'
-- IF @.member_group_id > 0
-- SET @.sql = @.sql + ' WHERE EXISTS ( SELECT * FROM
KBArticle_MemberGroup_Xref x WHERE x.idKBArticle=kba.idKBArticle AND
x.member_group_id=' + CAST( @.member_group_id AS Varchar(10) ) + ' AND
x.is_released=''Y'' )'
SET @.sql = @.sql + '
UNION ALL
SELECT
kba.idKBArticle,
[Rank] = 9999,
''''
FROM KBArticle kba
WHERE kba.ArticleFileName = ''' + @.SearchKeywords +
''' ) AS dt
ORDER BY Rank DESC'
end
else
begin
set @.sql = N'
select idKBArticle, 9999, ''''
from kbarticle k
where 1=1
and is_external_url_link_broken = ''N''
'
-- Search clause
if @.FileType <> ''
set @.sql = @.sql + N' and FileType=@.FileType'
else if @.idEvents = 0
set @.sql = @.sql + N' and FileType<>''wmv''' -- WMV is dealt
with in its own control now so without this we would duplicate content (ok
on the search though!)
if @.idEvents > 0
set @.sql = @.sql + N' and k.idEvents = @.idEvents'
else
set @.sql = @.sql + N' and FileType <> ''ZIP'''
IF @.member_group_id > 0
SET @.sql = @.sql + ' AND EXISTS ( SELECT * FROM
KBArticle_MemberGroup_Xref x WHERE x.idKBArticle=k.idKBArticle AND
x.member_group_id=' + CAST( @.member_group_id AS Varchar(10) ) + ' AND
x.is_released=''Y'' )'
set @.sql = @.sql + N'
order by EntryDate desc'
end
print @.sql
insert #results ( idKBArticle, Rank, Characterization )
exec sp_executesql @.sql,
N'@.FileType varchar(10), @.idEvents int',
@.FileType, @.idEvents
set @.max_pages = ( @.@.rowcount + ( @.rows_per_page - 1 ) ) /
@.rows_per_page
select id,
title,
author_name,
entry_date,
article_summary = dt.article_summary + case when len(
dt.article_summary ) = 100 then '...' else '' end,
FileType,
ArticleFileName,
CompressedSize,
UncompressedSize,
movie_length,
external_url_link
from (
select t.idRow,
id = t.idKBArticle,
title = k.KBArticleTitle,
author_name = case when k.external_url_link = '' or
k.external_url_link = 'HTTP://' then isnull( r.fullname, '' ) else '' end,
entry_date = CONVERT( varchar(20), k.ModifiedDate, 106 ),
article_summary= SUBSTRING( CASE WHEN t.Characterization = ''
THEN k.KBArticleAbstract ELSE t.Characterization END, 1, 100 ),
FileType = ISNULL( FileType, '' ),
ArticleFileName= ISNULL( ArticleFileName, '' ),
CompressedSize,
UncompressedSize,
movie_length,
external_url_link
from #results t
inner join kbarticle k on k.idKBArticle = t.idKBArticle
left outer join registrations r on r.idregistrations =
k.idregistrations
where idrow between @.from_row and @.to_row ) as dt
order by idrow
end
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:3960701C-287D-450B-B610-3C36919B9ABF@.microsoft.com...
> Tony,
> Could you provide an example of using Meta Data for such a scanario?
>
> "Tony Rogerson" wrote:
>|||And the ASP.NET (VB.NET) to call the proc...
Dim dbConn As New
SqlClient.SqlConnection(ConfigurationSettings.AppSettings("DBConnection"))
dbConn.Open()
Dim cmdSQL As SqlClient.SqlCommand
Dim daSQL As New SqlDataAdapter
Dim dsSQL As New DataSet
' Get Articles
cmdSQL = New SqlCommand("ukug3_selGetKBArticles", dbConn)
cmdSQL.CommandType = CommandType.StoredProcedure
cmdSQL.Parameters.Add(New SqlParameter("@.required_page",
Me.ResultsPageNumber))
cmdSQL.Parameters.Add(New SqlParameter("@.rows_per_page",
ConfigurationSettings.AppSettings("KBRowsPerPage")))
If Me.Search_FileType <> "" Then cmdSQL.Parameters.Add(New
SqlParameter("@.FileType", Me.Search_FileType))
If Me.Search_EventId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.idEvents", Me.Search_EventId))
If Me.Search_Keywords <> "" Then cmdSQL.Parameters.Add(New
SqlParameter("@.SearchKeywords", Me.Search_Keywords))
If Me.MemberGroupId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.member_group_id", Me.MemberGroupId))
If Me.MemberGroupId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.is_member_group_restrict", IIf(Me.isRestrictToMemberGroup,
"Y", "N")))
Dim sqlParm As SqlParameter
sqlParm = cmdSQL.Parameters.Add(New SqlParameter("@.max_pages",
CInt(0)))
sqlParm.Direction = ParameterDirection.Output
daSQL.SelectCommand = cmdSQL
Dim iPages As Integer
Try
daSQL.Fill(dsSQL)
datlArt.DataSource = dsSQL
datlArt.DataBind()
iPages = cmdSQL.Parameters("@.max_pages").Value
Catch ex As Exception
iPages = 0
End Try
trNoArticles.Visible = (datlArt.Items.Count = 0)
If iPages = 0 Then
tdPage.Visible = False
Else
lbtnPageNext.Visible = (Me.ResultsPageNumber < iPages)
lbtnPagePrev.Visible = Me.ResultsPageNumber > 1
lblPageCur.Text = Me.ResultsPageNumber.ToString
lblPageLast.Text = iPages.ToString
tdPage.Visible = True
End If
dbConn.Close()
dbConn.Dispose()
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:3960701C-287D-450B-B610-3C36919B9ABF@.microsoft.com...
> Tony,
> Could you provide an example of using Meta Data for such a scanario?
>
> "Tony Rogerson" wrote:
>

CTE Vs Temp Table in Yukon

Hi All,

I would like to know which gives better performance: CTE or Temporary Table?

Thanks,

Suresh

I wanted to add another information here.

When I replaced Temporary Tables with CTE in my query, it took more execution time. For ex., the query using Temp Table took 1 min 5 secs. The query using CTE took 4 mins. The no. of records hold by temp table in my query is apprx. 44000.

Why CTE is slower?

Suresh

|||

Check the execution plan, that is the only way you will find out. Clearly the CTE is using a different plan. You can force the execution plan to use a certain order using the FORCE command or option force_order at the end of the query.

Clarity Consulting (www.claritycon.com)

|||You cannot compare CTE and temporary table. They are different beasts. There are cases where you can break a complex query into simpler parts using temporary tables and get better performance. I am not sure how you used CTE in your query so it is hard to say. Note that CTEs also provide the capability to perform recursive queries in a declarative manner. And what are you measuring regarding the performance? Is it the temporary table creation vs query using CTE returning rows? If you are just measuring creation part then it doesn't include the time taken to send results to whatever client you are using. You need to elaborate on the actual problem. Best is to post a sample script that reproes the performance problem.

CTE to replace cursor

I am trying to replace a cursor with a CTE. Is it possible to scroll
through the records returned using the CTE?
i.e.
If I get more than one row returned check the values in each row? A bit
like a result set?
I know there is some recursive stuff you can do but I don't think it
will work for this as it is not a Parent/Child i.e. hierarchy
situation.
Thanks for any help offered
JamieYou'll need to post more details of your problem.
If you are interested in recursive CTEs, there is an excellent article
here
http://www.sqlservercentral.com/col...lserver2005.asp|||>I am trying to replace a cursor with a CTE. Is it possible to scroll
> through the records returned using the CTE?
Can you explain exactly what you are trying to accomplish? This description
is a bit vague. http://www.aspfaq.com/5006

> If I get more than one row returned check the values in each row? A bit
> like a result set?
Depending on exactly what you are doing "in each row," a cursor may be the
only way. Again, if you can explain better exactly what you are doing, with
real specs, we may be able to provide more helpful input.
http://www.aspfaq.com/5006|||Thanks for your responses.
The following is what I am attempting.
If I have:
select 1 AS Counter, custid, code from customer where custid IN (8072,
7786)
which returns:
CUSTID CODE
7786 [code]
8072 #
I turn this into a CTE
WITH CustCte(CustID, Code)
AS
(SELECT CustID, Code FROM dbo.Customer WHERE CustID IN (8072, 7786))
SELECT CustID, Code FROM CustCte
GO
Which naturally gives me:
CUSTID CODE
7786 [code]
8072 #
I now want navigate through each row i.e.
For first row if Code = XYZ then do something. else move to next row
If next row Code = XYX then do something.
I came up with the following and various different variations but can't
get the Counter to increment.
WITH CustCte(Counter, CustID, Code)
AS
(SELECT 1 AS Counter, CustID, Code FROM dbo.Customer WHERE CustID IN
(8072, 7786))
SELECT Counter = Counter + 1, CustID, Code FROM CustCte
do you have further thoughts.|||jamie.downs@.risk.sungard.com wrote:
> Thanks for your responses.
> The following is what I am attempting.
> If I have:
> select 1 AS Counter, custid, code from customer where custid IN (8072,
> 7786)
> which returns:
> CUSTID CODE
> 7786 [code]
> 8072 #
> I turn this into a CTE
> WITH CustCte(CustID, Code)
> AS
> (SELECT CustID, Code FROM dbo.Customer WHERE CustID IN (8072, 7786))
> SELECT CustID, Code FROM CustCte
> GO
> Which naturally gives me:
> CUSTID CODE
> 7786 [code]
> 8072 #
> I now want navigate through each row i.e.
> For first row if Code = XYZ then do something. else move to next row
> If next row Code = XYX then do something.
>
This is not a full description of the problem. What we need to know is
what is the operation that you are calling "do something". If "do
something" means "do some data manipulation" then chances are you can
do it without a cursor or a loop.
The best way to specify your problem is to post DDL, sample data and
show your required end results.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If the "do something" part can be written as a function you don't need a
cursor. Even if "do something" returns a set - CROSS APPLY can be used.
The Counter in your CTE would only increment in a recursion - i.e. if there
was a recursive member in addition to the anchor member. And even then the
value would reflect the number of recursions, not the number of rows.
An alternative to cursors may be a table variable, but this would require
you to add the logic to "simulate" a cursor.
ML
http://milambda.blogspot.com/|||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. 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.
A CTE is like a VIEW, and not anything like a cursor. There is no
scrolling in SQL since it is a declarative language.|||>> I now want navigate through each row.. <<
"Navigate" is a dirty word in RDBMS. In fact, we replaced navigational
DBMS systems with RDBMS to get rid of it.
Since I cannot figure out the "do something" function from what you
posted, my guess would be that you want a CASE expression. something
like this:
UPDATE Customers
SET foobar
= CASE WHEN foo_code = 'xyz'
THEN 42 ELSE foobar END
WHERE cust_id IN (8072, 7786);
But who knows?|||> CREATE TABLE #Cust2 (CustID INT, Code VARCHAR(10))
INSERT #Cust2 SELECT CustID, CASE Code
WHEN 'Cust1' THEN NULL ELSE Code END
FROM #Cust;
SELECT * FROM #Cust2;
-- poof! Cursor be gone
Or better yet, eliminate the need for #Cust2 in the first place:
SELECT CustID, CASE Code
WHEN 'Cust1' THEN NULL ELSE Code END
FROM #Cust;
If we had seen how #Cust was populated in the first place, we can probably
improve the performance even further by eliminating that I/O. But again,
you haven't provided enough details. The line count of your stored
procedure hardly tells us what it is actually doing. In general, stop
trying to do Visual Basic style programming in the database...|||Hi Aaron,
Thanks for your reply. I did say it was a much simplified version of
the original proc. I find it quite amusing that the general comments
you hear about TSQL is to avoid Cursors and Temp tables wherever
possible and to make SQL objects as simple as possible which what I am
trying to achieve. Not VB. You still want more details. Well her is
the full blown proc. No I did not write it.
CREATE procedure DBO.Prc_Get_Checks
@.CURSORSQL VARCHAR(8000),
@.WHERECLAUSE TEXT,
@.CHECKSFORDEALVIOLATIONS BIT,
@.CALCULATEMANUAL BIT,
@.SHOWINACTIVE BIT = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.CheckID INT
DECLARE @.PortfolioID INT
DECLARE @.ClassID INT
DECLARE @.MeasureID INT
DECLARE @.ChecksType INT
DECLARE @.Value1 VARCHAR(200)
DECLARE @.Value2 VARCHAR(800)
DECLARE @.Message VARCHAR(200)
DECLARE @.MessageID INT
DECLARE @.Severity INT
DECLARE @.ReviewDate DATETIME
DECLARE @.ExpiryDate DATETIME
DECLARE @.AssetID INT
DECLARE @.TemplateRef INT
DECLARE @.IsConditional BIT
DECLARE @.Active BIT
DECLARE @.Threshold FLOAT
DECLARE @.HasTransfers BIT
DECLARE @.CheckSymbol VARCHAR(20)
DECLARE @.ChecksTypeSymbol VARCHAR(20)
-- template variables
DECLARE @.TemplateMeasureID INT
DECLARE @.TemplateChecksType INT
DECLARE @.TemplateValue1 VARCHAR(200)
DECLARE @.TemplateValue2 VARCHAR(800)
DECLARE @.TemplateMessage VARCHAR(200)
DECLARE @.TemplateMessageID INT
DECLARE @.TemplateSeverity INT
DECLARE @.TemplateReviewDate DATETIME
DECLARE @.TemplateExpiryDate DATETIME
DECLARE @.TemplateAssetID INT
DECLARE @.TemplateActive BIT
DECLARE @.TemplateThreshold FLOAT
DECLARE @.TemplateChecksTypeSymbol VARCHAR(20)
DECLARE @.TemplatePortfSymbol VARCHAR(20)
DECLARE @.Ctrl_Amt_Class_Symbol VARCHAR(20)
DECLARE @.Ctrl_Amt FLOAT
DECLARE @.Ctrl_Active BIT
DECLARE @.Duplicate_CheckCount INT
DECLARE @.CHECKSTAB TABLE
(CheckID INT,
PortfolioID INT,
ClassID INT,
MeasureID INT,
ChecksType INT,
Value1 VARCHAR(200),
Value2 VARCHAR(800),
Message VARCHAR(200),
MessageID INT,
Severity INT,
ReviewDate DATETIME,
ExpiryDate DATETIME,
AssetID INT,
TemplateRef INT,
IsConditional BIT,
Active BIT,
Threshold FLOAT,
HasTransfers BIT)
-- Declare a cursor that represents placeholder/ordinary check rows
from the checks table
-- for portfolios specified in this function's parameter
-- concatenate the two...
EXEC(@.CURSORSQL + @.WHERECLAUSE)
OPEN CHECK_CURSOR
-- Fetch the cursor column values into variables.
-- These some of these variables' values may be overridden for
placeholders
FETCH NEXT FROM CHECK_CURSOR INTO
@.CheckID,
@.PortfolioID,
@.ClassID,
@.MeasureID,
@.ChecksType,
@.Value1,
@.Value2,
@.Message,
@.MessageID,
@.Severity,
@.ReviewDate,
@.ExpiryDate,
@.AssetID,
@.TemplateRef,
@.IsConditional,
@.Active,
@.Threshold,
@.HasTransfers,
@.CheckSymbol,
@.ChecksTypeSymbol
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- IF @.CHECKSYMBOL = 'CHECK_LIMIT_AUTO' OR (@.CHECKSYMBOL =
'CHECK_LIMIT_MANUAL' AND @.CALCULATEMANUAL = 1)
-- then we have a placeholder template, so override some of the
values
-- If we have a placeholder template with a Template Reference,
override some of the values
-- NB: It's now possible to have placeholders without template
references stored in the DB, in which case the template
-- is determined at runtime by the CRS Server software. This is
part of CHG109212, June 2004.
IF ((@.ChecksTypeSymbol = 'CHECKS_AUTO') AND (@.TemplateRef IS
NOT NULL))
BEGIN
-- Get the placeholder's template column values using the
template ref checkID
SELECT
@.TemplateMeasureID = Checks.MeasureID,
@.TemplateChecksType = Checks.ChecksType,
@.TemplateValue1 = Checks.Value1,
@.TemplateValue2 = Checks.Value2,
@.TemplateMessage = Checks.Message,
@.TemplateMessageID = Checks.MessageID,
@.TemplateSeverity = Checks.Severity,
@.TemplateReviewDate = Checks.ReviewDate,
@.TemplateExpiryDate = Checks.ExpiryDate,
@.TemplateAssetID = Checks.AssetID,
@.TemplateActive = Checks.Active,
@.TemplateThreshold = Checks.Threshold,
@.TemplatePortfSymbol = DefnPortfolio.Symbol,
@.TemplateChecksTypeSymbol = C2.Symbol
FROM
Checks,Portfolio, DefnPortfolio, Class C2
WHERE Checks.CheckID = @.TemplateRef
AND Checks.PortfolioID = Portfolio.PortfolioID
AND DefnPortfolio.PfolDefnID = Portfolio.ClassID
AND C2.ClassID = Checks.ChecksType
-- Override placeholder's MESSAGE, SEVERITY, VALUE1,
VALUE2, REVIEWDATE, EXPIRYDATE, MEASUREID, and MESSAGEID values with
those
-- of its template
SET @.MeasureID = @.TemplateMeasureID
SET @.Value1 = @.TemplateValue1
SET @.Value2 = @.TemplateValue2
SET @.Message = @.TemplateMessage
SET @.MessageID = @.TemplateMessageID
SET @.Severity = @.TemplateSeverity
SET @.ReviewDate = @.TemplateReviewDate
SET @.ExpiryDate = @.TemplateExpiryDate
-- If the Template Limit is 'unlimited' then set the
CheckType in the returned (placeholder) row to 'unlimited' as well
IF (@.TemplateChecksTypeSymbol = 'CHECKS_UNLIMITED')
BEGIN
SET @.ChecksType = @.TemplateChecksType
END
-- Find out which control amount class (customer,parent or
ultimate) the template uses (e.g. match PFOL_CUSTOMER)
SELECT @.Ctrl_Amt_Class_Symbol =
dbo. GET_CTRL_AMT_SYMBOL(@.TemplatePortfSymbol
)
-- override the VALUE of the placeholder using the template
(and in certain cases the control amt)
-- " " ASSETID of the placeholder using either the
template's ASSETID (or in certain cases the ASSETID of the control
amount)
-- " " ACTIVE column of the placeholder so that if the
template is inactive the placeholder is also INACTIVE (or in certain
cases the ACTIVE column
IF @.Ctrl_Amt_Class_Symbol IS NULL --then there isn't a
control amount, so return the template value as actual values (i.e.
non-perecentages)
BEGIN
-- ...then return the template's value column as the
result
SET @.Value1 = @.TemplateValue1
SET @.Value2 = @.TemplateValue2
SET @.AssetID = @.TemplateAssetID
IF @.TemplateActive = 0 SET @.Active=0
END
ELSE
-- VALUE is the template's percentages * control
currency,
-- ASSETID is that of the control currency
-- ACTIVE is only true if the control currency,
template and placeholder are all active
BEGIN
-- first get information from the CustControlAmounts
table
SELECT @.Ctrl_Amt=Ctrl.Amount,
@.AssetID=Ctrl.AssetID,@.Ctrl_Active=Ctrl.Active
FROM dbo.CustControlAmounts Ctrl, dbo.Portfolio
PlaceholderPortf, dbo.Checks C, dbo.Class CL
WHERE --join conditions for getting the control
amount from CUSTCONTROLAMOUNTS.
--a row in CUSTCONTROLAMOUNTS is
idenitifed with a HOSTID,CUSTID and CLASSID
Ctrl.HostID = PlaceholderPortf.HostID
AND Ctrl.CustID = PlaceholderPortf.CustID
AND Ctrl.TypeID = CL.ClassID
AND CL.Symbol = @.Ctrl_Amt_Class_Symbol
AND PlaceholderPortf.PortfolioID =
C.PortfolioID
AND C.CheckID = @.CheckID
-- fix for BUG111446: if there is no control amount,
use values from the template
IF (@.AssetID IS NULL) OR (@.Ctrl_Amt IS NULL) OR
(@.Ctrl_Active IS NULL)
BEGIN
SET @.Value1 = @.TemplateValue1
SET @.Value2 = @.TemplateValue2
SET @.AssetID = @.TemplateAssetID
IF @.TemplateActive = 0 SET @.Active=0
END
ELSE
BEGIN
-- we do have a control amount, so act on it
accordingly
IF (@.Ctrl_Active = 0) OR (@.TemplateActive = 0)
SET @.Active = 0
-- if the placeholder is still considered active,
check for duplicate checks of the same measure
-- on the same portfolio
IF @.Active = 1
BEGIN
-- look for active ordinary/manual checks that
cover the same measure and portfolio
-- although there really shouldn;t be any of
these!!
SELECT @.DUPLICATE_CHECKCOUNT = COUNT(CheckID)
FROM dbo.Checks
WHERE DBO.GET_CHECKMEASUREID(CheckID) =
@.MeasureID
AND PortfolioID =
@.PortfolioID
AND CheckID <>
@.CheckID
AND Active =
1
-- if we found an active ordinary/manual check,
use that instead of the placeholder
IF @.DUPLICATE_CHECKCOUNT > 0
SET @.Active = 0
END
IF (@.Active = 1) OR (@.ShowInActive=1)
SELECT @.Value1 =
DBO. GET_PLACEHOLDERVALUE(@.TemplateChecksType
Symbol, @.TemplateValue1,
@.Ctrl_Amt)
END
END
END
-- If it's a 'dynamic' placeholder check (with no template)
then NULL out the limit value as an indication to the
-- calling softwatre that the value neds computing and has not
been done by this routine. This is part of CHG109212, June 2004.
IF ((@.ChecksTypeSymbol = 'CHECKS_AUTO') AND (@.TemplateRef IS
NULL))
BEGIN
SET @.Value1 = NULL
SET @.Value2 = NULL
END
-- Don't allow templates or inactive checks to appear in the
result set for deal violations
-- for browsing, all checks will appear
IF (@.ChecksForDealViolations=1 AND @.CheckSymbol <>
'CHECK_LIMIT_TEMPLATE' AND (@.Active=1 OR @.ShowInActive=1)) OR
(@.ChecksForDealViolations=0 AND (@.Active=1 OR @.ShowInActive=1))
BEGIN
-- Insert into result table the values (which have been
overriden in the case of placeholder checks)
INSERT INTO @.CHECKSTAB
SELECT @.CheckID, @.PortfolioID, @.ClassID, @.MeasureID,
@.ChecksType, @.Value1, @.Value2, @.Message, @.MessageID, @.Severity,
@.ReviewDate,
@.ExpiryDate, @.AssetID, @.TemplateRef,
@.IsConditional, @.Active, @.Threshold, @.HasTransfers
END
FETCH NEXT FROM CHECK_CURSOR INTO
@.CheckID, @.PortfolioID, @.ClassID, @.MeasureID, @.ChecksType,
@.Value1, @.Value2, @.Message, @.MessageID, @.Severity, @.ReviewDate,
@.ExpiryDate,
@.AssetID, @.TemplateRef, @.IsConditional, @.Active,
@.Threshold, @.HasTransfers, @.CheckSymbol, @.ChecksTypeSymbol
END -- while
CLOSE CHECK_CURSOR
DEALLOCATE CHECK_CURSOR
IF @.ChecksForDealViolations = 1
SELECT * FROM @.CHECKSTAB ORDER BY PortfolioID
ELSE --return a formatted view for browsing checks
SELECT
DefnPortfolio.PortfolioName AS Portfolio,
Checks.MESSAGE AS Legend, -- DummyColumn
Checks.CheckID,
Class.Name AS Type,
ChecksType,
DefnPortfolioMeasureClass.Name AS Measure,
-- Checks.Message Description, -- DummyColumn
Checks.Severity,
Checks.REVIEWDATE AS Review,
Checks.EXPIRYDATE AS Expiry,
Checks.PortfolioID,
Checks.ClassID,
Checks.Value1,
Checks.Value2,
Checks.Active,
Checks.Threshold,
Checks.HasTransfers,
dbo.Asset.Code AS Currency,
(SELECT COUNT(1)
FROM dbo.Violation
WHERE Violation.CheckID = Checks.CheckID) AS
Violations,
DefnPortfolioMeasureClass.MeasureID AS MeasureID,
Checks.AssetID,
IsConditional Conditional,
Checks.Message Details
FROM @.CHECKSTAB Checks
INNER JOIN dbo.Class
ON Checks.CLASSID = Class.ClassID
LEFT OUTER JOIN dbo.Asset
ON Checks.ASSETID = Asset.AssetID
LEFT OUTER JOIN dbo.DefnPortfolio
INNER JOIN dbo.Portfolio
ON DefnPortfolio.PfolDefnID = Portfolio.ClassID
ON Checks.PORTFOLIOID = Portfolio.PortfolioID
LEFT OUTER JOIN dbo.DefnPortfolioMeasureClass
ON Checks.MEASUREID =
DefnPortfolioMeasureClass.MeasureID
END

CTE Optimization

Hi,
Below is the traditional use of CTE for retrieving nodes of a tree. My
question is that when I use a condition like 'where lvl<=2' the execution
plan shows that filtering the result is the final phase of execution. Does
it mean that if I have a deep level of hierarchies in my table, the
performance will not be good? Will it prepare all of the records and then
filters the result?
Thanks in advance,
Leila
--
USE Northwind
GO
WITH MyChart(EmployeeID,EmpName,BossID,BossNa
me,lvl) AS
(SELECT EmployeeID,FirstName,EmployeeID,FirstNam
e, 1
FROM Employees WHERE EmployeeID=2
UNION ALL
SELECT Emp.EmployeeID,Emp.FirstName,MyChart.EmployeeID,
MyChart.EmpName, MyChart.lvl+1
FROM Employees Emp INNER JOIN MyChart
ON Emp.ReportsTo=MyChart.EmployeeID
)
SELECT * FROM MyChart
where lvl<=2Yes -- why don't you put your filter in the recursive query instead?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:uAXNjWzKGHA.740@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Below is the traditional use of CTE for retrieving nodes of a tree. My
> question is that when I use a condition like 'where lvl<=2' the execution
> plan shows that filtering the result is the final phase of execution. Does
> it mean that if I have a deep level of hierarchies in my table, the
> performance will not be good? Will it prepare all of the records and then
> filters the result?
> Thanks in advance,
> Leila
> --
> USE Northwind
> GO
> WITH MyChart(EmployeeID,EmpName,BossID,BossNa
me,lvl) AS
> (SELECT EmployeeID,FirstName,EmployeeID,FirstNam
e, 1
> FROM Employees WHERE EmployeeID=2
> UNION ALL
> SELECT Emp.EmployeeID,Emp.FirstName,MyChart.EmployeeID,
> MyChart.EmpName, MyChart.lvl+1
> FROM Employees Emp INNER JOIN MyChart
> ON Emp.ReportsTo=MyChart.EmployeeID
> )
> SELECT * FROM MyChart
> where lvl<=2
>

CTE in OLE DB Command Data Flow Transformation

I am trying to use a CTE in an OLE DB Command data flow transformation object. However, when I enter the cte and corresponding query in the SqlCommand field of the OLE DB command editor dialog, I get a syntax error. Can CTE's be used data flow objects? I have been able to use them in an Execute SQL Control Flow Item, but not in any data flow item.

I was able to paste a query using a CTE inside of the OLE DB Command with no problem(just a quick copy and paste- no parameters invloved); then I think CTEs are not the problem. May be is the parameter mapping or something else. Could you post the query and the error so folks around here can get a better picture of the problem.|||

Thank you for your reply. I was able to successfully use a CTE in an OLE DB Source data flow component. I had a silly syntax error.

I am now running into a problem using a CTE in an OLEDB command object. There are two levels of the problem.

1. I can use a straight forward CTE in an OLEDB command object if I use the "Native OLE DB\SQL Native Client" provider.

For example, this works:

/**BEGIN QUERY**/

with TestCTE2(prod_id) as
(
select ProductID from Production.Product p
where p.ProductID = 321
)

select prod_id from TestCTE2

/**END QUERY**/

but if I try to modify and use a parameter, like:

/**BEGIN QUERY**/

with TestCTE2(prod_id) as
(
select ProductID from Production.Product p
where p.ProductID= ?
)

select prod_id from TestCTE2

/**END QUERY**/

I get the following error:

Error 2 Validation error. Data Flow Task: OLE DB SQL Native Client [863]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Package.dtsx 0 0

2. If I try and use a CTE in an OLE DB Command data flow object using the "Native OLE DB\Microsoft OLE DB Provider for SQL Server" provider

I can't enter the first query from above at all, i get the following error message:

Error 1 Validation error. Data Flow Task: OLE DB provider for SQL Server [875]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Incorrect syntax near the keyword 'with'.". Package.dtsx 0 0

which I find curious, because there is no previous stataments.

The above queries are going against the adventureworks database.

Any further insight? Thanks for your help.

Kyle Key

|||

Kyle,

That was the test I performed in my initial post; and you are right, as soon as you add a parameter it throws an error.

Just out of curiosity; what is your ultimate goal when trying to combine a CTE and an OLEDB command? Are you trying to update rows or what?

For performance reasons, I try to stay away of OLE DB Commands. In some cases, you could replace the OLE DB Command by a OLEDB Destination that point to a staging table and then in control flow you can use a Execute SQL task to execute the same SQL statement using staging table as a reference to constraint which rows should be affected. The advantage of this procedure is to execute the sql statement only once; instead of 1 per row when an OLE DB command is used.

This does not answer your question but could give you other options

|||

I'm using the CTE to flatten out a hierarchy. I can get a list of nodes in a hierarchy, and was hoping to use the OLE DB Command transformation to loop through those nodes to get the leaf nodes and update those leaf nodes. I was going under the assumption that using a staging/temporary table would be costly to performance, but I may have to rethink my approach. I guess getting it done is better than spinning my wheels.

Do you know if the problem I'm seeing is a bug or functioning as designed? I was going to install the latest CTP for SP2 and see if the behavior is any different.

|||

I actually don't know if that would be fixed in SP2. But I found a work around; just place the SQL Statement inside of a stored procedure and then called it from the OLE DB Command.

I ran a test against AdventureWorks, see here for more details:

http://rafael-salas.blogspot.com/2006/12/passing-parameters-to-ole-db-command.html

Pleas, let me know if you found a diffrent approach.

thanks

|||

Kyle Key wrote:

I'm using the CTE to flatten out a hierarchy. I can get a list of nodes in a hierarchy, and was hoping to use the OLE DB Command transformation to loop through those nodes to get the leaf nodes and update those leaf nodes. I was going under the assumption that using a staging/temporary table would be costly to performance, but I may have to rethink my approach. I guess getting it done is better than spinning my wheels.

Do you know if the problem I'm seeing is a bug or functioning as designed? I was going to install the latest CTP for SP2 and see if the behavior is any different.

Kyle,

This probably isn't the optimum way of going about this. As Rafael says the OLE DB Command is not at all performant (we can elaborate as to why if needs be).

In your case it does seem as though using T-SQL will be a better bet. If you really do want to execute this functionality in the data-flow and not have to operate on the data row-by-row a la the OLE DB Command then you could use an asychsronous script component.

It would help to understand the problem better. How are your values in the data-flow being used to update the existing data?

-Jamie

CTE in a DSV named query

Hello,

I have a CTE that I want to put into a DSV named query. here is the CTE (which anyone can run):

WITH mycte AS (SELECT TOP (200) object_id, name, column_id, system_type_id
FROM sys.columns)
SELECT object_id, name, column_id, system_type_id
FROM mycte AS mycte_1

I can put that into the named query editor and run it, no problems. Upon clicking "OK" I get:

Incorrect syntax near ')'

Incorrect syntax near the keyword 'with'/ If this statement is a common table expression or an xmlnamespaces clause the previous statement must be terminated with a semicolon.

So, I put a semicolon in front of it so it looks liek this:

;WITH mycte AS (SELECT TOP (200) object_id, name, column_id, system_type_id
FROM sys.columns)
SELECT object_id, name, column_id, system_type_id
FROM mycte AS mycte_1

This time I get:

Unable to parse query text

Incorrect syntax near ')'

Incorrect syntax near ';'

Please don't tell me that CTEs are not allowed in a DSV. That would be very VERY bad.

Please will someone try this for me? Can you repro the problem?

Thanks

Jamie

Jamie,

You might want to pose this question to the SQL Server Programming folks as well - from what I can tell, the problem is that the Named Query is embedded as a subquery, and I'm not sure how/whether a CTE can be used in a subquery. The generated query looks like:

select [CTETest].*

from

(

WITH mycte AS (SELECT TOP (200) object_id, name, column_id, system_type_id

FROM sys.columns)

SELECT object_id, name, column_id, system_type_id

FROM mycte as mycte_1

) AS [CTETest]

Defining the CTE at the outer select works, but obviously doesn't help for the DSV:

WITH mycte AS (SELECT TOP (200) object_id, name, column_id, system_type_id

FROM sys.columns)

select [CTETest].*

from

(

SELECT object_id, name, column_id, system_type_id

FROM mycte as mycte_1

) AS [CTETest]

|||

Thanks Deepak,

Fundamentally though, the fact that you can't put valid T-SQL into a DSV is bad bad bad wouldn't you agree?

I'm not too enamoured with this.

-Jamie

|||Jamie, I share your frustation - just curious whether the CTE scenario was considered when the DSV/Named Query framework was being architected? Maybe someone from MS can shed some light on this, meanwhile I'll post a question to see if there's any work-around on the relational side...|||

To follow up - I got this response from Erland on the SQL Server Programming newsgroup, which suggests that an SSAS bug report be opened. There are some XSL Cartridge files that can be tweaked to change the generated SQL for other databases (DB2, SQL 2000, etc), but I'm not sure that there is a cartridge for SQL Server 2005 itself.

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/5150eba86d06e5b0

>>

microsoft.public.sqlserver.programming > Can CTE be used inside a FROM subquery?

This query works:

WITH mycte AS (SELECT TOP (200) object_id, name, column_id,
system_type_id
FROM sys.columns)
select [CTETest].*
from
(

SELECT object_id, name, column_id, system_type_id
FROM mycte as mycte_1
) AS [CTETest]

That is, the CTE should be at the head of the outer query.

If Analysis Services generates the incorrect syntax, I assume that
this is a bug in AS, and I suggest that you submit a bug on
http://lab.msdn.microsoft.com/ProductFeedback/.

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
...

>>

|||

Thank you Deepak (and to Erland). I have raised this at the feedback centre - feel free to vote for it and add comments.

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK49479

Thanks

Jamie

|||

The reason why we are trying to wrap it as sub select statement is to avoid some statements like Create, Delete, order by.

You can try to remove the subselect capability in the cartridge by removing the line in Sql2000.xsl

<mssqlcrt:supports-subselect />

The cartridges are located in the following directories for tools and engine respectively.

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\DataWarehouseDesigner\UIRdmsCartridge

C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\Cartridges

After removing this, you should be able to create the named query but it may hurt the performance when processing in engine.

CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expre

I am having this error when using execute query for CTE

Help will be appriciated

Would be interesting to have the code you tried to execute, because this i ibviously a syntax error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||? As Jens noted, you haven't showed us any code... But I'm betting you just need to use a semicolon before the "WITH": ;WITH myCTE AS ... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <dba_sql@.discussions.microsoft.com> wrote in message news:35bbde4b-222b-4311-8087-09d80efaa94b@.discussions.microsoft.com... I am having this error when using execute query for CTE Help will be appriciated|||

I am getting this error when running the following code:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION ClassificationsInTree(@.ClassificationTreeId int)

RETURNS @.ClassificationsInTree TABLE (ClassificationId int)

AS

BEGIN

WITH CLINTREE(ClassificationId) AS (

SELECT TopClassificationId FROM ClassificationTree

WHERE ClassificationTreeId = 81203717

UNION ALL

SELECT ClassificationId FROM Classification

INNER JOIN CLINTREE ON

CLINTREE.ClassificationId = Classification.ParentClassificationTree

WHERE Classification.ClassificationId <> CLINTREE.ClassificationId

)

--INSERT @.ClassificationsInTree

SELECT ClassificationId FROM CLINTREE

OPTION (MAXRECURSION 10);

RETURN

END

GO

The error messages:

Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7

Incorrect syntax near the keyword 'WITH'.

Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18

Line 18: Incorrect syntax near 'MAXRECURSION'.

Any thoughts? This is the exact syntax found in the help files, no?

|||? I was able to run that batch on my end with no errors once I uncommented the insert line... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JGilbertie@.discussions.microsoft.com> wrote in message news:0b38d9cc-f171-4ae6-8377-fc50393d8045@.discussions.microsoft.com... I am getting this error when running the following code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION ClassificationsInTree(@.ClassificationTreeId int) RETURNS @.ClassificationsInTree TABLE (ClassificationId int) AS BEGIN WITH CLINTREE(ClassificationId) AS ( SELECT TopClassificationId FROM ClassificationTree WHERE ClassificationTreeId = 81203717 UNION ALL SELECT ClassificationId FROM Classification INNER JOIN CLINTREE ON CLINTREE.ClassificationId = Classification.ParentClassificationTree WHERE Classification.ClassificationId <> CLINTREE.ClassificationId ) --INSERT @.ClassificationsInTree SELECT ClassificationId FROM CLINTREE OPTION (MAXRECURSION 10); RETURN END GO The error messages: Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7 Incorrect syntax near the keyword 'WITH'. Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18 Line 18: Incorrect syntax near 'MAXRECURSION'. Any thoughts? This is the exact syntax found in the help files, no?|||

Thanks for the reply.

I get those same two errors whether that line is commented or not...

Is there some kind of configuration I need to do to enable the WITH statement? shot in the dark, but I can't see any difference from examples I've found for using WITH.

|||? None that I know of. The only one I could think of was compatability level -- but I just tested with a database set to compatability level 80 (SQL Server 2000) and was still not able to replicate the error. Regardless, you should probably make sure yours is set correctly. Right-click on your database in SSMS, click Properties, then Options. Make sure Compatability Level is set to SQL Server 2005... Aside from that, though, I'm not sure what's going on. Can you use CTEs at all (outside of UDFs?) And did you try adding a semicolon before the WITH, as I suggested before? It appears to be unnecessary on my end, but it's always a good idea anyway... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JGilbertie@.discussions.microsoft.com> wrote in message news:1cbe0923-6981-44eb-b4cd-8bdee48ad315@.discussions.microsoft.com... Thanks for the reply. I get those same two errors whether that line is commented or not... Is there some kind of configuration I need to do to enable the WITH statement? shot in the dark, but I can't see any difference from examples I've found for using WITH.|||

So your suggestion to check the compatability level led me to the answer. The database server I was trying to run the query against is a SQL2000 server. We have many instances of SQL Server running for development purposes, and I didn't realize I was working against a SQL2000 instance. That database has the data I need in it, so I will have to move it to another machine.

A silly mistake, but I wouldn't have realized it, Thanks for your help!

|||I got the same error in Crystal Reports XI. I used Toad to write the SQL, then copied it into the command editor in Crystal. It worked fine, until I opened the same saved report on CRXI from a Citrix client. Still works fine using Toad, same datasource, credentials, etc. After reading this thread, I tried putting the semicolon in front of WITH and it worked. Strange to me, but it works,

CTE behind the scenes

When using CTE's in SQL 2005, what's going on behind the scenes? More
specifically, suppose you create a CTE, and then in the following
query that uses the CTE it's referenced multiple times. Is the query
nested within the CTE executed multiple times (for each instance it's
used in the final query)?
Here's a mock-up, not a great example of why you'd need to reference
the CTE more than once in your final query, but that's not the point.
Let us assume we have a CTE that's referenced more than once in the
query that consumes it:
WITH OrdersByMonth (year, month, order_count)
AS
(
SELECT
YEAR(order_date) AS year,
MONTH(order_date) AS month,
COUNT(order_id) AS order_count
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
)
SELECT a.year, a.month, a.order_count / b.annual_order_count AS
percent_of_year
FROM OrdersByMonth a
INNER JOIN (
SELECT year, SUM(order_count) as annual_order_count
FROM OrdersByMonth
) b
ON a.year = b.year
I realize that the derived table "b" could in fact be another CTE, but
that's not the point. I just want to show something where a CTE is
created (OrdersByMonth), and then used more than once in the following
query.
My question is this: the final query references OrdersByMonth twice.
Does that mean the statement within that CTE is actually executed
twice, or is it only run once and held in memory or something like
that?
Here's why it matters to me: I'm using a CTE to reference data I'm
bringing over from a linked oracle server. Imagine the CTE above re-
written as follows:
WITH OrdersByMonth (year, month, order_count)
AS
(
SELECT a.year, a.month, a.order_count
FROM OPENQUERY(MYLINKEDSERVER, '
SELECT YEAR(order_date) AS year,
MONTH(order_date) AS month,
COUNT(order_id) AS order_count
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
') a
)
Suppose this statement brings over thousands and thousands of rows,
and takes an hour to run. If I reference this CTE multiple times in
the final query, does it actually execute the openquery statement each
time, or is it run just once, and then subsequent references to the
CTE use the data it has presumable cached? Or in other words, how
many times would I be hitting the linked server?
Apologies for the example, I'd post the real thing here but it's huge,
so much more processing/joining with other data going on it would be
difficult to filter down to my question. But I'd really appreciate
any insight you might have!
I have been using CTEs extensively to find the first and last sale dates per
customer given a transaction file. I find that SQL treats a CTE much like a
view. That is, it is simply a convienent way to express what you want and
not an absolute set of instructions. This goes back to the fact that SQL
is, strictly speaking, not a programming language. You are not telling SQL
how to solve a problem, you are describing the aanswer you want. Big
difference there.
As for your specific question, I have been using aparticular CTE that
references a 13M row table across a linked server multiple times in a single
query. The additional references do seem to increase the duration somewhat,
but in a less than linear fashion. There is some caching and data reuse
when the query executes, which is what you would expect from the SQL
optimizer.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Arthur Dent" <dwt12777@.gmail.com> wrote in message
news:1194542752.426205.49980@.t8g2000prg.googlegrou ps.com...
> When using CTE's in SQL 2005, what's going on behind the scenes? More
> specifically, suppose you create a CTE, and then in the following
> query that uses the CTE it's referenced multiple times. Is the query
> nested within the CTE executed multiple times (for each instance it's
> used in the final query)?
> Here's a mock-up, not a great example of why you'd need to reference
> the CTE more than once in your final query, but that's not the point.
> Let us assume we have a CTE that's referenced more than once in the
> query that consumes it:
> WITH OrdersByMonth (year, month, order_count)
> AS
> (
> SELECT
> YEAR(order_date) AS year,
> MONTH(order_date) AS month,
> COUNT(order_id) AS order_count
> FROM orders
> GROUP BY YEAR(order_date), MONTH(order_date)
> )
> SELECT a.year, a.month, a.order_count / b.annual_order_count AS
> percent_of_year
> FROM OrdersByMonth a
> INNER JOIN (
> SELECT year, SUM(order_count) as annual_order_count
> FROM OrdersByMonth
> ) b
> ON a.year = b.year
> I realize that the derived table "b" could in fact be another CTE, but
> that's not the point. I just want to show something where a CTE is
> created (OrdersByMonth), and then used more than once in the following
> query.
> My question is this: the final query references OrdersByMonth twice.
> Does that mean the statement within that CTE is actually executed
> twice, or is it only run once and held in memory or something like
> that?
> Here's why it matters to me: I'm using a CTE to reference data I'm
> bringing over from a linked oracle server. Imagine the CTE above re-
> written as follows:
> WITH OrdersByMonth (year, month, order_count)
> AS
> (
> SELECT a.year, a.month, a.order_count
> FROM OPENQUERY(MYLINKEDSERVER, '
> SELECT YEAR(order_date) AS year,
> MONTH(order_date) AS month,
> COUNT(order_id) AS order_count
> FROM orders
> GROUP BY YEAR(order_date), MONTH(order_date)
> ') a
> )
> Suppose this statement brings over thousands and thousands of rows,
> and takes an hour to run. If I reference this CTE multiple times in
> the final query, does it actually execute the openquery statement each
> time, or is it run just once, and then subsequent references to the
> CTE use the data it has presumable cached? Or in other words, how
> many times would I be hitting the linked server?
> Apologies for the example, I'd post the real thing here but it's huge,
> so much more processing/joining with other data going on it would be
> difficult to filter down to my question. But I'd really appreciate
> any insight you might have!
>
|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NuFT%23iIIHA.4480@.TK2MSFTNGP04.phx.gbl...
>.
> This goes back to the fact that SQL is, strictly speaking, not a
> programming language. You are not telling SQL how to solve a problem, you
> are describing the aanswer you want.
> Big difference there.
>
And what does 'not a programming language' really mean?
http://beyondsql.blogspot.com/2007/10/sql-whats-really-with-with.html
|||I appreciate the response Geoff, it makes sense. The procedure in
question originally did all of its processing and data manipulation
through temp tables and derrived tables. I reworked it to only use
CTE's and it's been running for 6.5 hours now. Prior to switching
over to CTE's it took between 1 and 2 hours. I'm not sure though that
my problem has anything to do with the CTE approach or not... still
debugging everything. But your insight helps and I'm leaning towards
taking a blended approach... bring everything over into a #temptable
and then CTE my way to victory. That way I can make sure it's only
using the linked server once.
Take care!
On Nov 8, 11:48 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> I have been using CTEs extensively to find the first and last sale dates per
> customer given a transaction file. I find that SQL treats a CTE much like a
> view. That is, it is simply a convienent way to express what you want and
> not an absolute set of instructions. This goes back to the fact that SQL
> is, strictly speaking, not a programming language. You are not telling SQL
> how to solve a problem, you are describing the aanswer you want. Big
> difference there.
> As for your specific question, I have been using aparticular CTE that
> references a 13M row table across a linked server multiple times in a single
> query. The additional references do seem to increase the duration somewhat,
> but in a less than linear fashion. There is some caching and data reuse
> when the query executes, which is what you would expect from the SQL
> optimizer.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "Arthur Dent" <dwt12...@.gmail.com> wrote in message
> news:1194542752.426205.49980@.t8g2000prg.googlegrou ps.com...
>
>
>
>
>
>
>
> - Show quoted text -

CTE behind the scenes

When using CTE's in SQL 2005, what's going on behind the scenes? More
specifically, suppose you create a CTE, and then in the following
query that uses the CTE it's referenced multiple times. Is the query
nested within the CTE executed multiple times (for each instance it's
used in the final query)?
Here's a mock-up, not a great example of why you'd need to reference
the CTE more than once in your final query, but that's not the point.
Let us assume we have a CTE that's referenced more than once in the
query that consumes it:
WITH OrdersByMonth (year, month, order_count)
AS
(
SELECT
YEAR(order_date) AS year,
MONTH(order_date) AS month,
COUNT(order_id) AS order_count
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
)
SELECT a.year, a.month, a.order_count / b.annual_order_count AS
percent_of_year
FROM OrdersByMonth a
INNER JOIN (
SELECT year, SUM(order_count) as annual_order_count
FROM OrdersByMonth
) b
ON a.year = b.year
I realize that the derived table "b" could in fact be another CTE, but
that's not the point. I just want to show something where a CTE is
created (OrdersByMonth), and then used more than once in the following
query.
My question is this: the final query references OrdersByMonth twice.
Does that mean the statement within that CTE is actually executed
twice, or is it only run once and held in memory or something like
that?
Here's why it matters to me: I'm using a CTE to reference data I'm
bringing over from a linked oracle server. Imagine the CTE above re-
written as follows:
WITH OrdersByMonth (year, month, order_count)
AS
(
SELECT a.year, a.month, a.order_count
FROM OPENQUERY(MYLINKEDSERVER, '
SELECT YEAR(order_date) AS year,
MONTH(order_date) AS month,
COUNT(order_id) AS order_count
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
') a
)
Suppose this statement brings over thousands and thousands of rows,
and takes an hour to run. If I reference this CTE multiple times in
the final query, does it actually execute the openquery statement each
time, or is it run just once, and then subsequent references to the
CTE use the data it has presumable cached? Or in other words, how
many times would I be hitting the linked server?
Apologies for the example, I'd post the real thing here but it's huge,
so much more processing/joining with other data going on it would be
difficult to filter down to my question. But I'd really appreciate
any insight you might have!I have been using CTEs extensively to find the first and last sale dates per
customer given a transaction file. I find that SQL treats a CTE much like a
view. That is, it is simply a convienent way to express what you want and
not an absolute set of instructions. This goes back to the fact that SQL
is, strictly speaking, not a programming language. You are not telling SQL
how to solve a problem, you are describing the aanswer you want. Big
difference there.
As for your specific question, I have been using aparticular CTE that
references a 13M row table across a linked server multiple times in a single
query. The additional references do seem to increase the duration somewhat,
but in a less than linear fashion. There is some caching and data reuse
when the query executes, which is what you would expect from the SQL
optimizer.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Arthur Dent" <dwt12777@.gmail.com> wrote in message
news:1194542752.426205.49980@.t8g2000prg.googlegroups.com...
> When using CTE's in SQL 2005, what's going on behind the scenes? More
> specifically, suppose you create a CTE, and then in the following
> query that uses the CTE it's referenced multiple times. Is the query
> nested within the CTE executed multiple times (for each instance it's
> used in the final query)?
> Here's a mock-up, not a great example of why you'd need to reference
> the CTE more than once in your final query, but that's not the point.
> Let us assume we have a CTE that's referenced more than once in the
> query that consumes it:
> WITH OrdersByMonth (year, month, order_count)
> AS
> (
> SELECT
> YEAR(order_date) AS year,
> MONTH(order_date) AS month,
> COUNT(order_id) AS order_count
> FROM orders
> GROUP BY YEAR(order_date), MONTH(order_date)
> )
> SELECT a.year, a.month, a.order_count / b.annual_order_count AS
> percent_of_year
> FROM OrdersByMonth a
> INNER JOIN (
> SELECT year, SUM(order_count) as annual_order_count
> FROM OrdersByMonth
> ) b
> ON a.year = b.year
> I realize that the derived table "b" could in fact be another CTE, but
> that's not the point. I just want to show something where a CTE is
> created (OrdersByMonth), and then used more than once in the following
> query.
> My question is this: the final query references OrdersByMonth twice.
> Does that mean the statement within that CTE is actually executed
> twice, or is it only run once and held in memory or something like
> that?
> Here's why it matters to me: I'm using a CTE to reference data I'm
> bringing over from a linked oracle server. Imagine the CTE above re-
> written as follows:
> WITH OrdersByMonth (year, month, order_count)
> AS
> (
> SELECT a.year, a.month, a.order_count
> FROM OPENQUERY(MYLINKEDSERVER, '
> SELECT YEAR(order_date) AS year,
> MONTH(order_date) AS month,
> COUNT(order_id) AS order_count
> FROM orders
> GROUP BY YEAR(order_date), MONTH(order_date)
> ') a
> )
> Suppose this statement brings over thousands and thousands of rows,
> and takes an hour to run. If I reference this CTE multiple times in
> the final query, does it actually execute the openquery statement each
> time, or is it run just once, and then subsequent references to the
> CTE use the data it has presumable cached? Or in other words, how
> many times would I be hitting the linked server?
> Apologies for the example, I'd post the real thing here but it's huge,
> so much more processing/joining with other data going on it would be
> difficult to filter down to my question. But I'd really appreciate
> any insight you might have!
>|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NuFT%23iIIHA.4480@.TK2MSFTNGP04.phx.gbl...
>.
> This goes back to the fact that SQL is, strictly speaking, not a
> programming language. You are not telling SQL how to solve a problem, you
> are describing the aanswer you want.
> Big difference there.
>
And what does 'not a programming language' really mean? :)
http://beyondsql.blogspot.com/2007/10/sql-whats-really-with-with.html|||I appreciate the response Geoff, it makes sense. The procedure in
question originally did all of its processing and data manipulation
through temp tables and derrived tables. I reworked it to only use
CTE's and it's been running for 6.5 hours now. Prior to switching
over to CTE's it took between 1 and 2 hours. I'm not sure though that
my problem has anything to do with the CTE approach or not... still
debugging everything. But your insight helps and I'm leaning towards
taking a blended approach... bring everything over into a #temptable
and then CTE my way to victory. That way I can make sure it's only
using the linked server once.
Take care!
On Nov 8, 11:48 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> I have been using CTEs extensively to find the first and last sale dates per
> customer given a transaction file. I find that SQL treats a CTE much like a
> view. That is, it is simply a convienent way to express what you want and
> not an absolute set of instructions. This goes back to the fact that SQL
> is, strictly speaking, not a programming language. You are not telling SQL
> how to solve a problem, you are describing the aanswer you want. Big
> difference there.
> As for your specific question, I have been using aparticular CTE that
> references a 13M row table across a linked server multiple times in a single
> query. The additional references do seem to increase the duration somewhat,
> but in a less than linear fashion. There is some caching and data reuse
> when the query executes, which is what you would expect from the SQL
> optimizer.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "Arthur Dent" <dwt12...@.gmail.com> wrote in message
> news:1194542752.426205.49980@.t8g2000prg.googlegroups.com...
>
> > When using CTE's in SQL 2005, what's going on behind the scenes? More
> > specifically, suppose you create a CTE, and then in the following
> > query that uses the CTE it's referenced multiple times. Is the query
> > nested within the CTE executed multiple times (for each instance it's
> > used in the final query)?
> > Here's a mock-up, not a great example of why you'd need to reference
> > the CTE more than once in your final query, but that's not the point.
> > Let us assume we have a CTE that's referenced more than once in the
> > query that consumes it:
> > WITH OrdersByMonth (year, month, order_count)
> > AS
> > (
> > SELECT
> > YEAR(order_date) AS year,
> > MONTH(order_date) AS month,
> > COUNT(order_id) AS order_count
> > FROM orders
> > GROUP BY YEAR(order_date), MONTH(order_date)
> > )
> > SELECT a.year, a.month, a.order_count / b.annual_order_count AS
> > percent_of_year
> > FROM OrdersByMonth a
> > INNER JOIN (
> > SELECT year, SUM(order_count) as annual_order_count
> > FROM OrdersByMonth
> > ) b
> > ON a.year = b.year
> > I realize that the derived table "b" could in fact be another CTE, but
> > that's not the point. I just want to show something where a CTE is
> > created (OrdersByMonth), and then used more than once in the following
> > query.
> > My question is this: the final query references OrdersByMonth twice.
> > Does that mean the statement within that CTE is actually executed
> > twice, or is it only run once and held in memory or something like
> > that?
> > Here's why it matters to me: I'm using a CTE to reference data I'm
> > bringing over from a linked oracle server. Imagine the CTE above re-
> > written as follows:
> > WITH OrdersByMonth (year, month, order_count)
> > AS
> > (
> > SELECT a.year, a.month, a.order_count
> > FROM OPENQUERY(MYLINKEDSERVER, '
> > SELECT YEAR(order_date) AS year,
> > MONTH(order_date) AS month,
> > COUNT(order_id) AS order_count
> > FROM orders
> > GROUP BY YEAR(order_date), MONTH(order_date)
> > ') a
> > )
> > Suppose this statement brings over thousands and thousands of rows,
> > and takes an hour to run. If I reference this CTE multiple times in
> > the final query, does it actually execute the openquery statement each
> > time, or is it run just once, and then subsequent references to the
> > CTE use the data it has presumable cached? Or in other words, how
> > many times would I be hitting the linked server?
> > Apologies for the example, I'd post the real thing here but it's huge,
> > so much more processing/joining with other data going on it would be
> > difficult to filter down to my question. But I'd really appreciate
> > any insight you might have!- Hide quoted text -
> - Show quoted text -

CTE behind the scenes

When using CTE's in SQL 2005, what's going on behind the scenes? More
specifically, suppose you create a CTE, and then in the following
query that uses the CTE it's referenced multiple times. Is the query
nested within the CTE executed multiple times (for each instance it's
used in the final query)?
Here's a mock-up, not a great example of why you'd need to reference
the CTE more than once in your final query, but that's not the point.
Let us assume we have a CTE that's referenced more than once in the
query that consumes it:
WITH OrdersByMonth (year, month, order_count)
AS
(
SELECT
YEAR(order_date) AS year,
MONTH(order_date) AS month,
COUNT(order_id) AS order_count
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
)
SELECT a.year, a.month, a.order_count / b.annual_order_count AS
percent_of_year
FROM OrdersByMonth a
INNER JOIN (
SELECT year, SUM(order_count) as annual_order_count
FROM OrdersByMonth
) b
ON a.year = b.year
I realize that the derived table "b" could in fact be another CTE, but
that's not the point. I just want to show something where a CTE is
created (OrdersByMonth), and then used more than once in the following
query.
My question is this: the final query references OrdersByMonth twice.
Does that mean the statement within that CTE is actually executed
twice, or is it only run once and held in memory or something like
that?
Here's why it matters to me: I'm using a CTE to reference data I'm
bringing over from a linked oracle server. Imagine the CTE above re-
written as follows:
WITH OrdersByMonth (year, month, order_count)
AS
(
SELECT a.year, a.month, a.order_count
FROM OPENQUERY(MYLINKEDSERVER, '
SELECT YEAR(order_date) AS year,
MONTH(order_date) AS month,
COUNT(order_id) AS order_count
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
') a
)
Suppose this statement brings over thousands and thousands of rows,
and takes an hour to run. If I reference this CTE multiple times in
the final query, does it actually execute the openquery statement each
time, or is it run just once, and then subsequent references to the
CTE use the data it has presumable cached? Or in other words, how
many times would I be hitting the linked server?
Apologies for the example, I'd post the real thing here but it's huge,
so much more processing/joining with other data going on it would be
difficult to filter down to my question. But I'd really appreciate
any insight you might have!I have been using CTEs extensively to find the first and last sale dates per
customer given a transaction file. I find that SQL treats a CTE much like a
view. That is, it is simply a convienent way to express what you want and
not an absolute set of instructions. This goes back to the fact that SQL
is, strictly speaking, not a programming language. You are not telling SQL
how to solve a problem, you are describing the aanswer you want. Big
difference there.
As for your specific question, I have been using aparticular CTE that
references a 13M row table across a linked server multiple times in a single
query. The additional references do seem to increase the duration somewhat,
but in a less than linear fashion. There is some caching and data reuse
when the query executes, which is what you would expect from the SQL
optimizer.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Arthur Dent" <dwt12777@.gmail.com> wrote in message
news:1194542752.426205.49980@.t8g2000prg.googlegroups.com...
> When using CTE's in SQL 2005, what's going on behind the scenes? More
> specifically, suppose you create a CTE, and then in the following
> query that uses the CTE it's referenced multiple times. Is the query
> nested within the CTE executed multiple times (for each instance it's
> used in the final query)?
> Here's a mock-up, not a great example of why you'd need to reference
> the CTE more than once in your final query, but that's not the point.
> Let us assume we have a CTE that's referenced more than once in the
> query that consumes it:
> WITH OrdersByMonth (year, month, order_count)
> AS
> (
> SELECT
> YEAR(order_date) AS year,
> MONTH(order_date) AS month,
> COUNT(order_id) AS order_count
> FROM orders
> GROUP BY YEAR(order_date), MONTH(order_date)
> )
> SELECT a.year, a.month, a.order_count / b.annual_order_count AS
> percent_of_year
> FROM OrdersByMonth a
> INNER JOIN (
> SELECT year, SUM(order_count) as annual_order_count
> FROM OrdersByMonth
> ) b
> ON a.year = b.year
> I realize that the derived table "b" could in fact be another CTE, but
> that's not the point. I just want to show something where a CTE is
> created (OrdersByMonth), and then used more than once in the following
> query.
> My question is this: the final query references OrdersByMonth twice.
> Does that mean the statement within that CTE is actually executed
> twice, or is it only run once and held in memory or something like
> that?
> Here's why it matters to me: I'm using a CTE to reference data I'm
> bringing over from a linked oracle server. Imagine the CTE above re-
> written as follows:
> WITH OrdersByMonth (year, month, order_count)
> AS
> (
> SELECT a.year, a.month, a.order_count
> FROM OPENQUERY(MYLINKEDSERVER, '
> SELECT YEAR(order_date) AS year,
> MONTH(order_date) AS month,
> COUNT(order_id) AS order_count
> FROM orders
> GROUP BY YEAR(order_date), MONTH(order_date)
> ') a
> )
> Suppose this statement brings over thousands and thousands of rows,
> and takes an hour to run. If I reference this CTE multiple times in
> the final query, does it actually execute the openquery statement each
> time, or is it run just once, and then subsequent references to the
> CTE use the data it has presumable cached? Or in other words, how
> many times would I be hitting the linked server?
> Apologies for the example, I'd post the real thing here but it's huge,
> so much more processing/joining with other data going on it would be
> difficult to filter down to my question. But I'd really appreciate
> any insight you might have!
>|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NuFT%23iIIHA.4480@.TK2MSFTNGP04.phx.gbl...
>.
> This goes back to the fact that SQL is, strictly speaking, not a
> programming language. You are not telling SQL how to solve a problem, you
> are describing the aanswer you want.
> Big difference there.
>
And what does 'not a programming language' really mean?
http://beyondsql.blogspot.com/2007/...-with-with.html|||I appreciate the response Geoff, it makes sense. The procedure in
question originally did all of its processing and data manipulation
through temp tables and derrived tables. I reworked it to only use
CTE's and it's been running for 6.5 hours now. Prior to switching
over to CTE's it took between 1 and 2 hours. I'm not sure though that
my problem has anything to do with the CTE approach or not... still
debugging everything. But your insight helps and I'm leaning towards
taking a blended approach... bring everything over into a #temptable
and then CTE my way to victory. That way I can make sure it's only
using the linked server once.
Take care!
On Nov 8, 11:48 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> I have been using CTEs extensively to find the first and last sale dates p
er
> customer given a transaction file. I find that SQL treats a CTE much like
a
> view. That is, it is simply a convienent way to express what you want and
> not an absolute set of instructions. This goes back to the fact that SQL
> is, strictly speaking, not a programming language. You are not telling SQ
L
> how to solve a problem, you are describing the aanswer you want. Big
> difference there.
> As for your specific question, I have been using aparticular CTE that
> references a 13M row table across a linked server multiple times in a sing
le
> query. The additional references do seem to increase the duration somewha
t,
> but in a less than linear fashion. There is some caching and data reuse
> when the query executes, which is what you would expect from the SQL
> optimizer.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "Arthur Dent" <dwt12...@.gmail.com> wrote in message
> news:1194542752.426205.49980@.t8g2000prg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -

CTE behaviour in SQL 2005

I were trying to achive paging through using a CTE etc, but ran into the following weither thing happening. The CTE allows me to use avariable as the ORder By field, although the CTE do not care at all what is in there? Have any one seen this or maybe can explain this?

USE AdventureWorks;

GO

DECLARE @.SortExpression Varchar(50)

Set @.SortExpression = 'SalesPersonID ASC';

WITH Sales_CTE (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by @.SortExpression) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE;

WITH Sales_CTE1 (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by SalesPersonID ASC) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE1

I know your post is a few months; however, I figured I'd post a response incase you or anyone else is interested in a workaround.

I came across the same problem with CTE (common table expressions) and sorting. this code below may work. I don't have adventureWorks installed so my code may cause errors if used exactly. Debugging shouldn't be a problem:

Code Snippet

USE AdventureWorks;

GO

DECLARE @.SortExpression Varchar(50)

Set @.SortExpression = 'SalesPersonID ASC';

DECLARE @.sql1 varchar(4000)

SET @.sql1 = 'SELECT RowNumber, SalesPersonID, NumberOfOrders, MaxDate

FROM

(

SELECT

ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

) tbl1'

exec sp_executesql @.sql1

If you're using a DataSource in .NET and want to implement paging, sorting, AND filtering (for searching or limiting data) then please read on. sp_executesql also supports bind variables. SQL will will reuse the cached execution plan, this SQL won't recalculate the execution plan each time the page, sort, or filter condition(s) change.

This procedure below will do the same as query above, let you page the data (if you have many salesPersonIDs), and will will let you limit which SalesPersonID you're looking at... all with bind variables for faster query execution using sp_executesql.

Code Snippet

CREATE PROCEDURE [dbo].[usp_getSalesOrders]

@.SalesPerson int,

@.startRowIndex int,

@.maximumRows int,

@.SortExpression varchar(20)

AS

BEGIN

SET NOCOUNT ON;

--if a sortExpression isn't passed in, give it a default

IF len(isnull(@.SortExpression, '') = 0

set @.SortExpression = 'SalesPersonID ASC'

DECLARE @.sql nvarchar(4000)

SET @.sql = 'SELECT SalesPersonID, NumberOfOrders, MaxDate

FROM

(

SELECT

ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader with(nolock)

WHERE

(SalesPersonID = @.SalesPerson OR @.SalesPerson IS NULL)

GROUP BY SalesPersonID

) t

WHERE t.RowNumber BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1'

exec sp_executesql @.sql,

N'@.SalesPerson int,@.startRowIndex int,@.maximumRows int',

@.NumOfOrders,

@.startRowIndex,

@.maximumRows;

END

You can further optimize this, and I welcome comments as it'll help to improve my own code.

Hope this helps!

Nick