Databases
StackOverflow2013
Postsdbo
POFastest performing way to JOIN a temp table to multiple columns in T-SQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFastest performing way to JOIN a temp table to multiple columns in T-SQL
    primarykey
    data
    text
    <p>I need to write a SQL query to filter a table on 4 potential columns from another table of the filter values. </p> <p>Example of what I am trying to do: </p> <pre><code>-- This table could have anywhere from 1 to 2000 possible rows DECLARE @WidgetOwners TABLE (OwnerID INT PRIMARY KEY) INSERT INTO @WidgetOwners VALUES (5) INSERT INTO @WidgetOwners VALUES (50) INSERT INTO @WidgetOwners VALUES (111) INSERT INTO @WidgetOwners VALUES (12345) INSERT INTO @WidgetOwners VALUES (6) --etc... SELECT w.WidgetID FROM Widgets w WHERE w.SellerManagerID IN (SELECT o.OwnerID FROM @WidgetOwners) OR w.SellerID IN (SELECT o.OwnerID FROM @WidgetOwners) OR w.BuyerManagerID IN (SELECT o.OwnerID FROM @WidgetOwners) OR w.BuyerID IN (SELECT o.OwnerID FROM @WidgetOwners) </code></pre> <p>I am under the impression that the SUB SELECTS in the query above will not perform very well. Is there a better way to do this? Could this be done by LEFT JOINING the @WidgetOwner table multiple times? Does anyone have any recommendations here? </p> <p>Keep in mind that I am stuck with SQL 2000 right now and cannot upgrade at the moment.</p> <p><strong>EDIT - Example 2 (This is something else I am trying)</strong></p> <pre><code>SELECT w2.WidgetID, w2.* -- etc FROM ( SELECT w.WidgetID FROM Widgets w INNER JOIN @WidgetOwners o ON w.SellerManagerID = o.OwnerID UNION SELECT w.WidgetID FROM Widgets w INNER JOIN @WidgetOwners o ON w.SellerID = o.OwnerID UNION SELECT w.WidgetID FROM Widgets w INNER JOIN @WidgetOwners o ON w.BuyerManagerID = o.OwnerID UNION SELECT w.WidgetID FROM Widgets w INNER JOIN @WidgetOwners o ON w.BuyerID = o.OwnerID ) x INNER JOIN Widgets w2 ON x.WidgetID = w2.WidgetID </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJon
    1. USJon
    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
    1. VO
      singulars
      1. POFastest performing way to JOIN a temp table to multiple columns in T-SQL
      1. USAndriy M
      1. VTFavorite
    1. COOne thing to bear in mind is that table variables do not have statistics maintained and the Query Optimiser always assumes they will contain one row. How many rows are in `Widgets`? i.e. what proportion of rows will typically be returned by the query?
      singulars
      1. POFastest performing way to JOIN a temp table to multiple columns in T-SQL
      1. USMartin Smith
    2. COApproximately 2 million rows in Widgets, but it grows by a few thousand every day. Would a temp table be better?
      singulars
      1. POFastest performing way to JOIN a temp table to multiple columns in T-SQL
      1. USJon
    3. COWorth trying and looking at the plans for both the case where the table has 1 row and where it has 2,000 rows. Are the 4 columns indexed?
      singulars
      1. POFastest performing way to JOIN a temp table to multiple columns in T-SQL
      1. USMartin Smith
 

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