Need Optimization for Stored Procedure -

can let me know optimize query. have indexes on input tables.

alter procedure [dbo].[sp_ctr_releaseservicesdata_roq_uploadjuly5th_nick]    if object_id('tempdb..#tempbuildtable') not null     drop table #tempbuildtable   if object_id('tempdb..#tempsinglebuildtable') not null     drop table #tempsinglebuildtable   if object_id('tempdb..#finalbuild') not null     drop table #finalbuild   if object_id('tempdb..#tempbuilds') not null     drop table #tempbuilds   --if object_id('tempdb..#tempduplicatebuilds') not null drop table  #tempduplicatebuilds   --if object_id('tempdb..#tempuniquebuilds') not null drop table  #tempuniquebuilds    /* last 6 months builds #tempbuilds table*/   select     * #tempbuilds   (select distinct     row_number() on (order min(createddateutc)) inx,     buildnumberv2,     min(createddateutc) mincreateddatetime,     max(createddateutc) maxcreateddatetime,     datediff(hour, min(createddateutc), max(createddateutc)) timebetweenfirstlastfile   releaseservicesdata_test_nick (nolock)   isnumeric(replace(buildnumberv2, '.', '')) = 1   , buildnumberv2 '%[0-9]%'   , buildnumberv2 '%.%'   , createddateutc > dateadd(month, -6, dateadd(dd, -datepart(dd, getdate()) + 1, cast(getdate() date)))   ,   --buildnumberv2 = '16.0.7129.6926' ,   requeststatus = 'released'   group buildnumberv2) tempbuilds   --select * #tempbuilds     declare @tempbuildloopcounter int,           @tempbuildcount int   declare @tempbuildnumber varchar(100)   select     @tempbuildcount = count(*)   #tempbuilds   set @tempbuildloopcounter = 1     declare @maxreplicationpubkeysingapore varchar(100),           @maxreplicationtimesingapore int   declare @maxreplicationpubkeydublin varchar(100),           @maxreplicationtimedublin int   declare @maxreplicationpubkeypr varchar(100),           @maxreplicationtimepr int   declare @maxreplicationpubkeyco1 varchar(100),           @maxreplicationtimeco1 int   declare @maxpubkeyupload varchar(100),           @maxtimeupload int   declare @maxpubkeyscan varchar(100),           @maxtimescan int    while (@tempbuildloopcounter <= @tempbuildcount)   begin     select       @tempbuildnumber = buildnumberv2     #tempbuilds     inx = @tempbuildloopcounter     print @tempbuildnumber     print @tempbuildloopcounter       declare @tempsinglebuildcount int,             @tempcounter int     declare @tempcreatedtime datetime,             @tempcreatedtimetocompare datetime     declare @tempbuildcounter varchar(10)     set @tempcounter = 1     set @tempbuildcounter = 0      if object_id('tempdb..#tempsinglebuildtable') not null       drop table #tempsinglebuildtable     select       * #tempsinglebuildtable     (select       0 inx,       buildnumberv2,       createddateutc,       dublinreplicationendtime,       dublinreplicationstarttime,       filesizeinmb,       fileuploadendtimeutc,       fileuploadstarttime,       fileuploadendtime,       fileuploadstarttimeutc,       importcopyendtime,       prevaultendtimeutc,       publisherkey,       puertoricoreplicationendtime,       puertoricoreplicationstarttime,       redmondreplicationendtime,       redmondreplicationstarttime,       requeststatus,       roqdubcompletiontimeutc,       roqmoprcompletiontimeutc,       roqscanendtimeutc,       roqscanstarttimeutc,       roqsincompletiontimeutc,       roqusoccompletiontimeutc,       singaporereplicationendtime,       singaporereplicationstarttime     releaseservicesdata_test_nick (nolock)     buildnumberv2 = @tempbuildnumber) tempsinglebuildtable;      cte     (select       inx,       row_number() on (order createddateutc) rownumber     #tempsinglebuildtable)     update cte     set inx = rownumber;      select       @tempsinglebuildcount = count(*)     #tempsinglebuildtable     while (@tempcounter <= @tempsinglebuildcount)     begin        select         @tempcreatedtime = createddateutc,         @tempbuildnumber = buildnumberv2       #tempsinglebuildtable       inx = @tempcounter       select         @tempcreatedtimetocompare = createddateutc       #tempsinglebuildtable       inx = @tempcounter + 1       if (datediff(hour, @tempcreatedtime, @tempcreatedtimetocompare) <= 2)       begin         if (@tempbuildcounter = 0)           update #tempsinglebuildtable           set buildnumberv2 = @tempbuildnumber           inx = @tempcounter         else           update #tempsinglebuildtable           set buildnumberv2 = @tempbuildnumber + '_d' + @tempbuildcounter           inx = @tempcounter       end       else       begin          if (@tempbuildcounter = 0)           update #tempsinglebuildtable           set buildnumberv2 = @tempbuildnumber           inx = @tempcounter         else           update #tempsinglebuildtable           set buildnumberv2 = @tempbuildnumber + '_d' + @tempbuildcounter           inx = @tempcounter          set @tempbuildcounter = @tempbuildcounter + 1       end        set @tempcounter = @tempcounter + 1;     end     --select distinct buildnumber #tempsinglebuildtable     --select createddateutc,buildnumberv2,* #tempsinglebuildtable order inx;     if (@tempbuildloopcounter = 1)     begin       select         * #finalbuild       (select         tsb.buildnumberv2         --,convert(date, createddateutc) createddateutc           ,         min(fileuploadstarttimeutc) fileuploadstarttimeutc,         max(fileuploadendtimeutc) fileuploadendtimeutc,         min(createddateutc) mincreateddateutc,         max(redmondreplicationendtime) redmondreplicationendtime,         max(singaporereplicationendtime) singaporereplicationendtime,         max(dublinreplicationendtime) dublinreplicationendtime,         max(importcopyendtime) importcopyendtime,         max(puertoricoreplicationendtime) puertoricoreplicationendtime,         max(roqscanendtimeutc) roqscanendtimeutc,         max(prevaultendtimeutc) prevaultendtimeutc,         max(roqmoprcompletiontimeutc) roqmoprcompletiontimeutc,         max(roqusoccompletiontimeutc) roqusoccompletiontimeutc,         max(roqdubcompletiontimeutc) roqdubcompletiontimeutc,         max(roqsincompletiontimeutc) roqsincompletiontimeutc,         (select           max(v)         (values (max(redmondreplicationendtime)),         (max(singaporereplicationendtime)),         (max(dublinreplicationendtime)),         (max(importcopyendtime)),         (max(puertoricoreplicationendtime)),         (max(roqscanendtimeutc)),         (max(roqmoprcompletiontimeutc)),         (max(roqusoccompletiontimeutc)),         (max(roqdubcompletiontimeutc)),         (max(roqsincompletiontimeutc)),         (max(prevaultendtimeutc))         ) value (v))         lastactivitytime,         max(filesizeinmb) filesizeinmb,         count(filesizeinmb) filecount,         0 maxreplicationtimesingapore,         'dummydummydummy' maxreplicationpubkeysingapore,         0 maxreplicationtimedublin,         'dummydummydummy' maxreplicationpubkeydublin,         0 maxreplicationtimepr,         'dummydummydummy' maxreplicationpubkeypr,         0 maxreplicationtimeco1,         'dummydummydummy' maxreplicationpubkeyco1,         0 maxtimeupload,         'dummydummydummy' maxpubkeyupload,         0 maxtimescan,         'dummydummydummy' maxpubkeyscan,         max(fileuploadstarttimeutc) maxfileuploadstarttimeutc       #tempsinglebuildtable tsb       --title not '%15.%' , title '%mondo%'       requeststatus = 'released'       , createddateutc > dateadd(mm, -6, dateadd(dd, -datepart(dd, getdate()) + 1, cast(getdate() date)))       group tsb.buildnumberv2       --having ltrim(rtrim(right(substring(title,0,charindex('_',title,0)),14))) != ''       order tsb.buildnumberv2 offset 0 rows) temp     end     else     begin       insert #finalbuild         select           tsb.buildnumberv2           --,convert(date, createddateutc) createddateutc             ,           min(fileuploadstarttimeutc) fileuploadstarttimeutc,           max(fileuploadendtimeutc) fileuploadendtimeutc,           min(createddateutc) mincreateddateutc,           max(redmondreplicationendtime) redmondreplicationendtime,           max(singaporereplicationendtime) singaporereplicationendtime,           max(dublinreplicationendtime) dublinreplicationendtime,           max(importcopyendtime) importcopyendtime,           max(puertoricoreplicationendtime) puertoricoreplicationendtime,           max(roqscanendtimeutc) roqscanendtimeutc,           max(prevaultendtimeutc) prevaultendtimeutc,           max(roqmoprcompletiontimeutc) roqmoprcompletiontimeutc,           max(roqusoccompletiontimeutc) roqusoccompletiontimeutc,           max(roqdubcompletiontimeutc) roqdubcompletiontimeutc,           max(roqsincompletiontimeutc) roqsincompletiontimeutc,           (select             max(v)           (values (max(redmondreplicationendtime)),           (max(singaporereplicationendtime)),           (max(dublinreplicationendtime)),           (max(importcopyendtime)),           (max(puertoricoreplicationendtime)),           (max(roqscanendtimeutc)),           (max(roqmoprcompletiontimeutc)),           (max(roqusoccompletiontimeutc)),           (max(roqdubcompletiontimeutc)),           (max(roqsincompletiontimeutc)),           (max(prevaultendtimeutc))           ) value (v))           lastactivitytime,           max(filesizeinmb) filesizeinmb,           count(filesizeinmb) filecount,           0 maxreplicationtimesingapore,           'dummy' maxreplicationpubkeysingapore,           0 maxreplicationtimedublin,           'dummy' maxreplicationpubkeydublin,           0 maxreplicationtimepr,           'dummy' maxreplicationpubkeypr,           0 maxreplicationtimeco1,           'dummy' maxreplicationpubkeyco1,           0 maxtimeupload,           'dummy' maxpubkeyupload,           0 maxtimescan,           'dummy' maxpubkeyscan,           max(fileuploadstarttimeutc) maxfileuploadstarttimeutc         #tempsinglebuildtable tsb         --title not '%15.%' , title '%mondo%'         requeststatus = 'released'         , createddateutc > dateadd(mm, -6, dateadd(dd, -datepart(dd, getdate()) + 1, cast(getdate() date)))         group tsb.buildnumberv2         --having ltrim(rtrim(right(substring(title,0,charindex('_',title,0)),14))) != ''         order tsb.buildnumberv2 offset 0 rows     end      set @maxreplicationtimesingapore = 0     set @maxreplicationtimedublin = 0     set @maxreplicationtimepr = 0     set @maxreplicationtimeco1 = 0     set @maxtimeupload = 0     set @maxtimescan = 0      if object_id('tempdb..#tempdistinctduplicatebuilds') not null       drop table #tempdistinctduplicatebuilds     declare @tempdistinctduplicatebuildcount int,             @tempduplicatebuildcount int,             @tempbuildduplicatenumber varchar(100)     set @tempdistinctduplicatebuildcount = 1     set @tempduplicatebuildcount = 1     set @tempbuildduplicatenumber = ''      create table #tempdistinctduplicatebuilds (       id int identity (1, 1),       buildnumber varchar(100)     )      insert #tempdistinctduplicatebuilds       select distinct         buildnumberv2       #tempsinglebuildtable      select       @tempduplicatebuildcount = count(*)     #tempdistinctduplicatebuilds      while (@tempdistinctduplicatebuildcount <= @tempduplicatebuildcount)     begin       select         @tempbuildduplicatenumber = buildnumber       #tempdistinctduplicatebuilds       id = @tempdistinctduplicatebuildcount       select top 1         @maxreplicationpubkeysingapore = publisherkey,         @maxreplicationtimesingapore = max(datediff(minute, singaporereplicationstarttime, singaporereplicationendtime))       #tempsinglebuildtable       buildnumberv2 = @tempbuildduplicatenumber       group publisherkey       order max(datediff(minute, singaporereplicationstarttime, singaporereplicationendtime)) desc       select top 1         @maxreplicationpubkeydublin = publisherkey,         @maxreplicationtimedublin = max(datediff(minute, dublinreplicationstarttime, dublinreplicationendtime))       #tempsinglebuildtable       buildnumberv2 = @tempbuildduplicatenumber       group publisherkey       order max(datediff(minute, dublinreplicationstarttime, dublinreplicationendtime)) desc       select top 1         @maxreplicationpubkeypr = publisherkey,         @maxreplicationtimepr = max(datediff(minute, puertoricoreplicationstarttime, puertoricoreplicationendtime))       #tempsinglebuildtable       buildnumberv2 = @tempbuildduplicatenumber       group publisherkey       order max(datediff(minute, puertoricoreplicationstarttime, puertoricoreplicationendtime)) desc       select top 1         @maxreplicationpubkeyco1 = publisherkey,         @maxreplicationtimeco1 = max(datediff(minute, redmondreplicationstarttime, redmondreplicationendtime))       #tempsinglebuildtable       buildnumberv2 = @tempbuildduplicatenumber       group publisherkey       order max(datediff(minute, redmondreplicationstarttime, redmondreplicationendtime)) desc       select top 1         @maxpubkeyupload = publisherkey,         @maxtimeupload = max(datediff(minute, fileuploadstarttime, fileuploadendtime))       #tempsinglebuildtable       buildnumberv2 = @tempbuildduplicatenumber       group publisherkey       order max(datediff(minute, fileuploadstarttime, fileuploadendtime)) desc       select top 1         @maxpubkeyscan = publisherkey,         @maxtimescan = max(datediff(minute, roqscanstarttimeutc, roqscanendtimeutc))       #tempsinglebuildtable       buildnumberv2 = @tempbuildduplicatenumber       group publisherkey       order max(datediff(minute, roqscanstarttimeutc, roqscanendtimeutc)) desc        update #finalbuild       set maxreplicationtimesingapore = @maxreplicationtimesingapore,           maxreplicationpubkeysingapore = @maxreplicationpubkeysingapore,           maxreplicationtimedublin = @maxreplicationtimedublin,           maxreplicationpubkeydublin = @maxreplicationpubkeydublin,           maxreplicationtimepr = @maxreplicationtimepr,           maxreplicationpubkeypr = @maxreplicationpubkeypr,           maxreplicationtimeco1 = @maxreplicationtimeco1,           maxreplicationpubkeyco1 = @maxreplicationpubkeyco1,           maxtimeupload = @maxtimeupload,           maxpubkeyupload = @maxpubkeyupload,           maxtimescan = @maxtimescan,           maxpubkeyscan = @maxpubkeyscan       buildnumberv2 = @tempbuildduplicatenumber            print @maxreplicationtimesingapore       print @maxreplicationtimedublin       print @maxreplicationtimepr       print @maxreplicationtimeco1       print @maxtimeupload       print @maxtimescan        print @maxreplicationpubkeysingapore       print @maxreplicationpubkeydublin       print @maxreplicationpubkeypr       print @maxreplicationpubkeyco1       print @maxpubkeyupload       print @maxpubkeyscan        print @tempbuildduplicatenumber        set @tempdistinctduplicatebuildcount = @tempdistinctduplicatebuildcount + 1     end     if object_id('tempdb..#tempdistinctduplicatebuilds') not null       drop table #tempdistinctduplicatebuilds       set @tempbuildloopcounter = @tempbuildloopcounter + 1   end        alter table #finalbuild   add id int identity (1, 1)    alter table #finalbuild   add buildtime int null    alter table #finalbuild   add whofinishedlast varchar(100)    update #finalbuild   set buildtime = datediff(minute, mincreateddateutc, lastactivitytime)    declare @lasttempcount int,           @lastmaxcount int,           @whofinishedlasttime datetime,           @whofinishedlast varchar(30)   set @lasttempcount = 1   select     @lastmaxcount = max(id)   #finalbuild    update #finalbuild   set whofinishedlast = ''    declare @totaluploadtime int   declare @totalbuildtime int    while (@lasttempcount <= @lastmaxcount)   begin     print 'upload update'     set @totalbuildtime = 1;     set @totaluploadtime = 1;      -- check if upload time more or equal half of total time taken     select       @totaluploadtime = datediff(minute, fileuploadstarttimeutc, fileuploadendtimeutc),       @totalbuildtime = buildtime     #finalbuild     id = @lasttempcount      print @totaluploadtime      if (@totaluploadtime >= @totalbuildtime / 2)     begin       set @whofinishedlast = 'upload'     end     else     begin       select         @whofinishedlasttime = lastactivitytime       #finalbuild       buildnumberv2 = (select         buildnumberv2       #finalbuild       id = @lasttempcount)          select         @whofinishedlast =                           case @whofinishedlasttime                             when puertoricoreplicationendtime 'pr'                             when dublinreplicationendtime 'dublin'                             when redmondreplicationendtime 'columbia'                             when singaporereplicationendtime 'singapore'                             when roqscanendtimeutc 'scan'                             when roqsincompletiontimeutc 'singapore'                             when roqdubcompletiontimeutc 'dublin'                             when prevaultendtimeutc 'prevault'                             when roqmoprcompletiontimeutc 'pr'                             when roqusoccompletiontimeutc 'columbia'                             else 'we have problem'                           end       #finalbuild       buildnumberv2 = (select         buildnumberv2       #finalbuild       id = @lasttempcount)     end     update #finalbuild     set whofinishedlast = @whofinishedlast     id = @lasttempcount     , buildtime > 300      set @lasttempcount = @lasttempcount + 1   end     truncate table bi_ctr_upload_test_nick    insert bi_ctr_upload_test_nick     select       id,       buildnumberv2,       fileuploadstarttimeutc,       fileuploadendtimeutc,       mincreateddateutc,       redmondreplicationendtime,       singaporereplicationendtime,       dublinreplicationendtime,       importcopyendtime,       puertoricoreplicationendtime,       roqscanendtimeutc,       prevaultendtimeutc,       roqmoprcompletiontimeutc,       roqusoccompletiontimeutc,       roqdubcompletiontimeutc,       roqsincompletiontimeutc,       lastactivitytime,       filesizeinmb,       maxreplicationtimesingapore,       maxreplicationpubkeysingapore,       maxreplicationtimedublin,       maxreplicationpubkeydublin,       maxreplicationtimepr,       maxreplicationpubkeypr,       maxreplicationtimeco1,       maxreplicationpubkeyco1,       maxtimeupload,       maxpubkeyupload,       maxtimescan,       maxpubkeyscan,       maxfileuploadstarttimeutc,       filecount,       buildtime,       whofinishedlast     #finalbuild     if object_id('tempdb..#tempbuildtable') not null     drop table #tempbuildtable   if object_id('tempdb..#duplicatebuilds') not null     drop table #duplicatebuilds   if object_id('tempdb..#finalbuild') not null     drop table #finalbuild   if object_id('tempdb..#tempbuilds') not null     drop table #tempbuilds   if object_id('tempdb..#tempduplicatebuilds') not null     drop table #tempduplicatebuilds   if object_id('tempdb..#tempuniquebuilds') not null     drop table #tempuniquebuilds