SQLTeam.com | Weblogs | Forums

Temp tables to CTEs

sql2008r2

#1

I have three temp tables like this.
How I can convert them into CTEs?

select st.*, CL.Clientname, StSt.studyStatusName
INTO #studies
from Studies st
INNER JOIN StudiesClients as STCL 
	ON STCL.StudySqlId = St.studySqlId AND STCL.studyIncId = St.StudyIncId AND St.isDeleted = 0x0 AND STCL.isDeleted = 0x0 AND STCL.sponsorRanking = 1
INNER JOIN Clients AS CL
	ON CL.clientSqlId = STCL.clientSqlId AND CL.clientIncId = STCL.clientIncId AND CL.isDeleted = 0x0
LEFT JOIN StudiesStatuses AS StSt
	ON StSt.studyStatusSqlId = St.studyStatusSqlId AND StSt.studyStatusIncId = St.studyStatusIncId AND StSt.isDeleted = 0x0 
WHERE  CHARINDEX(CAST(LEFT(DATEPART(yyyy, GETDATE()), 2)  + SUBSTRING(St.studyCode, 2, 2) AS NVARCHAR(30)), @Year) > 0
	AND (CHARINDEX(CL.clientCode, @SponsorCode) > 0 OR @sponsorCode IS NULL)
	AND  CHARINDEX(STST.studyStatusCode, @StudiesStatuses) > 0 


SELECT DISTINCT AC.activityCode ,AC.activityStartDate
INTO #Temp1
FROM Activities AC
inner join #studies st on ac.studyincid=st.studyincid
	and AC.studysqlid=AC.studysqlid
	and AC.isdeleted=0x0
INNER JOIN ActivitiesAdditionalFieldsValues AAFV ON AC.activityIncId = AAFV.activityIncId
	AND AC.activitySqlId = AAFV.activitySqlId
	AND AC.isDeleted = 0x0 AND AAFV.isDeleted = 0x0 AND ac.activityCode LIKE 's%'
INNER JOIN CboValues CBO ON CBO.cboValueIncId = AAFV.cboRecordIncId
	AND CBO.cboValueSqlId = AAFV.cboRecordSqlId
	AND CBO.isDeleted = 0x0
	AND cbo.cboValueName LIKE '%First%'
	AND Cbo.cboValueName LIKE '%GLP%'
  
 
SELECT DISTINCT AC.activityCode ,AC.activityEndDate
INTO #Temp2
FROM Activities AC
inner join #studies st on ac.studyincid=st.studyincid
	and AC.studysqlid=AC.studysqlid
	and AC.isdeleted=0x0
INNER JOIN ActivitiesAdditionalFieldsValues AAFV ON AC.activityIncId = AAFV.activityIncId
	AND AC.activitySqlId = AAFV.activitySqlId
	AND AC.isDeleted = 0x0 AND AAFV.isDeleted = 0x0 AND ac.activityCode LIKE 's%'
INNER JOIN CboValues CBO ON CBO.cboValueIncId = AAFV.cboRecordIncId
	AND CBO.cboValueSqlId = AAFV.cboRecordSqlId
	AND CBO.isDeleted = 0x0
	AND cbo.cboValueName LIKE '%Last%'
	AND Cbo.cboValueName LIKE '%GLP%'

#2

[quote="GeoJul, post:1, topic:9383"]
select st.*
[/quote] will not work since you need to explicitly specify columns names in order to use cte. can you explain why you would like to use cte rather than temp tables?


#3

CTE's are not replacements for temp tables...with that said, in some cases moving a temp table to a CTE can improve performance by allowing SQL Server to push operations related to the tables/filtering in the CTE earlier in the process resulting in fewer IO or CPU cycles.

However - that is not a guarantee and in some cases it will result in worse performance because the complexity of the process goes up. May also end up not being able to optimize better than building temp tables.

@yosiasz - select st.* will work because all columns will be returned with a name as long as the other columns being returned don't have the same name. It would not work if you explicitly define the columns in the CTE declaration - but you don't have to do that.

Example:

WITH cte (col1, col2, col3)
AS (SELECT t.* FROM table t)
SELECT * FROM cte;

The above will only work if we only have 3 columns in the table. If you have four columns it will break because you are returning more columns than specified.

WITH cte AS (SELECT t.* FROM table t)
SELECT * FROM cte;

This will work because we are not limiting the CTE to an explicitly defined list of columns.


#4

If your server has a lot of cores CTEs can allow better parallel processing. With temp tables it's sequential.


#5

I have changed my query like the one below. Hope it is correct. Please verify.

;WITH CTE_Studies AS
(select st.*, CL.Clientname, StSt.studyStatusName
from Studies st
INNER JOIN StudiesClients as STCL 
	ON STCL.StudySqlId = St.studySqlId AND STCL.studyIncId = St.StudyIncId AND St.isDeleted = 0x0 AND STCL.isDeleted = 0x0 AND STCL.sponsorRanking = 1
INNER JOIN Clients AS CL
	ON CL.clientSqlId = STCL.clientSqlId AND CL.clientIncId = STCL.clientIncId AND CL.isDeleted = 0x0
LEFT JOIN StudiesStatuses AS StSt
	ON StSt.studyStatusSqlId = St.studyStatusSqlId AND StSt.studyStatusIncId = St.studyStatusIncId AND StSt.isDeleted = 0x0 
WHERE  CHARINDEX(CAST(LEFT(DATEPART(yyyy, GETDATE()), 2)  + SUBSTRING(St.studyCode, 2, 2) AS NVARCHAR(30)), @Year) > 0
	AND (CHARINDEX(CL.clientCode, @SponsorCode) > 0 OR @sponsorCode IS NULL)
	AND  CHARINDEX(STST.studyStatusCode, @StudiesStatuses) > 0 )

,CTE_Temp1 AS
(SELECT DISTINCT AC.activityCode ,AC.activityStartDate
FROM Activities AC
inner join CTE_Studies st on ac.studyincid=st.studyincid
	and AC.studysqlid=AC.studysqlid
	and AC.isdeleted=0x0
INNER JOIN ActivitiesAdditionalFieldsValues AAFV ON AC.activityIncId = AAFV.activityIncId
	AND AC.activitySqlId = AAFV.activitySqlId
	AND AC.isDeleted = 0x0 AND AAFV.isDeleted = 0x0 AND ac.activityCode LIKE 's%'
INNER JOIN CboValues CBO ON CBO.cboValueIncId = AAFV.cboRecordIncId
	AND CBO.cboValueSqlId = AAFV.cboRecordSqlId
	AND CBO.isDeleted = 0x0
	AND cbo.cboValueName LIKE '%First%'
	AND Cbo.cboValueName LIKE '%GLP%')

, CTE_Temp2 AS
(SELECT DISTINCT AC.activityCode ,AC.activityEndDate
FROM Activities AC
inner join CTE_Studies st on ac.studyincid=st.studyincid
	and AC.studysqlid=AC.studysqlid
	and AC.isdeleted=0x0
INNER JOIN ActivitiesAdditionalFieldsValues AAFV ON AC.activityIncId = AAFV.activityIncId
	AND AC.activitySqlId = AAFV.activitySqlId
	AND AC.isDeleted = 0x0 AND AAFV.isDeleted = 0x0 AND ac.activityCode LIKE 's%'
INNER JOIN CboValues CBO ON CBO.cboValueIncId = AAFV.cboRecordIncId
	AND CBO.cboValueSqlId = AAFV.cboRecordSqlId
	AND CBO.isDeleted = 0x0
	AND cbo.cboValueName LIKE '%Last%'
	AND Cbo.cboValueName LIKE '%GLP%')

#6

No idea if that is correct...

A common table expression is not a replacement for a temp table. You have to test and evaluate which method works best for your situation.

What is the purpose of moving to cte's?


#7

Please verify my converted code. The purpose of moving to CTE is to test the performance improvement.
Studies table is having almost 10 lac records and activities is having 12 lac records.
Want to test the performance improvement if any after the conversion.


#8

To improve performance my first step would be to make the query SARGable.

WHERE  CHARINDEX(CAST(LEFT(DATEPART(yyyy, GETDATE()), 2)  + SUBSTRING(St.studyCode, 2, 2) AS NVARCHAR(30)), @Year) > 0
	AND (CHARINDEX(CL.clientCode, @SponsorCode) > 0 OR @sponsorCode IS NULL)
	AND  CHARINDEX(STST.studyStatusCode, @StudiesStatuses) > 0 )

and

	AND cbo.cboValueName LIKE '%First%'
	AND Cbo.cboValueName LIKE '%GLP%')

are HUGE performance bottlenecks because SQL cannot use indexes to resolve them and will SCAN every row to find the result. Thus the query will NOT scale well.

Altering the DDL to provide additional child tables and/or columns so that you don't need to have wildcard searches WITHIN columns will help a lot.


#9

All you have done is created some common table expressions - you are not using them. It is impossible to say whether or not that is correct - all I can say is that it looks like you created a CTE, but you are not using it so no idea.

Again...a common table expression IS NOT a replacement for a temp table. A common table expression - by itself - is not a method to improve performance. Switching from a temp table to a CTE is not guaranteed to improve performance.

If your goal is to improve performance - identify the worst offender in your code - and figure out how to do that faster. Then identify the next one...until you have address all performance issues or have something executing within the expected amount of time.