Query taking long time to execute

Question

Hi All,

 

 

I have following Stored Procedure. When I executed this procedure in sql server. It is taking over 25 seconds to execute for 100 records. Please help me or suggest to come over this issue.

 

CREATE PROCEDURE [dbo].[GetHistoryData]
(
@accountRepId varchar(50) = null,
@showAll bit = null,
@startDate DateTime = null,
@endDate DateTime = null
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @query as nvarchar(MAX);
IF (SELECT object_id('#tempRecommendationHistory')) IS NOT NULL
BEGIN
DROP TABLE #tempRecommendationHistory
END
CREATE TABLE #tempRecommendationHistory
(
Id bigint,
MemberName varchar(max),
MembersName varchar(max)
)

SET @query = 'INSERT INTO #tempRecommendationHistory(id,MemberName,MembersName) SELECT
ID
,MemberName = (SELECT (FirstName + '' '' + LastName) FROM [Sirius_Tool].[dbo].Contacts where [Sirius_Tool].[dbo].Contacts.ProfileId = RM.MemberId)
,MembersName = (SELECT STUFF((
select '',''+ (FirstName + '' '' + LastName)
FROM [Sirius_Tool].[dbo].Contacts C1 inner Join [Sirius_Concierge].[SiriusDecisions].[RecommendationMemberDetails] R1 on C1.ProfileId = R1.MemberId where R1.Recommendation_Id=RM.Id
FOR XML PATH('''')
)
,1,1,''''))

FROM [Sirius_Concierge].[SiriusDecisions].[Recommendations] as RM
where 1=1'

IF @accountRepId != '' AND @showAll <> 'true'
BEGIN
SET @query = @query + ' AND RM.AccountRepId = '''+@accountRepId+'''';
END


IF @startDate is not null AND @endDate is not null
BEGIN
SET @query = @query + ' AND RM.CreateDateTime >= ''' + Convert(varchar(20),@startDate) + ''' AND RM.CreateDateTime < ''' + Convert(varchar(20),@endDate) + ''''
END


SET @query = @query + ' Order by CreateDateTime DESC';

EXECUTE sp_executesql @query

select * from #tempRecommendationHistory

END