We need more info like Gail said to be able to help with performance.

But I would like to point out a common TSQL programming flaw I see:

IF ISNULL(@AFonly, 0) = 0

SELECT * FROM views(creted some view)

SELECT od.* FROM

tableA AS h

INNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2

AND h.couponend_date ts > GETDATE() AND h.coupon_ startdate = 5

That really executes like this:

IF ISNULL(@AFonly, 0) = 0

BEGIN

SELECT * FROM views(creted some view)

END

SELECT od.* FROM

tableA AS h

INNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2

AND h.couponend_date ts > GETDATE() AND h.coupon_ startdate = 5

Even if that is what you intended (it often isn't) ALWAYS EXPLICITLY USE BEGIN/END with IF/ELSE constructs:

IF ISNULL(@AFonly, 0) = 0

BEGIN

SELECT * FROM views(creted some view)

END

SELECT od.* FROM

tableA AS h

INNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2

AND h.couponend_date ts > GETDATE() AND h.coupon_ startdate = 5

Or you may have meant this, which is VERY different:

IF ISNULL(@AFonly, 0) = 0

BEGIN

SELECT * FROM views(creted some view)

SELECT od.* FROM

tableA AS h

INNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2

AND h.couponend_date ts > GETDATE() AND h.coupon_ startdate = 5

END

Doing this not only ensures YOU get the right code, but it keeps devs who may debug/refactor this in the future from screwing up too! 🙂