SQL JOIN with two or more tables as output - the most efficient way?
I have a SQL query that executes a LEFT JOIN on another table and then prints out all the results that might be associated with the assigned table. Then I have a second SQL query that runs the LEFT JOIN again and then outputs results that cannot be linked to the specified table. In code, it is something like:
INSERT INTO coupledrecords
SELECT b.col1, b.col2... s.col1, s.col2... FROM bigtable AS b
LEFT JOIN smallertable AS s
ON criterium
WHERE s.col1 IS NOT NULL
INSERT INTO notcoupledrecords
SELECT b.col1, b.col2... bigtable AS b
LEFT JOIN smallertable AS s
ON criterium
WHERE s.col1 IS NULL
My question is: I need to do a JOIN twice to achieve what I want. I have a feeling that this is twice as slow as it could be. Is this true, and if so, is there a way to do it more efficiently?
a source to share
If you are inserting different results into two different tables, you will need 2 different queries.
The only thing I'd like to suggest is that the "linkedrecords" query can just be an INNER JOIN:
INSERT INTO coupledrecords
SELECT b.col1, b.col2... s.col1, s.col2... FROM bigtable AS b
INNER JOIN smallertable AS s
ON criterium
If you are inserting into a SAME table with a field indicating whether it was a matched record or not, then yes, you can do it as a single query.
a source to share
In one case, I think you could do this to create a partitioned view with a check constraint on a column indicating a relationship / unrelated. Then insert into the view and let SQL Server define the target table. Not suggesting that you do this, just thought I was mentioning this as an opportunity!
INSERT INTO coupledrecordsView
SELECT case WHEN s.col1 IS NULL THEN 1 ELSE 0 END AS IsCoupled,
b.col1, b.col2... s.col1, s.col2... FROM bigtable AS b
LEFT JOIN smallertable AS s
ON criterium
a source to share
If you can change the clustered indexes coupledrecords
and notcoupledrecords
to contain one column from the smalltable (including the computed bit column binding at the end of each clustered index, solely for this purpose - see @ Martin Smith's answer above for details), then you can use Partitioned View to insert ... This is an easy opportunity.
If this is not possible, you can also try the non-partitioning solution. See below - this is more active participation.
Not knowing how your data is distributed (e.g. row size, number of columns with zero or non-empty value), difficult to link with unbound), it's hard to recommend a general solution, but one solution that can work in most cases is using views to simulate connected and decoupled tables on top of one table " maybecoupled
". Using views means your existing request code (other than the insert) won't change.
This seems terribly inefficient at first glance, but remember that nulls take up zero storage space, and with the correct indexes, SQL won't spend a lot of time filtering "different view" rows.
This is how it works:
- Insert all records into base table (for example
maybecoupled
) in one pass - Make sure there is an index (ideally a clustered index, but non-clustered, ok too) on one of the columns you got from
smalltable
. Suppose it isindexedcol1
- Create two top views:
coupledrecords
andnotcoupledrecords
, whose definitions areSELECT col1, ... FROM maybecoupled WHERE indexedcol1 IS NULL
andSELECT col1, ... FROM maybecoupled WHERE indexedcol1 IS NOT NULL
. - If you have a clustered index on indexedcol1, you will pay little or no penalty for most queries, as each query on both views will only hit the corresponding half of the records and never touch the other half. Your non-clustered indexes will be slightly larger and therefore slightly slower, but even that could be improved with filtered indexes .
- If you cannot use a clustered index, make sure indexcol1 is part of (or INCLUDE-d 's) a clustered index. This prevents a fallback to the clustered index for indexcol1 lookups for queries that pull data only from nonclustered indexes.
Here are some cases where the above solution will not work:
- if the number of related rows is relatively small and you have many columns with zero value in,
bigtable
or you have very small rows. Then the overhead of all those unrelated rows can hurt. (zeros take no space, but columns with a non-empty value). - if you are using nonclustered indexcol1 and cannot change your indexes to ensure that indexcol1 is present in your nonclustered indexes
- if the above shenanigans cause SQL Server to choose the wrong indexes to use in query plans due to increased query complexity (although you can fix this with index hints)
Caveat: you will definitely want to test the performance of any view-based solution to make sure it doesn't make things worse. SQL is usually good at choosing good query plans, but not always. Test, test, test!
a source to share