How to Rewrite Queries with AI

The pressure to deliver fast, resilient, and scalable systems has never been higher. If you’re a DBA, DevOps, Tech Lead, or Infrastructure Manager, you know that query optimization is no trivial task; it’s a constant challenge, a high-complexity chess game with the database optimizer. One wrong move and your high-availability system can turn into a performance bottleneck, with frustrated customers, paralyzed operations, and, ultimately, financial loss.

In this urgent scenario, Artificial Intelligence (AI) emerges as a beacon of hope. The promise of tools that, in seconds, analyze a complex query and rewrite it in an optimized way is, to say the least, seductive. What once took hours or even days of manual investigation can now be done with a simple command. But is this technology the definitive answer to your database’s performance problems? HTI Tecnologia, with over 25 years of experience in 24/7 consulting and support for data environments, understands that AI is a powerful co-pilot, but never the pilot.

In this article, we will take a deep dive into the topic of “how to rewrite queries with AI” and reveal the hidden dangers of blind automation. We will use our expertise in Oracle, SQL Server, MySQL, PostgreSQL, and MongoDB to show why human expertise remains the central pillar of optimization and how outsourcing your DBA service can be the most strategic decision your company makes.

Mistake 1: The Context Dilemma – Why AI Doesn’t Understand Your Database

The first and most serious mistake when trying to rewrite queries with AI is the assumption that the tool understands the unique context of your infrastructure. Generative AI tools are trained on vast volumes of code data, but they have no visibility into your:

Data schema: AI doesn’t know which tables are most accessed, the cardinality of your indexes, or if the data distribution in a column is uniform.
Workload: AI cannot predict your system’s usage pattern, peak hours, or the most frequently executed queries. A query that looks great for a small dataset can fail catastrophically in a production environment with millions of records.
Execution plan: AI doesn’t have access to the actual execution plan that your database optimizer will generate. It might suggest a rewrite that, in theory, seems more efficient, but in practice, forces a full table scan or an inefficient nested loop join.

The expertise of an HTI Tecnologia DBA begins exactly where AI stops. Our team doesn’t just read the query; they analyze the environment as a whole. Through advanced monitoring and diagnostic tools, we identify performance bottlenecks, analyze existing indexes, and check database statistics to ensure that any optimization is, in fact, beneficial for your scenario.

Code Snippet: Execution Plan Analysis (Human + Tool)

Here, AI can suggest an optimization, but a DBA would need to analyze the real execution plan to validate it.

SELECT
    p.nome_produto,
    c.nome_categoria,
    SUM(ip.quantidade * ip.preco_unitario) AS total_vendido
FROM
    produtos p
JOIN
    itens_pedido ip ON p.id_produto = ip.id_produto
JOIN
    pedidos pe ON ip.id_pedido = pe.id_pedido
JOIN
    categorias c ON p.id_categoria = c.id_categoria
WHERE
    pe.data_pedido BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
    p.nome_produto, c.nome_categoria
ORDER BY
    total_vendido DESC;

Command to get the execution plan (SQL Server):

SET STATISTICS PROFILE ON;
SET SHOWPLAN_TEXT ON;
GO

SELECT
    p.nome_produto,
    c.nome_categoria,
    SUM(ip.quantidade * ip.preco_unitario) AS total_vendido
FROM
    produtos p
JOIN
    itens_pedido ip ON p.id_produto = ip.id_produto
JOIN
    pedidos pe ON ip.id_pedido = pe.id_pedido
JOIN
    categorias c ON p.id_categoria = c.id_categoria
WHERE
    pe.data_pedido BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
    p.nome_produto, c.nome_categoria
ORDER BY
    total_vendido DESC;

GO

Mistake 2: The Hidden Cost of Non-Optimization: The Money You’re Losing

The decision not to optimize queries or to do it superficially with non-contextualized tools costs money. And we’re not just talking about the lost time of your IT team. We’re talking about tangible and intangible costs that directly impact your financial bottom line.

Infrastructure expenses: A poorly optimized query can lead to CPU and memory consumption spikes, forcing your company to make hardware upgrades or purchase more expensive cloud plans, like larger instances on AWS, Azure, or Google Cloud.
Increased incidents: Slow queries can cause application timeouts, forcing restarts and generating incidents that consume the valuable time of your 24/7 support team, who should be focused on strategic problems.
User frustration and revenue loss: In e-commerce environments, a slow query on a product page can make the customer give up on the purchase. In internal systems, it can decrease team productivity. The impact on customer satisfaction and business results is immense and difficult to measure.

Code Snippet: Resource Monitoring (DBA Tool)

