Solution for SQL view takes long time to finish, but the same internal query executes in 1 second
is Given Below:
I am facing a weird experience with a query which is for handling data from ADO. Here is the view
ALTER view [dbo].[v_Missing_Pepics] as select ProjectName Title,PM.ProjectCode,LifeCycle,dbo.fn_kip_ado_status_mapping(LifeCycle,PM.ProjectCode) KipStatus,TeamCode,ISNULL((select top 1 value from string_split(TA.AreaPath,'') where LTRIM(value) like 'Domain%'),'Automation') Domain,DM.DomainName KipDomain,TeamName from [dbo].[v_ProjectMaster_Latest] PM left outer join areapath_mapping TA on TA.KeyedInTeamCode=PM.TeamCode left join v_portfolio_epics PE on PE.ProjectCode=PM.ProjectCode inner join domain_master DM on DM.DomainCode=PM.DomainCode where ProjectActive="yes" and LifeCycle not in ('In Close-Down', 'Completed','Withdrawn') and PE.ProjectCode is null and DM.DomainName not in ('Data Power') and PM.ProjectCode not like 'EXP%' GO
When I try to execute the query like this
Select * from v_Missing_Pepics
It took more than 80sec to finish. But when I copy the query alone (Within the view), it executes in just 1 second.
I don’t understand why??
I am working in Azure SQL.
And see if that resolves the performance problems with the view.