Otimizar consulta SQL com CHATGPT

[Fernando queiroz]

fiz um teste de como otimizar uma QUERY SQL usando o CHATGPT o resultado foi muito bom

a query original era assim:

Código: Selecionar todos

        cQuery:="SELECT  "
        cQuery+="DATE_FORMAT(pedidos.DATVEN, '%m/%Y') AS MESANO, "

        cQuery+="IF ( "
        cQuery+="( SELECT "
        cQuery+="SUM(entrada.W_VNF) "
        cQuery+="FROM entrada "
        cQuery+="WHERE entrada.ST = '1'   "
        cQuery+="AND DATE_FORMAT(entrada.B_DHEMI, '%m/%Y') = DATE_FORMAT(pedidos.DATVEN, '%m/%Y') "
        cQuery+="AND SUBSTRING(entrada.A_ID, 36, 5) != '00000' "
        cQuery+="AND entrada.TIPO_OPERACAO != 85  "
        cQuery+="AND entrada.TIPO_OPERACAO != 90   "
        cQuery+="AND entrada.TIPO_OPERACAO != 91   "
        cQuery+="GROUP BY DATE_FORMAT(entrada.B_DHEMI, '%Y') ) IS NOT NULL, "
        cQuery+="( SELECT "
        cQuery+="SUM(entrada.W_VNF) "
        cQuery+="FROM entrada "
        cQuery+="WHERE entrada.ST = '1'   "
        cQuery+="AND DATE_FORMAT(entrada.B_DHEMI, '%m/%Y') = DATE_FORMAT(pedidos.DATVEN, '%m/%Y') "
        cQuery+="AND SUBSTRING(entrada.A_ID, 36, 5) != '00000' " 
        cQuery+="AND entrada.TIPO_OPERACAO != 85  "
        cQuery+="AND entrada.TIPO_OPERACAO != 90   "
        cQuery+="AND entrada.TIPO_OPERACAO != 91   "
        cQuery+="GROUP BY DATE_FORMAT(entrada.B_DHEMI, '%Y') ), 0.00) AS TOTAL_COMPRAS_MES, "

        cQuery+="COALESCE(SUM(pedidos.TOTNOT) , 0.00 ) AS VENDAS_MES,  "
