Optimizing T-SQL where an array would be nice
- by Polatrite
Alright, first you'll need to grab a barf bag. I've been tasked with optimizing several old stored procedures in our database. This SP does the following:
1) cursor loops through a series of "buildings"
2) cursor loops through a week, Sunday-Saturday
3) has a huge set of IF blocks that are responsible for counting how many Objects of what Types are present in a given building
Essentially what you'll see in this code block is that, if there are 5 objects of type #2, it will increment @Type_2_Objects_5 by 1.
IF @Number_Type_1_Objects = 0
BEGIN
SET @Type_1_Objects_0 = @Type_1_Objects_0 + 1
END
IF @Number_Type_1_Objects = 1
BEGIN
SET @Type_1_Objects_1 = @Type_1_Objects_1 + 1
END
IF @Number_Type_1_Objects = 2
BEGIN
SET @Type_1_Objects_2 = @Type_1_Objects_2 + 1
END
IF @Number_Type_1_Objects = 3
BEGIN
SET @Type_1_Objects_3 = @Type_1_Objects_3 + 1
END
[... Objects_4 through Objects_20 for Type_1]
IF @Number_Type_2_Objects = 0
BEGIN
SET @Type_2_Objects_0 = @Type_2_Objects_0 + 1
END
IF @Number_Type_2_Objects = 1
BEGIN
SET @Type_2_Objects_1 = @Type_2_Objects_1 + 1
END
IF @Number_Type_2_Objects = 2
BEGIN
SET @Type_2_Objects_2 = @Type_2_Objects_2 + 1
END
IF @Number_Type_2_Objects = 3
BEGIN
SET @Type_2_Objects_3 = @Type_2_Objects_3 + 1
END
[... Objects_4 through Objects_20 for Type_2]
In addition to being extremely hacky (and limited to a quantity of 20 objects), it seems like a terrible way of handling this. In a traditional language, this could easily be solved with a 2-dimensional array...
objects[type][quantity] += 1;
I'm a T-SQL novice, but since writing stored procedures often uses a lot of temporary tables (which could essentially be a 2-dimensional array) I was wondering if someone could illuminate a better way of handling a situation like this with two dynamic pieces of data to store.
Requested in comments:
The columns are simply Number_Type_1_Objects, Number_Type_2_Objects, Number_Type_3_Objects, Number_Type_4_Objects, Number_Type_5_Objects, and CurrentDateTime. Each row in the table represents 5 minutes.
The expected output is to figure out what percentage of time a given quantity of objects is present throughout each day.
Sunday - Object Type 1
0 objects - 69 rows, 5:45, 34.85%
1 object - 85 rows, 7:05, 42.93%
2 objects - 44 rows, 3:40, 22.22%
On Sunday, there were 0 objects of type 1 for 34.85% of the day. There was 1 object for 42.93% of the day, and 2 objects for 22.22% of the day. Repeat for each object type.
