3
\$\begingroup\$

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>?

\$\endgroup\$
7
  • 1
    \$\begingroup\$ 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? \$\endgroup\$
    – svick
    Jan 21, 2013 at 15:46
  • \$\begingroup\$ @svick tmp, tmp2 and tmp3 are basically the same string, but with different encoding. \$\endgroup\$
    – Srcee
    Jan 21, 2013 at 18:45
  • \$\begingroup\$ @svick myTable has a primary key which is int and autoincremented. It doesn't have indexes. \$\endgroup\$
    – Srcee
    Jan 21, 2013 at 18:46
  • 1
    \$\begingroup\$ Can't you modify your database so that it used only one encoding? \$\endgroup\$
    – svick
    Jan 21, 2013 at 18:50
  • \$\begingroup\$ Also, are you performing this query in a loop, or something like that? \$\endgroup\$
    – svick
    Jan 21, 2013 at 18:54

1 Answer 1

6
\$\begingroup\$
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();
\$\endgroup\$
4
  • \$\begingroup\$ Tnx for your answer. But will you pls explain how will this fasten the execution? \$\endgroup\$
    – Srcee
    Jan 21, 2013 at 18:43
  • \$\begingroup\$ @Srcee It will always make a single query, while your code might make two queries. Making smaller number of queries is usually more efficient. \$\endgroup\$
    – svick
    Jan 21, 2013 at 18:52
  • \$\begingroup\$ @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. \$\endgroup\$ Jan 21, 2013 at 21:45
  • \$\begingroup\$ I wanted to fasten the time, so I guess your solution will work. \$\endgroup\$
    – Srcee
    Jan 22, 2013 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.