Efficient way to select multiple or all records from a table

Question

I have a query optimization problem. Say there is a table in which there are all invoices. Using TVP (Table Valued Parameter) I would like to select multiple records by providing 1..n identifiers or return all records by providing a single identifier with a value of -1.

    DECLARE @InvoiceIdSet AS dbo.TBIGINT;
    INSERT INTO @InvoiceIdSet VALUES (1),(2),(3),(4)
    --INSERT INTO @InvoiceIdSet VALUES (-1)

    SELECT TOP 100
            I.Id ,
            Number ,
            DueDate ,
            IssuedDate ,
            Amount ,
            Test3
    FROM    dbo.Invoices I
    --WHERE   EXISTS ( SELECT NULL
    --                 FROM   @InvoiceIdSet
    --                 WHERE  I.Id = ID
    --                        OR ID = -1 )
    --CROSS APPLY @InvoiceIdSet s WHERE i.Id = s.ID OR s.ID = -1
    JOIN @InvoiceIdSet S ON S.ID = I.Id OR S.ID=-1

Regardless of which selection method I use, the query is fairly efficient until I start using the OR operator, and at that moment it starts to take a very long time to return a few records, but all records are returned very quickly.

Any pointers and suggestions would be highly appreciated.

The first plan is without OR, the second is with OR.

UPDATE: , .

UserDefinedTableType, :

CREATE TYPE [dbo].[TBIGINT] AS TABLE(
[ID] [bigint] NOT NULL PRIMARY KEY CLUSTERED
)

select :

SELECT TOP 100
    I.Id ,
    Number ,
    DueDate ,
    IssuedDate ,
    Amount ,
    Test3
FROM    dbo.Invoices I
WHERE   I.ID IN ( SELECT    S.ID
              FROM      @InvoiceIdSet S
              WHERE     S.ID <> -1
              UNION ALL
              SELECT    S.ID
              FROM      dbo.Invoices S
              WHERE     EXISTS ( SELECT NULL
                                 FROM   @InvoiceIdSet
                                 WHERE  ID = -1 ) )

, , ( ) ( ).

, 1M.

, .

.

Answer 1

:

DECLARE @InvoiceIdSet AS TBIGINT
--INSERT  INTO @InvoiceIdSet
--VALUES  ( 1 ),
--        ( 2 ),
--        ( 3 ),
--        ( 4 )
INSERT  INTO @InvoiceIdSet VALUES  ( -1 )

SELECT TOP 100
        I.Id ,
        Number ,
        DueDate ,
        IssuedDate ,
        Amount ,
        Test3
FROM    dbo.Invoices I
WHERE   I.ID IN ( SELECT    S.ID
                  FROM      @InvoiceIdSet S
                  WHERE     NOT EXISTS ( SELECT NULL
                                         FROM   @InvoiceIdSet
                                         WHERE  ID = -1 )
                  UNION ALL
                  SELECT    S.ID
                  FROM      dbo.Invoices S
                  WHERE     EXISTS ( SELECT NULL
                                     FROM   @InvoiceIdSet
                                     WHERE  ID = -1 ) )

.

Answer 2

or S.ID=-1, SQL Server , ; "", . , SQL Server . 2 (, -1 , , ). , SQL Server 2 , , . ( RECOMPILE , ). . , 2 , , , , , SQL-.

Answer 3

- .

DECLARE @InvoiceIdSet AS dbo.TBIGINT;
INSERT INTO @InvoiceIdSet VALUES (1),(2),(3),(4)
--INSERT INTO @InvoiceIdSet VALUES (-1);

DECLARE @SqlStatement NVARCHAR(MAX), @Params NVARCHAR(MAX);

SET @SqlStatement = 
N'    SELECT TOP 100
            inv.Id ,
            inv.Number ,
            inv.DueDate ,
            inv.IssuedDate ,
            inv.Amount ,
            inv.Test3
    FROM    dbo.Invoices inv
' + CASE 
        WHEN EXISTS(SELECT * FROM @InvoiceIdSet i WHERE i.ID = -1) 
        THEN '' 
        ELSE 'WHERE inv.ID IN (SELECT i.ID FROM @pInvoiceIdSet i)'
    END;

SET @Params = N'@pInvoiceIdSet dbo.TBIGINT READONLY';

EXEC sp_executesql @SqlStatement, @Params, @pInvoiceIdSet = @InvoiceIdSet;

Answer 4

It looks like you are doing the trick here, providing minus 1 as a parameter as a way to get everything.

I assume this is in Stored Proc or something like that, so maybe put zero as a parameter in this script and try the following:

DECLARE @IDparam int

SELECT TOP 100
            I.Id ,
            Number ,
            DueDate ,
            IssuedDate ,
            Amount ,
            Test3
    FROM    dbo.Invoices I
    JOIN @InvoiceIdSet S ON S.ID = I.Id AND COALESCE(@IDparam, I.Id) = I.Id

If @IDParamnull, it will use I.Idin the where clause. May speed it up.