Do you know why dropping and recreating makes things go faster, and they gradually slow down?

Do you have maintenance that updates statistics, and reindexes, if needed?

Plus, if the code within the view is indicative of your coding, there is likely a lot of optimization that can occur.

You are using the old style joins, which functionally are the same, but are not very readable.

Also, doing the join on the substring(sn, 7, 5) is referred to as NON-sargable. Please research this further.

I took the liberty of re-writing thie code. There are many other ways to do this, but since I am at the office, you are only getting the "quick" version.

select

p.model,

i.defect_title,

p.item,

d.sn,

d.dateinspected,

t.type,

i.defect_id

from

(SELECT sn,

substring(sn, 7, 5) as [sn_join]

dateinspected,

defect_id

FROM productiondefect) D

INNER JOIN assignworkorder p ON d.[sn_join] = p.workorder

INNER JOIN tlkp_defects i ON d.defect_id = i.defect_id

INNER JOIN tlkp_item as t ON p.item = t.item