SQL query too slow in OpenEdge bank, what can it be?

Question

SQL query too slow in OpenEdge bank, what can it be?

Navigation

#1 by (0 votes)

0

I am having difficulty running queries on the OpenEdge 11.6 database. Several queries take a long time to execute, sometimes almost 2 minutes.

Here is an example query I'm trying to perform:

SELECT
docto_item.cod_fil,
count(*) qtd_item,
sum(docto_item.vlr_item_bruto) as vl_nota,
sum(docto_item.vlr_icms) as vl_icm,
sum(docto_item.vlr_pis),
sum(docto_item.vlr_cofins),
sum(docto_item.vlr_icms_subst) as vlr_icms_subst
FROM
pub.docto_item, pub.docto
WHERE

docto_item.cod_emp=1
AND docto_item.cod_fil=5
AND docto_item.tipo_ent_sai=0 --0 = Saida ou 1 = entrada
AND docto_item.dt_movto='2017-09-22'
AND docto.modulo='edi'
AND docto.cod_destino=docto_item.cod_destino
AND docto.cod_emp=docto_item.cod_emp
AND docto.cod_fil=docto_item.cod_fil
AND docto.num_docto=docto_item.num_docto
AND docto.serie_docto=docto_item.serie_docto
GROUP BY docto_item.cod_fil;

I know that just looking at the query can be difficult to know what it can be. But if there is someone who has had this problem and managed to solve it, it would be very good.

sql odbc

asked by anonymous 26.09.2017 / 21:00

1 answer

Table lines do not break at the end of the page - PDF & Laravel Error 407 when connecting in proxy

score 0 · Answer 1

Based on the comment I did some research and I was able to find the solution and leave it to anyone who has the same problem.

I made the query the with(index(nome_do_indice)) clause more or less like this:

SELECT CAMPO
FROM TABELA T WITH (INDEX(UM_INDICE))
INNER JOIN OUTRA_TABELA OT
WITH (INDEX(OUTRO_INDICE))
ON OT.ID = T.ID

Reference: How to use one specific index in a query in SQL Server?