Databases
StackOverflow2013
Postsdbo
POEfficient way to select few or all records from a table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEfficient way to select few or all records from a table
    primarykey
    data
    text
    <p>I have a query optimization problem. Let's say there is a table which has all the invoices. Using a TVP (Table Valued Parameter) I'd like to select few records by providing 1..n ids, or return all records by providing a single id with the value of -1.</p> <pre><code> 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 </code></pre> <p>Regardless of which method of selection I use, the query performs quite efficiently, until I start using the OR operator, at which point it starts taking a very long time to return few records, but all records are being returned real fast.</p> <p>Any pointers and suggestions will be highly appreciated.</p> <p><img src="https://i.stack.imgur.com/icclZ.png" alt="Without OR"></p> <p><img src="https://i.stack.imgur.com/Nk4Uo.png" alt="With OR"></p> <p>The first plan is without OR, the second is with OR. </p> <p><strong>UPDATE:</strong> After fiddling with different options, I have arrived at this solution as the fastest performing, regardless of number of parameters.</p> <p>First change the UserDefinedTableType to include a primary key index:</p> <pre><code>CREATE TYPE [dbo].[TBIGINT] AS TABLE( [ID] [bigint] NOT NULL PRIMARY KEY CLUSTERED ) </code></pre> <p>The select statement now looks like this:</p> <pre><code>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 &lt;&gt; -1 UNION ALL SELECT S.ID FROM dbo.Invoices S WHERE EXISTS ( SELECT NULL FROM @InvoiceIdSet WHERE ID = -1 ) ) </code></pre> <p>The plans got much bigger, but performance is almost constant, between few (first plan) and all (second plan) records.</p> <p><img src="https://i.stack.imgur.com/I4oDi.png" alt="Few Records"></p> <p><img src="https://i.stack.imgur.com/YbUlj.png" alt="All Records"></p> <p>As you can see the plans are now identical and return the required records in less than a second from 1M rows.</p> <p>I'd love to hear what the community thinks about this solution.</p> <p>Thanks everyone for your help.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDarek
    1. USDarek
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POEfficient way to select few or all records from a table
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POEfficient way to select few or all records from a table
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POEfficient way to select few or all records from a table
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODo an explain, and see what those ORs are causing.
      singulars
      1. POEfficient way to select few or all records from a table
      1. USAndrew
    2. COAlready did. No clue.
      singulars
      1. POEfficient way to select few or all records from a table
      1. USDarek
    3. COWhy do you have ID = -1 separated? Can the Invoices table have an ID of -1? All of your chpices are going to result in a cartesian join, which I don't think is what you want.
      singulars
      1. POEfficient way to select few or all records from a table
      1. USAndrew
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload