cte query taking forever - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

cte query taking forever

Author

Topic

shahab03
Starting Member

28 Posts

Posted - 2008-05-14 : 10:59:42

I have following common table expression query which is taking like 15 hours to run. would someone suggest what can I do to speed this thing up..

; with
a as (select proj_id, proj_start_dt,proj_end_dt, case when charindex('.', Proj_ID) > 0 then left(Proj_ID, len(Proj_ID) - charindex('.', reverse(Proj_ID))) end as Parent_Proj_ID from ods32.dbo.Proj a), --add Parent_Proj_ID column
b as (select proj_id, proj_start_dt,proj_end_dt,Parent_Proj_ID from a where PROJ_START_DT is not null and PROJ_END_DT is not null --get all valid rows
union all
select a.Proj_Id, b.PROJ_START_DT, b.PROJ_END_DT, a.Parent_Proj_ID from b inner join a on b.Proj_Id = a.Parent_Proj_ID where a.PROJ_START_DT is null or a.PROJ_END_DT is null) --get all invalid children of valid rows and give them the dates of their parents
update a set PROJ_START_DT = b.PROJ_START_DT, PROJ_END_DT = b.PROJ_END_DT
from WPData a left outer join b on a.Proj_ID = b.Proj_ID -- join up and update

thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-14 : 11:09:28

Shahab,

Start with the smalles subset first and work your way up. the purpose of CTE is to break things down. you are not breaking your stuff down modularly/logically. it speeds things for me doing it that way

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-14 : 11:13:03

15 HOURS!?!?!
1. What do you want the result to bring back?
2. What is the smalles subset of the results you want?
--add Parent_Proj_ID column
; WITH a as
(
select proj_id,
proj_start_dt,
proj_end_dt,
case when charindex('.', Proj_ID) > 0
then left(Proj_ID, len(Proj_ID) - charindex('.', reverse(Proj_ID)))
end as Parent_Proj_ID
from ods32.dbo.Proj a
),
--get all valid rows
b as
(
select proj_id,
proj_start_dt,
proj_end_dt,
Parent_Proj_ID
from a
where PROJ_START_DT is not null and PROJ_END_DT is not null
union all
select a.Proj_Id,
b.PROJ_START_DT,
b.PROJ_END_DT,
a.Parent_Proj_ID
from b inner join a on b.Proj_Id = a.Parent_Proj_ID
where a.PROJ_START_DT is null or a.PROJ_END_DT is null) --get all invalid children of valid rows and give them the dates of their parents
update a set PROJ_START_DT = b.PROJ_START_DT, PROJ_END_DT = b.PROJ_END_DT
from WPData a left outer join b on a.Proj_ID = b.Proj_ID -- join up and update

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-14 : 11:15:33

dude it could be a conflict between your alias for CTE and alias for the tables...you can't do that. make the cte alias and table alias uniqe

shahab03
Starting Member

28 Posts

Posted - 2008-05-14 : 11:28:34

well the thing is that this works with small data sets with no issues. but when it comes to a bigger data set it just chokes. ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 11:35:33

How many records are we talking about?

;with
a
as (
	SELECT	proj_id,
		proj_start_dt,
		proj_end_dt,
		LEFT(Proj_ID, NULLIF(LEN(Proj_ID) - LEN(PARSENAME(Proj_ID, 1)), 0) - 1) AS Parent_Proj_ID
	from	ods32.dbo.Proj),
b
as (
	select	proj_id,
		proj_start_dt,
		proj_end_dt,
		Parent_Proj_ID
	from	a
	where	PROJ_START_DT is not null
		and PROJ_END_DT is not null

	union all

	select		a.Proj_Id,
			b.PROJ_START_DT,
			b.PROJ_END_DT,
			a.Parent_Proj_ID
	from		b
	inner join	a on b.Proj_Id = a.Parent_Proj_ID
	where		a.PROJ_START_DT is null
			or a.PROJ_END_DT is null
)

update		x
set		x.PROJ_START_DT = b.PROJ_START_DT,
		x.PROJ_END_DT = b.PROJ_END_DT
from		WPData AS x
left join	b on x.Proj_ID = b.Proj_ID

E 12°55'05.25"
N 56°04'39.16"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-14 : 12:02:37

with my little experience with CTE I am thinking maybe the first select is the bottle neck - ods32.dbo.Proj must be a big table- maybe this will help if it brings a smaller subset? then you do the rest?

;with
cProjNotNull
as (
select proj_id,
proj_start_dt,
proj_end_dt,
Parent_Proj_ID
from ods32.dbo.Proj
where PROJ_START_DT is not null
and PROJ_END_DT is not NULL),
cProjNull
as (
select proj_id,
proj_start_dt,
proj_end_dt,
Parent_Proj_ID
from ods32.dbo.Proj
where PROJ_START_DT is null
or PROJ_END_DT is null
),

shahab03
Starting Member

28 Posts

Posted - 2008-05-14 : 12:08:41

well ods32.dbo.Proj has 330,000 rows whereas wpdata has 70,000 rows. i have given wpdata table a different alias as well. however not noticing any change in performance yet.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-14 : 14:39:22

why r u doing the following? the or what is that for? how could the project have a start null or end null? is that correct

where PROJ_START_DT is null
or PROJ_END_DT is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources