13 Replies - 713 Views - Last Post: 04 December 2017 - 02:18 PM

#1 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Getting caught in an infinite loop

Posted 04 December 2017 - 09:47 AM

Below is the entire procedure I've written. Hopefully it reads relatively self-explanatorily, but a quick overview: I have a table of Employees, a table of Charts, a table of Teams, and some relational tables to associate them all.

What I WANT to do is collect each Employee who meets the criteria into a table that will track the related info, and then loop through it until everyone has been assigned the maximum number of charts.

Just reading through this again I may have identified a potential fix for the problem I'm having, which is that every now and then it just never breaks out of the loop. I'm not sure what's causing it to happen, but there have been a couple days where I've come in and found that the job (which runs every 10 minutes) has been running since 1am, 3am, whenever.

So first, the unmodified procedure:

ALTER PROCEDURE [dbo].[AutoAssignAll]
AS
DECLARE @Teams TABLE(TeamID int, FacilityID int, EmployeeID int, MinCharts int, ChartCount int)
DECLARE @coder int, @facility int, @min int, @count int, @chart int

INSERT INTO @Teams
SELECT        
	FT.TeamID, 
	FT.FacilityID,
	Emp.ID,
	Emp.CodingMinCharts,
	0
FROM            Coding.FacilityTeams FT
	INNER JOIN Employees Emp
		ON Emp.CodingTeamID = FT.TeamID
			AND CodingAssignEnabled = 1
ORDER BY FacilityID

UPDATE @Teams
SET ChartCount = (SELECT COUNT(*) FROM Charts WHERE AssignedCoderID = EmployeeID and StatusID in(7, 8))

/** Get all the Charts that need to be assigned **/
DECLARE @Charts TABLE(ChartID int, FacilityID int)
INSERT INTO @Charts
SELECT ID, FacilityID FROM Charts
WHERE StatusID = 7 AND (AssignedCoderID IS NULL OR AssignedCoderID < 1) AND CoderID IS NULL
ORDER BY DateOfService ASC

/* We're going to iterate through every single entry on repeat to assign */

DECLARE team_cursor SCROLL CURSOR FOR
SELECT EmployeeID, FacilityID, MinCharts FROM @Teams

OPEN team_cursor
FETCH NEXT FROM team_cursor INTO @coder, @facility, @min

WHILE 1 = 1
	BEGIN
		IF (SELECT COUNT(*) FROM @Teams WHERE ChartCount < MinCharts) = 0 OR (SELECT COUNT(*) FROM @Charts) = 0
			BEGIN
				BREAK
			END
		
		set @count = (select ChartCount from @Teams where EmployeeID = @coder)
		
		IF @count < @min
		BEGIN
			SET @chart = -1 --Remove any previous value
			SET @chart = (select top 1 ChartID from @Charts where FacilityID = @facility)
			IF @chart > 0
				BEGIN
					--assign the chart
					UPDATE Charts SET AssignedCoderID = @coder WHERE ID = @chart

					--remove from the temp chart table
					DELETE FROM @Charts WHERE ChartID = @chart

					--increment the count
					UPDATE @Teams 
					SET		ChartCount = ChartCount + 1
					WHERE EmployeeID = @coder
				END
		END
			
		
		IF @@FETCH_STATUS = 0
			FETCH NEXT FROM team_cursor INTO @coder, @facility, @min
		ELSE
			FETCH FIRST FROM team_cursor INTO @coder, @facility, @min
		

	END
	
CLOSE team_cursor
DEALLOCATE team_cursor


I'm gonna go smoke after I post this and before I test it but I did realize a fairly obvious solution:

I'm doing an infinite loop (while 1=1) and then immediately checking a condition. So I'm going to start looping on that condition as so:

WHILE (SELECT COUNT(*) FROM @Charts) > 0 
	AND (SELECT COUNT(*) FROM @Teams WHERE ChartCount < MinCharts) > 0


Basically saying, while there are still charts available to assign, and still people in need of charts, do the loop

SQL has never been my strong suit, though I'm definitely far better with it now than I was even a year ago. I think that may potentially solve the problem, but I'd still love a good critique and suggestions on how to make this better.

Is This A Good Question/Topic? 0
  • +

Replies To: Getting caught in an infinite loop

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15357
  • View blog
  • Posts: 61,571
  • Joined: 12-June 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 09:58 AM

Off the top of my head I would think you would want to be prepared for weird edge cases where there may be more charts than people or vise versa.

What's "MinCharts"?

Let me futz with it for a bit.
Was This Post Helpful? 0
  • +
  • -

#3 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 10:15 AM

View Postmodi123_1, on 04 December 2017 - 11:58 AM, said:

Off the top of my head I would think you would want to be prepared for weird edge cases where there may be more charts than people or vise versa.

I think that's what I'm preparing for with this check
IF (SELECT COUNT(*) FROM @Teams WHERE ChartCount < MinCharts) = 0 OR (SELECT COUNT(*) FROM @Charts) = 0

