October 12, 2012 at 1:46 pm
the "MyPlan" is the plan for the whole query and the "NarrowIndexplan" is just the "select count(*) ..." plan with nrrower index being used and also the "largerindex" is the plan for the "select count(*) ..." plan with larger index being used
Pooyan
October 12, 2012 at 2:15 pm
Ow, nasty query...
Firstly, toss the nolocks unless you're happy with potentially inaccurate results. See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Multiple subqueries all hitting the same table multiple times. I'm sure we can make that better.
I've just done a bit, but there's a lot more that can be done. This should be equivalent, but can't be sure.
SELECT PointTypes.TypeID ,
PointTypes.[Name] ,
PointTypes.[Color] ,
PointTypes.HasTitle ,
PointTypes.DependsOn ,
[RANK] AS Ranking ,
CountPoints AS [OutOf] ,
ISNULL(( SELECT TOP 1
BackTotal
FROM PointHistory
WHERE PointHistory.MemberID = @MemberID
AND PointHistory.PointTypeID = PointTypes.TypeID
ORDER BY RefDate DESC
), 0) AS [Points] ,
ISNULL(( SELECT TOP 1
BackTotal
FROM PointCurrent
WHERE PointCurrent.PointTypeID = PointTypes.TypeID
ORDER BY BackTotal DESC
), 0) AS [Highest] ,
ISNULL([Level], 1) AS [Level] ,
CASE WHEN PointTypes.HasTitle = 1
THEN CASE WHEN PointTypes.DependsOn IS NULL THEN ( SELECT [Title]
FROM PointLevels
WHERE PointLevels.PointTypeID = PointTypes.TypeID
AND PointLevels.[Level] = ISNULL(PointCurrent.[Level], 1)
)
ELSE ( SELECT [Title]
FROM PointLevels
WHERE PointLevels.PointTypeID = PointTypes.TypeID
AND PointLevels.[Level] = ISNULL(( SELECT MIN(tblBothLevels.[Level])
FROM ( SELECT [Level]
FROM PointCurrent
WHERE PointCurrent.MemberID = @MemberID
AND PointCurrent.PointTypeID IN ( PointTypes.TypeID,
PointTypes.DependsOn )
) AS tblBothLevels
), 1)
)
END
ELSE ''
END AS [Title] ,
CASE WHEN PointTypes.HasTitle = 1
THEN CASE WHEN PointTypes.DependsOn IS NULL THEN ( SELECT [TitleFemale]
FROM PointLevels
WHERE PointLevels.PointTypeID = PointTypes.TypeID
AND PointLevels.[Level] = ISNULL(PointCurrent.[Level], 1)
)
ELSE ( SELECT [TitleFemale]
FROM PointLevels
WHERE PointLevels.PointTypeID = PointTypes.TypeID
AND PointLevels.[Level] = ISNULL(( SELECT MIN(tblBothLevels.[Level])
FROM ( SELECT [Level]
FROM PointCurrent
WHERE PointCurrent.MemberID = @MemberID
AND PointCurrent.PointTypeID IN ( PointTypes.TypeID,
PointTypes.DependsOn )
) AS tblBothLevels
), 1)
)
END
ELSE ''
END AS [TitleFemale]
FROM PointTypes
LEFT OUTER JOIN ( SELECT [Rank] ,
Level
FROM PointCurrent
WHERE MemberID = @MemberID
) PointCurrent ON PointCurrent.PointTypeID = PointTypes.TypeID
INNER JOIN ( SELECT COUNT(*) AS CountPoints
FROM PointCurrent
GROUP BY PointTypeID
) PointTotals ON PointTypes.PointTypeID = PointTotals.PointTypeID
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
October 12, 2012 at 2:36 pm
thanks Gila I'm a new DBA in this company and there's lots of bad written queries like this one . The query need to optimized obviously :crazy:
Pooyan
October 12, 2012 at 2:59 pm
Were the IO stats you posted from the count or from the entire query?
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
October 12, 2012 at 3:08 pm
just for the count(*) using set statistics io on
Pooyan
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply