CODEWITHSUNDEEP
c#linqlinq-to-sql
tofutim
tofutim

Reputation: 23384

Linq efficiency - How do I best query a database for a list of values?

Let us say I have a database of Terms and a list of strings, is this a good (efficient) idea? It works smoothly, but I'm not sure it is scalable or the most efficient.

var results =
            from t in Terms
            join x in Targets on t.Term equals x
            select t;

Here Terms is a database table with index table Term. Targets is an IEnumerable of strings. Terms might hold millions, Targets between 10-20 strings. Any thoughts?

Upvotes: 1

Views: 261

Answers (3)

Amy B
Amy B

Reputation: 110221

Hmm, I didn't know you could join a local collection in like that. Perhaps that's a .Net 4.0 feature?

I have frequently issued queries like this:

IQueryable<Term> query =
  from t in Terms 
  where Targets.Contains(t.Term)
  select t;

There's a few caveats.

  • The variable x must be a List<string> reference. The variable x may not be an IList<string> reference.

  • Each string in the list is translated into a sql parameter. While linq to sql will happily translate many thousands of strings into parameters (I've seen 50k parameters), Sql Server will only accept ~2100. If you exceed this limit, you'll get a sql exception.

  • nvarchar vs varchar indexes.

Upvotes: 0

Blindy
Blindy

Reputation: 67584

If you need to join two tables on one key, as in your example, there's no other way to express it than an actual join. What you have is as efficient as it CAN get.

However, change the select to return only the fields you're interested in, and make sure you trim them, because sql databases like to return char fields with trailing spaces, and they take time to process and transfer across the network.

Upvotes: 0

Brian Ball
Brian Ball

Reputation: 12606

Ultimately what matters, as far as efficiency is concerned, is if the query that is executed against the database is efficient. To see this, you can either use SQL Profiler or find an application that will show you SQL generated by linq-to-sql.

If you use SQL Profiler, be sure to have it look for stored procedures, as Linq-to-sql uses the exec_sql procedure to execute queries.

Upvotes: 1

Related Questions