Performace in native consultation

Asked

Viewed 384 times

3

Good morning, I’m on a project in which I must assemble charts based on queries held in a bank Postgres.

This bank has a structure for BI and the tables have on average 40 million lines.

The consultations that I must carry out at the bank, are being made through native querys for JPA, these queries take up to 5 minutes to return the data, but when executed on Pgadmin or in the Dbeaver (Generic ide for banks based on eclipse ide), return time is 20ms.

The application is in EJB, using JPA and Hibernate, we use the Jboss EAP 6.3 as middleware, the connection to the bank is being managed by the bank itself Jboss, could be some configuration of it or the problem is another same?

Consultation

select date_part('day', dt.dta) dia,    
    sum(distinct(fp.valorvendido)) venda,   
    sum(distinct(fp.valortotalcustodia)) custo,     
    sum(distinct(fp.valorlucrototal)) lucro,    
    round(((sum(fp.valorlucrototal) / sum(fp.valortotalcustodia)) * 100),2) margem,     
    sum(distinct(dp.qtdvendido)) qtd_vendido 
from ft_produto fp  
    inner join dmn_tempo dt         
        on dt.dta = ft.dta 
where fp.nrodivisao in (1, 2, 3)    
    and fp.nroempresa = 9   
    and fp.nrosegmento in (1, 2, 3, 4, 7, 8, 9, 10)     
    and dt.anomes = 201604  
    and fp.seqproduto = 20615 
group by dt.dta order by dia

Execution plan generated by Pgadmin

"Sort  (cost=444.86..444.86 rows=1 width=34)"
"  Sort Key: (date_part('day'::text, dt.dta))"
"  ->  GroupAggregate  (cost=444.27..444.85 rows=1 width=34)"
"        ->  Merge Join  (cost=444.27..444.81 rows=1 width=34)"
"              Merge Cond: (fp.dta = dt.dta)"
"              ->  Sort  (cost=434.74..434.99 rows=100 width=34)"
"                    Sort Key: fp.dta"
"                    ->  Bitmap Heap Scan on ft_produto dp  (cost=27.15..431.41 rows=100 width=34)"
"                          Recheck Cond: ((nroempresa = 9) AND (nrodivisao = ANY ('{1,2,3}'::bigint[])) AND (seqproduto = 20615))"
"                          Filter: (nrosegmento = ANY ('{1,2,3,4,7,8,9,10}'::integer[]))"
"                          ->  Bitmap Index Scan on ind_dmn_produto15  (cost=0.00..27.13 rows=101 width=0)"
"                                Index Cond: ((nroempresa = 9) AND (nrodivisao = ANY ('{1,2,3}'::bigint[])) AND (seqproduto = 20615))"
"              ->  Sort  (cost=9.51..9.59 rows=30 width=8)"
"                    Sort Key: dt.dta"
"                    ->  Index Scan using ind_dmn_tempo01 on dmn_tempo dt  (cost=0.00..8.78 rows=30 width=8)"
"                          Index Cond: (anomes = 201604)"
java hibernate jpa jboss
  • 1

    Take a look at the query generated by Hibernate and how this same query reaches the database. Probably the parameters passed may not match the correct type in the database and thus not using the column index in the tables. In SQL Server, for example, I already made a query per text field that expected the parameter passed to arrive as VARCHAR but the jTDS driver placed a N at the front, converting to NVARCHAR.

  • @So I’ve done this, the same query I run in the application that lega 5 minutes, when executed in ides takes 20~30ms, what I’m finding strange

  • Did you see how the parameters are coming into the database in this query? You need to have certainty if the query that reaches the database and that it executes is exactly the same one that you use when running in Pdagmin. It would be interesting to show how you capture the SQL run by the database and how you capture the SQL generated by Hibernate, maybe the problem is how you are investigating the problem.

  • @Dherik Yes, I confirmed the parameters in the application debug, and the tests I ran on the ides are exactly the same.

  • @John the result of the query is an Object List ? You can activate the Hibernate statistics and see how much time precisely it spends with the execution of select. To activate take a look at this answer: http://stackoverflow.com/questions/12656452/spring-hibernate-show-query-execution-time

  • Can you identify the time-consuming query occurring in the database? Ideally, try to see the execution plan of this query. Even, hitching a ride in the comment of Delmo, the delay may be on the side of Hibernate, after the result (although I find it very rare to occur, I’ve never seen it take so long to be honest).

  • @adelmo00 I already use statistics and the return time is always between 3 to 5 minutes

  • @Dherik added the execution plan and the query if it helps the problem, but yes I analyzed all the statistics, and answering your question yes, I bust an Array Object List

  • But what about the consultation plan sent by Hibernate? The ideal was to start identifying whether this query sent by Hibernate really takes 3-5 minutes inside the database.

  • @Dherik then the time said above is the time said in Hibernate statistics, I entered by an image.

  • @Dherik did the tests based on his first comment that was edited and had not seen, worked, was really a parament that at the time that Hibernate gave parse he was putting in a different kind.

  • hehehe, very good! I’ll put as an answer then.

Show 7 more comments

1 answer

2


Take a look at the query generated by Hibernate and how this same query reaches the database.

Probably the parameters passed may not be coinciding with the correct type of the columns involved in the table in the database and thus not using the index of the columns in the tables and generating this higher time in the return of the results.

In SQL Server, for example, I already made a query per text field that expected the parameter passed to arrive as VARCHAR:

SELECT * from Pessoa WHERE nome = 'Paulo'

But the jTDS driver put an N in front, converting to NVARCHAR.

SELECT * from Pessoa WHERE nome = N'Paulo'

Not using the database index and impairing the performance of the query.

Browser other questions tagged java hibernate jpa jboss

You are not signed in. Login or sign up in order to post.