One query is slower than 3 queries

Question

I am using the .NET framework 4.5.1 Linq to SQL.

I have this product class using code first:

public class Part { public int PartID { get; set; } [Required(ErrorMessage = "xxx")] public string Title { get; set; } [MaxLength(50)] [Index(IsClustered = false, IsUnique = false,Order =1)] public string Part_Number { get; set; } [MaxLength(50)] [Index(IsClustered = false, IsUnique = false, Order = 2)] public string Manufacturer_Number { get; set; } } 

I have approximately 2500000 of these objects in the database.

First approach

 var query = db.Parts.Where(s => s.Manufacturer_Number == sstring).ToList(); query.AddRange(db.Parts.Where(s => s.Part_Number == sstring).ToList()); query.AddRange(db.Parts.Where(s => s.Title == sstring).ToList()); 

Second approach

 var query = db.Parts.Where(s => s.Manufacturer_Number == sstring || s.Part_Number == sstring || s.Title == sstring).ToList(); 

The first approach is 100 times faster than the second approach. Can anyone explain this?

Answer 1

At first, without a heading index, it’s hard for me to believe that you are getting the behavior you require.

Set statistics at a minimum and add results to this question.

But the first approach is actually three trips to the database, but the created index will use it.

The second approach is a single trip to the database, but, without a doubt, this will lead to a full scan of the table, and 250,000 rows will most likely take a non-trivial amount of time.

Answer 2

How can I say the problem may be in the indexes, if you want your query to be accelerated with this exact query, I suggest you create this index:

 CREATE NONCLUSTERED INDEX PartIndex ON Part (PartID, Manufacturer_Number, Part_Number, Title) 

Do not forget to update the statistics if you greatly change the table data.