SQL Performance: Multiple Binds or Bind to One SQL Variable for Re-use?

Question

My question is related to determining how to get the best SQL performance out of my queries. Currently, I'm connecting to SQL Server using a Java Spring application. In my app, I do queries that join multiple tables and bind values to the query.

Here's an example of what one of these queries looks like. The key to note for my question is that all the bound variables are the same value.

SELECT * FROM MY_DATA data
LEFT OUTER JOIN join1 ON data.id1=join1.id and join1.id2=?
LEFT OUTER JOIN join2 ON data.id2=join2.id and join2.id2=?
LEFT OUTER JOIN join3 ON data.id3=join3.id and join3.id2=?

What I was thinking might be better would be to bind that value to a SQL parameter instead. So the revised query might look like this:

DECLARE @bind_value varchar
SET @bind_value=?    
SELECT * FROM MY_DATA data
LEFT OUTER JOIN join1 ON data.id1=join1.id and join1.id2=@bind_value
LEFT OUTER JOIN join2 ON data.id2=join2.id and join2.id2=@bind_value
LEFT OUTER JOIN join3 ON data.id3=join3.id and join3.id2=@bind_value

Does this help or hurt performance? Or is there a better way to write the query altogether? Thanks for taking the time to look at this!

Answer 1

For example, I have two equal queries:

--1

DECLARE @Date DATETIME
SELECT @Date = '20130101'

SELECT *
FROM dbo.Absence a
LEFT JOIN dbo.ScheduleDetail sd ON a.AbsenceCode = sd.AbsenceCode 
    AND sd.DateOut = @Date

--2

SELECT *
FROM dbo.Absence a
LEFT JOIN dbo.ScheduleDetail sd ON a.AbsenceCode = sd.AbsenceCode 
    AND sd.DateOut = '20130101'

Execution plan in SSMS:

Сomparison for two queries: