# Optimization of code for searching in db

This is my code:

var test = (from x in myDb.myTable
where (x.name == tmp || x.name == tmp2 || x.name == tmp3) && x.unit == u
select x).FirstOrDefault();

if (test == null)
test = (from x in myDb.myTable
where (x.name == tmp || x.name == tmp2 || x.name == tmp3)
select x).FirstOrDefault();


How to optimize it>?

-
Could you explain your logic? Why are you doing it this way? If there are rows with both tmp and tmp2 (which are bad variable names, BTW) as their name, why is it okay to get any one of them? What is the schema of your table? How many rows does your table have? Does it have any indexes? – svick Jan 21 '13 at 15:46
@svick tmp, tmp2 and tmp3 are basically the same string, but with different encoding. – Srcee Jan 21 '13 at 18:45
@svick myTable has a primary key which is int and autoincremented. It doesn't have indexes. – Srcee Jan 21 '13 at 18:46
Can't you modify your database so that it used only one encoding? – svick Jan 21 '13 at 18:50
Also, are you performing this query in a loop, or something like that? – svick Jan 21 '13 at 18:54

var test = (from x in myDb.myTable
where (x.name == tmp || x.name == tmp2 || x.name == tmp3)
select x)
.AsEnumerable()
.OrderBy(x => x.unit == u ? 0 : 1)
.FirstOrDefault();


I removed the x.unit == u condition. Instead I sort the items to make the ones where this condition would be met to appear first. FirstOrDefault then makes the rest.

I split the EF part from the LINQ-to-objects part with AsEnumerable() as I am not sure if EF can translate the order by to SQL. If it can, you can try this

var test = (from x in myDb.myTable
where (x.name == tmp || x.name == tmp2 || x.name == tmp3)
orderby x.unit == u ? 0 : 1
select x)
.FirstOrDefault();

-
Tnx for your answer. But will you pls explain how will this fasten the execution? – Srcee Jan 21 '13 at 18:43
@Srcee It will always make a single query, while your code might make two queries. Making smaller number of queries is usually more efficient. – svick Jan 21 '13 at 18:52
@Srcee: You did not specify what you wanted to have optimized (execution speed, code size, code maintainablity, others?). My solution will be faster, because it queries the database only once in any case. – Olivier Jacot-Descombes Jan 21 '13 at 21:45
I wanted to fasten the time, so I guess your solution will work. – Srcee Jan 22 '13 at 7:21