Thursday, September 4, 2008

A Tricky Scenario with T-SQL

The good part of T-SQL is that unlike most other areas of databases in general, one needs to think in order to write a good piece of valid code. Thinking not just to establish functionality but also to ensure performance. Now there are hundreds, even thousands of scenarios where the code can be better than what it has been already written as just to establish functionality. Let us take up one such scenario.

There is a supplier of Dell, HP, and IBM servers. Servers are shipped from the warehouse to three locations. Each location is represented by a location code. Each machine to be shipped to the warehouse has a ten digit Machine_X_Code that has leading zeroes for data that has less than ten digits in it. All this is represented roughly by the Main_Table below:

create table Main_Table
(Location_Code smallint not null
,Machine_Brand varchar(25) not null
,Machine_X_Code varchar(10) not null
,Number_Of_CPUs tinyint not null
,Procurement_Date Datetime not null default GETDATE()
constraint PK_LC_MC Primary Key Clustered (Location_Code, Machine_X_Code, Procurement_Date) with fillfactor = 70)
go


There is another table called Code_Generation_Table. This table determines what the Machine_X_Code for the next procured machine would be. For a given combination of store location code and a machine brand in this Code_Generation_Table, the Machine_X_Code for the Main_Table needs to be generated. The new machine_X_Code generated should be within the Start_code and End_Code range for that Location_Code and Machine_Brand. The table is as follows:

create table Code_Generation_Table
(Location_Code smallint not null
,Machine_Brand varchar(25) not null
,Start_Code int not null
,Current_Code int not null
,End_Code int not null
,Increment_Value tinyint not null
constraint PK_LC_MB Primary Key clustered (Location_Code, Machine_Brand) with fillfactor = 90)
go

Lets populate these two tables now with relevant data:

insert into Code_Generation_Table
select 1, 'DELL', 1001, 1001, 1005, 1
union all
select 2, 'DELL', 4001, 4001, 4005, 1
union all
select 3, 'DELL', 7001, 7001, 7005, 1
union all
select 1, 'HP', 2001, 2003, 2005, 1
union all
select 2, 'HP', 5001, 5003, 5005, 1
union all
select 1, 'IBM', 3001, 3001, 3005, 1
union all
select 2, 'IBM', 8001, 8003, 8005, 1
union all
select 3, 'IBM', 6001, 6004, 6005, 1
union all
select 3, 'SUN', 6011, 6011, 6015, 1

go


insert into Main_Table (Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs)
select 1, 'DELL', '0000001001', 2
union all
select 1, 'DELL', '0000001002', 4
union all
select 1, 'DELL', '0000001003', 8
union all
select 1, 'DELL', '0000001004', 16
union all
select 1, 'HP', '0000002002', 2
union all
select 1, 'HP', '0000002003', 4
union all
select 1, 'HP', '0000002004', 8
union all
select 1, 'HP', '0000002005', 32
union all
select 1, 'IBM', '0000003003', 2
union all
select 1, 'IBM', '0000003004', 8
union all
select 1, 'IBM', '0000003005', 32
union all
select 2, 'DELL', '0000004001', 2
union all
select 2, 'DELL', '0000004002', 4
union all
select 2, 'DELL', '0000004003', 8
union all
select 3, 'DELL', '0000007004', 16
union all
select 2, 'HP', '0000005002', 2
union all
select 2, 'HP', '0000005003', 4
union all
select 2, 'HP', '0000005004', 8
union all
select 2, 'HP', '0000005005', 32
union all
select 3, 'IBM', '0000006001', 2
union all
select 3, 'IBM', '0000006003', 2
union all
select 3, 'IBM', '0000006004', 8
union all
select 3, 'IBM', '0000006005', 32
Go



Here are the conditions that must be met for generating the Machine_X_Code:
1. Start from Current_Code value in the table, Code_Generation_Table.
2. Look for any values between Current_Code and End_Code that don't already exist in
the Machine_X_Code column of the Main_Table for the given Location_Code and
Machine_Brand. That becomes the new Machine_X_Code.
3. Given a scenario like the one below in the table Code_Generation_Table, the following conditions must also be met:
Location_Code, Machine_Brand, Start_Code, Current_Code, End_Code, Increment_Value
1 DELL 1001 1003 1005 1

