September 14, 2012 at 4:55 am
Hi Professional,
CREATE TABLE [dbo].[tmp_load](
[dq_src_id] [int] NOT NULL,
[dq_org_dist_id] [nvarchar](140) NULL,
[dq_rec_seq_nbr] [nvarchar](200) NULL,
[dq_status_cd] [nvarchar](220) NULL,
[dq_prev_status_cd] [nvarchar](230) NULL,
[dq_curr_status] [nvarchar](220) NULL,
[dq_rec_eff_dt] [nvarchar](200) NULL,
[dq_rec_upd_user] [nvarchar](280) NULL
)
go
insert into [tmp_load] values (44172,'1010','4','Active','-None','Active','2014-05-05','ABC')
go
insert into [tmp_load] values (41195,'1020','3','Active','-None','Active','2014-05-05','ABC')
go
insert into [tmp_load] values (44172,'1010','2','Disactive','-None','Disactive','2014-05-05','ABC')
go
insert into [tmp_load] values (49169,'1020','1','Active','-None','Active','2012-08-09','ABC')
go
I am facing some problem from my below sql.Its taking very long time for fetching records.
So request you to please provide me the solution how to get fast result from my below SQL.
WITH q AS (
SELECT
dq_src_id
,dq_org_dist_id
,dq_rec_seq_nbr
,dq_status_cd
,dq_prev_status_cd
,dq_curr_status
,dq_rec_eff_dt
,dq_rec_upd_user
FROM tmp_load WITH (NOLOCK)
) ,
qq AS
(
SELECT main.dq_src_id,
main.dq_org_dist_id,
main.dq_rec_seq_nbr,
main.dq_status_cd,
q_prev.dq_status_cd AS dq_prev_status_cd,
CAST(CASE WHEN q_next.dq_rec_eff_dt IS NULL THEN 1 ELSE 0 END AS BIT) AS dq_rec_latest_ind,
CAST(CASE WHEN main.dq_status_cd = 'Qualified' THEN 1 ELSE 0 END AS BIT) AS dq_rec_qualified_ind,
CAST(CASE WHEN q_prev.dq_status_cd = 'Qualified' THEN 1 ELSE 0 END AS INT) AS dq_rec_start_cycle_ind,
main.dq_rec_eff_dt,
COALESCE(q_next.dq_rec_eff_dt,'2079-06-06') AS dq_rec_end_dt,
CASE WHEN DATEDIFF(DAY,main.dq_rec_eff_dt,q_next.dq_rec_eff_dt)< 0 then 0 else DATEDIFF(DAY,main.dq_rec_eff_dt,q_next.dq_rec_eff_dt) END AS dq_duration,
main.dq_rec_upd_user,
ROW_NUMBER() OVER (PARTITION BY main.dq_org_dist_id ORDER BY main.dq_org_dist_id ,CAST(main.dq_rec_seq_nbr AS INT)) rownum
FROM q main
LEFT JOIN q q_next
ON main.dq_org_dist_id = q_next.dq_org_dist_id
AND main.dq_rec_seq_nbr = q_next.dq_rec_seq_nbr - 1
LEFT JOIN q q_prev
ON main.dq_org_dist_id = q_prev.dq_org_dist_id
AND main.dq_rec_seq_nbr = q_prev.dq_rec_seq_nbr + 1
),
test
AS
(SELECT * , dq_rec_start_cycle_ind AS running_sum FROM qq WHERE rownum = 1
UNION ALL
SELECT t.*, t.dq_rec_start_cycle_ind + t1.running_sum FROM qq t
INNER JOIN test t1
ON t.rownum = t1.rownum+1 AND t.dq_src_id = t1.dq_src_id and t.dq_org_dist_id=t1.dq_org_dist_id WHERE t.rownum > 1
)
SELECT dq_src_id,
dq_org_dist_id ,
dq_rec_seq_nbr,
dq_status_cd,
dq_prev_status_cd,
running_sum+1 AS dq_cycle_cnt,
dq_rec_latest_ind,
dq_rec_qualified_ind,
dq_rec_start_cycle_ind,
dq_rec_eff_dt,
dq_rec_end_dt,
dq_duration,
dq_rec_upd_user
FROM test
ORDER BY dq_org_dist_id,CAST(dq_rec_seq_nbr AS INT) DESC
OPTION (maxrecursion 0)
September 14, 2012 at 5:00 am
Please can you follow the 4th link in my signature on posting performance problems?
Would require the execution plan and what indexes are on the base tables as you have not provided them in the post.
September 14, 2012 at 5:04 am
Do you have any indexes on [tmp_load]?
It should speed up the joins if you created appropriate indexes
e.g. on dq_org_dist_id & dq_rec_seq_nbr.
September 14, 2012 at 5:12 am
please use #temp table in plase of CTE.
September 14, 2012 at 5:19 am
But How can I use #tmp table?
Indexes are there on my tmp_load table.
So please suggest me the best solution....In my above sql it giving me 50125 rows
September 14, 2012 at 5:27 am
post execution plan and index details
September 14, 2012 at 5:29 am
subbareddy542 (9/14/2012)
please use #temp table in plase of CTE.
Please explain why a #temp table should be used.
I have been using CTE expressions and used #temp tables to 'force' the optimiser to work in a specific order.
But what is good practise, for both clarity and performance?
Does building a #temp table take extra resources, or is this similar to a equivalent CTE expression?
Thanks for your time and attention,
Ben Brugman
September 14, 2012 at 7:59 am
kiran.rajenimbalkar (9/14/2012)
But How can I use #tmp table?Indexes are there on my tmp_load table.
So please suggest me the best solution....In my above sql it giving me 50125 rows
You haven't provided the actual execution plan, as requested twice. Also, if there are indexes on tmp_load, why didn't you provide those along with the CREATE TABLE statement for the table, it would help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply