Prev: Let the Experts to solve all your queries
Next: environment variables
From: Arne Vajhøj on 16 Apr 2010 19:48
On 16-04-2010 19:41, markspace wrote:
> Arne Vajh�j wrote:
>>> What's paging?
>>
>> http://en.wikipedia.org/wiki/Pagination_%28web%29
>
> Thanks. Normally when I think of "paging" I think of virtual memory.
> I've never thought of "return only some results of a query" as a
> performance enhancement, but I see how it could be considered one.

I am pretty sure that Google works faster showing only a
page instead of all hits.

Arne

From: markspace on 16 Apr 2010 19:56
Arne Vajh�j wrote:

>
> I am pretty sure that Google works faster showing only a
> page instead of all hits.


Perhaps the whole concept is just too obvious. It's less of a
"performance enhancement" and more of a "duh."
From: Arved Sandstrom on 16 Apr 2010 20:38
carmelo wrote:
> Hi everybody,
> I developed a desktop application which accesses data from a remote
> database with JPA using TopLink Essentials as provider. I'm having a
> very slow performance in loading data when the database is remote,
> which does not happen locally.
>
> I hope you can help me about how to fix it. I hope that it's not
> necessary to modify the whole application for loading data using JDBC,
> which I saw is fast in this case...
>
> Thank you in advance for your help!

For starters, how slow is slow? Roughly how many seconds for what size
result sets?

If you're going to stick with Oracle the first thing I'd suggest is
switching to EclipseLink from Toplink Essentials. If you want to handle
large result sets with streams or cursors, and do so in a fashion that
is close to JPA (query hints on JPA queries) then EclipseLink exposes
this capability (which was in full Toplink, but IIRC not in TLE).

Also, if you decide to page using setFirstResult()/setMaxResults() then
EclipseLink is also a much better bet than TLE: Toplink Essentials
brings back the complete result set from a query and then uses JDBC
absolute() etc at the driver level to actually give _you_ the limited
set. Whereas EclipseLink uses appropriate syntax on various platforms
(e.g. ROWNUM filtering on Oracle) to get the _database_ to do that
subsetting for you.

If you can't switch from TLE right now you can consider chunking by
retrieving all the primary keys for the full result set, and then using
IN with subsets of the keys (although even EclipseLink 1.2.x and up do
this better because they let you set Java lists as single parameters for
situations like this).

Other things to consider are:

1) caching - what's the nature of the data being queried? Is it amenable
to caching? What type of caching is best?

2) database query optimization - look at the SQL generated by your ORM.
Then look at the query execution plan for that query on your target DB.
Do you have an optimized situation? Can you improve the indices etc etc?

3) Is the actual JPA query efficient? Are you using joins, fetch joins
etc if they are suitable? Again, look at the generated SQL.

These are some initial ideas.

AHS
From: Donkey Hottie on 17 Apr 2010 05:19
On 17.4.2010 2:29, Arne Vajh�j wrote:
> On 16-04-2010 19:02, markspace wrote:
>> carmelo wrote:
>>> Maybe I could solve the problem with paging...
>>> What do you think? How could I implement it? The application was
>>> developed with Netbeans, do you know if it offers anything about
>>> paging?
>>
>> What's paging?
>
> http://en.wikipedia.org/wiki/Pagination_%28web%29
>

I have implemented pagination in an JPA application by leaving the JPA
out of that specific function, in pure jdbc.

The ResultSet (if so constructed) has this method absolute(int pos)

It allows me to jump to the correct "page" without too much hassle,
although the actual query must be prosessed again for each page. It
could be optimized, I guess, but performace was well enough for my
purpose that way.

--
Q: Why do mountain climbers rope themselves together?
A: To prevent the sensible ones from going home.
From: carmelo on 17 Apr 2010 14:31
Thank you guys for your answers.

< how slow is slow? Roughly how many seconds for what size result
sets?
59 seconds for 439 records!

< database query optimization - joins, fetch joins etc
I'm using anything but a simple "SELECT m FROM Mytable WHERE <simcple
conditions>"

< I'd suggest is switching to EclipseLink from Toplink Essentials
Do you think EclipseLink will have a better performance? How can I
swith from Toplink to EclipseLink? I'm using Netbeans IDE

< I have implemented pagination in an JPA application by leaving the
JPA out of that specific function, in pure jdbc.
I've implemented a pagination in JPA following this useful tutorial
http://rubenlaguna.com/wp/2009/08/18/jtable-beans-binding-and-jpa-pagination.
But I am not satisfied by its performance, in fact, scrolling down is
not immediate, you have to wait until it loads the remaining rows
subset. Do you think I need pure JDBC for better performance?
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Let the Experts to solve all your queries
Next: environment variables