Solving simple Linear Programming Problem via MS SQL Server - 1

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.

Thank you and enjoy it! If you want to support Informula, you can buy us a coffee here :)

𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