Database

CREATE TABLE ps_data.test (
  Test_ID int(11) NOT NULL AUTO_INCREMENT,
  Idx int(11) NOT NULL,
  Grp int(11) DEFAULT NULL,
  PRIMARY KEY (Test_ID)
)
ENGINE = INNODB
AUTO_INCREMENT = 10
AVG_ROW_LENGTH = 1820
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(1, 1, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(2, 2, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(3, 3, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(4, 4, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(5, 5, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(6, 6, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(7, 7, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(8, 8, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(9, 9, 2);

UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test) tbl WHERE tbl.Grp=1)
WHERE Test_ID=1 

UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test WHERE Grp=1) tbl)
WHERE Test_ID=1 

UPDATE test t, (SELECT * FROM test WHERE Grp=1) tbl
SET t.Idx= (SELECT COALESCE(MAX(tbl.Idx), 0)+1)
WHERE t.Test_ID=1 

Declare	@FundCodes VarChar(Max)='Bateleur|Tower Fund',--Tower Fund
		@StartDate DateTime='2016/04/20',
		@EndDate DateTime='2016/04/29',
		@Zero integer=1,
		@LongShortAll integer = 0, --0 for all, 1 for long value, 2 for short value
		@PhysEff integer = 0 --0 for physical, 1 for Effective


		--check the fulldate 
		select fulldate into #TableDates 
		from AssetData.dbo.Calendar cal where cal.FullDate between @StartDate and @EndDate
		and IsWeekDay = 1 and IsHoliday = 0
	
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
		SET NOCOUNT ON;
	     
		declare @NextDate DateTime
		set @NextDate = @StartDate
		declare @TempValues table(FundCode varchar(200),EffectiveDate DateTime, Value Float)	
	
		declare @fundcodestable table (ID int, fundcode varchar(300))
		declare @Val float

		--Date Counter
		declare @DateCounter int = 0
		select @DateCounter = count(Fulldate) from #TableDates
		declare @Date date
				
		while @DateCounter <> 0 
	
		begin

		set @Date = (select top 1 fulldate from #TableDates order by 1)
	
		--fundcode
		insert into @fundcodestable
		select	pn, s
		from	StagedFundReportingData..FnxSplit('|', @fundcodes)

		--fundcode Counter
		Declare @counterFundId int = 0
		select @counterFundId = count(fundcode) from @fundcodestable
		declare @fundC varchar(300) 
		
		while @counterFundId <>0 and @NextDate<=@EndDate
		begin
			set @fundC = (select top 1 fundcode from @fundcodestable)
				Set @Val = 0
				if @Zero = 0
				   begin
						if @LongShortAll = 0 begin
							exec GetFundValue @fundC, @NextDate, @Val output
						end else begin
							if @LongShortAll = 1 begin
								if @PhysEff = 0 begin
									exec GetFundValueLongOnly @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueLongOnly @fundC, @NextDate, @Val output
								end
							end else begin
								if @PhysEff = 0 begin
									exec GetFundValueShortOnly @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueShortOnly @fundC, @NextDate, @Val output
								end
							end
						end
					end
					else
					begin
						If @LongShortAll = 0 begin
							exec GetFundValue_ZeroFutures @fundC, @NextDate, @Val output
						end else begin
							if @LongShortAll = 1 begin
								if @PhysEff = 0 begin
									exec GetFundValueLongOnly_ZeroFutures @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueLongOnly_ZeroFutures @fundC, @NextDate, @Val output
								end
							end else begin
								if @PhysEff = 0 begin
									exec GetFundValueShortOnly_ZeroFutures @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueShortOnly_ZeroFutures @fundC, @NextDate, @Val output
								end
							end
						end
					end
					Set @Val = isnull(@Val,0)

					if @Val <> 0
					begin
						insert into @TempValues (FundCode,EffectiveDate, Value) Values (@fundC,@NextDate, @Val)
					end

					if datepart(dw,@NextDate) = 7
					begin
						Set @NextDate = DATEADD(Day,2,@NextDate)
					end
					else
					begin
						if datepart(dw,@NextDate) = 6
						begin
							Set @NextDate = DATEADD(Day,3,@NextDate)
						end
						else
						begin
							Set @NextDate = DATEADD(Day,1,@NextDate)
						end 
					end
				  end
			    delete #TableDates where FullDate = @NextDate
		      select @DateCounter = @DateCounter-1
			 --delete @fundcodestable where fundcode = @fundC
		  --     set @counterFundId = @counterFundId - 1  	
		 end
			

		Select * from @TempValues order by FundCode
		drop table #TableDates

How to speedup the execution of a query in Sql Server?