Does "Join" make the search faster?

Question

I have two tables, one of patients and the other of consultations. In short, the tables have the following structure:

Patients

int id;
string nome;
string cartaoSus;

Consultation

int id;
int idPaciente;
dateTime data;

When I want a patient report with scheduled appointments, I do so:

//Crio uma classe que engloba as duas tabelas:
private class consultaCompleta
{
    int idConsulta;
    int idPaciente;
    dateTime data;
    string nome;
    string cartaoSus;
}

//Crio as listas referentes à cada uma das tabelas:
private void montaConsulta(int idConsulta)
{
    List<pacientes> listaPaciente = model.pacientes.ToList(); //pego tudo da tabela
    List<consultas> listaConsulta = model.consultas.ToList(); //pego tudo da tabela
    List<consultaCompleta> listaConsultaCompleta = (from c in listaConsulta
                                                    join p in listaPaciente on c.idPaciente equals p.id
                                                    select new consultaCompleta()
                                                    {
                                                        idConsulta = c.id,
                                                        idPaciente = p.id,
                                                        data = c.data,
                                                        nome = p.nome,
                                                        cartaoSus = p.cartaoSus
                                                    })
                                                    .Where(p => p.id.Equals(idConsulta)) 
                                                    .ToList();
}

The bigger question is this: I always take all the data from the tables and do the join after. Is there another way to do this, taking first from the table (as Maniero answered me)? How to do then?

I’m sorry I have practically redone the whole question, but I need to know well about this issue of slow consultations, because my bank will be very large in the future

Answer 1

It will probably get faster, the database can optimize, and bring less information. Just testing to know. But you need to do correct tests. But whenever you take a lot of information and try to filter on the client side it tends to be slower.

I believe it will bring more consistent results. Between taking information from one table and then taking something else can change and become inconsistent. Taking everything at once in the same query ensures consistency. I just can’t guarantee because I don’t know if everything is generated in the same transaction, and how is the database configuration.

I could only tell you how to do if you have information on the model question, how you’re using it.

In conclusion I understand what you mean, but probably the term lambda is being misused. Behold what is a lambda.