getting rid of "OR"

Question

getting rid of "OR"

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797
More actions
February 10, 2020 at 5:00 pm

#3724428

I have a statement that has a where clause that looks like this
WHERE iv.AccountCode = @AccountCode
AND iv.ItemID = @ItemID
AND iv.VarianceID = @VarianceID
AND (
Active <> @Active
OR SKU <> @SKU
OR Position <> @Position
OR MilliLitres <> @MilliLitres
OR FluidOunces <> @FluidOunces
OR Grams <> @Grams
OR Ounces <> @Ounces
OR Calories <> @Calories
OR KiloJoules <> @KiloJoules
OR TotalFat <> @TotalFat
OR SaturatedFat <> @SaturatedFat
OR Carbohydrates <> @Carbohydrates
OR NaturalSugar <> @NaturalSugar
OR AddedSugar <> @AddedSugar
OR TotalSugar <> @TotalSugar
OR Fiber <> @Fiber
OR Protein <> @Protein
OR Salt <> @Salt
OR Caffine <> @Caffine
);
anyone got any ideas on how to make this a tiny bit better - we have indexes in place, but i'm looking for better solutions

MVDBA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

Here's a way of writing it without ORs, but I'm not sure it's any better:

WHERE iv.AccountCode = @AccountCode
  AND iv.ItemID = @ItemID
  AND iv.VarianceID = @VarianceID
  AND NOT (
                  Active = @Active
              AND SKU = @SKU
              AND Position = @Position
              AND MilliLitres = @MilliLitres
              AND FluidOunces = @FluidOunces
              AND Grams = @Grams
              AND Ounces = @Ounces
              AND Calories = @Calories
              AND KiloJoules = @KiloJoules
              AND TotalFat = @TotalFat
              AND SaturatedFat = @SaturatedFat
              AND Carbohydrates = @Carbohydrates
              AND NaturalSugar = @NaturalSugar
              AND AddedSugar = @AddedSugar
              AND TotalSugar = @TotalSugar
              AND Fiber = @Fiber
              AND Protein = @Protein
              AND Salt = @Salt
              AND Caffine = @Caffine
          );

you might have a bit more success with this:

SELECT *
  FROM iv
 WHERE iv.AccountCode = @AccountCode
   AND iv.ItemID = @ItemID
   AND iv.VarianceID = @VarianceID
EXCEPT
SELECT *
  FROM iv
 WHERE iv.AccountCode = @AccountCode
   AND iv.ItemID = @ItemID
   AND iv.VarianceID = @VarianceID
   AND Active = @Active
   AND SKU = @SKU
   AND Position = @Position
   AND MilliLitres = @MilliLitres
   AND FluidOunces = @FluidOunces
   AND Grams = @Grams
   AND Ounces = @Ounces
   AND Calories = @Calories
   AND KiloJoules = @KiloJoules
   AND TotalFat = @TotalFat
   AND SaturatedFat = @SaturatedFat
   AND Carbohydrates = @Carbohydrates
   AND NaturalSugar = @NaturalSugar
   AND AddedSugar = @AddedSugar
   AND TotalSugar = @TotalSugar
   AND Fiber = @Fiber
   AND Protein = @Protein
   AND Salt = @Salt
   AND Caffine = @Caffine

This reply was modified 4 years, 9 months ago by Jonathan AC Roberts. Reason: Added EXCEPT

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

I think creating a table and using EXCEPT / INTERSECT would perform better than a long string of ORs. Either would also take care of NULL values for you. So, maybe try something along this line:


--setup code, prior to main query
CREATE TABLE #comparison_values (
Active <data_type> NULL,
SKU <data_type> NULL,
Position <data_type> NULL,
MilliLitres <data_type> NULL, ...
FluidOunces
Grams
Ounces
Calories
KiloJoules
TotalFat
SaturatedFat
Carbohydrates
NaturalSugar
AddedSugar
TotalSugar
Fiber
Protein
Salt
Caffine
)

INSERT INTO #comparison_values
SELECT 
    @Active, @SKU, @Position, @MilliLitres,
    @FluidOunces, @Grams, @Ounces, @Calories,
    @KiloJoules, @TotalFat, @SaturatedFat, @Carbohydrates,
    @NaturalSugar, @AddedSugar, @TotalSugar, @Fiber,
    @Protein, @Salt, @Caffine

------------------------------------------------------------------------------------------
--back to the main query code for mods there

WHERE iv.AccountCode = @AccountCode
AND iv.ItemID = @ItemID
AND iv.VarianceID = @VarianceID

AND EXISTS(
    SELECT 
        Active, SKU, Position, MilliLitres,
        FluidOunces, Grams, Ounces, Calories,
        KiloJoules, TotalFat, SaturatedFat, Carbohydrates,
        NaturalSugar, AddedSugar, TotalSugar, Fiber,
        Protein, Salt, Caffine
    EXCEPT
    SELECT 
        *
    FROM #comparison_values
    )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

I'd also vote for the temp table option. Makes the code cleaner.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

i'm going to try all the suggestions and see what works (when my hands get warm it's still snowing here)

MVDBA