A DBA would use monitoring tools to identify resource-heavy queries, before and after an optimization.

Example of performance monitoring (Pseudo-SQL for a monitoring tool):

SELECT TOP 10
    total_elapsed_time / execution_count AS avg_elapsed_time_ms,
    total_elapsed_time AS total_elapsed_time_ms,
    execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
              ((CASE qs.statement_end_offset
                  WHEN -1 THEN DATALENGTH(st.text)
                  ELSE qs.statement_end_offset
              END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM
    sys.dm_exec_query_stats qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY
    avg_elapsed_time_ms DESC;

Mistake 3: The Failure to Validate and the Risk of Silent Errors

AI generated a new query. What happens next is the breaking point between a solution and a new problem. The third mistake, and one of the most dangerous, is the failure to validate the generated code. Simply copying and pasting the new query into production without a robust testing process is like playing a game of Russian roulette with your data integrity.

The validation process, which is part of our 24/7 Support and Maintenance service, must be meticulous and include:

Performance tests: Compare the execution time of the original query with the new query, using a real dataset or an accurate mockup of the production environment.
Integrity tests: Ensure that the new query returns exactly the same dataset as the original. An error here can corrupt reports, analyses, and even transaction data.
Stress tests: Subject the new query to a simulated workload to check how it behaves under pressure, identifying possible deadlocks or concurrency issues.

Code Snippet: Data Integrity Test (Human)

Even after AI rewrites the query, human validation is essential.

Example of results validation (Pseudo-code / SQL):

SELECT 'Original' AS source, COUNT(*) FROM (original_query) AS q_orig
UNION ALL
SELECT 'AI_Optimized' AS source, COUNT(*) FROM (ai_optimized_query) AS q_ia;

SELECT 'Original_Missing_in_IA' AS issue, *
FROM (original_query) AS q_orig
EXCEPT
SELECT *
FROM (ai_optimized_query) AS q_ia;

SELECT 'IA_Extra_or_Different' AS issue, *
FROM (ai_optimized_query) AS q_ia
EXCEPT
SELECT *
FROM (original_query) AS q_orig;

Mistake 4: The Abyss of Dependence on Generic Tools

AI offers a vast range of tools to “optimize” queries. From code co-pilots to assistants embedded in IDEs, the options are many. But the fourth mistake is falling into dependence on generic solutions that were not designed for your specific database. An AI model that optimizes MySQL may not be the most effective for a complex Oracle environment, which handles hinting and subqueries differently.

HTI Tecnologia does not just use generic tools. Our team uses a set of methodologies and tools specific to each type of database, whether they are relational like SQL Server and PostgreSQL, or non-relational like MongoDB and Neo4J. Our specialization in multiple DBMS allows us to choose the most precise approach, combining the best market tools with our proprietary know-how.

Code Snippet: DBMS-Specific Hints (Human and DBMS-Aware)

AI can suggest a query, but only an experienced DBA would know when and how to apply specific DBMS hints to guide the optimizer.

Example of an Optimized Query with Hint (Oracle):

SELECT e.nome, d.nome_departamento
FROM empregados e JOIN departamentos d ON e.id_departamento = d.id_departamento
WHERE e.status = 'ATIVO';

-- Optimized version with Oracle hint
SELECT /*+ USE_NL(e d) INDEX(e idx_emp_status) */
    e.nome, d.nome_departamento
FROM empregados e JOIN departamentos d ON e.id_departamento = d.id_departamento
WHERE e.status = 'ATIVO';

Mistake 5: The “Factory” of Queries vs. The Data Artisan

Think of AI as a factory. It is incredibly fast at mass-producing code. Now, think of the DBA as an artisan. They work with precision, with a clinical eye for detail. The fifth mistake is the confusion between the speed of the “factory” and the mastery of the “artisan.”

AI is fantastic for:

Generating boilerplate code: Creating stored procedures, functions, or triggers from a prompt.
Syntax and quick fixes: Helping with the syntax of complex commands that you don’t use often.
Initial drafts: Generating the first version of a query for a DBA to review and optimize.

However, the HTI Tecnologia DBA is the “architect” of the data. They don’t just rewrite queries with AI; they understand the business logic, foresee the future impact of each change, and plan the optimization as part of a long-term strategy. AI can be the hammer, but the DBA is the master builder who knows exactly where and how to strike.

Prompt for AI: “Create a stored procedure to insert a new product into the produtos table. The parameters should be nome_produto, descricao, preco, id_categoria.”

CREATE PROCEDURE sp_InserirProduto
    @nome_produto VARCHAR(255),
    @descricao TEXT,
    @preco DECIMAL(10, 2),
    @id_categoria INT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO produtos (nome_produto, descricao, preco, id_categoria, data_cadastro)
    VALUES (@nome_produto, @descricao, @preco, @id_categoria, GETDATE());

    SELECT SCOPE_IDENTITY() AS novo_id_produto;
END;
GO

Mistake 6: The Problem of Scalability and the Need for a 24/7 Remote DBA

The sixth mistake is thinking that optimizing queries with AI solves your team’s scalability problem. As your business grows, performance problems don’t decrease; they multiply. Query optimization becomes a constant and complex effort that requires a team of specialists.

Maintaining an in-house 24/7 DBA team is a costly investment. With outsourced DBA, your company gains immediate access to an elite team without the fixed cost of hiring. HTI Tecnologia offers the Remote DBA service, ensuring that your databases are under the supervision of specialists, ready to act at any time of the day or night, to solve everything from slow queries to critical incidents. This means your developers and DevOps can focus on developing new features, knowing that the database is in good hands.

Code Snippet: Routine DBA Task Automation (AI / Scripting)

AI or scripts can automate index maintenance, a routine task that, while not direct query optimization, profoundly impacts performance and frees up the DBA for complex optimizations.

Example of a script for rebuilding/reorganizing indexes (SQL Server):

DECLARE @TableName VARCHAR(255)
DECLARE @SchemaName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)

DECLARE index_cursor CURSOR FOR
SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName
FROM
    sys.indexes i
INNER JOIN
    sys.tables t ON i.object_id = t.object_id
INNER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    i.type > 0 
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
ORDER BY
    s.name, t.name, i.name;

OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @SchemaName, @TableName, @IndexName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'ALTER INDEX ' + QUOTENAME(@IndexName) +
               N' ON ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) +
               N' REORGANIZE;';
    PRINT @SQL;
    EXEC sp_executesql @SQL;

    FETCH NEXT FROM index_cursor INTO @SchemaName, @TableName, @IndexName;
END;

CLOSE index_cursor;
DEALLOCATE index_cursor;

Mistake 7: The Failure in Security Defense and AI Vulnerability

The last mistake, but not the least important, is ignoring the security aspect when using AI to rewrite queries. While most AI tools are secure, inserting AI-generated code into a production environment can introduce unintentional vulnerabilities. A poorly written query can open doors to SQL injection attacks or expose sensitive data.

The expertise of an HTI Tecnologia DBA includes security analysis as a crucial component. Our specialists not only ensure that queries are fast but that they are also secure and compliant with best practices. Query optimization without due attention to security can turn a performance gain into a data leak problem.

Code Snippet: SQL Injection Prevention (Human / Best Practices)

Here, AI can generate the query, but the secure implementation with parameters is a best practice that a DBA would always validate.

Vulnerable Query (AI might generate something similar if not instructed):

DECLARE @usuario_input VARCHAR(100) = 'admin'' OR ''1''=''1';
DECLARE @sql_dinamico NVARCHAR(MAX);

SET @sql_dinamico = 'SELECT * FROM usuarios WHERE username = ''' + @usuario_input + ''' AND password = ''' + @senha_input + '''';

Secure Query with Parameters (DBA/Developer Obligation):

DECLARE @usuario_input VARCHAR(100) = 'admin'; 
DECLARE @senha_input VARCHAR(100) = 'minhasenha';
DECLARE @sql_seguro NVARCHAR(MAX) = N'SELECT * FROM usuarios WHERE username = @p_username AND password = @p_password';

EXEC sp_executesql @sql_seguro,
                   N'@p_username VARCHAR(100), @p_password VARCHAR(100)',
                   @p_username = @usuario_input,
                   @p_password = @senha_input;

The Solution? AI as Co-pilot, the Specialist as Pilot

AI is a fantastic tool. And in the HTI Tecnologia ecosystem, it is already part of our work routine. But we use it intelligently, as an accelerator of the optimization process, and not as a substitute for human knowledge. Our philosophy is simple: combine the power of AI with the expertise of a team of highly qualified specialists.

HTI Tecnologia’s DBA as a Service is the answer for companies seeking high performance, availability, and security, without the costs and complexity of managing an in-house DBA team. We take care of your database, from query optimization to 24/7 support, so you can focus on what really matters: the growth of your business.

Don’t run the risk of compromising the performance and security of your data environment with blind optimization. Let HTI Tecnologia combine the innovation of AI with years of technical market experience to optimize your environment.

Want to know how to elevate the performance of your data infrastructure? Schedule a meeting with one of our consultants and discover how HTI Tecnologia can be your strategic partner.

Schedule a meeting here