Performance Tuning

• PSB

  SSChampion

  Points: 13195

  More actions

  May 11, 2013 at 10:39 am

  #272464

  Hi,

  I am working on tuning an sp . I tried to remove the cursor and get the values in a temp table, but the performance did not improve. The first WHILE loop is causing performance issues. Is there any way I can make it more usable ?

  ALTER PROCEDURE [dbo].[spreport]

  @pSiteSeqId VARCHAR(MAX),

  @pStartDate DATETIME2,

  @pEndDate DATETIME2

  AS

  BEGIN

  SET NOCOUNT ON;

  IF @pSiteSeqId LIKE '%<Filter>%'

  BEGIN

  DECLARE @tSiteSeqID VARCHAR(MAX)

  DECLARE @FilterXML XML = CAST(@pSiteSeqId AS XML)

  DECLARE @tmpSites TABLE (SiteSeqID INT)

  INSERT INTO @tmpSites (SiteSeqID)

  SELECT Sites.ID.value('@Value[1]', 'INT')

  FROM @FilterXML.nodes('/Filter/Site_Seq_Id') as Sites(ID)

  SELECT @tSiteSeqID = COALESCE(@tSiteSeqID + ',', '') + CAST(SiteSeqID AS VARCHAR(10))

  FROM @tmpSites

  SET @pSiteSeqId = @tSiteSeqID

  END

  DECLARE @Sensor_State varchar(3)

  DECLARE @Sensor_State2 varchar(29)

  DECLARE @Tank_Config_Seq_Id int

  DECLARE @Site_Seq_Id int

  DECLARE @Site_Name varchar(30)

  DECLARE @Address_Line_1 varchar(30)

  DECLARE @Address_Line_2 varchar(30)

  DECLARE @City varchar(20)

  DECLARE @State varchar(2)

  DECLARE @Zip_Code varchar(9)

  DECLARE @Tank_Config_Nbr varchar(12)

  DECLARE @Tank_Number smallint

  DECLARE @Fuel_Type varchar(8)

  DECLARE @Poll_Date datetime

  DECLARE @Water_Gallons int

  DECLARE @PollDate varchar(30)

  DECLARE @Water_Inches real

  DECLARE @Fuel_Inches real

  DECLARE @Temperature real

  DECLARE @Gallons int

  DECLARE @Deliveries float

  DECLARE @InventDisp float

  DECLARE @SensorStatus varchar(30)

  DECLARE @ATGLeakTest varchar(30)

  DECLARE @RateResult varchar(23)

  DECLARE @NextDayInventory int

  DECLARE @Test_Passed_Cd varchar(25)

  DECLARE @Leak_Test_Type smallint

  DECLARE @Compliance int

  DECLARE @Company_Name varchar(50)

  DECLARE @Tank_Material varchar(20)

  DECLARE @Compliance_Method varchar(50)

  DECLARE @Capacity_qty int

  DECLARE @LOOPCOUNT int

  DECLARE @EndingInvent int

  DECLARE @TotalInvt float

  DECLARE @TotalDel float

  DECLARE @DailyAvg float

  DECLARE @StartInvent int

  DECLARE @CustID VARCHAR(25)

  DECLARE @Equipment_Name VARCHAR(200)

  DECLARE @Periodic_Status VARCHAR(10)

  Declare @CompTemp as table

  (

  Sensor_State varchar(29),

  Tank_Config_Seq_Id int,

  Site_Seq_Id int,

  Site_Name varchar(30),

  Address_Line_1 varchar(30),

  Address_Line_2 varchar(30),

  City varchar(20),

  State varchar(2),

  Zip_Code varchar(9),

  Tank_Config_Nbr varchar(12),

  Tank_Number smallint,

  Fuel_Type varchar(20),

  Poll_Date datetime,

  Water_Gallons int,

  PollDate varchar(30),

  Water_Inches real,

  Fuel_Inches real,

  Temperature real,

  Gallons int,

  Deliveries float,

  InventDisp float,

  SensorStatus varchar(30),

  ATGLeakTest varchar(30),

  RateResult varchar(23),

  Compliance int,

  EndingInvent int,

  TotalInvtDisp int,

  TotalDeliveries float,

  DailyAvg float,

  Company_Name varchar(50),

  Tank_Material varchar(20),

  Compliance_Method varchar(50),

  Capacity_Qty int ,

  CustID VARCHAR(25)

  )

  --- parse site id list into temp table

  declare @TempList as table

  (

  recidINT IDENTITY(1,1),

  SiteID int,

  Tank_Number int

  )

  DECLARE @SiteID varchar(10), @Pos int

  INSERT INTO @TempList (SiteID,Tank_Number)

  SELECT

  a.Site_Seq_Id,

  a.Tank_Number

  FROM dbo.Tanks a

  INNER JOIN dbo.fn_CreateTableFromCSV(@pSiteSeqId) T ON A.Site_Seq_Id = T.value

  SET @LOOPCOUNT = 1

  WHILE exists(Select 1 from @TempList where recid = @LOOPCOUNT)

  Begin

  Select @SiteID=SiteID,@Tank_Number=Tank_Number from @TempList where recid = @LOOPCOUNT

  Set @Poll_Date=@pstartDate

  While (datediff(day,@Poll_Date,@pendDate)>=0)

  Begin

  if exists( select * FROM FN_vwComplianceFinal tmp WHERE Site_Seq_Id=@SiteID and Tank_Number=@Tank_Number and

  datediff (day,Poll_Date,@Poll_Date )=0 )

  Begin

  DECLARE @Cursor1 CURSOR

  SET @Cursor1 = CURSOR FAST_FORWARD

  FOR

  SELECT Top 1 Sensor_State, Sensor_State2, tmp.Tank_Config_Seq_Id, Site_Seq_Id, Site_Name,

  Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr,

  Tank_Number, Fuel_Type, Water_Gallons, PollDate, Water_Inches,

  Fuel_Inches, Temperature, Gallons, Deliveries, InventDisp, SensorStatus,

  ATGLeakTest, RateResult, Company_Name,tank_material,Compliance_Method,Capacity_Qty,

  Test_Passed_Cd, Leak_Test_Type,Cust_Site_ID ,Equipment_Name,Periodic_Status

  FROM vwComplianceFinal tmp

  left join vwCompliance_LeakTest cl on tmp.Tank_Config_Seq_Id =cl.Tank_Config_Seq_Id

  AND cl.TestStartDate = tmp.PollDate and cl.Leak_Test_type != 2

  WHERE Site_Seq_Id=@SiteID and Tank_Number=@Tank_Number and

  datediff (day,Poll_Date,@Poll_Date )=0

  order by convert(time,Polling_date) desc

  OPEN @Cursor1

  FETCH NEXT FROM @Cursor1

  INTO @Sensor_State, @Sensor_State2, @Tank_Config_Seq_Id, @Site_Seq_Id, @Site_Name,

  @Address_Line_1, @Address_Line_2, @City, @State, @Zip_Code, @Tank_Config_Nbr,

  @Tank_Number, @Fuel_Type, @Water_Gallons, @PollDate, @Water_Inches,

  @Fuel_Inches, @Temperature, @Gallons, @Deliveries, @InventDisp, @SensorStatus,

  @ATGLeakTest, @RateResult,@Company_Name,@tank_material,@Compliance_Method,@Capacity_Qty,

  @Test_Passed_Cd, @Leak_Test_Type,@custID,@Equipment_Name,@Periodic_Status

  WHILE @@FETCH_STATUS = 0

  BEGIN

  Set @ATGLeakTest= CASE WHEN @Equipment_name LIKE '%VEEDER%' THEN

  case when @Test_Passed_Cd like '%CSLD%' then @Test_Passed_Cd

  else

  @Periodic_Status + ' 0.2'

  END

  ELSE

  case when @Test_Passed_Cd like '%CSLD%' then @Test_Passed_Cd

  else

  case when @Leak_Test_Type is null then CAST(@Test_Passed_Cd AS varchar(30))

  else

  case when @Leak_Test_Type = 0 then CAST(@Test_Passed_Cd As varchar(10)) + ' 0.2'

  when @Leak_Test_Type = 1 then CAST(@Test_Passed_Cd As varchar(10)) + ' 0.1'

  when @Leak_Test_Type = 2 then CAST(@Test_Passed_Cd As varchar(10)) + ' Gross'

  End

  End

  END

  END

  SELECT @NextDayInventory=Gallons FROM FN_vwComplianceFinal WHERE

  Site_Seq_Id = @Site_Seq_Id AND

  Tank_Config_Seq_Id = @Tank_Config_Seq_Id AND

  PollDate = CONVERT(VARCHAR, DATEADD(day, 1, @Poll_Date), 101)

  IF @Gallons IS NOT NULL AND @NextDayInventory IS NOT NULL --AND @Deliveries IS NOT NULL

  BEGIN

  if @Deliveries is not null

  Begin

  SET @InventDisp = ((CAST(@Gallons AS float) + @Deliveries) - CAST(@NextDayInventory As float))

  end

  else

  begin

  SET @InventDisp = ((CAST(@Gallons AS float)) - CAST(@NextDayInventory As float))

  end

  END

  select @EndingInvent = SUM(Gallons)

  FROM FN_vwComplianceFinal

  WHERE Site_Seq_Id = @Site_Seq_Id and Tank_Number = @Tank_Number and

  PollDate = CONVERT(VARCHAR, DATEADD(day, 1, @pendDate), 101)

  Group By Site_seq_id, Tank_Number

  select @Compliance=Compliance from sites where Site_Seq_Id = @Site_Seq_Id

  INSERT INTO @CompTemp

  (Sensor_State, Tank_Config_Seq_Id, Site_Seq_Id, Site_Name,

  Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr,

  Tank_Number, Fuel_Type, Poll_Date, Water_Gallons, PollDate, Water_Inches,

  Fuel_Inches, Temperature, Gallons, Deliveries, InventDisp, SensorStatus,

  ATGLeakTest, RateResult, Compliance, EndingInvent, TotalInvtDisp, TotalDeliveries,

  DailyAvg,Company_Name,Tank_Material,Compliance_Method,Capacity_Qty,CustID)

  VALUES

  (@Sensor_State, @Tank_Config_Seq_Id, @Site_Seq_Id, @Site_Name,

  @Address_Line_1, @Address_Line_2, @City, @State, @Zip_Code, @Tank_Config_Nbr,

  @Tank_Number, @Fuel_Type, @Poll_Date, @Water_Gallons, @PollDate, @Water_Inches,

  @Fuel_Inches, @Temperature, @Gallons, @Deliveries, @InventDisp, @SensorStatus,

  @ATGLeakTest, @RateResult, @Compliance, @EndingInvent, @TotalInvt, @TotalDel,

  @DailyAvg,@Company_Name,@Tank_Material,@Compliance_Method,@Capacity_qty,@CustID)

  FETCH NEXT FROM @Cursor1

  INTO @Sensor_State, @Sensor_State2, @Tank_Config_Seq_Id, @Site_Seq_Id, @Site_Name,

  @Address_Line_1, @Address_Line_2, @City, @State, @Zip_Code, @Tank_Config_Nbr,

  @Tank_Number, @Fuel_Type, @Water_Gallons, @PollDate, @Water_Inches,

  @Fuel_Inches, @Temperature, @Gallons, @Deliveries, @InventDisp, @SensorStatus,

  @ATGLeakTest, @RateResult,@Company_Name,@tank_material,@Compliance_Method,@Capacity_Qty,

  @Test_Passed_Cd, @Leak_Test_Type,@custID,@Equipment_Name,@Periodic_Status

  END

  CLOSE @Cursor1

  DEALLOCATE @Cursor1

  End

  Else

  Begin

  INSERT INTO @CompTemp

  (Tank_Config_Seq_Id, Site_Seq_Id, Site_Name,

  Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr,

  Tank_Number, Fuel_Type, Poll_Date, PollDate,

  Compliance,Company_Name,Capacity_Qty,CustID )

  Select

  t.Tank_Config_Seq_Id, s.Site_Seq_Id, s.Site_Name,

  s.Address_Line_1, s.Address_Line_2, s.City, s.State, s.Zip_Code, Tank_Config_Nbr,

  @Tank_Number, Fuel_Type, @Poll_Date, CONVERT(VARCHAR, @Poll_Date, 101),

  s.Compliance,c.Company_Name,Tank_Models.Capacity_Qty,s.Cust_Site_ID

  From Sites s inner join Tanks t on s.Site_Seq_Id =t.Site_Seq_Id

  join Tank_Models ON t.Tank_Model_Seq_Id = Tank_Models.Tank_Model_Seq_Id

  join Companies c on c.Company_Id=s.Company_Id

  where s.Site_Seq_Id=@SiteID and t.Tank_Number=@Tank_Number

  End

  Set @Poll_Date=dateadd(day, 1,@Poll_Date)

  --Select @Poll_Date

  End

  Set @LOOPCOUNT +=1 --looping to next

  --select @LOOPCOUNT

  End

  SELECT Sensor_State, Tank_Config_Seq_Id, Site_Seq_Id, Site_Name,

  Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr,

  Tank_Number, Fuel_Type, Poll_Date, Water_Gallons, PollDate, Water_Inches,

  Fuel_Inches, Temperature, Gallons, Deliveries, InventDisp, SensorStatus,

  ATGLeakTest, RateResult, Compliance, EndingInvent, TotalInvtDisp, TotalDeliveries,

  DailyAvg,Company_Name,Tank_Material,Compliance_Method,Capacity_Qty,CustID

  FROM @CompTemp

  Order by Site_Seq_Id, Tank_Number, Poll_Date

  END

  GO

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  May 13, 2013 at 4:35 am

  #1614539

  First of all: it's really hard to work with code which looks like vietnamese noodles.

  I've tried to add some indentation there:

  ALTER PROCEDURE [dbo].[spreport]

  @pSiteSeqId VARCHAR(MAX),

  @pStartDate DATETIME2,

  @pEndDate DATETIME2

  AS

  BEGIN

  SET NOCOUNT ON;

  IF @pSiteSeqId LIKE '%<Filter>%'

  BEGIN

  DECLARE @tSiteSeqID VARCHAR(MAX)

  DECLARE @FilterXML XML = CAST(@pSiteSeqId AS XML)

  DECLARE @tmpSites TABLE (SiteSeqID INT)

  INSERT INTO @tmpSites (SiteSeqID)

  SELECT Sites.ID.value('@Value[1]', 'INT')

  FROM @FilterXML.nodes('/Filter/Site_Seq_Id') as Sites(ID)

  SELECT @tSiteSeqID = COALESCE(@tSiteSeqID + ',', '') + CAST(SiteSeqID AS VARCHAR(10))

  FROM @tmpSites

  SET @pSiteSeqId = @tSiteSeqID

  END

  DECLARE @Sensor_State varchar(3)

  DECLARE @Sensor_State2 varchar(29)

  DECLARE @Tank_Config_Seq_Id int

  DECLARE @Site_Seq_Id int

  DECLARE @Site_Name varchar(30)

  DECLARE @Address_Line_1 varchar(30)

  DECLARE @Address_Line_2 varchar(30)

  DECLARE @City varchar(20)

  DECLARE @State varchar(2)

  DECLARE @Zip_Code varchar(9)

  DECLARE @Tank_Config_Nbr varchar(12)

  DECLARE @Tank_Number smallint

  DECLARE @Fuel_Type varchar(8)

  DECLARE @Poll_Date datetime

  DECLARE @Water_Gallons int

  DECLARE @PollDate varchar(30)

  DECLARE @Water_Inches real

  DECLARE @Fuel_Inches real

  DECLARE @Temperature real

  DECLARE @Gallons int

  DECLARE @Deliveries float

  DECLARE @InventDisp float

  DECLARE @SensorStatus varchar(30)

  DECLARE @ATGLeakTest varchar(30)

  DECLARE @RateResult varchar(23)

  DECLARE @NextDayInventory int

  DECLARE @Test_Passed_Cd varchar(25)

  DECLARE @Leak_Test_Type smallint

  DECLARE @Compliance int

  DECLARE @Company_Name varchar(50)

  DECLARE @Tank_Material varchar(20)

  DECLARE @Compliance_Method varchar(50)

  DECLARE @Capacity_qty int

  DECLARE @LOOPCOUNT int

  DECLARE @EndingInvent int

  DECLARE @TotalInvt float

  DECLARE @TotalDel float

  DECLARE @DailyAvg float

  DECLARE @StartInvent int

  DECLARE @CustID VARCHAR(25)

  DECLARE @Equipment_Name VARCHAR(200)

  DECLARE @Periodic_Status VARCHAR(10)

  Declare @CompTemp as table

  (

  Sensor_State varchar(29),

  Tank_Config_Seq_Id int,

  Site_Seq_Id int,

  Site_Name varchar(30),

  Address_Line_1 varchar(30),

  Address_Line_2 varchar(30),

  City varchar(20),

  State varchar(2),

  Zip_Code varchar(9),

  Tank_Config_Nbr varchar(12),

  Tank_Number smallint,

  Fuel_Type varchar(20),

  Poll_Date datetime,

  Water_Gallons int,

  PollDate varchar(30),

  Water_Inches real,

  Fuel_Inches real,

  Temperature real,

  Gallons int,

  Deliveries float,

  InventDisp float,

  SensorStatus varchar(30),

  ATGLeakTest varchar(30),

  RateResult varchar(23),

  Compliance int,

  EndingInvent int,

  TotalInvtDisp int,

  TotalDeliveries float,

  DailyAvg float,

  Company_Name varchar(50),

  Tank_Material varchar(20),

  Compliance_Method varchar(50),

  Capacity_Qty int ,

  CustID VARCHAR(25)

  )

  --- parse site id list into temp table

  declare @TempList as table

  (

  recidINT IDENTITY(1,1),

  SiteID int,

  Tank_Number int

  )

  DECLARE @SiteID varchar(10), @Pos int

  INSERT INTO @TempList (SiteID,Tank_Number)

  SELECT a.Site_Seq_Id, a.Tank_Number

  FROM dbo.Tanks a

  INNER JOIN dbo.fn_CreateTableFromCSV(@pSiteSeqId) T ON A.Site_Seq_Id = T.value

  SET @LOOPCOUNT = 1

  WHILE exists(Select 1 from @TempList where recid = @LOOPCOUNT)

  Begin

  Select @SiteID=SiteID,@Tank_Number=Tank_Number from @TempList where recid = @LOOPCOUNT

  Set @Poll_Date=@pstartDate

  While (datediff(day,@Poll_Date,@pendDate)>=0)

  Begin

  if exists( select * FROM FN_vwComplianceFinal tmp WHERE Site_Seq_Id=@SiteID and Tank_Number=@Tank_Number and

  datediff (day,Poll_Date,@Poll_Date )=0 )

  Begin

  DECLARE @Cursor1 CURSOR

  SET @Cursor1 = CURSOR FAST_FORWARD

  FOR

  SELECT Top 1 Sensor_State, Sensor_State2, tmp.Tank_Config_Seq_Id, Site_Seq_Id, Site_Name,

  Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr,

  Tank_Number, Fuel_Type, Water_Gallons, PollDate, Water_Inches,

  Fuel_Inches, Temperature, Gallons, Deliveries, InventDisp, SensorStatus,

  ATGLeakTest, RateResult, Company_Name,tank_material,Compliance_Method,Capacity_Qty,

  Test_Passed_Cd, Leak_Test_Type,Cust_Site_ID ,Equipment_Name,Periodic_Status

  FROM vwComplianceFinal tmp

  left join vwCompliance_LeakTest cl on tmp.Tank_Config_Seq_Id =cl.Tank_Config_Seq_Id

  AND cl.TestStartDate = tmp.PollDate and cl.Leak_Test_type != 2

  WHERE Site_Seq_Id=@SiteID and Tank_Number=@Tank_Number and

  datediff (day,Poll_Date,@Poll_Date )=0

  order by convert(time,Polling_date) desc

  OPEN @Cursor1

  FETCH NEXT FROM @Cursor1

  INTO @Sensor_State, @Sensor_State2, @Tank_Config_Seq_Id, @Site_Seq_Id, @Site_Name,

  @Address_Line_1, @Address_Line_2, @City, @State, @Zip_Code, @Tank_Config_Nbr,

  @Tank_Number, @Fuel_Type, @Water_Gallons, @PollDate, @Water_Inches,

  @Fuel_Inches, @Temperature, @Gallons, @Deliveries, @InventDisp, @SensorStatus,

  @ATGLeakTest, @RateResult,@Company_Name,@tank_material,@Compliance_Method,@Capacity_Qty,

  @Test_Passed_Cd, @Leak_Test_Type,@custID,@Equipment_Name,@Periodic_Status

  WHILE @@FETCH_STATUS = 0

  BEGIN

  Set @ATGLeakTest= CASE WHEN @Equipment_name LIKE '%VEEDER%' THEN

  case when @Test_Passed_Cd like '%CSLD%' then @Test_Passed_Cd

  else @Periodic_Status + ' 0.2'

  END

  ELSE

  case when @Test_Passed_Cd like '%CSLD%' then @Test_Passed_Cd

  else

  case when @Leak_Test_Type is null then CAST(@Test_Passed_Cd AS varchar(30))

  else

  case when @Leak_Test_Type = 0 then CAST(@Test_Passed_Cd As varchar(10)) + ' 0.2'

  when @Leak_Test_Type = 1 then CAST(@Test_Passed_Cd As varchar(10)) + ' 0.1'

  when @Leak_Test_Type = 2 then CAST(@Test_Passed_Cd As varchar(10)) + ' Gross'

  End

  End

  END

  END

  SELECT @NextDayInventory=Gallons

  FROM FN_vwComplianceFinal

  WHERE Site_Seq_Id = @Site_Seq_Id AND

  Tank_Config_Seq_Id = @Tank_Config_Seq_Id AND

  PollDate = CONVERT(VARCHAR, DATEADD(day, 1, @Poll_Date), 101)

  IF @Gallons IS NOT NULL AND @NextDayInventory IS NOT NULL --AND @Deliveries IS NOT NULL

  BEGIN

  if @Deliveries is not null

  Begin

  SET @InventDisp = ((CAST(@Gallons AS float) + @Deliveries) - CAST(@NextDayInventory As float))

  end

  else

  begin

  SET @InventDisp = ((CAST(@Gallons AS float)) - CAST(@NextDayInventory As float))

  end

  END

  select @EndingInvent = SUM(Gallons)

  FROM FN_vwComplianceFinal

  WHERE Site_Seq_Id = @Site_Seq_Id and

  Tank_Number = @Tank_Number and

  PollDate = CONVERT(VARCHAR, DATEADD(day, 1, @pendDate), 101)

  Group By Site_seq_id, Tank_Number

  select @Compliance=Compliance from sites where Site_Seq_Id = @Site_Seq_Id

  INSERT INTO @CompTemp

  (Sensor_State, Tank_Config_Seq_Id, Site_Seq_Id, Site_Name,

  Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr,

  Tank_Number, Fuel_Type, Poll_Date, Water_Gallons, PollDate, Water_Inches,

  Fuel_Inches, Temperature, Gallons, Deliveries, InventDisp, SensorStatus,

  ATGLeakTest, RateResult, Compliance, EndingInvent, TotalInvtDisp, TotalDeliveries,

  DailyAvg,Company_Name,Tank_Material,Compliance_Method,Capacity_Qty,CustID)

  VALUES

  (@Sensor_State, @Tank_Config_Seq_Id, @Site_Seq_Id, @Site_Name,

  @Address_Line_1, @Address_Line_2, @City, @State, @Zip_Code, @Tank_Config_Nbr,

  @Tank_Number, @Fuel_Type, @Poll_Date, @Water_Gallons, @PollDate, @Water_Inches,

  @Fuel_Inches, @Temperature, @Gallons, @Deliveries, @InventDisp, @SensorStatus,

  @ATGLeakTest, @RateResult, @Compliance, @EndingInvent, @TotalInvt, @TotalDel,

  @DailyAvg,@Company_Name,@Tank_Material,@Compliance_Method,@Capacity_qty,@CustID)

  FETCH NEXT FROM @Cursor1

  INTO @Sensor_State, @Sensor_State2, @Tank_Config_Seq_Id, @Site_Seq_Id, @Site_Name,

  @Address_Line_1, @Address_Line_2, @City, @State, @Zip_Code, @Tank_Config_Nbr,

  @Tank_Number, @Fuel_Type, @Water_Gallons, @PollDate, @Water_Inches,

  @Fuel_Inches, @Temperature, @Gallons, @Deliveries, @InventDisp, @SensorStatus,

  @ATGLeakTest, @RateResult,@Company_Name,@tank_material,@Compliance_Method,@Capacity_Qty,

  @Test_Passed_Cd, @Leak_Test_Type,@custID,@Equipment_Name,@Periodic_Status

  END

  CLOSE @Cursor1

  DEALLOCATE @Cursor1

  End

  Else

  Begin

  INSERT INTO @CompTemp

  (Tank_Config_Seq_Id, Site_Seq_Id, Site_Name,

  Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr,

  Tank_Number, Fuel_Type, Poll_Date, PollDate,

  Compliance,Company_Name,Capacity_Qty,CustID )

  Select

  t.Tank_Config_Seq_Id, s.Site_Seq_Id, s.Site_Name,

  s.Address_Line_1, s.Address_Line_2, s.City, s.State, s.Zip_Code, Tank_Config_Nbr,

  @Tank_Number, Fuel_Type, @Poll_Date, CONVERT(VARCHAR, @Poll_Date, 101),

  s.Compliance,c.Company_Name,Tank_Models.Capacity_Qty,s.Cust_Site_ID

  From Sites s inner join Tanks t on s.Site_Seq_Id =t.Site_Seq_Id

  join Tank_Models ON t.Tank_Model_Seq_Id = Tank_Models.Tank_Model_Seq_Id

  join Companies c on c.Company_Id=s.Company_Id

  where s.Site_Seq_Id=@SiteID and t.Tank_Number=@Tank_Number

  End

  Set @Poll_Date=dateadd(day, 1,@Poll_Date)

  --Select @Poll_Date

  End

  Set @LOOPCOUNT +=1 --looping to next

  --select @LOOPCOUNT

  End

  SELECT Sensor_State, Tank_Config_Seq_Id, Site_Seq_Id, Site_Name,

  Address_Line_1, Address_Line_2, City, State, Zip_Code, Tank_Config_Nbr,

  Tank_Number, Fuel_Type, Poll_Date, Water_Gallons, PollDate, Water_Inches,

  Fuel_Inches, Temperature, Gallons, Deliveries, InventDisp, SensorStatus,

  ATGLeakTest, RateResult, Compliance, EndingInvent, TotalInvtDisp, TotalDeliveries,

  DailyAvg,Company_Name,Tank_Material,Compliance_Method,Capacity_Qty,CustID

  FROM @CompTemp

  Order by Site_Seq_Id, Tank_Number, Poll_Date

  END

  GO

  Now, WHILE LOOP is not really replacement of a CURSOR as it still process row-by-row.

  Looking into your sp I can see that there is 99% possibility to get rid of all cursors and loops, but it's not an easy work without having your tables and some of data to test.

  You may want to follow the link at the bottom of my signature which explains how to post questions here in order to attract most relevant help in timely manner. Otherwise, you may want to wait for someone who is much more patient, has more free time and willingness to to work from limited information supplied

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]