Do you think that might be what caused it to get stuck? Am I overlooking a piece of logic?

Quote

What's "MinCharts"?

Min[imum] Charts. It's the number that it attempts to assign up to.

So what it should be doing is saying (now) WHILE [there are Charts available to assign] AND [There are Team members who have less charts than the minimum]

WHILE (SELECT COUNT(*) FROM @Charts) > 0
    AND (SELECT COUNT(*) FROM @Teams WHERE ChartCount < MinCharts) > 0

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15357
  • View blog
  • Posts: 61,571
  • Joined: 12-June 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 10:17 AM

You may want to reorientated your process to only be concerned about the chart quantity. Check if there are any charts available.. so the loop only concerns itself with if there are charts left... and less about employees... then you can go into some odd edge cases for distribution..
Was This Post Helpful? 1
  • +
  • -

#5 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 10:31 AM

Hm. I'll take a look at it from that angle.

I think I just realized what's happening though!

So another piece I should have mentioned and didn't think to was the facility.

Charts have a Facility, and Employees have a Facility they can receive charts from. So Employee 464 can be assigned Charts from Facility 3 and 6, but not Facility 8 or 5.

If Employee 464 has less than the minimum charts, and Facility 8 has charts to be assigned, then both of those checks will return true - but it will never assign and just loop!
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15357
  • View blog
  • Posts: 61,571
  • Joined: 12-June 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 10:41 AM

Just an example of what I was meaning..

declare @tasks table (taskID int)
declare @emp table (empId int, minTasks int, taskCount int)
declare @match table (taskID int, empId int)

inse rt into @tasks (taskID) values (1)
inse rt into @tasks (taskID) values (2)
inse rt into @tasks (taskID) values (3)
inse rt into @tasks (taskID) values (4)
inse rt into @tasks (taskID) values (5)
inse rt into @tasks (taskID) values (6)
inse rt into @tasks (taskID) values (7)
inse rt into @tasks (taskID) values (8)
inse rt into @tasks (taskID) values (9)

insert into @emp(empId, minTasks, taskCount) values (1, 2, 0)
insert into @emp(empId, minTasks, taskCount) values (2, 2, 0)
insert into @emp(empId, minTasks, taskCount) values (3, 1, 0)


declare @tempTaskID int
Declare @tempEmpID int 

declare foo_cur cursor FAST_FORWARD  for
select taskID FROM @tasks

OPEN foo_cur

FETCH NEXT FROM foo_cur   
INTO @tempTaskID

WHILE @@FETCH_STATUS = 0  
BEGIN  
	-- 1.  clear temp employee id
	set @tempEmpID = -1
	-- 2. Get the top one who hasn't hit their minimum count yet.
	select top(1) @tempEmpID = empId
	FROM @emp
	WHERE minTasks > taskCount

	-- 3.  If everyone has met their quota then randomly select someone and stick them with the joy of more work.
	if (@tempEmpID < 0)
	BEGIN
		SELECT TOP 1 @tempEmpID = empId FROM @emp
		ORDER BY NEWID()

		--select @tempEmpID
	END

	-- 4.  Do the insert either way.
	INSERT INTO @match(taskid, empid) values (@tempTAskID, @tempEmpID)
	update @emp set taskCount = taskCount + 1 WHERE empId = @tempEmpID
	
	-- 	print @tempEmpID

	FETCH NEXT FROM foo_cur   
	INTO @tempTaskID 

END   
CLOSE foo_cur;  
DEALLOCATE foo_cur;  

select *
from @emp


empId       minTasks    taskCount
----------- ----------- -----------
1           2           3
2           2           2
3           1           4

empId       minTasks    taskCount
----------- ----------- -----------
1           2           2
2           2           5
3           1           2

empId       minTasks    taskCount
----------- ----------- -----------
1           2           3
2           2           3
3           1           3

Was This Post Helpful? 1
  • +
  • -

#7 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 10:53 AM

Hm. I see what you mean. Part of the challenge here is that I need to evenly assign, especially for cases where there are fewer charts than the combined minimum - and also leave anything in excess as unassigned.
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15357
  • View blog
  • Posts: 61,571
  • Joined: 12-June 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 10:55 AM

Oooooooooh.. if that's the case, and you don't want to play the malevolent work assigning imp, then slap a break in the IF statement instead of getting a random id.
Was This Post Helpful? 1
  • +
  • -

#9 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 11:00 AM

