Thanks for your reply Jeff.

The datatype char(2) in both tables and client is indexed on both tables. Here's the SQL which runs instantly but if I change @client to 'XX' then it takes 20 seconds.

SELECT 'OBL' AS valtype, 'CY' AS Year, NULL AS TT, trans.account, SUM(trans.amount) AS Euro, GETDATE() AS date_created,

users_clients.user_id

FROM agr.dbo.trans AS trans INNER JOIN

ccas.gen_users_clients AS users_clients ON trans.client = users_clients.client

WHERE (trans.period BETWEEN users_clients.afs_cy_obperiod AND users_clients.afs_pto) AND (users_clients.active = 1)

GROUP BY trans.account, users_clients.user_id, trans.client

HAVING (trans.account BETWEEN '21100' AND '21270') AND (users_clients.user_id = @user_id) AND (trans.client = @client)