June 4, 2011 at 10:56 am
Most of the cost is around "Cov" table. I need to create a covering index because it is doing an clustered index scan. Can someone please suggest me a covering index based on this query? Schema is something like this:
HEID - int
COvStat- char
ID - int
RID - int
FPC - nvarchar
Please specify the order of columns also in the index is possible.
SELECT Row_number() OVER (PARTITION BY c.rid ORDER BY
HEID ASC,
CovStat
DESC
)
AS ident,
t.rid,
t.h_cdr as cdr ,
c.id as MCDI,
t.id as TempCoverageId,
c.FPC as C_FPC,
pp_a.PYC as C_PYC,
t.h_cdr as C_cdr
INTO #TempTable_2
FROM TMPCOV t
INNER JOIN COV c
ON t.rid= c.rid
LEFT OUTER JOIN PPYS pp_a
ON pp_a.FPC = c.FPC
LEFT OUTER JOIN PPYS pp_h
ON pp_h.FPC = t.h_FPC
LEFT JOIN TMPCOV tempcovjoin
ON c.id = tempcovjoin.MCDI
WHERE t.h_cdr = 2
AND tempcovjoin.MCDI IS NULL
AND ( ( t.h_SubC = c.SubC
AND pp_a.PYC IS NOT NULL
AND pp_a.PYC = pp_h.PYC )
OR ( c.FPC = t.h_FPC )
OR ( pp_h.ptype = pp_a.ptype
AND pp_a.ptype = 'SLF' ) )
AND NOT EXISTS (SELECT *
FROM TMPCOV
WHERE MCDI = c.id)
Thanks for your time
June 4, 2011 at 11:33 am
A covering index won't make that seekable. There's no predicate (other than join predicates) for the Cov table, it's probably faster to scan than to seek for each value in the other table.
Is that clustered index scan the biggest performance problem with that query?
Formatted for readability:
SELECT Row_number() OVER ( PARTITION BY c.rid ORDER BY HEID ASC, CovStat DESC ) AS ident ,
t.rid ,
t.h_cdr AS cdr ,
c.id AS MCDI ,
t.id AS TempCoverageId ,
c.FPC AS C_FPC ,
pp_a.PYC AS C_PYC ,
t.h_cdr AS C_cdr
INTO #TempTable_2
FROM TMPCOV t
INNER JOIN COV c ON t.rid = c.rid
LEFT OUTER JOIN PPYS pp_a ON pp_a.FPC = c.FPC
LEFT OUTER JOIN PPYS pp_h ON pp_h.FPC = t.h_FPC
LEFT OUTER JOIN TMPCOV tempcovjoin ON c.id = tempcovjoin.MCDI
WHERE t.h_cdr = 2
AND tempcovjoin.MCDI IS NULL
AND ( ( t.h_SubC = c.SubC
AND pp_a.PYC IS NOT NULL
AND pp_a.PYC = pp_h.PYC
)
OR ( c.FPC = t.h_FPC )
OR ( pp_h.ptype = pp_a.ptype
AND pp_a.ptype = 'SLF'
)
)
AND NOT EXISTS ( SELECT *
FROM TMPCOV
WHERE MCDI = c.id )
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
June 4, 2011 at 11:54 am
GilaMonster (6/4/2011)
A covering index won't make that seekable. There's no predicate (other than join predicates) for the Cov table, it's probably faster to scan than to seek for each value in the other table.Is that clustered index scan the biggest performance problem with that query?
Formatted for readability:
SELECT Row_number() OVER ( PARTITION BY c.rid ORDER BY HEID ASC, CovStat DESC ) AS ident ,t.rid ,
t.h_cdr AS cdr ,
c.id AS MCDI ,
t.id AS TempCoverageId ,
c.FPC AS C_FPC ,
pp_a.PYC AS C_PYC ,
t.h_cdr AS C_cdr
INTO #TempTable_2
FROM TMPCOV t
INNER JOIN COV c ON t.rid = c.rid
LEFT OUTER JOIN PPYS pp_a ON pp_a.FPC = c.FPC
LEFT OUTER JOIN PPYS pp_h ON pp_h.FPC = t.h_FPC
LEFT OUTER JOIN TMPCOV tempcovjoin ON c.id = tempcovjoin.MCDI
WHERE t.h_cdr = 2
AND tempcovjoin.MCDI IS NULL
AND ( ( t.h_SubC = c.SubC
AND pp_a.PYC IS NOT NULL
AND pp_a.PYC = pp_h.PYC
)
OR ( c.FPC = t.h_FPC )
OR ( pp_h.ptype = pp_a.ptype
AND pp_a.ptype = 'SLF'
)
)
AND NOT EXISTS ( SELECT *
FROM TMPCOV
WHERE MCDI = c.id )
Thanks. That's correct, i might not be able to achieve seek but i have seen with a similar query that estimated cost on clustered index scan was 440 and if used covering index..it came down to 50 (both did scan). In my case i think nc scan is better than clustered index scan..what do you say?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply