Query to bring 1 parent record and in it can contain multiple children records (performance)

Question

Supposing for the client I can add more joins as not only sales, and the result of this consultation bring much more records. This is a performative way of working or would be better to query each separate table and populate my object Client in C#?

CREATE TABLE CLIENTE 
(
    ID_CLIENTE INT PRIMARY KEY IDENTITY (1, 1),
    NOME_CLIENTE VARCHAR(200) NOT NULL
)
INSERT INTO CLIENTE (NOME_CLIENTE) VALUES ('TESTE 1')

CREATE TABLE VENDA
(
    ID_VENDA INT PRIMARY KEY IDENTITY (1, 1),
    ID_CLIENTE INT NOT NULL
)
ALTER TABLE VENDA ADD CONSTRAINT PK_VENDA_CLIENTE PRIMARY KEY (ID_VENDA, ID_CLIENTE)

INSERT INTO CLIENTE (ID_CLIENTE) VALUES ((SELECT ID_CLIENTE FROM CLIENTE WHERE NOME_CLIENTE = 'TESTE 1'))

CREATE TABLE PRODUTO
(
    ID_PRODUTO INT PRIMARY KEY IDENTITY (1, 1),
    NOME_PRODUTO VARCHAR(200) NOT NULL,
    VALOR_PRODUTO DECIMAL(10,4) NOT NULL
)

INSERT INTO PRODUTO (NOME_PRODUTO, VALOR_PRODUTO) VALUES ('TOMATE', 10)
INSERT INTO PRODUTO (NOME_PRODUTO, VALOR_PRODUTO) VALUES ('ABACAXI', 20)
INSERT INTO PRODUTO (NOME_PRODUTO, VALOR_PRODUTO) VALUES ('UVA', 30)
INSERT INTO PRODUTO (NOME_PRODUTO, VALOR_PRODUTO) VALUES ('MORANGO', 40)
INSERT INTO PRODUTO (NOME_PRODUTO, VALOR_PRODUTO) VALUES ('ALFACE', 50)

CREATE TABLE VENDAPRODUTO
(
    ID_VENDA INT NOT NULL,
    ID_PRODUTO INT NOT NULL,
    QUANTIDADE INT NOT NULL,
    VALOR_PRODUTO INT NOT NULL
)

INSERT INTO VENDAPRODUTO (ID_PRODUTO, QUANTIDADE, VALOR_PRODUTO) VALUES ((SELECT ID_PRODUTO FROM PRODUTO WHERE NOME_PRODUTO = 'TOMATE'), 1, 10)
INSERT INTO VENDAPRODUTO (ID_PRODUTO, QUANTIDADE, VALOR_PRODUTO) VALUES ((SELECT ID_PRODUTO FROM PRODUTO WHERE NOME_PRODUTO = 'ABACAXI'), 2, 20)
INSERT INTO VENDAPRODUTO (ID_PRODUTO, QUANTIDADE, VALOR_PRODUTO) VALUES ((SELECT ID_PRODUTO FROM PRODUTO WHERE NOME_PRODUTO = 'UVA'), 7, 30)
INSERT INTO VENDAPRODUTO (ID_PRODUTO, QUANTIDADE, VALOR_PRODUTO) VALUES ((SELECT ID_PRODUTO FROM PRODUTO WHERE NOME_PRODUTO = 'MORANGO'), 4, 40)
INSERT INTO VENDAPRODUTO (ID_PRODUTO, QUANTIDADE, VALOR_PRODUTO) VALUES ((SELECT ID_PRODUTO FROM PRODUTO WHERE NOME_PRODUTO = 'ALFACE'), 2, 50)

SELECT CLIENTE.ID_CLIENTE,
      CLIENTE.NOME_CLIENTE,
      VENDAPRODUTO.ID_PRODUTO,
      PRODUTO.NOME_PRODUTO,
      VENDAPRODUTO.QUANTIDADE,
      VENDAPRODUTO.VALOR_PRODUTO
 FROM CLIENTE CLIENTE
 LEFT JOIN VENDA VENDA ON VENDA.ID_CLIENTE = CLIENTE.ID_CLIENTE
 LEFT JOIN VENDAPRODUTO VENDAPRODUTO ON VENDAPRODUTO.ID_VENDA = VENDA.ID_VENDA 
 LEFT JOIN PRODUTO PRODUTO ON PRODUTO.ID_PRODUTO = VENDAPRODUTO.ID_PRODUTO
WHERE CLIENTE.ID_CLIENTE = 1

Upshot

/* 

ID_CLIENTE | NOME_CLIENTE | ID_PRODUTO | NOME_PRODUTO | QUANTIDADE | VALOR_PRODUTO
1          | TESTE 1      | 1          | TOMATE       | 1          | 10
1          | TESTE 1      | 2          | ABACAXI      | 2          | 20
1          | TESTE 1      | 3          | UVA          | 7          | 30
1          | TESTE 1      | 4          | MORANGO      | 4          | 40
1          | TESTE 1      | 5          | ALFACE       | 2          | 50

*/

Answer 1

Hello, Nicola!

When the volume of data is very large and I have the possibility to reduce the volume of data I will have to handle, I use Ctes(Common Table Expressions).

I create one or more Ctes with data ranges, already filtered by the conditions I want (date range or a product code, for example).

Ctes work as tables in memory and they exist only in the execution of your query. They are not created in TEMPDB, like a temporary table, which helps in performance. Her basic difference for a temporary table is the impossibility of running UPDATES, DELETES and INSERTS, which doesn’t seem to be a problem for her situation. After I have filtered Ctes, I make Join between them and filter something else that is needed and make clusters.

The documentation of the use of Ctes is in this link, recommend you take a look.

As for the grouping part in C#, I do not recommend, you would carry a lot of data between the database and application. Ideal is to already return your ideal SQL resultset and only display in your application.

In the company where I work as a DEV, we have views and even procedures that are called with the Dapper(a simple and fast ORM, we use it only for reporting. It is the ORM used here in Stackoverflow) and the resultset is rendered on the user screen.

Here too is a step by step legal use of Ctes, in the blog of Dirceu Resende (MVP Microsoft in the area of Data).

I hope I’ve helped!