I'm wondering if using a table variable开发者_如何学JAVA is more or less performant than using an inner join (select)
Example:DECLARE @tab TABLE(Id int)
INSERT INTO @tab
SELECT Id
FROM SomeTable
WHERE SomeDate = "10 DAYS AGO"
SELECT *
FROM SomeOtherTable
INNER JOIN @tab t
ON SomeOtherTable.id = t.id
--VERSUS--
SELECT *
FROM SomeOtherTable
INNER JOIN (SELECT Id FROM SomeTable WHERE SomeDate = "10 DAYS AGO") t
ON SomeOtherTable.id = t.id
For large queries the first is more maintainable if you have to make the same join a few times, but what is the most performant?
Greetings
SQL Server does not maintain detailed statistics for table variables or automatically recompile to reflect less granular cardinality information changes (without TF 2453) so will generally assume that they output a single row.
This means that sometimes you will get a sub optimal join strategy. The second version can use statistics and also avoids the overhead of inserting the intermediate results into a temporary object.
However if the second query is expensive to evaluate as SomeDate
is not indexed you may get improved performance from materialising this up front (compared to repeatedly re-evaluating).
You could also consider using a #temp
table as this avoids the statistics issue. Some people suggest never using a table variable in JOINs
精彩评论