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%'
[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?
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.
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%')
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.
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.
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.