[SOLVED] SQL Where clause parameter faster than string literal. How do I fix?

SQL Where clause parameter faster than string literal. How do I fix?

Created following script to load user accounts. Used a parameter string for username in initial testing in management studio. Odd thing is after I changed from parameter string to string literal the query slowed down by 20 seconds. Isn't it the other way usually for parameter sniffing? I've tried DBCC FREEPROCCACHE and creating a stored procedure with setting some local vars, but that didn't speed up the query. Any suggestions?

DECLARE @accntRep VARCHAR(50)
SET @accntRep = 'someUserName'

SELECT  accntRep,transBalance FROM companyView AS cv
OUTER APPLY 
    (SELECT SUM(CASE WHEN pastdue > 0 THEN balance ELSE 0 END) AS pastDueBalance,
      SUM(balance) AS transBalance FROM pastDueView WHERE compID = cv.compID
    ) AS balance
WHERE 
--  accntRep = @accntRep   
accntRep = 'someUserName'
GROUP BY accntRep,transBalance

Solution

The difference between a string literal and a local variable is usually an issue of stats where using a local variable uses an average distribution. So if the average is better than the actual stats (assuming an index does exist on the [accntRep] field) then it sounds like your statistics are out of date. At the very least update stats. Or you can do a REBUILD of the index which, as long as the index is not partitioned, will include a stats update WITH FULLSCAN (prior to SQL Server 2012, partitioned indexes also received the full stats update).