SQL INNER JOIN vs Where Exists Performance Overview

Question

Suppose I have a list of strings that is sent with a client, in this case regions. And I have a table called Compliance Regions. I want to find strings that have a name property, and a region element must exist in the string.

In LINQ, I can do this in two different ways. As shown below, they create two different SQL queries. My question is which one to choose? Which query has the best performance?

List<string> regions = new List<string>() { "Canada", "EN 50530" }; var cregions = from c in complianceRegions from r in regions where c.Name.Equals(r) select c; var cregions2 = from c in complianceRegions where regions.Any(x => x == c.Name) select c; 

The generated sql is shown below.

  -- cregions SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Description] AS [Description] FROM [Administration].[ComplianceRegions] AS [Extent1] INNER JOIN (SELECT N'Canada' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] UNION ALL SELECT N'EN 50530' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1] ON [Extent1].[Name] = [UnionAll1].[C1] 

AND

 --cregions2 SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Description] AS [Description] FROM [Administration].[ComplianceRegions] AS [Extent1] WHERE EXISTS (SELECT 1 AS [C1] FROM (SELECT N'Canada' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] UNION ALL SELECT N'EN 50530' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1] WHERE [UnionAll1].[C1] = [Extent1].[Name] ) 

Added execution plan on request.

Answer 1

Given the two alternatives, the second is probably better because there is no function call for the column name.

However, these two options do not match. The first performs a partial match, and the second performs an exact match. You must really do what does what you intend. Performance is secondary to accuracy.

Both look painfully. Output CHARINDEX() output to index? Can we say “redundant”?

They ask why the query is not simple:

 select . . . from [Administration].[ComplianceRegions] AS [Extent1] where Extent1.Name in (N'Canada', N'EN 50530'); 

This is the simplest and most effective version of this logic.

Answer 2

Quotes @JNK will reply in this post .

EXISTS is used to return a boolean value, JOIN returns a whole other table

EXISTS is only used to check if a subquery returns results and short circuits as soon as this happens. A JOIN is used to expand a result set by combining it with additional fields from another table to which it relates.

In your example, queries are symbolically equivalent.

In general, use EXISTS when:

You do not need to return data from the corresponding table You have a hoax in the linked table (JOIN can cause duplicate rows if values ​​are repeated) You want to check for existence (use LEFT OUTER JOIN ... NULL instead) If you have the correct indexes, most of EXISTS times will execute identically to JOINs. The exception is very complex subqueries, where it is usually faster to use EXISTS.

If your JOIN key is not indexed, it may be faster to use EXISTS, but you will need to check your specific circumstances.

JOIN syntax is easier to read and more clearly, as usual.

Answer 3

These queries are equal. And both are terrible, I would not choose either of them. They have hard-coded values, so they cannot be reused. I'm not sure if SQL Server autoparametrization can handle this, so the execution cache plan is likely to suffer. The correct solution is the table parameters, which, unfortunately, are not supported by linq providers, as far as I know. Therefore, you will need to make the request yourself, you can use linq only to materialize the result.

You can try the solution provided by Ivan Stoev, how well it depends on what your supplier produces. Linq2sql does not scale well for longer lists, since it produces as many parameters as you have in the list. At the very least, you can reuse the execution plan when the number of items in the list is the same.