Optimize and SQL Query

Need to optimize the below query, Presently it takes 10 mins to load in database, need to modify it and get the result in short time.


Select *, ISNULL(((len(General)-len(REPLACE(General,'QuestionAnswerId','')))/len('QuestionAnswerId')/2),0) as GeneralCount, ISNULL(((len(General)-len(REPLACE(General,'QuestionAnswerId','')))/len('QuestionAnswerId')/2),0) as Total from ( Select distinct SA.OptionType, AnswerEnglish,SAA.AnswerType, Answer=STUFF ( ( select QuestionAnswerId from SurveyQuestionAnswer g Left join EmployeeRegistration ER on g.UserId = ER.EmpRegId Left join EmployeeCheckedIn EC on ER.ControlNo= EC.ControlNo Left join CateringCompanyMaster CM on EC.CampId = CM.CampId where g.OptionType=SA.OptionType and g.QuestionAnswerId=SAA.SurveyAnswerId and g.UserSurveyStatus='Complete' and (',' + Convert(varchar(max),'1,2,3,4,5,6,7,8,9,10,13,') + ',' Like '%,'+ CONVERT(varchar(max),EC.CampId) + ',%') and (',' + Convert(varchar(max),'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,') + ',' Like '%,'+ CONVERT(varchar(max),ER.ProjectId) + ',%') and (',' + Convert(varchar(max),'1,2,3,4,5,6,7,8,9,10,11,12,13,14,16,') + ',' Like '%,'+ CONVERT(varchar(max),ER.NationalityId) + ',%') and (',' + Convert(varchar(max),'1,2,3,4,5,6,7,8,') + ',' Like '%,'+ CONVERT(varchar(max),CM.CateringCompanyId) + ',%') FOR XMl PATH('') ),1,1,'' ) from QuestionAnswer SA left join SurveyAnswer SAA on SA.AnswerId=SAA.SurveyAnswerId left join EmpSurvey ES on SA.SurveyId=ES.SurveyId Left join EmployeeRegistration ER on ES.UserId = ER.EmpRegId Left join EmployeeCheckedIn EC on ER.ControlNo= EC.ControlNo Left join CateringCompanyMaster CM on EC.CampId = CM.CampId where SA.SurveyId=1 and ES.SurveyStatus='Complete' and (',' + Convert(varchar(max),'1,2,3,4,5,6,7,8,9,10,13,') + ',' Like '%,'+ CONVERT(varchar(max),EC.CampId) + ',%') and (',' + Convert(varchar(max),'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,') + ',' Like '%,'+ CONVERT(varchar(max),ER.ProjectId) + ',%') and (',' + Convert(varchar(max),'1,2,3,4,5,6,7,8,9,10,11,12,13,14,16,') + ',' Like '%,'+ CONVERT(varchar(max),ER.NationalityId) + ',%') ) as SourceTable Pivot ( max(Answer) for OptionType in ([General])) as pvttable



Capable Candidates, Please message.