Single query slower than 3 queries [closed]

Question

I'm using .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 those entities 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

As i say problem could be in indexes if you want your query go faster with this exact query i suggest you to create this index:

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

Don't forget to update statistics if you change your table data a lot.

Answer 2

First without an index on title I find it a bit hard to believe that you're getting the behaviour you claim.

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

But that said, the first approach is actually three trips to the database, but will leverage the indexe created.

The second approach is a single trip to the database but will al out certainly result in a full table scan, with 2,500,000 rows is likely to take a non trivial amount of time.