CODEWITHSUNDEEP
sqlsql-serveroptimizationquery-optimization
Afnan Ahmad
Afnan Ahmad

Reputation: 2542

How to optimize a query with multiple OR, AND, IN statements?

I have a an SQL query as below:

    Declare @ConnectionType int = 5, 
    @UserId int = 2

    select * from CallDetails 
    Where ((@ConnectionType = 0 AND CallDetails.DeviceType IN (0,1,2,3,4,5,7,8))
            OR (@ConnectionType = 1 AND CallDetails.DeviceType = 4)
            OR (@ConnectionType = 2 AND CallDetails.DeviceType IN (0,1,2,3,7))
            OR (@ConnectionType = 3 AND CallDetails.DeviceType = 5)
            OR (@ConnectionType = 4 AND CallDetails.DeviceType IN (0,1,2,3,4,7))
            OR (@ConnectionType = 5 AND CallDetails.DeviceType IN (4,5))
            OR (@ConnectionType = 6 AND CallDetails.DeviceType IN (0,1,2,3,5,7))
            OR (@ConnectionType = 7 AND CallDetails.DeviceType IN (8))
            OR (@ConnectionType = 8 AND CallDetails.DeviceType IN (0,1,2,3,7,8))
            OR (@ConnectionType = 9 AND CallDetails.DeviceType IN (5,8))
            OR (@ConnectionType = 10 AND CallDetails.DeviceType IN (4,8))
            OR (@ConnectionType = 11 AND CallDetails.DeviceType IN (0,1,2,3,4,8))
            OR (@ConnectionType = 12 AND CallDetails.DeviceType IN (0,1,2,3,5,8))
            OR (@ConnectionType = 13 AND CallDetails.DeviceType IN (4,5,8)) 
            OR (@ConnectionType = 14 AND CallDetails.DeviceType IN (0,1,2,3,7,4,5))
            OR @ConnectionType IS NULL)

Another portion of the query is: 

            AND (@UserId IS NULL OR @ConnectionType IN (1,3,5,7,9,10,13)

            OR (@ConnectionType = 0 AND (CallDetails.DeviceType IN (4,5,8) OR (CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 2 AND ((CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 4 AND (CallDetails.DeviceType = 4 OR (CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 6 AND (CallDetails.DeviceType = 5 OR (CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 8 AND (CallDetails.DeviceType = 8 OR (CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 11 AND (CallDetails.DeviceType IN (4,8) OR (CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 12 AND (CallDetails.DeviceType IN (5,8) OR (CallDetails.UserId = @UserId))) 
            OR (@ConnectionType = 14 AND (CallDetails.DeviceType IN (4,5) OR (CallDetails.UserId = @UserId)))
        )

@ConnectionType is a combination of Multiple devices and at this basis DeviceType will be decided. Once in future any other device will be added @ConnectionType combinations will be increased and so on. This query is being used in multiple Store Procedures too. How can I optimize this query ?

Upvotes: 2

Views: 2182

Answers (3)

droebi
droebi

Reputation: 944

You have several options to optimize: 1. add indices on the columns of your where clause, this should speed up your query 2. create a helper table (association table) with ConnectionType, DeviceType and join it 3. adjust your stored procedure and use a dynamic query, like this:

DECLARE @ConnectionType INT = 0,
   @UserId INT = 2,
   @DeviceTypeString NVARCHAR(100) = NULL

IF @ConnectionType = 0
   SET @DeviceTypeString = N'0,1,2,3,4,5,7,8'

IF @ConnectionType = 1
    SET @DeviceTypeString = N'4'

IF @ConnectionType = 2
   SET @DeviceTypeString = N'0,1,2,3,7'

IF @ConnectionType = 3
    SET @DeviceTypeString = N'5'

IF @ConnectionType = 4
    SET @DeviceTypeString = N'0,1,2,3,4,7'

IF @ConnectionType = 5
    SET @DeviceTypeString = N'4,5'

IF @ConnectionType = 6
    SET @DeviceTypeString = N'0,1,2,3,5,7'

IF @ConnectionType = 7
    SET @DeviceTypeString = N'8'

IF @ConnectionType = 8
    SET @DeviceTypeString = N'0,1,2,3,7,8'

EXEC sp_executesql N'SELECT * FROM CallDetails AS cd WHERE (@1 IS NULL OR @1 = @1) AND (cd.DeviceType IN (SELECT [Value] FROM STRING_SPLIT(@2, '','')) OR cd.UserID = @3)',
    N'@1 INT, @2 NVARCHAR(100), @3 INT',
    @ConnectionType,
    @DeviceTypeString,
    @UserID

I do not want to be stubborn and have adapted my example to work with sp_executesql ;-)

Upvotes: 1

Thom A
Thom A

Reputation: 95567

As I mentioned, this looks like you would be better off with a lookup table. Your table could look as simple as this (note I add no indexes, foreign key constraints, etc, but you will likely want/need them):

CREATE TABLE dbo.ConnectionLookup (ConnectionType int,
                                   DeviceType int);

INSERT INTO dbo.ConnectionLookup (ConnectionType,
                                  DeviceType)
VALUES(0,0),
      (0,1),
      (0,2),
      (0,3),
      (0,4),
      (0,5),
      (0,7),
      (0,8),
      (1,4),
      ...
      (14,0),
      (14,1),
      (14,2),
      (14,3),
      (14,4),
      (14,5),
      (14,7);

Then, instead, you can perform a JOIN on the lookup table:

DECLARE @ConnectionType int = 5;
        --@UserId int = 2; --Commented out, as never used.

SELECT {Your Columns}
FROM dbo.CallDetails CD
     JOIN dbo.ConnectionLookup CL On CD.DeviceType = CL.DeviceType
WHERE CL.ConnectionType = @ConnectionType;

This is, however, a bit of a guess, as there is a lack of sample data and expected results but should (none the less) get you on the right path

Upvotes: 3

Afshin Rashidi
Afshin Rashidi

Reputation: 353

You can create a table with two columns(ConnectionType, DeviceType) that have multiple records for each connection types. Then join call details with this table.

Upvotes: 0

Related Questions