//        cQuery+="IF(SUM(pedidos.TOTNOT) IS NULL, 0.00, SUM(pedidos.TOTNOT) ) AS VENDAS_MES,  "

        cQuery+="IF ( "
        cQuery+="( SELECT  "
        cQuery+="sum(danfe.W_VNF) "
        cQuery+="FROM danfe "
        cQuery+="WHERE DATE_FORMAT(danfe.B_DHEMI, '%m/%Y') = DATE_FORMAT(pedidos.DATVEN, '%m/%Y') "
        cQuery+="AND danfe.FL_CANCEL = 0 "
        cQuery+="AND danfe.TIPO_OPERACAO != 85  "
        cQuery+="AND danfe.TIPO_OPERACAO != 90  "
        cQuery+="AND danfe.TIPO_OPERACAO != 91  "
        cQuery+="GROUP BY DATE_FORMAT(danfe.B_DHEMI, '%Y/%m') ) IS NOT NULL, "
        cQuery+="( SELECT  "
        cQuery+="sum(danfe.W_VNF) "
        cQuery+="FROM danfe "
        cQuery+="WHERE DATE_FORMAT(danfe.B_DHEMI, '%m/%Y') = DATE_FORMAT(pedidos.DATVEN, '%m/%Y') "
        cQuery+="AND danfe.FL_CANCEL = 0 "
        cQuery+="AND danfe.TIPO_OPERACAO != 85  "
        cQuery+="AND danfe.TIPO_OPERACAO != 90  "
        cQuery+="AND danfe.TIPO_OPERACAO != 91  "
        cQuery+="GROUP BY DATE_FORMAT(danfe.B_DHEMI, '%Y/%m') ), 0.00) AS TOTAL_DANFE_MES, "

        cQuery+="IF ("
        cQuery+="( SELECT  "
        cQuery+="sum(danfe.W_VNF) "
        cQuery+="FROM danfe "
        cQuery+="WHERE DATE_FORMAT(danfe.B_DHEMI, '%m/%Y') = DATE_FORMAT(pedidos.DATVEN, '%m/%Y') "
        cQuery+="AND danfe.B_MOD = '55' "
        cQuery+="AND danfe.FL_CANCEL = 0 "
        cQuery+="AND danfe.TIPO_OPERACAO != 85  "
        cQuery+="AND danfe.TIPO_OPERACAO != 90  "
        cQuery+="AND danfe.TIPO_OPERACAO != 91  "
        cQuery+="GROUP BY DATE_FORMAT(danfe.B_DHEMI, '%Y/%m') ) IS NOT NULL, "
        cQuery+="( SELECT  "
        cQuery+="sum(danfe.W_VNF) "
        cQuery+="FROM danfe "
        cQuery+="WHERE DATE_FORMAT(danfe.B_DHEMI, '%m/%Y') = DATE_FORMAT(pedidos.DATVEN, '%m/%Y') "
        cQuery+="AND danfe.B_MOD = '55' "
        cQuery+="AND danfe.FL_CANCEL = 0 "
        cQuery+="AND danfe.TIPO_OPERACAO != 85  "
        cQuery+="AND danfe.TIPO_OPERACAO != 90  "
        cQuery+="AND danfe.TIPO_OPERACAO != 91  "
        cQuery+="GROUP BY DATE_FORMAT(danfe.B_DHEMI, '%Y/%m') ), 0.00) AS TOTAL_55_MES, "

        cQuery+="IF ( "
        cQuery+="( SELECT  "
        cQuery+="sum(danfe.W_VNF)  "
        cQuery+="FROM danfe "
        cQuery+="WHERE DATE_FORMAT(danfe.B_DHEMI, '%m/%Y') = DATE_FORMAT(pedidos.DATVEN, '%m/%Y') "
        cQuery+="AND danfe.B_MOD = '65' "
        cQuery+="AND danfe.FL_CANCEL = 0 "
        cQuery+="AND danfe.TIPO_OPERACAO != 85  "
        cQuery+="AND danfe.TIPO_OPERACAO != 90  "
        cQuery+="AND danfe.TIPO_OPERACAO != 91  "
        cQuery+="GROUP BY DATE_FORMAT(danfe.B_DHEMI, '%Y/%m') ) IS NOT NULL, "
        cQuery+="( SELECT  "
        cQuery+="sum(danfe.W_VNF) "
        cQuery+="FROM danfe "
        cQuery+="WHERE DATE_FORMAT(danfe.B_DHEMI, '%m/%Y') = DATE_FORMAT(pedidos.DATVEN, '%m/%Y') "
        cQuery+="AND danfe.B_MOD = '65' "
        cQuery+="AND danfe.FL_CANCEL = 0 "
        cQuery+="AND danfe.TIPO_OPERACAO != 85  "
        cQuery+="AND danfe.TIPO_OPERACAO != 90  "
        cQuery+="AND danfe.TIPO_OPERACAO != 91  "
        cQuery+="GROUP BY DATE_FORMAT(danfe.B_DHEMI, '%Y/%m') ), 0.00) AS TOTAL_65_MES "

        cQuery+="FROM pedidos  "
        cQuery+="WHERE DATE_FORMAT(pedidos.DATVEN, '%Y') = '" + str(YEAR(M->DAT_HOJE),4,0) + "' "
        cQuery+="AND pedidos.ST = '0'  "
        cQuery+="AND pedidos.TIPO_OPERACAO != 85 "
        cQuery+="AND pedidos.TIPO_OPERACAO != 90  "
        cQuery+="AND pedidos.TIPO_OPERACAO != 91  "
        cQuery+="GROUP BY DATE_FORMAT(pedidos.DATVEN, '%Y/%m') "
        cQuery+="ORDER BY  DATE_FORMAT(pedidos.DATVEN, '%Y/%m') desc  "
        HB_GTINFO( HB_GTI_CLIPBOARDDATA, cQuery)
        oQuery1:= ConeccoesClass():ExecuteSQL(::oServer, cQuery)

e ficou assim:

