CREATE PROCEDURE [dbo].[spGetCostCentre]
(
@StaffID char(10),
@PayDate datetime
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT DISTINCT TOP 100 PERCENT
e.EMPNUMBER AS STAFFID,
s.SHOPDESC AS COSTCENTRENAME
FROM dbo.EMP AS e
LEFT OUTER JOIN dbo.EHIST AS eh
ON e.EMPNUMBER = eh.EMPNUMBER AND CONVERT(datetime, eh.EHISTSTART) =
(SELECT MAX(CONVERT(datetime, EHISTSTART)) AS Expr1
FROM dbo.EHIST AS eh2
WHERE (DATEDIFF(dd, @PayDate, CONVERT(datetime, EHISTSTART)) <= 0) AND (eh.EMPNUMBER = EMPNUMBER))
LEFT OUTER JOIN dbo.PAYMISC AS pm
ON eh.EHISTDBADD = pm.EHISTDBADD AND CONVERT(datetime, pm.PAYMISCSTART) =
(SELECT MAX(CONVERT(datetime, PAYMISCSTART)) AS Expr1
FROM dbo.PAYMISC AS pm2
WHERE (DATEDIFF(dd, @PayDate, CONVERT(datetime, PAYMISCSTART)) <= 0) AND (pm.EHISTDBADD = EHISTDBADD))
LEFT OUTER JOIN dbo.SHOP AS s
ON RTRIM(pm.PAYMISCCC1CODE) + 'ROI' = s.SHOPCODE
WHERE (s.SHOPTLCC = 'IRL')
AND e.EMPNUMBER = @StaffID
ORDER BY STAFFID
END
....
Which part of this join would cause the biggest performance hit? The CONVERT() statement, or the lack of an index on EMPNUMBER? Something else?