January 7, 2010 at 2:48 am
Hi
update t
set
t.respondent_stub = r1.respondent_stub,
t.respondent_status_id = r1.respondent_status_id,
t.multi_response_flag = @response_flag
from #tmp_answers t
join
(
Select re.acct_id,re.cont_Stub, re.respondent_stub,
re.respondent_status_id
,RANK() OVER (PARTITION BY re.cont_stub ORDER BY re.created_date DESC) AS 'RANK'
From dbo.[RESPONDENT] re with (nolock)
join #tmp_answers t
on re.acct_id = @acct_id and re.cont_stub = t.cont_stub
Where re.survey_stub = @survey_stub
)r1
on r1.acct_id = @acct_id and t.cont_stub = r1.cont_stub
Where r1.acct_id = @acct_id and r1.RANK = 1
---------------tables schema------------------------------------------
create table [RESPONDENT]
(
acct_idut_synthetic_key
respondent_stubut_stub
survey_tl_stubut_stub
survey_stubut_stub
cont_stubut_stub
respondent_status_idsmallint
response_codeut_friendly_code
response_numberbigint
response_scorenumeric
respondent_noteut_long_description
invited_by_idsmallint
invited_by_cont_stubut_stub
responded_through_idut_synthetic_key
created_byut_medium_description
created_datedatetime
last_modified_byut_medium_description
last_modified_datedatetime
)
-indexes
acct_id, cont_stub
acct_id, survey_stub, respondent_stub
respondent_stub
acct_id, respondent_stub
acct_id, survey_stub, cont_stub
CREATE TABLE #tmp_answers
(
RowId INT not NULL IDENTITY(1,1),
first_name nvarchar(30),
last_name nvarchar(50),
email_addr nvarchar(80),
qstn_stub ut_stub,
answer_detail nvarchar(max),
respondent_stub uniqueidentifier,
cont_stub uniqueidentifier,
dup_match_key nvarchar(300),
respondent_status_id smallint,
multi_response_flag smallint,
have_new_respondent smallint,
new_respondent_stub uniqueidentifier,
answer_score numeric(9,2),
response_score numeric(9,2),
have_new_audit smallint,
unique_respondent_record int,
no_response_audit_record_flag int
)
CREATE nonclustered index
idx_#tmp_answer_respondent_stub_cont_stub ON #tmp_answers (respondent_stub ,cont_stub )
CREATE nonclustered index idx_#tmp_answer_cont_stub ON #tmp_answers (cont_stub )
--------------------------------------------------------------------------------------------
Above query is taking heavy resources like time , cpu etc
can any body help me to tune it.?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 7, 2010 at 2:54 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2010 at 3:04 am
Gail,
i posted as u suggested as u have asked to do in that link
i have pasted tables definition along with index defintion
but for more detail : respondent's indexes
ix_RESPONDENT_acct_id_cont_stubnonclustered located on PRIMARYINDEXacct_id, cont_stub
ix_RESPONDENT_acct_id_survey_stub_respondent_stub_plus_respondent_status_id_response_numbernonclustered located on SECONDARYINDEXacct_id, survey_stub, respondent_stub
ix_RESPONDENT_acct_id_survey_stub_survey_tl_stubnonclustered located on PRIMARYINDEXacct_id, survey_stub, survey_tl_stub
ix_RESPONDENT_respondent_stubnonclustered located on SECONDARYINDEXrespondent_stub
PK_RESPONDENT_acct_id_respondent_stubnonclustered, unique, primary key located on PRIMARYINDEXacct_id, respondent_stub
RESPONDENT_acct_id_survey_stub_plus_cont_stub_created_datenonclustered located on SECONDARYINDEXacct_id, survey_stub, cont_stub
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 7, 2010 at 6:44 am
I don't see an execution plan anywhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply