Path: csiph.com!news.swapon.de!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: Cecil Westerhof Newsgroups: comp.lang.java.programmer Subject: Re: Performance JPA Date: Tue, 03 Nov 2015 19:11:48 +0100 Organization: Decebal Computing Lines: 52 Message-ID: <87ziyvhs0r.fsf@Equus.decebal.nl> References: <87lhagkcsh.fsf@Equus.decebal.nl> <563763d9$0$23727$e4fe514c@news.xs4all.nl> <87d1vrk20x.fsf@Equus.decebal.nl> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit Injection-Info: mx02.eternal-september.org; posting-host="528adfd6ad074c92fdc6a7f8fb9e23d8"; logging-data="19932"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18awhgynVn3aiI6IlMXeq8rmu4SHt9DK7w=" User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux) Cancel-Lock: sha1:cRcZ9X/JKmElJ3nWfHZjrUm5yw8= sha1:paCM/tO8oWdJ12Z2SxqUVV6A9dE= X-Homepage: http://www.decebal.nl/ Xref: csiph.com comp.lang.java.programmer:32985 On Tuesday 3 Nov 2015 12:16 CET, Martin Gregorie wrote: > On Tue, 03 Nov 2015 07:52:46 +0100, Cecil Westerhof wrote: > >> At the moment I am told that everything is slower. I am told that a >> query that took at most 5 seconds now takes one to two minutes. But >> I asked them to measure the old situation and until now they did >> not do this. I hope to get today real data about this. But several >> pages now give a timeout. >> > Usually slow retrievals mean that the DBMS is sequentially scanning > tables rather than using indexes, so the obvious starting point is > to see how the JPA queries differ from the ones used by JDBC and, if > the difference can't be found by inspection, to use the DBMS query > cost analysis facilities to run the queries manually and report > execution plan used for them. In principal the JPA should do about the same queries as the JDBC. I am busy trying to get the logging of the queries at the database side enabled. I hope I will succeed. One remark is that I should use the unit test to look which statements are generated. I have a hard time to convince them that a unit test is something else as a live run. > Typically the poor performance is that joins, WHERE or ORDER BY > clauses on large tables are not supported by indexes: the solution > is to add these indexes to the schema if the query is run frequently > enough to justify doing so. > > Bad performance can also be the result of poor DB management: most > DBMS use DB statistics (row counts, etc) as input to the query > optimiser. If these stats aren't automatically updated by the DBMS > and the DBA doesn't update them regularly, or turned auto-stats > update off then the optimiser can do a spectacularly poor job, e.g. > if stats say a 100,000 table has only 10 rows then most optimisers > would scan it sequentially because a sequential scan on a table that > fits in one disk block will always be quicker than indexed access. But that should go wrong with JDBC and JPA I would think. > Welcome to the world of RDBMS tuning: I hope your DBA is up to speed > on this passtime! I will need ‘some’ skills in convincing I am afraid. :'-( -- Cecil Westerhof Senior Software Engineer LinkedIn: http://www.linkedin.com/in/cecilwesterhof