All pastes #956580 Raw Edit

Unnamed

public text v1 · immutable
#956580 ·published 2008-03-25 12:34 UTC
rendered paste body
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?