Bad filtering
-
Hi guys im new here but i tough i would share this example of code i encountered a couple months back in one class
Select field1,field2,field3
From table1
Where type="EDU"the only fucntion using this result set was coded like this
If(field1=cond1 and field2=con4 and Active=true)
do something
else If(field1=cond1 and field2=con2 and Active=true)
do something
else If(field3=cond10 and field2=con2 and Active=true)
do something elsethis went on for like 9 time :wtf: now someone added another If lower down in the code but forgot the Active=true hense causing a bug ! now after searching to make sure that the Selqct wasent used anywhere else(the name was a dead give away but you never know) First off
Select field1,field2,field3
From table1
Where type="EDU" AND Active=TruePLEASE add this AND ... just reducing the result set will bost this application speed has its database was Huge as for the rest we where not allowed to modify working code but come on 9 inbricked iff with some repeating coditions ....
-
Hi guys im new here but i tough i would share this example of code i encountered a couple months back in one class
Select field1,field2,field3
From table1
Where type="EDU"the only fucntion using this result set was coded like this
If(field1=cond1 and field2=con4 and Active=true)
do something
else If(field1=cond1 and field2=con2 and Active=true)
do something
else If(field3=cond10 and field2=con2 and Active=true)
do something elsethis went on for like 9 time :wtf: now someone added another If lower down in the code but forgot the Active=true hense causing a bug ! now after searching to make sure that the Selqct wasent used anywhere else(the name was a dead give away but you never know) First off
Select field1,field2,field3
From table1
Where type="EDU" AND Active=TruePLEASE add this AND ... just reducing the result set will bost this application speed has its database was Huge as for the rest we where not allowed to modify working code but come on 9 inbricked iff with some repeating coditions ....
-
Next time, B positive.
-
Hi guys im new here but i tough i would share this example of code i encountered a couple months back in one class
Select field1,field2,field3
From table1
Where type="EDU"the only fucntion using this result set was coded like this
If(field1=cond1 and field2=con4 and Active=true)
do something
else If(field1=cond1 and field2=con2 and Active=true)
do something
else If(field3=cond10 and field2=con2 and Active=true)
do something elsethis went on for like 9 time :wtf: now someone added another If lower down in the code but forgot the Active=true hense causing a bug ! now after searching to make sure that the Selqct wasent used anywhere else(the name was a dead give away but you never know) First off
Select field1,field2,field3
From table1
Where type="EDU" AND Active=TruePLEASE add this AND ... just reducing the result set will bost this application speed has its database was Huge as for the rest we where not allowed to modify working code but come on 9 inbricked iff with some repeating coditions ....
Jtai wrote:
Select field1,field2,field3
From table1
Where type="EDU"When I read the code, I thought it was a pity that Active wasn't in the DB so you could restrict the number of lines based on Active. Then I thought, don't even run the logic if Active isn't true. As I continued reading, I realized the real condition. Did you drop the Active check in the code along with modifying the select? If it's still a large # of rows being read, you might want to include your condition checks in the selection criteria. Also, hopefully, you are really running a sproc to get your data. Here's an example of using 4 condition checks with very different selection criteria. If two condition checks overlap then the data produced would overlap. The table had 5K rows in it, the selection had 39 rows in it.
declare @tbl table (Cond int, sTotID int, stot1 int, stot2 int, stot3 int)
insert into @tbl
values (1,-1, 20, -1, 49), (2, 203148, -1, -1, -1), (3, -1, 15, 29, -1), (4, -1, 25, 32, -1)
SELECT TOP 1000 Cond, TotID
,NumbRecs
,tot1
,tot2
,tot3
FROM dbo.ThreeNumberSum
JOIN @tbl on (sTotID = -1 or sTotID = TotID) AND (stot1 = -1 OR stot1 = tot1)
AND (stot2 = -1 OR stot2 = tot2) AND (stot3 = -1 OR stot3 = tot3)Your code could just check 1 value to match all the multiple conditions. Here is an extract of all the rows:
Cond TotID NumbRecs tot1 tot2 tot3
1 202749 32 20 27 49
... middle number 28-30
1 203149 208 20 31 49
1 203249 184 20 32 49
1 203349 128 20 33 49
1 203449 56 20 34 49
1 203549 8 20 35 49
1 203649 8 20 36 49
2 203148 424 20 31 48
3 152940 24 15 29 40
3 152941 40 15 29 41
3 152942 32 15 29 42
3 152943 48 15 29 43
3 152944 8 15 29 44
3 152945 8 15 29 45
4 253228 28956 25 32 28
4 253229 49698 25 32 29
... last number 30-35
4 253236 595694 25 32 36
4 253237 529858 25 32 37
... last number 38-48
4 253249 8 25 32 49