Scenario 1:

If there is no record in Main_Table for that Machine Brand, then new Machine_X_Code needs to be 0000001003.

Scenario 2:

If Main_Table has records with Machine_X_Codes 0000001003, 0000001004, 0000001005 then the new Machine_X_Code will be 0000001001.

Scenario 3:

If Main_Table has all records with Machine_X_Code between and including 0000001001 to 0000001005 except 0000001002, then the next newly generated Machine_X_Code should be 0000001002.

The requirements having been made clear, let us see how a developer approached this problem. He chose to create a stored procedure (SP), usp_Generate_Machine_X_Code and a user-defined function (UDF), F_Validate_Machine_Code whihc is invoked from within the SP. The SP gives out the new Machine_X_Code. The unnctionality of the code below is explained in more granular detail just before the object definitions below. The code for both is given below.

Code for the user-defined function (UDF), F_Validate_Machine_Code:

--UDF
--This function checks that thet newly generated Machine_X_Code lies between
--the Start_Code and the End_Code. If it does, then the newly generated Machine_X_Code
--represented by the variable @l_Temp_Machine_X_Value is valid. If by chance, the newly
--generated Machine_X_Code lies outside the range of Start_Code and End_Code, then
--the new machine_x_code is initialized to the Start_Code generated Machine_X_Code
--reprsented by the variable, @l_Start_Machine_X_Value. The newely generated
--machine_X_code, the Start_Code and the ENd_Code are inputs to this function.
--A supposedly valid Machine_X_Code is returned by this function. The output however
--is not guarenteed to be valid unless it is re-checked for validity in the SP,
--usp_Generate_Machine_X_Code. If it is found to be invalid, then this
--user-defined function is invoked again and again as need be.

CREATE Function DBO.F_Validate_Machine_Code
(@l_Temp_Machine_X_Value Int,
@l_Start_Machine_X_Value int,
@l_End_Machine_X_Value int
) Returns Integer
AS
Begin
-- This function validates the whether the newly generated Machine_X_Code lies within the Range of Start_Code and End_Code.
If (@l_Temp_Machine_X_Value > @l_Start_Machine_X_Value)
or (@l_Temp_Machine_X_Value < @l_End_Machine_X_Value)
Set @l_Temp_Machine_X_Value = @l_Temp_Machine_X_Value

If (@l_Temp_Machine_X_Value < @l_Start_Machine_X_Value)
or (@l_Temp_Machine_X_Value > @l_End_Machine_X_Value)
Set @l_Temp_Machine_X_Value = @l_Start_Machine_X_Value

Return @l_Temp_Machine_X_Value
End
GO


Code for the stored procedure (SP), usp_Generate_Machine_X_Code:



--This SP's purpose is to generate a new Machine_X_Code. It generates a new
--Machine_X_Code from the Current_Code column of the table, Code_Generation_Table
--while checking that the newly generated Machine_X_Code does not already exist
--in the Machine_X_Code column of the table Main_Table. In addition it call the
--user-defined function F_Validate_Machine_Code to check that a newly generated
--Machine_X_Code lies between Start_Code and End_Code in the table, Code_Generation_Table.
--If the newly generated machin_x_code falls outside that range, then the machine_x_code
--is reset to Start_Code. In this whole process it keeps updating the Current_Code column
--of the table, Code_Generation_Table with the newly generated Machine_X_Code before its
--validation through the function, F_Validate_Machine_Code. It also updates the the
--Current_Code column of the table, Code_Generation_Table with the next possible
--Machine_X_Code value. Looping occurs till a valid Machine_X_Code is found. So,
--effectively there would be as many iterations of the loop as the number of attempts to
--find one good Machine_X_Number.


Create Procedure usp_Generate_Machine_X_Code
@i_Location_Code Smallint,
@i_Machine_Brand VarChar(10),
@o_Machine_X_Code Integer OutPut,
@o_Return_Value Smallint OutPut
As
Begin
Set nocount on
Set @o_Return_Value = 0

Declare @l_Increment_Value Smallint,
@l_Temp_Machine_X_Value Integer,
@l_Next_Machine_X_Value Integer,
@l_Start_Machine_X_Value Int,
@l_End_Machine_X_Value Integer,
@l_First_Machine_X_Value_Taken Integer,
@l_Affected_Rows Smallint,
@l_Execution_String VarChar(2000)

