Edit Question - KnowledgeHub

Title Body

I'm trying to optimize a complex SQL query and getting wildly different results when I make seemingly inconsequential changes. For example, this takes 336 ms to run: Declare @InstanceID int set @InstanceID=1; With myResults as ( Select Row = Row_Number() Over (Order by sv.LastFirst), ContactID From DirectoryContactsByContact(1) sv Join ContainsTable(_s_Contacts, SearchText, 'john') fulltext on (fulltext.[Key]=ContactID) Where IsNull(sv.InstanceID,1) = @InstanceID and len(sv.LastFirst)>1 ) Select * From myResults Where Row between 1 and 20; If I replace the @InstanceID with a hard-coded number, it takes over 13 seconds (13890 ms) to run: Declare @InstanceID int set @InstanceID=1; With myResults as ( Select Row = Row_Number() Over (Order by sv.LastFirst), ContactID From DirectoryContactsByContact(1) sv Join ContainsTable(_s_Contacts, SearchText, 'john') fulltext on (fulltext.[Key]=ContactID) Where IsNull(sv.InstanceID,1) = 1 and len(sv.LastFirst)>1 ) Select * From myResults Where Row between 1 and 20; In other cases I get the exact opposite effect: For example, using a variable @s instead of the literal 'john' makes the query run more slowly by an order of magnitude. Can someone help me tie this together? When does a variable make things faster, and when does it make things slower?

Tags (comma-separated) Cancel