SQL Sentry Plan Explorer – SQL Execution varies from 8 seconds to 20 Minutes Because of Where Clause

Ken Smith 2017-06-22 14:23:36

We have SQL generated from our application. In the WHERE clause, This takes 20 minutes.
WHERE
( ( vwFormItem_3_Building_PortfolioType.[Building_PortfolioType] IN (
'Operating Real Estate' ) )
AND
( vwAdHocTenantHierarchy_Building.[Tenant Hierarchy] IN (
'Critical Sites', 'Offices' ) )
AND ( vwFormItem_4_LeaseStatusID.[LeaseStatusID] NOT IN ( 'Expired', 'Terminated' ) )
)
AND tblLeaseIndex.LeaseActive = 1
AND tblLeaseAbstract.ObjectTypeTypeid <> 404

This takes 8 seconds:
WHERE
( vwAdHocTenantHierarchy_Building.[Tenant Hierarchy] IN (
'Critical Sites', 'Offices' ) )
AND ( vwFormItem_4_LeaseStatusID.[LeaseStatusID] NOT IN ( 'Expired', 'Terminated' ) )
AND tblLeaseIndex.LeaseActive = 1
AND tblLeaseAbstract.ObjectTypeTypeid <> 404

The plan diagram for both is huge. How can I compare to find what is different and how to increase performance?