Select @l_Temp_Machine_X_Value = Current_Code
,@l_Increment_Value = Increment_Value
,@l_Start_Machine_X_Value = Start_Code
,@l_End_Machine_X_Value = End_Code
From DBO.Code_Generation_Table With (NoLock)
Where Location_Code = @i_Location_Code
And Machine_Brand = @i_Machine_Brand

Set @l_Temp_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Temp_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

Set @l_First_Machine_X_Value_Taken = @l_Temp_Machine_X_Value

Set @l_Affected_Rows = 0

While @l_Affected_Rows = 0
Begin

Set @l_Next_Machine_X_Value = @l_Temp_Machine_X_Value + @l_Increment_Value

Set @l_Next_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Next_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

Set @l_Execution_String = 'Update A Set A.Current_Code = ' + Cast(@l_Next_Machine_X_Value as VarChar) +
' From Dbo.Code_Generation_Table A '+
+ ' Where Location_Code = ' + Cast(@i_Location_Code AS VarChar) + ' And Machine_Brand = ' + ''''+ @i_Machine_Brand + '''' +
' And Not Exists (Select ' + ''''
+ 'x' + '''' + ' From Main_Table ' +
'Where Location_Code = ' + Cast(@i_Location_Code AS VarChar) +
' AND Machine_Brand = ' + '''' + @i_Machine_Brand + '''' +
' And Machine_X_Code = ' + '''' + Replicate('0',10 - Len(@l_Temp_Machine_X_Value)) + Cast(@l_Temp_Machine_X_Value as Varchar(10)) + '''' + ')'

Exec (@l_Execution_String)

Set @l_Affected_Rows = @@Rowcount

If @l_Affected_Rows = 1
Begin
Select @o_Machine_X_Code = @l_Temp_Machine_X_Value, @o_Return_Value = 1
Break
End

Set @l_Temp_Machine_X_Value = @l_Temp_Machine_X_Value + @l_Increment_Value

Set @l_Temp_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Temp_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

If @l_First_Machine_X_Value_Taken = @l_Temp_Machine_X_Value
Begin
-- For the current day all the Machine X Codes have been used up.
Select @l_Affected_Rows = 2, @o_Return_Value = -1
Break
End

End
Set nocount off
End
GO


Before we proceed further, please familiarize yourself with the logic of the SP and UDF, and the data in the two tables so that we can try to execute his logic. Let us try to generate a new Machine_X_Code for the Location_Code = 1 and Brand_Name = 'Dell'.

TEST 1:
Execute the following:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 1
and Machine_Brand = 'Dell'


The result set is as follows:

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
1 DELL 0000001001 2
1 DELL 0000001002 4
1 DELL 0000001003 8
1 DELL 0000001004 16

So the next generated machine_x_code to has to be 1005. Remember zeroes are suffixed later and that is not so important right now. What is important is generating the new Machine_X_Code that meets all the requirements above.

Now run the following code:

select * from Code_Generation_Table

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
1 DELL 1001 1001 1005 1
1 HP 2001 2003 2005 1
1 IBM 3001 3001 3005 1
2 DELL 4001 4001 4005 1
2 HP 5001 5003 5005 1
2 IBM 8001 8003 8005 1
3 DELL 7001 7001 7005 1
3 IBM 6001 6004 6005 1
3 SUN 6011 6011 6015 1


Since the codes (1001, 1002, 1003 and 1004) already exists in the Main_Table for Location_Code =1 and Machine_Brand = 'Dell', the next one has to be 1005 as it exists in the Code_Generation_Table between Start_Code and End_Code. Let's see if the SP generates this machine code. Execute the following code to execute the SP:

declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 1
,@i_Machine_Brand = 'Dell'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value


Here is the result set:

Machine_Code Return_Value
------------ ------------
1005 1

We got the exact machine code. Now let's check the table Code_Generation_Table for the column Current_Code by executing the following code:

select * from Code_Generation_Table
where Location_Code = 1
and Machine_Brand = 'Dell'

The result set is as follows:

Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
1 DELL 1001 1001 1005 1

