Clever Geek Handbook

SQL "IN" CLAUSE Performance issue

I have a simple SQL statement:

select * from Employee 
where LEFT(REPLACE(EmployeeName,'.',''),4) IN ('ABCE', 'BCDS', 'EDSC', 'XDSD', 'EFSE')

And I'm trying to do this in Linq:

Employees.Where(x => new[] { "ABCE", "BCDS", "EDSC", "XDSD", "EFSE" }.Contains((x.EmployeeName.Replace(".", "").Substring(0, 4))));

but the SQL query he created is ineffective. The EmployeeName is updated every time before comparing against strings, not just once:

SELECT 
[Extent1].[EmployeeID] AS [EmployeeID], 
[Extent1].[EmployeeName] AS [EmployeeName], 
[Extent1].[EmployeeTypeID] AS [EmployeeTypeID], 
[Extent1].[Active] AS [Active]
FROM [dbo].[Employee] AS [Extent1]
WHERE (N'ABCE' = (SUBSTRING(REPLACE([Extent1].[EmployeeName], N'.', N''), 0 + 1, 4))) 
OR (N'BCDS' = (SUBSTRING(REPLACE([Extent1].[EmployeeName], N'.', N''), 0 + 1, 4))) 
OR (N'EDsC' = (SUBSTRING(REPLACE([Extent1].[EmployeeName], N'.', N''), 0 + 1, 4))) 
OR (N'XDSs' = (SUBSTRING(REPLACE([Extent1].[EmployeeName], N'.', N''), 0 + 1, 4))) 
OR (N'EFSE' = (SUBSTRING(REPLACE([Extent1].[EmployeeName], N'.', N''), 0 + 1, 4)))

How do I make the generated SQL look more like the original sql statement? Thanks to

+3


source to share


4 answers


I've only tested it with linqpad, but I think this will create a more efficient query.



var result = from record in Employee
             let name = record.EmployeeName.Replace( ".", "" ).Substring( 0, 4 )
             where new[] { "ABCE", "BCDS", [...] }.Contains( name )
             select record;
+2


source


It's weird, but I rewrote your query and the generated sql statement was

WHERE (SUBSTRING(REPLACE([Extent1].[EmployeeName], N'.', N''), 0 + 1, 4) IN (N'ABCE', N'BCDS', N'EDSC', N'XDSD', N'EFSE')) AND (SUBSTRING(REPLACE([Extent1].[Name], N'.', N'')

I guess it depends on the database and framework used (in my case SQL Server 2008 and EF).



If you are using EF you can force EF to use sql statement

context.Set<Employee>().SqlQuery("select * from Employee where LEFT(REPLACE(EmployeeName,'.',''),4) IN ('ABCE', 'BCDS', 'EDSC', 'XDSD', 'EFSE')");
+1


source


I think you cannot (easily at least) control such a detail of the sql statement generation process.

But if you're looking for the best possible query, I would try the operator LIKE

(using a number of conditions in a LINQ query .StartsWith

).

LIKE

can use sql indexes, which is probably not the case with SUBSTRING

and REPLACE

.

+1


source


I think you are comparing a character array instead of a string. try it

Employees.Where(x => new string[] { "ABCE", "BCDS", "EDSC", "XDSD", "EFSE" }
         .Contains((x.EmployeeName.Replace(".", "").Substring(0, 4).ToString())));
0


source






More articles:


All Articles