Código: Selecionar todos

                cQuery:="SELECT "
                        cQuery+="DATE_FORMAT(pedidos.DATVEN, '%m/%Y') AS MESANO, "
                        cQuery+="COALESCE(entrada_sum.W_VNF, 0.00) AS TOTAL_COMPRAS_MES, "
                        cQuery+="COALESCE(SUM(pedidos.TOTNOT), 0.00) AS VENDAS_MES, "
                        cQuery+="COALESCE(danfe_sum.W_VNF, 0.00) AS TOTAL_DANFE_MES,"
                        cQuery+="COALESCE(danfe_55_sum.W_VNF, 0.00) AS TOTAL_55_MES, "
                        cQuery+="COALESCE(danfe_65_sum.W_VNF, 0.00) AS TOTAL_65_MES "
                cQuery+="FROM pedidos "
                cQuery+="LEFT JOIN ( "
                        cQuery+="SELECT DATE_FORMAT(B_DHEMI, '%m/%Y') AS MESANO, SUM(W_VNF) AS W_VNF "
                        cQuery+="FROM entrada "
                        cQuery+="WHERE ST = '1' AND SUBSTRING(A_ID, 36, 5) != '00000' "
                        cQuery+="GROUP BY MESANO "
                        cQuery+=") entrada_sum ON DATE_FORMAT(pedidos.DATVEN, '%m/%Y') = entrada_sum.MESANO "
                cQuery+="LEFT JOIN ( "
                        cQuery+="SELECT DATE_FORMAT(B_DHEMI, '%m/%Y') AS MESANO, SUM(W_VNF) AS W_VNF "
                        cQuery+="FROM danfe "
                        cQuery+="WHERE FL_CANCEL = 0 AND B_MOD IN ('55', '65') "
                        cQuery+="GROUP BY MESANO "
                        cQuery+=") danfe_sum ON DATE_FORMAT(pedidos.DATVEN, '%m/%Y') = danfe_sum.MESANO "
                cQuery+="LEFT JOIN ( "
                        cQuery+="SELECT DATE_FORMAT(B_DHEMI, '%m/%Y') AS MESANO, SUM(W_VNF) AS W_VNF "
                        cQuery+="FROM danfe "
                        cQuery+="WHERE FL_CANCEL = 0 AND B_MOD = '55' "
                        cQuery+="GROUP BY MESANO "
                        cQuery+=") danfe_55_sum ON DATE_FORMAT(pedidos.DATVEN, '%m/%Y') = danfe_55_sum.MESANO "
                cQuery+="LEFT JOIN ( "
                        cQuery+="SELECT DATE_FORMAT(B_DHEMI, '%m/%Y') AS MESANO, SUM(W_VNF) AS W_VNF "
                        cQuery+="FROM danfe "
                        cQuery+="WHERE FL_CANCEL = 0 AND B_MOD = '65' "
                        cQuery+="GROUP BY MESANO "
                        cQuery+=") danfe_65_sum ON DATE_FORMAT(pedidos.DATVEN, '%m/%Y') = danfe_65_sum.MESANO "
                cQuery+="WHERE DATE_FORMAT(pedidos.DATVEN, '%Y') = '" + str(YEAR(M->DAT_HOJE),4,0) + "'  AND pedidos.ST = '0' "
                        cQuery+="AND pedidos.TIPO_OPERACAO NOT IN (85, 90, 91) "
                cQuery+="GROUP BY MESANO "
                cQuery+="ORDER BY MESANO DESC "
                oQuery1:= ConeccoesClass():ExecuteSQL(::oServer, cQuery)

ficou muito mais rapido, e menor

[JoséQuintas]

Interessante isso.
Fez no chatgpt normal ?

Nota:
O difícil é comparar pra ver o que foi feito, porque é grande e dividida.
Pode pensar em colocar como stored procedure por ser grande, tirar do fonte, e deixar mais legível.
E pra brincar com stored procedure também kkkk

Outra nota:
Cuidado com esse !=
Não sei porque, em determinada situação não deu o resultado esperado, acabei trocando por <>.
Apenas fique de olho.

[Fernando queiroz]

Olha as dicas que o CHATGPT da em relação a otimizar a query


2 / 2

Optimizing a query involves several steps, including analyzing the structure of the query, indexes, data distribution, and database configuration. Without knowing your database schema, indexes, and the volume of data, it's challenging to provide precise optimization advice. However, I can offer some general tips to consider:

Use Indexes: Ensure that the columns involved in filtering, joining, and grouping are properly indexed. For instance, columns like pedidos.DATVEN, entrada.B_DHEMI, and danfe.B_DHEMI should be indexed.

Consolidate Subqueries: Instead of repeating similar subqueries, you can consolidate them into a single subquery and then join the results to your main query. This reduces redundancy and can improve performance.

Avoid Functions on Columns: Avoid using functions on columns in WHERE clauses or JOIN conditions, as it can prevent the database from using indexes efficiently. Instead of using DATE_FORMAT, consider using direct date comparisons.

Limit Data Retrieval: Only retrieve the necessary columns. In your current query, you're selecting several columns but using only a few of them. This can reduce network and processing overhead.

Caching: If the data doesn't change frequently, consider caching the aggregated results to avoid recalculating them on every query.

Analyze Execution Plan: Use your database's tools to analyze the query execution plan. This will help you understand how the database is processing the query and identify potential bottlenecks.

Here's a simplified example of how you could consolidate similar subqueries and improve readability:

sql

[alxsts]

Olá!

Como você formulou a pergunta ao chatGPT? Enviou o SQL puro ou esta concatenação de string do primeiro post? Se ele entendeu a concatenação, acho ainda mais surpreendente...

[marco.prodata]

A otimização que ele fez foi de sintaxe, mas como mesmo explicou, isso depende também do banco que esta usando, indices e etc, as vezes simplesmente trocando um teste do where de lugar vc ganha muita velocidade na execução da query, dependendo do SGDB usado, no postgresql sempre instalo um gerenciador de logs pra gravar as querys que demoram mais que 1 segundo, e ai sempre analiso (via explain) o que a query tá usando, e se dá pra melhorar as vezes criando um índice, e se compensa criar o indice, porque o ideal também, não é ir criando indice pra cada coluna, pq ai depois na execução da query vai ser rápido, mas na gravação dos dados vai passar a demorar mais por causa da atualização dos indices. Então, a questão da otimização de fato é um pouco mais complexa do que apenas otimização de sintaxe.

[Fernando queiroz]

A otimização que ele fez foi de sintaxe, mas como mesmo explicou, isso depende também do banco que esta usando, indices e etc, as vezes simplesmente trocando um teste do where de lugar vc ganha muita velocidade na execução da query, dependendo do SGDB usado, no postgresql sempre instalo um gerenciador de logs pra gravar as querys que demoram mais que 1 segundo, e ai sempre analiso (via explain) o que a query tá usando, e se dá pra melhorar as vezes criando um índice, e se compensa criar o indice, porque o ideal também, não é ir criando indice pra cada coluna, pq ai depois na execução da query vai ser rápido, mas na gravação dos dados vai passar a demorar mais por causa da atualização dos indices. Então, a questão da otimização de fato é um pouco mais complexa do que apenas otimização de sintaxe.

Marcos eu so pedi para ele otimizar a query, "COMO OTIMIZAR ESSA QUERY" e mandei a tripa da query
ai ele deu umas dicas e uma nova query, mas achei ainda muito grande , pedi para regerar ai ele trouxe mais dicas e uma bem melhor, ja em outro caso ele fez a primeira e pedi para regerar e ele trouxe outro que nao funcionava, mas eu vou testando e vendo como fica, no meu caso eu estou usando MYSQL/MARIADB , mas nao precisei informar isso, pode ate ser que ele otimizasse melhor, vou testar.

[Fernando queiroz]

fazendo um novo teste

Código: Selecionar todos

