Suggest Index

Question

Suggest Index

sushil_dwid

Ten Centuries

Points: 1241
More actions
February 23, 2016 at 1:38 am

#316246

Hi,
Could you please suggest the index on table(dbo.person) that can fast my below sql..
select Region,type,UID,Week
(select Region,type,UID,
CASE
WHEN epu.updatetime >= 'Feb 15 2016 12:00AM'
AND epu.updatetime < 'Feb 22 2016 12:00AM'
THEN 'wk1'
WHEN epu.EventTimestamp >= 'Feb 8 2016 12:00AM'
AND epu.EventTimestamp < 'Feb 15 2016 12:00AM'
THEN 'wk2'
ELSE NULL
END [Week]
FROM dbo.person epu WITH(NOLOCK)
Join employee B WITH(NOLOCK)
ON epu.personid=B.personid AND epu.employeeid=B.employeeid
WHERE epu.updatetime >= ''' +convert(varchar(10), @datFrom, 112) + '''
AND epu.updatetime < ''' + convert(varchar(10), @datTo, 112)+ ''') A
GROUP BY Region,type,UID,Week

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

There's no way you can get a sensible answer without posting the table definition, including current indexes. Please post the actual execution plan too.

-- Gianluca Sartori

sushil_dwid Ten Centuries Points: 1241 More actions · Answer 2

Thanks.. this hint was sufficient to get the answer..

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

sushil_dwid (2/23/2016)
Hi,
Could you please suggest the index on table(dbo.person) that can fast my below sql..
select Region,type,UID,Week
(select Region,type,UID,
CASE
WHEN epu.updatetime >= 'Feb 15 2016 12:00AM'
AND epu.updatetime < 'Feb 22 2016 12:00AM'
THEN 'wk1'
WHEN epu.EventTimestamp >= 'Feb 8 2016 12:00AM'
AND epu.EventTimestamp < 'Feb 15 2016 12:00AM'
THEN 'wk2'
ELSE NULL
END [Week]
FROM dbo.person epu WITH(NOLOCK)
Join employee B WITH(NOLOCK)
ON epu.personid=B.personid AND epu.employeeid=B.employeeid
WHERE epu.updatetime >= ''' +convert(varchar(10), @datFrom, 112) + '''
AND epu.updatetime < ''' + convert(varchar(10), @datTo, 112)+ ''') A
GROUP BY Region,type,UID,Week

Syntax errors in this query would raise an error - it wouldn't run.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 4

You do know that NOLOCK leads to extra or missing rows if you're experiencing scans, right?

"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