That why I'm using a scroll cursor - so I can just round robin through the table. I just pushed this into my test db and I'm setting it up to run. I added an ELSE statement for when the chart ID isn't > 0 that should force out the cases that are stopping it, I think.
WHILE (SELECT COUNT(*) FROM @Charts) > 0 
	AND (SELECT COUNT(*) FROM @Teams WHERE ChartCount < MinCharts) > 0
	BEGIN
		set @count = (select ChartCount from @Teams where EmployeeID = @coder)
		
		IF @count < @min
		BEGIN
			SET @chart = -1 --Remove any previous value
			SET @chart = (select top 1 ChartID from @Charts where FacilityID = @facility)
			
			IF @chart > 0 --a chart is available for this coder
				BEGIN
					--assign the chart
					UPDATE Charts SET AssignedCoderID = @coder WHERE ID = @chart

					--remove from the temp chart table
					DELETE FROM @Charts WHERE ChartID = @chart

					--increment the count
					UPDATE @Teams 
					SET		ChartCount = ChartCount + 1
					WHERE EmployeeID = @coder
				END
			ELSE --no more charts are available for this facility
				BEGIN
					--change the MinCharts values of the temp table to reflect that all possible charts have been assigned for this facility
					UPDATE @Teams 
					SET MinCharts = ChartCount 
					WHERE FacilityID = @facility
				END
				
		END
			
		
		IF @@FETCH_STATUS = 0
			FETCH NEXT FROM team_cursor INTO @coder, @facility, @min
		ELSE
			FETCH FIRST FROM team_cursor INTO @coder, @facility, @min
		

	END

Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15357
  • View blog
  • Posts: 61,571
  • Joined: 12-June 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 11:55 AM

So your plan is to assign work sequentially?

Task #1, 2, 3, 4, 5
Emp: a, b, c

So the passes would be:

a - 1, 4
b - 2, 5
c - 3,

?

In my example if you add an 'order by' on that could work out that way too.

	-- 2. Get the top one who hasn't hit their minimum count yet.
	select top(1) @tempEmpID = empId
	FROM @emp
	WHERE minTasks > taskCount
	ORDER BY taskCount,empId


empId       minTasks    taskCount
----------- ----------- -----------
1           2           2
2           2           2
3           1           1



taskID      empId
----------- -----------
1           1
2           2
3           3
4           1
5           2

Was This Post Helpful? 0
  • +
  • -

#11 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 02:05 PM

That's definitely a whole lot neater to read.

I'm giving it a test run now as this:
DECLARE @Teams TABLE(TeamID int, FacilityID int, EmployeeID int, MinCharts int, ChartCount int)
DECLARE @employee int, @facility int, @chart int

INSERT INTO @Teams
SELECT        
	FT.TeamID, 
	FT.FacilityID,
	Emp.ID,
	Emp.CodingMinCharts,
	0
FROM            Coding.FacilityTeams FT
	INNER JOIN Employees Emp
		ON Emp.CodingTeamID = FT.TeamID
			AND CodingAssignEnabled = 1
ORDER BY FacilityID

UPDATE @Teams
SET ChartCount = 0 --(SELECT COUNT(*) FROM Charts WHERE AssignedCoderID = EmployeeID and StatusID in(7, 8))

/** Get all the Charts that need to be assigned **/
DECLARE @Charts TABLE(ChartID int, FacilityID int)
INSERT INTO @Charts
SELECT ID, FacilityID FROM Charts
WHERE StatusID = 7 AND (AssignedCoderID IS NULL OR AssignedCoderID < 1) AND CoderID IS NULL
ORDER BY DateOfService ASC

declare chartassign_cursor cursor FAST_FORWARD for
select ChartID, FacilityID from @Charts

open chartassign_cursor

declare @modi TABLE(EmployeeID int, ChartID int, ChartCount int, MinCharts int)
declare @count int, @min int

FETCH NEXT FROM chartassign_cursor
INTO @chart, @facility

WHILE @@FETCH_STATUS = 0
BEGIN
	set @employee = -1
	SELECT TOP 1 
			@employee = EmployeeID,
			@count = ChartCount,
			@min = MinCharts
	FROM @Teams
	WHERE MinCharts > ChartCount
	AND FacilityID = @facility
	ORDER BY ChartCount ASC

	if (@employee > 0)
	BEGIN
		--UPDATE Charts SET AssignedCoderID = @employee
		insert into @modi values(@employee, @chart, @count, @min)
		UPDATE @Teams SET ChartCount = ChartCount + 1 WHERE EmployeeID = @employee
	END

	FETCH NEXT FROM chartassign_cursor INTO @chart, @facility
END

close chartassign_cursor
deallocate chartassign_cursor

select * from @modi

They both seem to run at about the same speed, but this is actually a whole lot easier to read.
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15357
  • View blog
  • Posts: 61,571
  • Joined: 12-June 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 02:07 PM

Woohoo! I'm a table!
Was This Post Helpful? 1
  • +
  • -

#13 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 02:15 PM

My assessment on their speed is based on iteration over about 110,000 'tasks'. Both do it in about a second. There are other things that will slow that down once I start running it to actually issue the updates.

Thank you modi <3

Later this week I'm revisiting this procedure to move assignment to a different table and this is going to make it so much less of a headache.

there are code changes that need to be made first though
Was This Post Helpful? 0
  • +
  • -

#14 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15357
  • View blog
  • Posts: 61,571
  • Joined: 12-June 08

Re: Getting caught in an infinite loop

Posted 04 December 2017 - 02:18 PM

No problemo . Sometimes it takes a bit of standing on the side of your head to get up and around crazy sql tasks.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1