You may note that the Current_Code has been updated. It has actually been initialized with the next possible Current_Code because 1005 has been taken by us via execution of the SP. So the next available value can be 1006. But since 1006 is outside the range of 1001 and 1005, the next possible value has to be re-initialized to the start_code which is 1001. So this condition has also been satisfied. Very well.


TEST 2:
Now let's test another condition:

Scenario 1:

For Location_Code = 2 and Machine_Brand = 'IBM', there is no record in the Main_Table. Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

(0 rows affected)

Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 IBM 8001 8003 8005 1

So as per the given condition, the new machine_x_code shoudl be 8003 and the Current_Code column of the table, Code_Generation_Table should get updated with 8004. So let's execute the SP from the developer by executng the following code:


declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 2
,@i_Machine_Brand = 'IBM'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value


Here is the result set:

Machine_Code Return_Value
------------ ------------
8003 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 IBM 8001 8004 8005 1

So the new Machine_X_Number generated in 8003 which is perfect. And the Crrent_Code column has also been updated to the next possible Machine_X_Number which is 8004. So the Scenario 1 has been satisfied.




TEST 3:
Let's check out if Scenario 2 is satisfied:

For Location_Code = 2 and Machine_Brand = 'HP', there are four records in the Main_Table with machine codes (0000005002, 0000005003, 0000005004 and 0000005005). Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 2
and Machine_Brand = 'HP'

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
2 HP 0000005002 2
2 HP 0000005003 4
2 HP 0000005004 8
2 HP 0000005005 32


Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'HP'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 HP 5001 5003 5005 1

So as per the given condition for Scenario 2, the new machine_x_code generated should be 5001 and the Current_Code column of the table, Code_Generation_Table should get updated with 8002. So let's execute the SP from the developer by executng the following code:


declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 2
,@i_Machine_Brand = 'HP'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value


Here is the result set:

Machine_Code Return_Value
------------ ------------
5001 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'HP'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 HP 5001 5002 5005 1

So the new Machine_X_Number generated in 5001 which is perfect. And the Current_Code column has also been updated to the next possible Machine_X_Number which is 5002. The next possible Machine_X_Number should be equal to the output of the SP + Increment_Value and should exists within the range of Start_Code and End_Code. So the Scenario 2 has been satisfied.



TEST 4:
Let's check the Scenario 3, the final scenario:

For Location_Code = 3 and Machine_Brand = 'IBM', there are four records in the Main_Table with machine codes (0000006001, 0000006003, 0000006004 and 0000006005). Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
3 IBM 0000006001 2
3 IBM 0000006003 2
3 IBM 0000006004 8
3 IBM 0000006005 32


Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
3 IBM 6001 6004 6005 1

So as per the given condition for Scenario 3, the new machine_x_code generated should be 6002 and the Current_Code column of the table, Code_Generation_Table should get updated with 6003. So let's execute the SP from the developer by executng the following code:


declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 3
,@i_Machine_Brand = 'IBM'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value


Here is the result set:

Machine_Code Return_Value
------------ ------------
6002 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
3 IBM 6001 6003 6005 1

So the new Machine_X_Number generated in 6002 which was the missing code in Main_Table for Location_Code = 3 and Machine_Brand = 'IBM'. And the Current_Code column has also been updated to the next possible Machine_X_Number which is 6003. The next possible Machine_X_Number should be equal to the output of the SP + Increment_Value and should exist within the range of Start_Code and End_Code. So the Scenario 3 has been satisfied.


The developer has satisifed all the functional requirements. Good job. Now what are the drawbacks in his code? Let's enlist them:

1. He uses an SP and a UDF, two database objects for one purpose. This is a minor drawback though.
2. Looping is being used. In a rare case, it may have just one iteration. But in almost all cases it would have multiple iterations upto a maximum of five iterations. We have a simple set of scenarios here. If the range between Start_Code and Stop_Code runs into several hundreds, the looping will happen those many times most of the time. And the invocation of the UDF would take place double those many times for generating just one new Machine_X_Number.
3. The Update to Code_Generation_Table takes place as many times as the number of iterations which could run into several hundred times. So many UPDATE's just to generate one Machine_X_Number seems too much processing. It should be avoided.
4. The code is accessing the Main_Table as many times as the number of iterations. In our case the Main_Table has just 22 rows. In the real situation, it actually has hundreds of thousands of rows. Why scan that table so many times just to generate one valid Machine_X_Number?
5. The SP performs record-by-record operation or row-by-row. An attempt should be made to perform set based operation for faster and efficient execution. That would also reduce scans of the underlying tables.

These are only a few drawbacks and there are others like usage of dynamic T-SQL which is not needed in this case, etc. We won't go into those detailed aspects in this article. We will concentrate more on logical programming.

Let's try to satisfy the same scenarios after making some refinements to the SP and by eliminating the user-defined function altogether.





A refined version of the SP is given below:

--This SP's purpose is to generate a new Machine_X_Code. It generates a new
--Machine_X_Code from the Current_Code column of the table, Code_Generation_Table
--while checking that the newly generated Machine_X_Code does not already exist
--in the Machine_X_Code column of the table Main_Table. It also checks that a newly
--generated Machine_X_Code lies between Start_Code and End_Code in the table,
--Code_Generation_Table.
--If the newly generated machine_x_code falls outside that range, then the machine_x_code
--is reset to Start_Code. In this whole process it updates the Current_Code column
--of the table, Code_Generation_Table with the newly generated Machine_X_Code once
--or at the most twice. The function, F_Validate_Machine_Code has been eliminated altogether.
--It also updates the the Current_Code column of the table, Code_Generation_Table with
--the next possible Machine_X_Code value. If at all, Looping occurs only twice in a rare case.
--So effectively, there would be no looping except in a scenario where there would be two iterations
--of the code when a new machine_x_code does not exist between the Current_Code and
--the End_Code. In such a scenario, it would check the valid machine codes from Start_Code
--through Current_Code in the second iteration.

Create Procedure usp_Generate_Machine_X_Code
@i_Location_Code Smallint,
@i_Machine_Brand VarChar(10),
@o_Machine_X_Code Integer OutPut,
@o_Return_Value Smallint OutPut
As
Begin
Set nocount on
Set @o_Return_Value = 0
/* WHILE Loop eliminated. Main_Table is now being referenced only once. New code below.
Set @o_Return_Value = 0

Declare @l_Increment_Value Smallint,
@l_Temp_Machine_X_Value Integer,
@l_Next_Machine_X_Value Integer,
@l_Start_Machine_X_Value Int,
@l_End_Machine_X_Value Integer,
@l_First_Machine_X_Value_Taken Integer,
@l_Affected_Rows Smallint,
@l_Execution_String VarChar(2000)

Select @l_Temp_Machine_X_Value = Current_Code
,@l_Increment_Value = Increment_Value
,@l_Start_Machine_X_Value = Start_Code
,@l_End_Machine_X_Value = End_Code
From DBO.Code_Generation_Table With (NoLock)
Where Location_Code = @i_Location_Code
And Machine_Brand = @i_Machine_Brand

Set @l_Temp_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Temp_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

Set @l_First_Machine_X_Value_Taken = @l_Temp_Machine_X_Value

Set @l_Affected_Rows = 0

While @l_Affected_Rows = 0
Begin

Set @l_Next_Machine_X_Value = @l_Temp_Machine_X_Value + @l_Increment_Value

Set @l_Next_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Next_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

Set @l_Execution_String = 'Update A Set A.Current_Code = ' + Cast(@l_Next_Machine_X_Value as VarChar) +
' From Dbo.Code_Generation_Table A '+
+ ' Where Location_Code = ' + Cast(@i_Location_Code AS VarChar) + ' And Machine_Brand = ' + ''''+ @i_Machine_Brand + '''' +
' And Not Exists (Select ' + ''''
+ 'x' + '''' + ' From Main_Table ' +
'Where Location_Code = ' + Cast(@i_Location_Code AS VarChar) +
' AND Machine_Brand = ' + '''' + @i_Machine_Brand + '''' +
' And Machine_X_Code = ' + '''' + Replicate('0',10 - Len(@l_Temp_Machine_X_Value)) + Cast(@l_Temp_Machine_X_Value as Varchar(10)) + '''' + ')'

Exec (@l_Execution_String)

Set @l_Affected_Rows = @@Rowcount

If @l_Affected_Rows = 1
Begin
Select @o_Machine_X_Code = @l_Temp_Machine_X_Value, @o_Return_Value = 1
Break
End

Set @l_Temp_Machine_X_Value = @l_Temp_Machine_X_Value + @l_Increment_Value

Set @l_Temp_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Temp_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

If @l_First_Machine_X_Value_Taken = @l_Temp_Machine_X_Value
Begin
-- For the current day all the Machine_X_Codes have been used up.
Select @l_Affected_Rows = 2, @o_Return_Value = -1
Break
End
*/

declare @l_next_Machine_X_Code int
,@l_Start_Machine_X_Code int
,@l_End_Machine_X_Code int
,@l_Current_Machine_X_Code int
,@l_Increment_Value tinyint

select @l_Start_Machine_X_Code = Start_Code
,@l_End_Machine_X_Code = End_Code
,@l_Increment_Value = Increment_Value
,@l_Current_Machine_X_Code = Current_Code
from Code_Generation_Table
where Location_Code = @i_Location_Code
and Machine_Brand = @i_Machine_Brand

if ((@l_Current_Machine_X_Code < @l_Start_Machine_X_Code) or (@l_Current_Machine_X_Code > @l_End_Machine_X_Code))
begin
Start_Processing:
update Code_Generation_Table
set @l_Current_Machine_X_Code = Current_Code = @l_Start_Machine_X_Code
where Location_Code = @i_Location_Code
and Machine_Brand = @i_Machine_Brand
end

if object_id ('tempdb.dbo.#Temp_Code_Table') is not null
drop table #Temp_Code_Table

create table #Temp_Code_Table
(Existing_Machine_Codes int
,Valid_machine_Codes int
,Serial_No int identity(0,1))


insert into #Temp_Code_Table (Existing_Machine_Codes, Valid_machine_Codes)
select convert(int, Machine_X_Code), @l_Current_Machine_X_Code from dbo.Main_Table
where Location_Code = @i_Location_Code
and Machine_Brand = @i_Machine_Brand
order by Machine_X_Code


if ((select COUNT(1) from #Temp_Code_Table) = 0)
begin
select @l_next_Machine_X_Code = @l_Current_Machine_X_Code
goto Final_Validation
end

update #Temp_Code_Table
set Valid_machine_Codes = Valid_machine_Codes + (Serial_No * @l_Increment_Value)

select @l_next_Machine_X_Code = min(a.Valid_machine_Codes) from #Temp_Code_Table a
where a.Valid_machine_Codes not in (select b.Existing_Machine_Codes from #Temp_Code_Table b)
and a.Valid_machine_Codes between @l_Start_Machine_X_Code and @l_End_Machine_X_Code

if (@l_next_Machine_X_Code is null)
begin
select @l_next_Machine_X_Code = max(Valid_machine_Codes) + @l_Increment_Value from #Temp_Code_Table
end

Final_Validation:
if (@l_next_Machine_X_Code > @l_End_Machine_X_Code)
begin
goto Start_Processing
end

if (@l_next_Machine_X_Code < @l_Start_Machine_X_Code) or (@l_next_Machine_X_Code > @l_End_Machine_X_Code)
begin
select @l_next_Machine_X_Code = @l_Start_Machine_X_Code
,@o_Return_Value = -1
set nocount off
if object_id ('tempdb.dbo.#Temp_Code_Table') is not null
drop table #Temp_Code_Table
return
end


if (@l_next_Machine_X_Code >= @l_Start_Machine_X_Code) and ((@l_next_Machine_X_Code >= @l_Start_Machine_X_Code) and (@l_next_Machine_X_Code <= @l_End_Machine_X_Code))
begin
update Code_Generation_Table with (readpast, rowlock, xlock)
set Current_Code = case
when @l_next_Machine_X_Code = @l_End_Machine_X_Code then @l_Start_Machine_X_Code
else @l_next_Machine_X_Code + @l_Increment_Value
end
where Location_Code = @i_Location_Code
and Machine_Brand = @i_Machine_Brand

if (@@ROWCOUNT <> 0)
begin
select @o_Machine_X_Code = @l_next_Machine_X_Code
,@o_Return_Value = 1
end
else
set @o_Return_Value = -1
end

if object_id ('tempdb.dbo.#Temp_Code_Table') is not null
drop table #Temp_Code_Table

set nocount off
return
End
GO





The new logic in the SP is given in the comments before the Create Procedure statement. The calls to function have been eliminated. This SP checks for valid Machine_X_Code values between Current_Code and End_Code. If not found, it checks for valid Machine_X_Code values between Start_Code and Current_Code. This is because there is a functional resttriction that the search should always start from the Current_Code only.


Let's now start from the beginning by emptynig (truncating) bith the tables, Code_Generation_Table and Main_Table and filling (inserting) the same data as above into them afresh by executing the following code:

truncate table Code_Generation_Table
truncate table Main_Table

Go

insert into Code_Generation_Table
select 1, 'DELL', 1001, 1001, 1005, 1
union all
select 2, 'DELL', 4001, 4001, 4005, 1
union all
select 3, 'DELL', 7001, 7001, 7005, 1
union all
select 1, 'HP', 2001, 2003, 2005, 1
union all
select 2, 'HP', 5001, 5003, 5005, 1
union all
select 1, 'IBM', 3001, 3001, 3005, 1
union all
select 2, 'IBM', 8001, 8003, 8005, 1
union all
select 3, 'IBM', 6001, 6004, 6005, 1
union all
select 3, 'SUN', 6011, 6011, 6015, 1

go

insert into Main_Table (Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs)
select 1, 'DELL', '0000001001', 2
union all
select 1, 'DELL', '0000001002', 4
union all
select 1, 'DELL', '0000001003', 8
union all
select 1, 'DELL', '0000001004', 16
union all
select 1, 'HP', '0000002002', 2
union all
select 1, 'HP', '0000002003', 4
union all
select 1, 'HP', '0000002004', 8
union all
select 1, 'HP', '0000002005', 32
union all
select 1, 'IBM', '0000003003', 2
union all
select 1, 'IBM', '0000003004', 8
union all
select 1, 'IBM', '0000003005', 32
union all
select 2, 'DELL', '0000004001', 2
union all
select 2, 'DELL', '0000004002', 4
union all
select 2, 'DELL', '0000004003', 8
union all
select 3, 'DELL', '0000007004', 16
union all
select 2, 'HP', '0000005002', 2
union all
select 2, 'HP', '0000005003', 4
union all
select 2, 'HP', '0000005004', 8
union all
select 2, 'HP', '0000005005', 32
union all
select 3, 'IBM', '0000006001', 2
union all
select 3, 'IBM', '0000006003', 2
union all
select 3, 'IBM', '0000006004', 8
union all
select 3, 'IBM', '0000006005', 32
Go


Having filled up the data, it's time to test this new version of the SP.

Let us try to generate a new Machine_X_Code for the Location_Code = 1 and Brand_Name = 'Dell'.

TEST 1:
Execute the following:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 1
and Machine_Brand = 'Dell'


The result set is as follows:

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
1 DELL 0000001001 2
1 DELL 0000001002 4
1 DELL 0000001003 8
1 DELL 0000001004 16

So the next generated machine_x_code to has to be 1005. Remember zeroes are suffixed later and that is not so important right now. What is important is generating the new Machine_X_Code that meets all the requirements above.

Now run the following code:

select * from Code_Generation_Table

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
1 DELL 1001 1001 1005 1
1 HP 2001 2003 2005 1
1 IBM 3001 3001 3005 1
2 DELL 4001 4001 4005 1
2 HP 5001 5003 5005 1
2 IBM 8001 8003 8005 1
3 DELL 7001 7001 7005 1
3 IBM 6001 6004 6005 1
3 SUN 6011 6011 6015 1


Since the codes (1001, 1002, 1003 and 1004) already exist in the Main_Table for Location_Code = 1 and Machine_Brand = 'Dell', the next one has to be 1005 as it exists in the Code_Generation_Table between Start_Code and End_Code. Let's see if the SP generates this machine code. Execute the following code to execute the SP:

declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 1
,@i_Machine_Brand = 'Dell'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value


Here is the result set:

Machine_Code Return_Value
------------ ------------
1005 1

We got the exact machine code. Now let's check the table Code_Generation_Table for the column Current_Code by executing the following code:

select * from Code_Generation_Table
where Location_Code = 1
and Machine_Brand = 'Dell'

The result set is as follows:

Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
1 DELL 1001 1001 1005 1

You may note that the Current_Code has been updated. It has actually been initialized with the next possible Current_Code because 1005 has been taken by us via execution of the SP. So the next available value can be 1006. But since 1006 is outside the range of 1001 and 1005, the next possible value has to be re-initialized to the start_code which is 1001. So this condition has also been satisfied by the new version of the SP.


TEST 2:
Now let's test another condition:

Scenario 1:

For Location_Code = 2 and Machine_Brand = 'IBM', there is no record in the Main_Table. Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

(0 rows affected)

Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 IBM 8001 8003 8005 1

So as per the given condition, the new machine_x_code shoudl be 8003 and the Current_Code column of the table, Code_Generation_Table should get updated with 8004. So let's execute the SP from the developer by executng the following code:


declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 2
,@i_Machine_Brand = 'IBM'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value


Here is the result set:

Machine_Code Return_Value
------------ ------------
8003 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 IBM 8001 8004 8005 1

So the new Machine_X_Number generated in 8003 which is perfect. And the Crrent_Code column has also been updated to the next possible Machine_X_Number which is 8004. So the Scenario 1 has been satisfied by the new SP.




TEST 3:
Let's check out if Scenario 2 is satisfied:

For Location_Code = 2 and Machine_Brand = 'HP', there are four records in the Main_Table with machine codes (0000005002, 0000005003, 0000005004 and 0000005005). Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 2
and Machine_Brand = 'HP'

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
2 HP 0000005002 2
2 HP 0000005003 4
2 HP 0000005004 8
2 HP 0000005005 32


Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'HP'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 HP 5001 5003 5005 1

So as per the given condition for Scenario 2, the new machine_x_code generated should be 5001 and the Current_Code column of the table, Code_Generation_Table should get updated with 8002. So let's execute the SP from the developer by executng the following code:


declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 2
,@i_Machine_Brand = 'HP'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value


Here is the result set:

Machine_Code Return_Value
------------ ------------
5001 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'HP'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 HP 5001 5002 5005 1

So the new Machine_X_Number generated in 5001 which is perfect. And the Current_Code column has also been updated to the next possible Machine_X_Number which is 5002. The next possible Machine_X_Number should be equal to the output of the SP + Increment_Value and should exists within the range of Start_Code and End_Code. So the Scenario 2 has been satisfied by the new SP.



TEST 4:
Let's check the Scenario 3, the final scenario:

For Location_Code = 3 and Machine_Brand = 'IBM', there are four records in the Main_Table with machine codes (0000006001, 0000006003, 0000006004 and 0000006005). Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
3 IBM 0000006001 2
3 IBM 0000006003 2
3 IBM 0000006004 8
3 IBM 0000006005 32


Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
3 IBM 6001 6004 6005 1

So as per the given condition for Scenario 3, the new machine_x_code generated should be 6002 and the Current_Code column of the table, Code_Generation_Table should get updated with 6003. So let's execute the SP from the developer by executng the following code:


declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 3
,@i_Machine_Brand = 'IBM'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value


Here is the result set:

Machine_Code Return_Value
------------ ------------
6002 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
3 IBM 6001 6003 6005 1

So the new Machine_X_Number generated in 6002 which was the missing code in Main_Table for Location_Code = 3 and Machine_Brand = 'IBM'. And the Current_Code column has also been updated to the next possible Machine_X_Number which is 6003. The next possible Machine_X_Number should be equal to the output of the SP + Increment_Value and should exist within the range of Start_Code and End_Code. So the Scenario 3 has been satisfied by the new SP.


We too have satisifed all the functional requirements with less scan of the tables Main_Table and Code_Generation_Table. We are not looping except in one scenario where we loop only twice at the most. No matter what the range of values between Start_Code and End_COde, we would scan any table once or at the most twice. Update to Code_Generation_Table also happenns only two times at the most. So the new code can handle larger volumes of data than the original code.

There is another method of optimiznig this SP further whereby there is only once scan of any table. May be in my next article, I would write about it as I am quite tired today. I feel any SP can be optimized no matter how optimized it already is. The dead end is always too distant. I hope you have enjoyed the article. Please let me know of your feedback which is always very welcome.

No comments: