How to Solve a simple Linear Programming Problem via MS SQL Server?

Purpose: Instead of using MS Solver or other optimization tool, we are going to build a process to solve LP problem via MS SQL Server.

Steps:

1. Let’s pick an example for our model

Max Z = 5 * X + 5 * Y
s.t. X <= 5
     Y <= 6
     2 * X + 3 * Y <= 18
     X >= 0, Y >=0

2. Create a table called [Optimization].[Iteration_Table] to store data of each iteration.

CREATE TABLE [Optimization].[Iteration_Table](
 [Iteration] int NULL,
 [X] INT, 
 [Y] INT,
 [Result] float);

3. Declare variables and set initial values for them.

DECLARE @X INT;
DECLARE @Y INT;
DECLARE @iteration INT;
DECLARE @Max int;
Set @iteration = 1;
Set @X = 0;
Set @Y = 0;
Set @Max = 0;

4. Create while loop to test which combination of X and Y can reach the maximum value. At the same time, if they don’t meet the constraints we defined at point 1, the iteration won’t be recorded and inserted into table [Optimization].[Iteration_Table].

while (@X <= 100)
begin
while (@Y <= 100)
begin
IF @X <= 5 AND @Y <=6 AND 2*@X + 3*@Y <= 18
   Insert into [Optimization].[Iteration_Table]
   Select @iteration, @X, @Y, 3*@X + 5*@Y;
ELSE GOTO Branch_End;

Set @iteration = @iteration +  1;
IF 3*@X + 5*@Y >= @Max
Set @Max = 3*@X + 5*@Y; 
ELSE GOTO Branch_End;

Branch_End:  
Set @Y = @Y +  1
END
Set @Y = 0;
Set @X = @X +  1;
end

5. When the loop ends (try all combination of X and Y), you can see what max value is and each iteration we record (insert) in the table [Optimization].[Iteration_Table].

Select @Max as [Max];
SELECT * FROM [Optimization].[Iteration_Table];

SELECT * FROM [Optimization].[Iteration_Table] 
Where  [Result] > = @Max;

6. You will find that we only took 30 iterations to finish it. It is because of the first two constraints. The process will only test X between 0 and 5 and Y between 0 and 6 (Total: 5 * 6). As a result, you can take your constraints into consideration before setting up loop function to make your code and query more efficient.

Thanks! Enjoy it :)