SELECT 
fp.FORNECEDOR_ID,
produtos.produtos_id, 
produtos.despro,
produtos.precus, 
produtos.estatu, 
produtos.estl02,  
produtos.TIPUNI, 
SUM( pedidositens.qtdpro ) as vendido,
ROUND( (sum( pedidositens.qtdpro ) / 31) * 30 - ( produtos.estatu + produtos.estl02 ), 0 ) AS QTDPRO
from pedidositens
inner join ( SELECT produtofornecedor.FORNECEDOR_ID, produtofornecedor.PRODUTO_ID FROM produtofornecedor WHERE produtofornecedor.FORNECEDOR_ID = '358' GROUP BY PRODUTO_ID ORDER BY produtofornecedor.PRODUTO_ID ) AS fp  on fp.produto_id = pedidositens.produtos_id   
inner join pedidos on pedidos.pedidos_id = pedidositens.pedidos_id
inner join produtos on produtos.PRODUTOS_Id = pedidositens.PRODUTOS_ID 
where pedidos.datven between date_sub( curdate(), interval 31 day ) and date_sub( curdate(), interval 1 DAY )
and pedidos.st = '0' AND pedidos.TIPO_OPERACAO != 85 
GROUP BY  
produtos.PRODUTOS_ID,
produtos.DESPRO 
having ROUND( (sum( pedidositens.qtdpro ) / 31) * 30 - ( produtos.estatu + produtos.estl02 ), 0 ) > 0 
ORDER BY DESPRO

achei interessante o que ele retornou, nessa nova eu informei que era para MARIADB e ele colocou umas coisas que eu nem sabia que existia, gostei do resultado:

Código: Selecionar todos

WITH RelevantProducts AS (
    SELECT DISTINCT pff.PRODUTO_ID
    FROM produtofornecedor pff
    WHERE pff.FORNECEDOR_ID = '358'
),
VendidoPerProduct AS (
    SELECT
        pi.produtos_id,
        SUM(pi.qtdpro) AS vendido
    FROM pedidositens pi
    INNER JOIN pedidos pd ON pd.pedidos_id = pi.pedidos_id
    WHERE pd.datven BETWEEN DATE_SUB(CURDATE(), INTERVAL 31 DAY) AND DATE_SUB(CURDATE(), INTERVAL 1 DAY)
        AND pd.st = '0' AND pd.TIPO_OPERACAO <> 85
    GROUP BY pi.produtos_id
),
ProductsWithStockCalc AS (
    SELECT
        p.PRODUTOS_ID,
        p.DESPRO,
        p.precus,
        p.estatu,
        p.estl02,
        p.TIPUNI,
        vp.vendido,
        ROUND((vp.vendido / 31) * 30 - (p.estatu + p.estl02), 0) AS QTDPRO
    FROM produtos p
    INNER JOIN RelevantProducts rp ON p.PRODUTOS_ID = rp.PRODUTO_ID
    LEFT JOIN VendidoPerProduct vp ON p.PRODUTOS_ID = vp.produtos_id
)
SELECT
    ps.PRODUTOS_ID,
    ps.DESPRO,
    ps.precus,
    ps.estatu,
    ps.estl02,
    ps.TIPUNI,
    COALESCE(ps.vendido, 0) AS vendido,
    COALESCE(ps.QTDPRO, 0) AS QTDPRO
FROM ProductsWithStockCalc ps
WHERE ps.QTDPRO > 0
ORDER BY ps.DESPRO;

e olha as dicas do que ele fez:
Esta consulta otimizada reduzirá a complexidade do plano de execução ao evitar subconsultas desnecessárias e otimizando as junções. Além disso, coloquei os filtros nas tabelas internas para limitar o número de linhas processadas antes das junções principais. Certifique-se de testar a consulta em seu ambiente para garantir que ela funcione conforme o esperado com seus dados.

[marco.prodata]

Esse with no postgresql é um espetáculo também, depois que lançaram esse recurso, uso muito, facilita muito o trabalho. No postgresql tem um explain, que mostra o tempo que a query gasta pra executar cada pedaço, e ali dá pra tentar ver se é possível melhorar a mesma, provavelmente deve ter o mesmo recurso no mysql/maria db.

[alxsts]

Olá!

Este "with" tem nome: Common Table Expressions, conhecido também como CTE, e está presente nas versões mais atuais dos principais SGBDR.

[smithloo]

ChatGPT sugeriu uma consulta exatamente igual à que forneci. Você pode verificar com os planos de execução que não há diferença no plano