April 13, 2009 at 12:51 am
hi
i am attaching sql script and its execution plan. please help me to tune it
one thing i cant avoid using DISTINCT word here
################################################################
select distinct
invitee_id = ea.invitee_id,
event_id = ea.event_id,
registration_option = eq.registration_option,
survey_type = eq.survey_type,
eq.cv_survey_type_id,
answer_1 = '',
answer_2 = '',
answer_3 = '',
answer_4 = '',
answer_5 = '',
answer_6 = '',
answer_7 = '',
answer_8 = '',
answer_9 = '',
answer_10 = '',
answer_11 = '',
answer_12 = '',
answer_13 = '',
answer_14 = '',
answer_15 = '',
answer_16 = '',
answer_17 = '',
answer_18 = '',
answer_19 = '',
answer_20 = '',
answer_21 = '',
answer_22 = '',
answer_23 = '',
answer_24 = '',
answer_25 = '',
answer_26 = '',
answer_27 = '',
answer_28 = '',
answer_29 = '',
answer_30 = '',
answer_31 = '',
answer_32 = '',
answer_33 = '',
answer_34 = '',
answer_35 = '',
answer_36 = '',
answer_37 = '',
answer_38 = '',
answer_39 = '',
answer_40 = '',
answer_41 = '',
answer_42 = '',
answer_43 = '',
answer_44 = '',
answer_45 = '',
answer_46 = '',
answer_47 = '',
answer_48 = '',
answer_49 = '',
answer_50 = '',
answer_51 = '',
answer_52 = '',
answer_53 = '',
answer_54 = '',
answer_55 = '',
answer_56 = '',
answer_57 = '',
answer_58 = '',
answer_59 = '',
answer_60 = '',
answer_61 = '',
answer_62 = '',
answer_63 = '',
answer_64 = '',
answer_65 = '',
answer_66 = '',
answer_67 = '',
answer_68 = '',
answer_69 = '',
answer_70 = '',
answer_71 = '',
answer_72 = '',
answer_73 = '',
answer_74 = '',
answer_75 = '',
answer_76 = '',
answer_77 = '',
answer_78 = '',
answer_79 = '',
answer_80 = '',
answer_81 = '',
answer_82 = '',
answer_83 = '',
answer_84 = '',
answer_85 = '',
answer_86 = '',
answer_87 = '',
answer_88 = '',
answer_89 = '',
answer_90 = '',
answer_91 = '',
answer_92 = '',
answer_93 = '',
answer_94 = '',
answer_95 = '',
answer_96 = '',
answer_97 = '',
answer_98 = '',
answer_99 = ''
FROM [dbo].[DMP_EVENT_EVENT_QUESTION] eq (NOLOCK)
INNER JOIN [dbo].[DMP_EVENT_EVENT_ANSWER] ea WITh (NOLOCK)
ON eq.event_id = ea.event_id
AND eq.qstn_id = ea.qstn_id
AND eq.registration_option = ea.registration_option
AND eq.survey_type = ea.survey_type
AND eq.cv_acct_id = 2217
ORDER BY 1, 3, 5
DECLARE @max_question_seqn smallint
DECLARE @loop smallint
DECLARE @sql nvarchar(4000)
--
--IF EXISTS (SELECT * FROM sys.indexes WHERE NAME = 'ix_DMP_EVENT_EVENT_QUESTION_answer_column_number')
--DROP INDEX DMP_EVENT_EVENT_QUESTION.ix_DMP_EVENT_EVENT_QUESTION_answer_column_number
--
--CREATE INDEX ix_DMP_EVENT_EVENT_QUESTION_answer_column_number ON [dbo].[DMP_EVENT_EVENT_QUESTION] (answer_column_number)
SELECT @max_question_seqn = max(answer_column_number)
FROM [dbo].[DMP_EVENT_EVENT_QUESTION]
--Loop for invitees
set @loop = 1
while (@loop <= @max_question_seqn and @loop <= 99) -- hard-coded limit
begin
set @sql = 'update ds
set answer_' + cast(@loop as nvarchar) + ' =
CASE WHEN EXISTS (SELECT TOP 1 1 FROM [dbo].[DMP_EVENT_EVENT_ANSWER_DETAIL] d WHERE dea.event_id = d.event_id
AND dea.qstn_id = d.qstn_id AND dea.invitee_id = d.invitee_id) THEN dea.answer_text
WHEN dea.answer_text IS NULL THEN '' ''
ELSE '' '' END
from [dbo].[DMP_EVENT_ANSWER_SUMMARY] ds (nolock)
inner join [dbo].[DMP_EVENT_EVENT_QUESTION] dq (nolock)
on dq.event_id = ds.event_id
and dq.registration_option = ds.registration_option
and dq.cv_survey_type_id = ds.cv_survey_type_id
and dq.survey_type = ds.survey_type
LEFT OUTER JOIN [dbo].[DMP_EVENT_EVENT_ANSWER] dea (nolock)
on dea.event_id = dq.event_id
and dea.invitee_id = ds.invitee_id
and dea.qstn_id = dq.qstn_id
where dq.answer_column_number = ' + cast(@loop as nvarchar)
print @sql
exec sp_executesql @sql
set @loop = @loop + 1
end
################################################################
in Upper regions like PRODUCTION it is taking heavy time as wellas resources where the records are in 100 of millions.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 13, 2009 at 6:58 am
DISTINCT is killing your peformance. You should try to find away around it. Plus, from the execution plan provided, it was five rows going in and five rows coming out. The DISTINCT operation didn't do anything apart from slow stuff down.
However, I'd suggest trying an index on cv_acct_id. I'm not sure if you should INCLUDE the other columns or not. Experiment with it to see. If so, it'd be qstn_id, registration_option, survey_type and cv_survey_type_id.
The use of NOLOCK can lead to bad data. Not merely data that's being updated currently, but multiple copies of rows or missing rows. Bad data. I would strongly suggest you avoid it's use.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply