Table Segments
has an index by DEPARTMENT (VARCHAR(10)) and BDPID(VARCHAR(10)).
Execution time of the first query is 34 seconds
SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
FROM Customers c
LEFT JOIN Segments seg
ON seg.DEPARTMENT = 'DEP345'
AND seg.BDPID = c.BDPID
When I moved the parameter for DEPARTMENT to a variable, execution time became 1 second. Execution plan #2 (fast)
DECLARE @dd VARCHAR(10)
SET @dd = 'DEP345'
SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
FROM Customers c
LEFT JOIN Segments seg
ON seg.DEPARTMENT = @dd
AND seg.BDPID = c.BDPID
But I have to use dynamic sql. And when I moved query to sp_execitesql the execution time again became 34 seconds. Execution plan #3 (slow)
EXECUTE sp_executesql
'SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
FROM Customers c
LEFT JOIN Segments seg
ON seg.DEPARTMENT = @dd
AND seg.BDPID = c.BDPID',
'@dd VARCHAR(10)',
@dd = 'DEP345'
How can I get performance of the second query using dynamic sql?