What is most advisable? Do you query through a list or directly in the database?

Question

What is most advisable? Do you query through a list or directly in the database?

Navigation

#1 by (2 votes)

2

I have to perform a query where I need to do 4 INNER JOIN and still use WHERE in several fields.

What is most advisable?

Conduct the query in this way?

using (DbContexto db = new DbContexto())
{
    var query = (from t1 in db.tabela1
                 join t2 in db.tabela2 on t1.id equals t2.fk_t1
                 join t3 in db.tabela3 on t1.idt3 equals t3.fk_t1
                 where (t1.v1 = 1) && (t2.v2 = 2) && (t3.v3 = 3)
                 select t1
             );
}

Or this?

List listaTabela1 = new List();
List listaTabela2 = new List();
List listaTabela3 = new List();

using (DbContexto db = new DbContexto())
{
    listaTabela1 = (from t1 in db.tabela1 select t1).ToList();
    listaTabela2 = (from t2 in db.tabela2 select t2).ToList();
    listaTabela3 = (from t3 in db.tabela3 select t3).ToList();
    var query    = (from t1 in listaTabela1 
                    join t2 in listaTabela2 on t1.id equals t2.fk_t1
                    join t3 in listaTabela3 on t1.idt3 equals t3.fk_t1
                    where (t1.v1 = 1) && (t2.v2 = 2) && (t3.v3 = 3)
                    select t1
                   );
}

mysql c# linq asp.net-mvc

asked by anonymous 19.11.2014 / 17:51

1 answer

Find commit in which file was deleted Retrieving list of outdated objects with Hibernate

score 2 · Accepted Answer

The most advisable way depends on your ultimate goal with the code. Only by the text around the code would it be easy to dismiss the question as dependent on opinion. By looking more carefully at the two alternatives, however, I think the former is more advisable.

The reason is this: in the first form, you will run a single query in the database to get the data you want. The server is able to create a well-defined search strategy and, if everything is OK on the SQL side, efficiently returns what you want.

In the second option you have three queries to the bank without any filtering criteria. The complete contents of the tables will be read on disk, allocated in the memory of the database server, then goes to the memory space of your application and finally you discard what you do not want. Whenever the result of your search does not contain all the records of the three tables, you will have done operations, well ... For lack of better word, useless operations.

Note also that the complexity of your joins should be roughly an O (n ^ m ^ o) algorithm. In people's language this means that your program's runtime does not grow proportionally to the amount of data involved ... The runtime growth is exponential , and can be proportional to the cube of the amount of elements with which it will work (may be even worse). I'm actually seeing two inner joins in your example - with four inner joins , we're talking about the fifth power. It is essential to optimize so that the minimum of elements is obtained from the database.

Keep the first option, unless you want to take advantage of items that are not part of the query result for something else.