Optimization in sql

Question

Optimization in sql

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)
#4 by (0 votes)

1

Considering these two tables in the database:

Product Table:

| id  | nome      |
|-----|-----------|
| aaa | Produto A |
| bbb | Produto B |
| ccc | Produto C |

Attributes Table:

| id_produto | atributo | valor   |
|------------|----------|---------|
| aaa        | cor      | azul    |
| aaa        | tamanho  | M       |
| bbb        | cor      | preto   |
| bbb        | tamanho  | P       |
| ccc        | cor      | amarelo |
| ccc        | tamanho  | G       |

and the following SQL query:

select
    p.nome,
    c.valor,
    t.valor
from
    Produto p,
    Atributos c,
    Atributos t
where
    p.id = c.id_produto and
    p.id = t.id_produto and
    c.atributo = 'cor' and
    t.atributo = 'tamanho'

Is there any way to do this select without duplicating the table attributes?

Edit # 1

Result:

| nome      | cor     | Tamanho |
|-----------|---------|---------|
| Produto A | azul    | M       |
| Produto B | preto   | P       |
| Produto C | amarelo | G       |

Note: I can not change the structure of the tables, the actual database has several different types of attributes (dynamically generated) and thousands of records.

sql oracle

asked by anonymous 21.03.2018 / 20:40

4 answers

VBA - Popular array string and no repeat values Radio alignment with image

score 1 · Answer 1

It's a good idea to check out the performance for your actual case, but for the example it would look like this:

SELECT NOME,
       COR,
       TAMANHO
FROM (
    SELECT 
        P.NOME AS NOME,
        A.ATRIBUTO AS ATRIBUTO ,
        A.VALOR AS VALOR
    FROM PRODUTO P
        JOIN ATRIBUTOS A ON A.ID_PRODUTO = P.ID_PRODUTO ) AS FONTE 
        PIVOT ( MIN(VALOR) FOR ATRIBUTO IN (COR, TAMANHO)) AS PVT

In the case of your example snippet, running the queries in both approaches, the first one (crossing the table) costs 71% while the second form, 29% , or approximately 1/3 of the effort . I believe the same is true for you in the real case, but be sure to measure it.

score 0 · Answer 2

No, because there are two different lines being searched. The best solution here would be to redo the tables, avoiding the comparison of strings of the attributes (which even with the indexed strings, would be slower than the direct attribute declaration in the type):

Product Table:

| id  | nome      |
|-----|-----------|
| aaa | Produto A |
| bbb | Produto B |
| ccc | Produto C |

Attributes Table:

| id_produto | tamanho | cor     |
|------------|---------|---------|
| aaa        | M       | azul    |
| bbb        | P       | preto   |
| ccc        | G       | amarelo |

score 0 · Answer 3

You can have a by clause of the attributes per id and get one of the attributes of the attribute table and then cross the product. Uses performance hint "Materialize" to optimize performance. You can enable parelism too.

So you do not duplicate information. If need be try to explain better

score 0 · Answer 4

Here's an example with Pivot, see if it suits you:

Pivot Script:

SELECT 
    p.nome,
    p.cor,
    p.tamanho

FROM 
    (
    select
        p.nome, 
        a.atributo,
        a.valor
    from #produto p
    inner join #atributos a on p.id = a.id_produto
    group by p.nome, a.atributo, a.valor
    ) as x
 PIVOT (max(valor) 
FOR atributo IN ([cor],[tamanho]))P
ORDER BY 1;

Result

**nome | cor | tamanho**

Produto A | azul |  M

Produto B | preto | P

Produto C | amarelo |   G

Script to create the database (SQL Server):

create table #produto(
    id varchar(50) primary key,
    nome varchar(50)
);

create table #atributos(
    id_produto varchar(50) references #produto(id),
    atributo varchar(50) not null,
    valor varchar(50) not null
);

insert into #produto values
('aaa', 'Produto A'),
('bbb', 'Produto B'),
('ccc', 'Produto C')

insert into #atributos values
('aaa', 'cor', 'azul'),
('aaa', 'tamanho', 'M'),
('bbb', 'cor', 'preto'),
('bbb', 'tamanho', 'P'),
('ccc', 'cor', 'amarelo'),
('ccc', 'tamanho', 'G')