Quantcast

Serious performance issues while using DTO's

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Serious performance issues while using DTO's

rosenfeld
I'm using some Data Transfer Objects with GORM and I'm experiencing a
big slow down when I change my DTO classes.

I mean, class reloading does work, but executeQuery will jump from about
800ms to more than 6s. This doesn't happen when I change other classes.

I can't create an example application reproducing the issue so that I
could create a JIRA, but I thought you might be interested that this is
happening to my application.

Maybe you could have some hint on what is slowing down the queries when
the DTO class used in the query is changed.

Cheers,
Rodrigo.


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

Graeme Rocher-4
Administrator
Your development environment may require more memory. Increase your
Xmx using JAVA_OPTS

export JAVA_OPTS=".."

Cheers

On Mon, Mar 5, 2012 at 8:26 PM, Rodrigo Rosenfeld Rosas
<[hidden email]> wrote:

> I'm using some Data Transfer Objects with GORM and I'm experiencing a big
> slow down when I change my DTO classes.
>
> I mean, class reloading does work, but executeQuery will jump from about
> 800ms to more than 6s. This doesn't happen when I change other classes.
>
> I can't create an example application reproducing the issue so that I could
> create a JIRA, but I thought you might be interested that this is happening
> to my application.
>
> Maybe you could have some hint on what is slowing down the queries when the
> DTO class used in the query is changed.
>
> Cheers,
> Rodrigo.
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>   http://xircles.codehaus.org/manage_email
>
>



--
Graeme Rocher
Grails Project Lead
SpringSource - A Division of VMware
http://www.springsource.com

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

rosenfeld
I was using 512MB and doubled it to 1GB. It had plenty of free heap and
permgen space according to VisualVM.

Even so, the problem persists, but I was wrong that only changes to the
DTO caused the issue.

The first time my action is called after the application boots was
giving me about 2.6s as the completion time.

After 3 refreshes, the time will fall down to 0.7s.

I've checked that changing my controller also triggered the issue.

The first time I call the action after touching any source file ("touch
grails-app/controllers/...."), it will take 5s.

The next time onwards it will remain about 3.5s no matter how much times
I press refresh and how many times I touch my source files (any of them).

Can you see a reason for this behavior?

Thanks in advance,
Rodrigo.


Em 05-03-2012 17:36, Graeme Rocher escreveu:

> Your development environment may require more memory. Increase your
> Xmx using JAVA_OPTS
>
> export JAVA_OPTS=".."
>
> Cheers
>
> On Mon, Mar 5, 2012 at 8:26 PM, Rodrigo Rosenfeld Rosas
> <[hidden email]>  wrote:
>> I'm using some Data Transfer Objects with GORM and I'm experiencing a big
>> slow down when I change my DTO classes.
>>
>> I mean, class reloading does work, but executeQuery will jump from about
>> 800ms to more than 6s. This doesn't happen when I change other classes.
>>
>> I can't create an example application reproducing the issue so that I could
>> create a JIRA, but I thought you might be interested that this is happening
>> to my application.
>>
>> Maybe you could have some hint on what is slowing down the queries when the
>> DTO class used in the query is changed.
>>
>> Cheers,
>> Rodrigo.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe from this list, please visit:
>>
>>    http://xircles.codehaus.org/manage_email
>>
>>
>
>


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

Graeme Rocher-4
Administrator
On Tue, Mar 6, 2012 at 12:43 AM, Rodrigo Rosenfeld Rosas
<[hidden email]> wrote:

> I was using 512MB and doubled it to 1GB. It had plenty of free heap and
> permgen space according to VisualVM.
>
> Even so, the problem persists, but I was wrong that only changes to the DTO
> caused the issue.
>
> The first time my action is called after the application boots was giving me
> about 2.6s as the completion time.
>
> After 3 refreshes, the time will fall down to 0.7s.

This difference can be explained by the the first request including
GSP compilation and then subsequent requests getting faster due to the
JVM JIT

>
> I've checked that changing my controller also triggered the issue.
>
> The first time I call the action after touching any source file ("touch
> grails-app/controllers/...."), it will take 5s.
>
> The next time onwards it will remain about 3.5s no matter how much times I
> press refresh and how many times I touch my source files (any of them).
>
> Can you see a reason for this behavior?

Only thing I can think is that it is recompiling GSPs on every request

Cheers

>
> Thanks in advance,
> Rodrigo.
>
>
> Em 05-03-2012 17:36, Graeme Rocher escreveu:
>
>> Your development environment may require more memory. Increase your
>> Xmx using JAVA_OPTS
>>
>> export JAVA_OPTS=".."
>>
>> Cheers
>>
>> On Mon, Mar 5, 2012 at 8:26 PM, Rodrigo Rosenfeld Rosas
>> <[hidden email]>  wrote:
>>>
>>> I'm using some Data Transfer Objects with GORM and I'm experiencing a big
>>> slow down when I change my DTO classes.
>>>
>>> I mean, class reloading does work, but executeQuery will jump from about
>>> 800ms to more than 6s. This doesn't happen when I change other classes.
>>>
>>> I can't create an example application reproducing the issue so that I
>>> could
>>> create a JIRA, but I thought you might be interested that this is
>>> happening
>>> to my application.
>>>
>>> Maybe you could have some hint on what is slowing down the queries when
>>> the
>>> DTO class used in the query is changed.
>>>
>>> Cheers,
>>> Rodrigo.
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe from this list, please visit:
>>>
>>>   http://xircles.codehaus.org/manage_email
>>>
>>>
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>   http://xircles.codehaus.org/manage_email
>
>



--
Graeme Rocher
Grails Project Lead
SpringSource - A Division of VMware
http://www.springsource.com

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

rosenfeld
Em 06-03-2012 04:50, Graeme Rocher escreveu:

> On Tue, Mar 6, 2012 at 12:43 AM, Rodrigo Rosenfeld Rosas
> <[hidden email]>  wrote:
>> I was using 512MB and doubled it to 1GB. It had plenty of free heap and
>> permgen space according to VisualVM.
>>
>> Even so, the problem persists, but I was wrong that only changes to the DTO
>> caused the issue.
>>
>> The first time my action is called after the application boots was giving me
>> about 2.6s as the completion time.
>>
>> After 3 refreshes, the time will fall down to 0.7s.
> This difference can be explained by the the first request including
> GSP compilation and then subsequent requests getting faster due to the
> JVM JIT
>
>> I've checked that changing my controller also triggered the issue.
>>
>> The first time I call the action after touching any source file ("touch
>> grails-app/controllers/...."), it will take 5s.
>>
>> The next time onwards it will remain about 3.5s no matter how much times I
>> press refresh and how many times I touch my source files (any of them).
>>
>> Can you see a reason for this behavior?
> Only thing I can think is that it is recompiling GSPs on every request

This wouldn't explain the behavior because the first time I call the
action after a "grails run-app", I get 2.6s including GSP compilation
and complete lack of Hibernate Cache (which I think is the main reason
for subsequent requests go from 2.6s to 0.7s).

I mean, even if Hibernate Cache is kept empty after changes and if GSP
are recompiled, shouldn't the requests take about 2.6s instead of 3.5s?
Since it took 5s after some change and then it went down to 3.5s it
means some optimization (probably Hibernate Cache) is still working
after reloading, but somehow the reloading is impacting negatively in
the performance in some permanent way.

Does this help guessing what else could be causing this behavior?

Thanks,
Rodrigo.


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

Graeme Rocher-4
Administrator
On Tue, Mar 6, 2012 at 1:28 PM, Rodrigo Rosenfeld Rosas
<[hidden email]> wrote:

> Em 06-03-2012 04:50, Graeme Rocher escreveu:
>
>> On Tue, Mar 6, 2012 at 12:43 AM, Rodrigo Rosenfeld Rosas
>> <[hidden email]>  wrote:
>>>
>>> I was using 512MB and doubled it to 1GB. It had plenty of free heap and
>>> permgen space according to VisualVM.
>>>
>>> Even so, the problem persists, but I was wrong that only changes to the
>>> DTO
>>> caused the issue.
>>>
>>> The first time my action is called after the application boots was giving
>>> me
>>> about 2.6s as the completion time.
>>>
>>> After 3 refreshes, the time will fall down to 0.7s.
>>
>> This difference can be explained by the the first request including
>> GSP compilation and then subsequent requests getting faster due to the
>> JVM JIT
>>
>>> I've checked that changing my controller also triggered the issue.
>>>
>>> The first time I call the action after touching any source file ("touch
>>> grails-app/controllers/...."), it will take 5s.
>>>
>>> The next time onwards it will remain about 3.5s no matter how much times
>>> I
>>> press refresh and how many times I touch my source files (any of them).
>>>
>>> Can you see a reason for this behavior?
>>
>> Only thing I can think is that it is recompiling GSPs on every request
>
>
> This wouldn't explain the behavior because the first time I call the action
> after a "grails run-app", I get 2.6s including GSP compilation and complete
> lack of Hibernate Cache (which I think is the main reason for subsequent
> requests go from 2.6s to 0.7s).
>
> I mean, even if Hibernate Cache is kept empty after changes and if GSP are
> recompiled, shouldn't the requests take about 2.6s instead of 3.5s? Since it
> took 5s after some change and then it went down to 3.5s it means some
> optimization (probably Hibernate Cache) is still working after reloading,
> but somehow the reloading is impacting negatively in the performance in some
> permanent way.
>
> Does this help guessing what else could be causing this behavior?

Not really, I suggest you create an example that reproduces the
problem and attach to a JIRA

Cheers

>
> Thanks,
>
> Rodrigo.
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>   http://xircles.codehaus.org/manage_email
>
>



--
Graeme Rocher
Grails Project Lead
SpringSource - A Division of VMware
http://www.springsource.com

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

rosenfeld
Em 06-03-2012 11:00, Graeme Rocher escreveu:

> On Tue, Mar 6, 2012 at 1:28 PM, Rodrigo Rosenfeld Rosas
> <[hidden email]>  wrote:
>> Em 06-03-2012 04:50, Graeme Rocher escreveu:
>>
>>> On Tue, Mar 6, 2012 at 12:43 AM, Rodrigo Rosenfeld Rosas
>>> <[hidden email]>    wrote:
>>>> I was using 512MB and doubled it to 1GB. It had plenty of free heap and
>>>> permgen space according to VisualVM.
>>>>
>>>> Even so, the problem persists, but I was wrong that only changes to the
>>>> DTO
>>>> caused the issue.
>>>>
>>>> The first time my action is called after the application boots was giving
>>>> me
>>>> about 2.6s as the completion time.
>>>>
>>>> After 3 refreshes, the time will fall down to 0.7s.
>>> This difference can be explained by the the first request including
>>> GSP compilation and then subsequent requests getting faster due to the
>>> JVM JIT
>>>
>>>> I've checked that changing my controller also triggered the issue.
>>>>
>>>> The first time I call the action after touching any source file ("touch
>>>> grails-app/controllers/...."), it will take 5s.
>>>>
>>>> The next time onwards it will remain about 3.5s no matter how much times
>>>> I
>>>> press refresh and how many times I touch my source files (any of them).
>>>>
>>>> Can you see a reason for this behavior?
>>> Only thing I can think is that it is recompiling GSPs on every request
>>
>> This wouldn't explain the behavior because the first time I call the action
>> after a "grails run-app", I get 2.6s including GSP compilation and complete
>> lack of Hibernate Cache (which I think is the main reason for subsequent
>> requests go from 2.6s to 0.7s).
>>
>> I mean, even if Hibernate Cache is kept empty after changes and if GSP are
>> recompiled, shouldn't the requests take about 2.6s instead of 3.5s? Since it
>> took 5s after some change and then it went down to 3.5s it means some
>> optimization (probably Hibernate Cache) is still working after reloading,
>> but somehow the reloading is impacting negatively in the performance in some
>> permanent way.
>>
>> Does this help guessing what else could be causing this behavior?
> Not really, I suggest you create an example that reproduces the
> problem and attach to a JIRA

I wish I could. I tried but no luck so far... :(


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

Roberto Guerra
I use jdbc when I need fast queries and complicated joins. It is magnitudes of order faster than hibernate. If that still doesn't solve the issue, then I would say it is probably something inherent with grails. To date though, I have not had an issue when I switch to jdbc for performance.

On Tue, Mar 6, 2012 at 8:29 AM, Rodrigo Rosenfeld Rosas <[hidden email]> wrote:
Em 06-03-2012 11:00, Graeme Rocher escreveu:

On Tue, Mar 6, 2012 at 1:28 PM, Rodrigo Rosenfeld Rosas
<[hidden email]>  wrote:
Em 06-03-2012 04:50, Graeme Rocher escreveu:

On Tue, Mar 6, 2012 at 12:43 AM, Rodrigo Rosenfeld Rosas
<[hidden email]>    wrote:
I was using 512MB and doubled it to 1GB. It had plenty of free heap and
permgen space according to VisualVM.

Even so, the problem persists, but I was wrong that only changes to the
DTO
caused the issue.

The first time my action is called after the application boots was giving
me
about 2.6s as the completion time.

After 3 refreshes, the time will fall down to 0.7s.
This difference can be explained by the the first request including
GSP compilation and then subsequent requests getting faster due to the
JVM JIT

I've checked that changing my controller also triggered the issue.

The first time I call the action after touching any source file ("touch
grails-app/controllers/...."), it will take 5s.

The next time onwards it will remain about 3.5s no matter how much times
I
press refresh and how many times I touch my source files (any of them).

Can you see a reason for this behavior?
Only thing I can think is that it is recompiling GSPs on every request

This wouldn't explain the behavior because the first time I call the action
after a "grails run-app", I get 2.6s including GSP compilation and complete
lack of Hibernate Cache (which I think is the main reason for subsequent
requests go from 2.6s to 0.7s).

I mean, even if Hibernate Cache is kept empty after changes and if GSP are
recompiled, shouldn't the requests take about 2.6s instead of 3.5s? Since it
took 5s after some change and then it went down to 3.5s it means some
optimization (probably Hibernate Cache) is still working after reloading,
but somehow the reloading is impacting negatively in the performance in some
permanent way.

Does this help guessing what else could be causing this behavior?
Not really, I suggest you create an example that reproduces the
problem and attach to a JIRA

I wish I could. I tried but no luck so far... :(



---------------------------------------------------------------------
To unsubscribe from this list, please visit:

  http://xircles.codehaus.org/manage_email





--
The Journey Is The Reward.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

rosenfeld
It is not that simple.

First, changing this specific implementation to pure JDBC (or Spring JdbcTemplate) would require much more code (probably 8 times more code at least) and it would be much harder to maintain as I would have to duplicate some logic.

But even if I did that, it would still be slower than using Hibernate. I've noticed that Hibernate is smart enough using its cache to avoid some queries that I'd expect it to perform.

I'm not complaining about Hibernate performance for this case as those queries take so long because they're really expensive.

What I'm complaining about is that they are taking much longer after changes to my Groovy source files in development mode.

It is ok for me to wait about 1 second each time I refresh the page to test some modification to my JavaScript files, but having to wait more than 5s just because I've changed some source file is what is bothering me.

What I'm doing for now is restarting the application after changing my Groovy sources as most of my changes are in the client-side.

But thanks for the suggestion anyway.

Cheers,
Rodrigo.

Em 06-03-2012 11:54, Roberto Guerra escreveu:
I use jdbc when I need fast queries and complicated joins. It is magnitudes of order faster than hibernate. If that still doesn't solve the issue, then I would say it is probably something inherent with grails. To date though, I have not had an issue when I switch to jdbc for performance.

On Tue, Mar 6, 2012 at 8:29 AM, Rodrigo Rosenfeld Rosas <[hidden email]> wrote:
Em 06-03-2012 11:00, Graeme Rocher escreveu:

On Tue, Mar 6, 2012 at 1:28 PM, Rodrigo Rosenfeld Rosas
<[hidden email]>  wrote:
Em 06-03-2012 04:50, Graeme Rocher escreveu:

On Tue, Mar 6, 2012 at 12:43 AM, Rodrigo Rosenfeld Rosas
<[hidden email]>    wrote:
I was using 512MB and doubled it to 1GB. It had plenty of free heap and
permgen space according to VisualVM.

Even so, the problem persists, but I was wrong that only changes to the
DTO
caused the issue.

The first time my action is called after the application boots was giving
me
about 2.6s as the completion time.

After 3 refreshes, the time will fall down to 0.7s.
This difference can be explained by the the first request including
GSP compilation and then subsequent requests getting faster due to the
JVM JIT

I've checked that changing my controller also triggered the issue.

The first time I call the action after touching any source file ("touch
grails-app/controllers/...."), it will take 5s.

The next time onwards it will remain about 3.5s no matter how much times
I
press refresh and how many times I touch my source files (any of them).

Can you see a reason for this behavior?
Only thing I can think is that it is recompiling GSPs on every request

This wouldn't explain the behavior because the first time I call the action
after a "grails run-app", I get 2.6s including GSP compilation and complete
lack of Hibernate Cache (which I think is the main reason for subsequent
requests go from 2.6s to 0.7s).

I mean, even if Hibernate Cache is kept empty after changes and if GSP are
recompiled, shouldn't the requests take about 2.6s instead of 3.5s? Since it
took 5s after some change and then it went down to 3.5s it means some
optimization (probably Hibernate Cache) is still working after reloading,
but somehow the reloading is impacting negatively in the performance in some
permanent way.

Does this help guessing what else could be causing this behavior?
Not really, I suggest you create an example that reproduces the
problem and attach to a JIRA

I wish I could. I tried but no luck so far... :(

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

Roberto Guerra
I remember having that issue with 1.3.7, have not experienced it with 2.0.

It was weird, because only had it in certain projects. I could never pin it down so just restarted the app, like you mentioned.

On Tue, Mar 6, 2012 at 9:51 AM, Rodrigo Rosenfeld Rosas <[hidden email]> wrote:
It is not that simple.

First, changing this specific implementation to pure JDBC (or Spring JdbcTemplate) would require much more code (probably 8 times more code at least) and it would be much harder to maintain as I would have to duplicate some logic.

But even if I did that, it would still be slower than using Hibernate. I've noticed that Hibernate is smart enough using its cache to avoid some queries that I'd expect it to perform.

I'm not complaining about Hibernate performance for this case as those queries take so long because they're really expensive.

What I'm complaining about is that they are taking much longer after changes to my Groovy source files in development mode.

It is ok for me to wait about 1 second each time I refresh the page to test some modification to my JavaScript files, but having to wait more than 5s just because I've changed some source file is what is bothering me.

What I'm doing for now is restarting the application after changing my Groovy sources as most of my changes are in the client-side.

But thanks for the suggestion anyway.

Cheers,
Rodrigo.

Em 06-03-2012 11:54, Roberto Guerra escreveu:
I use jdbc when I need fast queries and complicated joins. It is magnitudes of order faster than hibernate. If that still doesn't solve the issue, then I would say it is probably something inherent with grails. To date though, I have not had an issue when I switch to jdbc for performance.

On Tue, Mar 6, 2012 at 8:29 AM, Rodrigo Rosenfeld Rosas <[hidden email]> wrote:
Em 06-03-2012 11:00, Graeme Rocher escreveu:

On Tue, Mar 6, 2012 at 1:28 PM, Rodrigo Rosenfeld Rosas
<[hidden email]>  wrote:
Em 06-03-2012 04:50, Graeme Rocher escreveu:

On Tue, Mar 6, 2012 at 12:43 AM, Rodrigo Rosenfeld Rosas
<[hidden email]>    wrote:
I was using 512MB and doubled it to 1GB. It had plenty of free heap and
permgen space according to VisualVM.

Even so, the problem persists, but I was wrong that only changes to the
DTO
caused the issue.

The first time my action is called after the application boots was giving
me
about 2.6s as the completion time.

After 3 refreshes, the time will fall down to 0.7s.
This difference can be explained by the the first request including
GSP compilation and then subsequent requests getting faster due to the
JVM JIT

I've checked that changing my controller also triggered the issue.

The first time I call the action after touching any source file ("touch
grails-app/controllers/...."), it will take 5s.

The next time onwards it will remain about 3.5s no matter how much times
I
press refresh and how many times I touch my source files (any of them).

Can you see a reason for this behavior?
Only thing I can think is that it is recompiling GSPs on every request

This wouldn't explain the behavior because the first time I call the action
after a "grails run-app", I get 2.6s including GSP compilation and complete
lack of Hibernate Cache (which I think is the main reason for subsequent
requests go from 2.6s to 0.7s).

I mean, even if Hibernate Cache is kept empty after changes and if GSP are
recompiled, shouldn't the requests take about 2.6s instead of 3.5s? Since it
took 5s after some change and then it went down to 3.5s it means some
optimization (probably Hibernate Cache) is still working after reloading,
but somehow the reloading is impacting negatively in the performance in some
permanent way.

Does this help guessing what else could be causing this behavior?
Not really, I suggest you create an example that reproduces the
problem and attach to a JIRA

I wish I could. I tried but no luck so far... :(




--
The Journey Is The Reward.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

burtbeckwith
In reply to this post by Roberto Guerra
Magnitudes or order faster than Hibernate? Seriously? Show me a query that's 1000x or 100x faster (or even just 10x, 1 order of magnitude) in JDBC than Hibernate.

Burt

On Tuesday, March 06, 2012 08:54:34 AM Roberto Guerra wrote:
> I use jdbc when I need fast queries and complicated joins. It is magnitudes
> of order faster than hibernate. If that still doesn't solve the issue, then
> I would say it is probably something inherent with grails. To date though,
> I have not had an issue when I switch to jdbc for performance.
>


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

Roberto Guerra
Here is an example.



SELECT   

CASE
WHEN '$P!{item_type}'='P'
  THEN
    (SELECT pharm.name FROM acsis_adt_pharmaceuticals AS pharm WHERE inv_item.details_id=pharm.pharmaceutical_id)
WHEN '$P!{item_type}'='S'
  THEN
    (SELECT supp.name FROM acsis_adt_supplies AS supp WHERE inv_item.details_id=supp.supply_id)
  ELSE NULL END AS item,



--DISPLAY THE QTY REQUESTED
--We first check if the requisition is the parent requisition. This can be
--obtained by querying if the ri.parent_requisition_item_id IS NULL.
(SELECT ri.quantity*(SELECT ppi1.items_per_package*(CASE WHEN ppi1.child_package_profile_item_id IS NOT NULL
                                THEN ppi2.items_per_package
                        ELSE 1 END)*(CASE WHEN ppi2.child_package_profile_item_id IS NOT NULL
                                THEN ppi3.items_per_package
                        ELSE 1 END)*(CASE WHEN ppi3.child_package_profile_item_id IS NOT NULL
                                THEN ppi4.items_per_package
                        ELSE 1 END)
                        FROM acsis_scm_package_profile_items AS ppi1
                        LEFT JOIN acsis_scm_package_profile_items AS ppi2 ON ppi1.child_package_profile_item_id=ppi2.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi3 ON ppi2.child_package_profile_item_id=ppi3.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi4 ON ppi3.child_package_profile_item_id=ppi4.package_profile_item_id
                WHERE ri.package_profile_item_id=ppi1.package_profile_item_id)
        --FROM acsis_scm_requisition_items AS ri2
        --WHERE ri2.requisition_item_id=ri.parent_requisition_item_id
        ) AS qty_req,

--DISPLAY THE QTY RECEIVED
(SELECT ril.quantity*(SELECT ppi1.items_per_package*(CASE WHEN ppi1.child_package_profile_item_id IS NOT NULL
                                THEN ppi2.items_per_package
                        ELSE 1 END)*(CASE WHEN ppi2.child_package_profile_item_id IS NOT NULL
                                THEN ppi3.items_per_package
                        ELSE 1 END)*(CASE WHEN ppi3.child_package_profile_item_id IS NOT NULL
                                THEN ppi4.items_per_package
                        ELSE 1 END)
                        FROM acsis_scm_package_profile_items AS ppi1
                        LEFT JOIN acsis_scm_package_profile_items AS ppi2 ON ppi1.child_package_profile_item_id=ppi2.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi3 ON ppi2.child_package_profile_item_id=ppi3.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi4 ON ppi3.child_package_profile_item_id=ppi4.package_profile_item_id
                WHERE ril.package_profile_item_id=ppi1.package_profile_item_id )
        --FROM acsis_scm_requisition_items AS ri2
        ) AS qty_req,

--DISPLAY THE QTY RECEIVED
(SELECT ril.quantity*(SELECT ppi1.items_per_package*(CASE WHEN ppi1.child_package_profile_item_id IS NOT NULL
                                THEN ppi2.items_per_package
                        ELSE 1 END)*(CASE WHEN ppi2.child_package_profile_item_id IS NOT NULL
                                THEN ppi3.items_per_package
                        ELSE 1 END)*(CASE WHEN ppi3.child_package_profile_item_id IS NOT NULL
                                THEN ppi4.items_per_package
                        ELSE 1 END)
                        FROM acsis_scm_package_profile_items AS ppi1
                        LEFT JOIN acsis_scm_package_profile_items AS ppi2 ON ppi1.child_package_profile_item_id=ppi2.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi3 ON ppi2.child_package_profile_item_id=ppi3.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi4 ON ppi3.child_package_profile_item_id=ppi4.package_profile_item_id
                WHERE ril.package_profile_item_id=ppi1.package_profile_item_id )
    

        --WHERE req2.requisition_id=req.reference_requisition_id
        --AND ril2.requisition_item_lot_id=ril.requisition_item_lot_id
        ) AS qty_rcvd,


--Display the requisition_item_id
req.requisition_id AS req_id,
--req.reference_requisition_id,
ril.arrival_time::date AS arrival_time,
--ri.inventory_item_id AS inv_itm_id,
--ri.requisition_item_id AS req_itm_id,

--DISPLAY THE ITEM LOT NUMBER
(SELECT iil.lot_number
        FROM acsis_scm_inventory_item_lots AS iil
        WHERE ril.inventory_item_lot_id=iil.inventory_item_lot_id
        AND ril.requisition_item_id=ri.requisition_item_id) AS lot_number,


--DISPLAY THE UNIT COST
(SELECT iil.cost
        FROM acsis_scm_inventory_item_lots AS iil
        WHERE ril.inventory_item_lot_id=iil.inventory_item_lot_id
        AND ril.requisition_item_id=ri.requisition_item_id) AS cost




FROM acsis_scm_requisitions AS req
  INNER JOIN acsis_scm_requisition_items AS ri ON req.requisition_id=ri.requisition_id
  INNER JOIN acsis_scm_inventory_items AS inv_item ON ri.inventory_item_id=inv_item.inventory_item_id --AND inv_item.item_type='$P!{item_type}'
  INNER JOIN acsis_scm_requisition_statuses AS rst ON req.requisition_status_id=rst.requisition_status_id
  INNER JOIN acsis_scm_requisition_item_lots AS ril ON ri.requisition_item_id=ril.requisition_item_id
  INNER JOIN acsis_scm_requisition_types AS rt ON req.requisition_type_id=rt.requisition_type_id
  --INNER JOIN acsis_scm_inventory_item_lots AS iil ON ril.inventory_item_lot_id=iil.inventory_item_lot_id
 
 
WHERE
inv_item.inventory_item_id<>2624
AND ri.active='true' AND req.active='true'
AND ril.active='true'
AND ril.arrival_time::date>='$P!{begin_date}' AND ril.arrival_time::date<='$P!{end_date}'

AND req.requisitioning_facility_id=31
AND inv_item.item_type='$P!{item_type}'

AND rt.constant ILIKE 'REQUEST%'

GROUP BY
item,
ri.parent_requisition_item_id,
ri.package_profile_item_id,
req.requisition_id,
req.reference_requisition_id,
ril.requisition_item_id,
ril.inventory_item_lot_id,
ri.requisition_item_id,
ril.quantity,
ri.quantity,
ril.package_profile_item_id,
ri.inventory_item_id,
ril.requisition_item_lot_id,
ril.arrival_time

ORDER BY item

                                                          
                                                                                                                            

On Tue, Mar 6, 2012 at 10:15 AM, Burt Beckwith <[hidden email]> wrote:
Magnitudes or order faster than Hibernate? Seriously? Show me a query that's 1000x or 100x faster (or even just 10x, 1 order of magnitude) in JDBC than Hibernate.

Burt

On Tuesday, March 06, 2012 08:54:34 AM Roberto Guerra wrote:
> I use jdbc when I need fast queries and complicated joins. It is magnitudes
> of order faster than hibernate. If that still doesn't solve the issue, then
> I would say it is probably something inherent with grails. To date though,
> I have not had an issue when I switch to jdbc for performance.
>


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email





--
The Journey Is The Reward.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

burtbeckwith
That's a bunch of examples of complicated queries. Where are the slow Hibernate equivalents? I agree that sometimes Hibernate isn't always appropriate for complex queries, but you said "magnitudes of order faster than hibernate".

Burt

Roberto Guerra wrote
Here is an example.



*SELECT

CASE
WHEN '$P!{item_type}'='P'
  THEN
    (SELECT pharm.name FROM acsis_adt_pharmaceuticals AS pharm WHERE
inv_item.details_id=pharm.pharmaceutical_id)
WHEN '$P!{item_type}'='S'
  THEN
    (SELECT supp.name FROM acsis_adt_supplies AS supp WHERE
inv_item.details_id=supp.supply_id)
  ELSE NULL END AS item,



--DISPLAY THE QTY REQUESTED
--We first check if the requisition is the parent requisition. This can be
--obtained by querying if the ri.parent_requisition_item_id IS NULL.
(SELECT ri.quantity*(SELECT ppi1.items_per_package*(CASE WHEN
ppi1.child_package_profile_item_id IS NOT NULL
                                THEN ppi2.items_per_package
                        ELSE 1 END)*(CASE WHEN
ppi2.child_package_profile_item_id IS NOT NULL
                                THEN ppi3.items_per_package
                        ELSE 1 END)*(CASE WHEN
ppi3.child_package_profile_item_id IS NOT NULL
                                THEN ppi4.items_per_package
                        ELSE 1 END)
                        FROM acsis_scm_package_profile_items AS ppi1
                        LEFT JOIN acsis_scm_package_profile_items AS ppi2
ON ppi1.child_package_profile_item_id=ppi2.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi3
ON ppi2.child_package_profile_item_id=ppi3.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi4
ON ppi3.child_package_profile_item_id=ppi4.package_profile_item_id
                WHERE
ri.package_profile_item_id=ppi1.package_profile_item_id)
        --FROM acsis_scm_requisition_items AS ri2
        --WHERE ri2.requisition_item_id=ri.parent_requisition_item_id
        ) AS qty_req,

--DISPLAY THE QTY RECEIVED
(SELECT ril.quantity*(SELECT ppi1.items_per_package*(CASE WHEN
ppi1.child_package_profile_item_id IS NOT NULL
                                THEN ppi2.items_per_package
                        ELSE 1 END)*(CASE WHEN
ppi2.child_package_profile_item_id IS NOT NULL
                                THEN ppi3.items_per_package
                        ELSE 1 END)*(CASE WHEN
ppi3.child_package_profile_item_id IS NOT NULL
                                THEN ppi4.items_per_package
                        ELSE 1 END)
                        FROM acsis_scm_package_profile_items AS ppi1
                        LEFT JOIN acsis_scm_package_profile_items AS ppi2
ON ppi1.child_package_profile_item_id=ppi2.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi3
ON ppi2.child_package_profile_item_id=ppi3.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi4
ON ppi3.child_package_profile_item_id=ppi4.package_profile_item_id
                WHERE
ril.package_profile_item_id=ppi1.package_profile_item_id )
        --FROM acsis_scm_requisition_items AS ri2
        ) AS qty_req,

--DISPLAY THE QTY RECEIVED
(SELECT ril.quantity*(SELECT ppi1.items_per_package*(CASE WHEN
ppi1.child_package_profile_item_id IS NOT NULL
                                THEN ppi2.items_per_package
                        ELSE 1 END)*(CASE WHEN
ppi2.child_package_profile_item_id IS NOT NULL
                                THEN ppi3.items_per_package
                        ELSE 1 END)*(CASE WHEN
ppi3.child_package_profile_item_id IS NOT NULL
                                THEN ppi4.items_per_package
                        ELSE 1 END)
                        FROM acsis_scm_package_profile_items AS ppi1
                        LEFT JOIN acsis_scm_package_profile_items AS ppi2
ON ppi1.child_package_profile_item_id=ppi2.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi3
ON ppi2.child_package_profile_item_id=ppi3.package_profile_item_id
                        LEFT JOIN acsis_scm_package_profile_items AS ppi4
ON ppi3.child_package_profile_item_id=ppi4.package_profile_item_id
                WHERE
ril.package_profile_item_id=ppi1.package_profile_item_id )


        --WHERE req2.requisition_id=req.reference_requisition_id
        --AND ril2.requisition_item_lot_id=ril.requisition_item_lot_id
        ) AS qty_rcvd,


--Display the requisition_item_id
req.requisition_id AS req_id,
--req.reference_requisition_id,
ril.arrival_time::date AS arrival_time,
--ri.inventory_item_id AS inv_itm_id,
--ri.requisition_item_id AS req_itm_id,

--DISPLAY THE ITEM LOT NUMBER
(SELECT iil.lot_number
        FROM acsis_scm_inventory_item_lots AS iil
        WHERE ril.inventory_item_lot_id=iil.inventory_item_lot_id
        AND ril.requisition_item_id=ri.requisition_item_id) AS lot_number,


--DISPLAY THE UNIT COST
(SELECT iil.cost
        FROM acsis_scm_inventory_item_lots AS iil
        WHERE ril.inventory_item_lot_id=iil.inventory_item_lot_id
        AND ril.requisition_item_id=ri.requisition_item_id) AS cost




FROM acsis_scm_requisitions AS req
  INNER JOIN acsis_scm_requisition_items AS ri ON
req.requisition_id=ri.requisition_id
  INNER JOIN acsis_scm_inventory_items AS inv_item ON
ri.inventory_item_id=inv_item.inventory_item_id --AND
inv_item.item_type='$P!{item_type}'
  INNER JOIN acsis_scm_requisition_statuses AS rst ON
req.requisition_status_id=rst.requisition_status_id
  INNER JOIN acsis_scm_requisition_item_lots AS ril ON
ri.requisition_item_id=ril.requisition_item_id
  INNER JOIN acsis_scm_requisition_types AS rt ON
req.requisition_type_id=rt.requisition_type_id
  --INNER JOIN acsis_scm_inventory_item_lots AS iil ON
ril.inventory_item_lot_id=iil.inventory_item_lot_id


WHERE
inv_item.inventory_item_id<>2624
AND ri.active='true' AND req.active='true'
AND ril.active='true'
AND ril.arrival_time::date>='$P!{begin_date}' AND
ril.arrival_time::date<='$P!{end_date}'

AND req.requisitioning_facility_id=31
AND inv_item.item_type='$P!{item_type}'

AND rt.constant ILIKE 'REQUEST%'

GROUP BY
item,
ri.parent_requisition_item_id,
ri.package_profile_item_id,
req.requisition_id,
req.reference_requisition_id,
ril.requisition_item_id,
ril.inventory_item_lot_id,
ri.requisition_item_id,
ril.quantity,
ri.quantity,
ril.package_profile_item_id,
ri.inventory_item_id,
ril.requisition_item_lot_id,
ril.arrival_time

ORDER BY item
*




On Tue, Mar 6, 2012 at 10:15 AM, Burt Beckwith <[hidden email]>wrote:

> Magnitudes or order faster than Hibernate? Seriously? Show me a query
> that's 1000x or 100x faster (or even just 10x, 1 order of magnitude) in
> JDBC than Hibernate.
>
> Burt
>
> On Tuesday, March 06, 2012 08:54:34 AM Roberto Guerra wrote:
> > I use jdbc when I need fast queries and complicated joins. It is
> magnitudes
> > of order faster than hibernate. If that still doesn't solve the issue,
> then
> > I would say it is probably something inherent with grails. To date
> though,
> > I have not had an issue when I switch to jdbc for performance.
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email
>
>
>


--
*The Journey Is The Reward.*
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

Roberto Guerra
It was probably an exaggeration, and I haven't really measured it. What I've experienced is that when dealing with many-to-many relationships, the queries are noticeably slower with hibernate. Especially when retrieving thousands of records. I can't really show you the specific query in hql vs sql because of NDA. However, the one I posted is an example of a legacy database (hate it sooo much). To make it fun, I introduced grails to move data around to another database where analysis is done. Initially I mapped the tables to use hibernate but it became a nightmare and the queries dragged along, and some were just not possible to do.

In another pure grails app, I noticed similar behaviour with many-to-many and large amounts of data. These were not simple queries anyway, but for these complicated queries, the solution I've resorted to is using jdbc and has worked pretty well. If Rodrigo's queries were also complex enough, he could probably try jdbc to see if that helps.

On Tue, Mar 6, 2012 at 10:58 AM, burtbeckwith <[hidden email]> wrote:
That's a bunch of examples of complicated queries. Where are the slow
Hibernate equivalents? I agree that sometimes Hibernate isn't always
appropriate for complex queries, but you said "magnitudes of order faster
than hibernate".

Burt


Roberto Guerra wrote
>
> Here is an example.
>
>
>
> *SELECT
>
> CASE
> WHEN '$P!{item_type}'='P'
>   THEN
>     (SELECT pharm.name FROM acsis_adt_pharmaceuticals AS pharm WHERE
> inv_item.details_id=pharm.pharmaceutical_id)
> WHEN '$P!{item_type}'='S'
>   THEN
>     (SELECT supp.name FROM acsis_adt_supplies AS supp WHERE
> inv_item.details_id=supp.supply_id)
>   ELSE NULL END AS item,
>
>
>
> --DISPLAY THE QTY REQUESTED
> --We first check if the requisition is the parent requisition. This can be
> --obtained by querying if the ri.parent_requisition_item_id IS NULL.
> (SELECT ri.quantity*(SELECT ppi1.items_per_package*(CASE WHEN
> ppi1.child_package_profile_item_id IS NOT NULL
>                                 THEN ppi2.items_per_package
>                         ELSE 1 END)*(CASE WHEN
> ppi2.child_package_profile_item_id IS NOT NULL
>                                 THEN ppi3.items_per_package
>                         ELSE 1 END)*(CASE WHEN
> ppi3.child_package_profile_item_id IS NOT NULL
>                                 THEN ppi4.items_per_package
>                         ELSE 1 END)
>                         FROM acsis_scm_package_profile_items AS ppi1
>                         LEFT JOIN acsis_scm_package_profile_items AS ppi2
> ON ppi1.child_package_profile_item_id=ppi2.package_profile_item_id
>                         LEFT JOIN acsis_scm_package_profile_items AS ppi3
> ON ppi2.child_package_profile_item_id=ppi3.package_profile_item_id
>                         LEFT JOIN acsis_scm_package_profile_items AS ppi4
> ON ppi3.child_package_profile_item_id=ppi4.package_profile_item_id
>                 WHERE
> ri.package_profile_item_id=ppi1.package_profile_item_id)
>         --FROM acsis_scm_requisition_items AS ri2
>         --WHERE ri2.requisition_item_id=ri.parent_requisition_item_id
>         ) AS qty_req,
>
> --DISPLAY THE QTY RECEIVED
> (SELECT ril.quantity*(SELECT ppi1.items_per_package*(CASE WHEN
> ppi1.child_package_profile_item_id IS NOT NULL
>                                 THEN ppi2.items_per_package
>                         ELSE 1 END)*(CASE WHEN
> ppi2.child_package_profile_item_id IS NOT NULL
>                                 THEN ppi3.items_per_package
>                         ELSE 1 END)*(CASE WHEN
> ppi3.child_package_profile_item_id IS NOT NULL
>                                 THEN ppi4.items_per_package
>                         ELSE 1 END)
>                         FROM acsis_scm_package_profile_items AS ppi1
>                         LEFT JOIN acsis_scm_package_profile_items AS ppi2
> ON ppi1.child_package_profile_item_id=ppi2.package_profile_item_id
>                         LEFT JOIN acsis_scm_package_profile_items AS ppi3
> ON ppi2.child_package_profile_item_id=ppi3.package_profile_item_id
>                         LEFT JOIN acsis_scm_package_profile_items AS ppi4
> ON ppi3.child_package_profile_item_id=ppi4.package_profile_item_id
>                 WHERE
> ril.package_profile_item_id=ppi1.package_profile_item_id )
>         --FROM acsis_scm_requisition_items AS ri2
>         ) AS qty_req,
>
> --DISPLAY THE QTY RECEIVED
> (SELECT ril.quantity*(SELECT ppi1.items_per_package*(CASE WHEN
> ppi1.child_package_profile_item_id IS NOT NULL
>                                 THEN ppi2.items_per_package
>                         ELSE 1 END)*(CASE WHEN
> ppi2.child_package_profile_item_id IS NOT NULL
>                                 THEN ppi3.items_per_package
>                         ELSE 1 END)*(CASE WHEN
> ppi3.child_package_profile_item_id IS NOT NULL
>                                 THEN ppi4.items_per_package
>                         ELSE 1 END)
>                         FROM acsis_scm_package_profile_items AS ppi1
>                         LEFT JOIN acsis_scm_package_profile_items AS ppi2
> ON ppi1.child_package_profile_item_id=ppi2.package_profile_item_id
>                         LEFT JOIN acsis_scm_package_profile_items AS ppi3
> ON ppi2.child_package_profile_item_id=ppi3.package_profile_item_id
>                         LEFT JOIN acsis_scm_package_profile_items AS ppi4
> ON ppi3.child_package_profile_item_id=ppi4.package_profile_item_id
>                 WHERE
> ril.package_profile_item_id=ppi1.package_profile_item_id )
>
>
>         --WHERE req2.requisition_id=req.reference_requisition_id
>         --AND ril2.requisition_item_lot_id=ril.requisition_item_lot_id
>         ) AS qty_rcvd,
>
>
> --Display the requisition_item_id
> req.requisition_id AS req_id,
> --req.reference_requisition_id,
> ril.arrival_time::date AS arrival_time,
> --ri.inventory_item_id AS inv_itm_id,
> --ri.requisition_item_id AS req_itm_id,
>
> --DISPLAY THE ITEM LOT NUMBER
> (SELECT iil.lot_number
>         FROM acsis_scm_inventory_item_lots AS iil
>         WHERE ril.inventory_item_lot_id=iil.inventory_item_lot_id
>         AND ril.requisition_item_id=ri.requisition_item_id) AS lot_number,
>
>
> --DISPLAY THE UNIT COST
> (SELECT iil.cost
>         FROM acsis_scm_inventory_item_lots AS iil
>         WHERE ril.inventory_item_lot_id=iil.inventory_item_lot_id
>         AND ril.requisition_item_id=ri.requisition_item_id) AS cost
>
>
>
>
> FROM acsis_scm_requisitions AS req
>   INNER JOIN acsis_scm_requisition_items AS ri ON
> req.requisition_id=ri.requisition_id
>   INNER JOIN acsis_scm_inventory_items AS inv_item ON
> ri.inventory_item_id=inv_item.inventory_item_id --AND
> inv_item.item_type='$P!{item_type}'
>   INNER JOIN acsis_scm_requisition_statuses AS rst ON
> req.requisition_status_id=rst.requisition_status_id
>   INNER JOIN acsis_scm_requisition_item_lots AS ril ON
> ri.requisition_item_id=ril.requisition_item_id
>   INNER JOIN acsis_scm_requisition_types AS rt ON
> req.requisition_type_id=rt.requisition_type_id
>   --INNER JOIN acsis_scm_inventory_item_lots AS iil ON
> ril.inventory_item_lot_id=iil.inventory_item_lot_id
>
>
> WHERE
> inv_item.inventory_item_id<>2624
> AND ri.active='true' AND req.active='true'
> AND ril.active='true'
> AND ril.arrival_time::date>='$P!{begin_date}' AND
> ril.arrival_time::date<='$P!{end_date}'
>
> AND req.requisitioning_facility_id=31
> AND inv_item.item_type='$P!{item_type}'
>
> AND rt.constant ILIKE 'REQUEST%'
>
> GROUP BY
> item,
> ri.parent_requisition_item_id,
> ri.package_profile_item_id,
> req.requisition_id,
> req.reference_requisition_id,
> ril.requisition_item_id,
> ril.inventory_item_lot_id,
> ri.requisition_item_id,
> ril.quantity,
> ri.quantity,
> ril.package_profile_item_id,
> ri.inventory_item_id,
> ril.requisition_item_lot_id,
> ril.arrival_time
>
> ORDER BY item
> *
>
>
>
>
> On Tue, Mar 6, 2012 at 10:15 AM, Burt Beckwith &lt;burt@&gt;wrote:
>
>> Magnitudes or order faster than Hibernate? Seriously? Show me a query
>> that's 1000x or 100x faster (or even just 10x, 1 order of magnitude) in
>> JDBC than Hibernate.
>>
>> Burt
>>
>> On Tuesday, March 06, 2012 08:54:34 AM Roberto Guerra wrote:
>> > I use jdbc when I need fast queries and complicated joins. It is
>> magnitudes
>> > of order faster than hibernate. If that still doesn't solve the issue,
>> then
>> > I would say it is probably something inherent with grails. To date
>> though,
>> > I have not had an issue when I switch to jdbc for performance.
>> >
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe from this list, please visit:
>>
>>    http://xircles.codehaus.org/manage_email
>>
>>
>>
>
>
> --
> *The Journey Is The Reward.*
>


--
View this message in context: http://grails.1312388.n4.nabble.com/Serious-performance-issues-while-using-DTO-s-tp4447339p4450532.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email





--
The Journey Is The Reward.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

rosenfeld
Getting better performance for my queries is another (although interesting) issue.

For now, I'd be happy to be able to just get about the same performance as I get before I do any changes to my sources.

But I've already optimized the queries to the database and I don't think it can get any better. If Hibernate is slowing down anything, it can only be data binding but I would doubt that would make much difference.

There is only a trick I had to do due to a bug I didn't have time to report yet as far as I remember.

This generates multiple queries for me:

MyDomain.executeQuery("select new myapp.MyDomainDTO(m.association, m.id) from MyDomain m")

while this doesn't:

MyDomain.executeQuery("select m.association, m.id from MyDomain m").collect{new MyDomainDTO(*it)}


I'm still trying to replicate the reloading performance issue in an example application to create a JIRA and I think I'm advancing a bit on it...

I'm waiting for my Bootstrap to generate a million records in a fresh database so that I can simulate the slow down...

Thank you for your interest,

Rodrigo.


Em 06-03-2012 14:09, Roberto Guerra escreveu:
It was probably an exaggeration, and I haven't really measured it. What I've experienced is that when dealing with many-to-many relationships, the queries are noticeably slower with hibernate. Especially when retrieving thousands of records. I can't really show you the specific query in hql vs sql because of NDA. However, the one I posted is an example of a legacy database (hate it sooo much). To make it fun, I introduced grails to move data around to another database where analysis is done. Initially I mapped the tables to use hibernate but it became a nightmare and the queries dragged along, and some were just not possible to do.

In another pure grails app, I noticed similar behaviour with many-to-many and large amounts of data. These were not simple queries anyway, but for these complicated queries, the solution I've resorted to is using jdbc and has worked pretty well. If Rodrigo's queries were also complex enough, he could probably try jdbc to see if that helps.

On Tue, Mar 6, 2012 at 10:58 AM, burtbeckwith <[hidden email]> wrote:
That's a bunch of examples of complicated queries. Where are the slow
Hibernate equivalents? I agree that sometimes Hibernate isn't always
appropriate for complex queries, but you said "magnitudes of order faster
than hibernate".

Burt

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate
star

Re: Serious performance issues while using DTO's

rosenfeld
In reply to this post by Graeme Rocher-4
Em 06-03-2012 11:00, Graeme Rocher escreveu:
On Tue, Mar 6, 2012 at 1:28 PM, Rodrigo Rosenfeld Rosas
[hidden email] wrote:
Em 06-03-2012 04:50, Graeme Rocher escreveu:

On Tue, Mar 6, 2012 at 12:43 AM, Rodrigo Rosenfeld Rosas
[hidden email]  wrote:
I was using 512MB and doubled it to 1GB. It had plenty of free heap and
permgen space according to VisualVM.

Even so, the problem persists, but I was wrong that only changes to the
DTO
caused the issue.

The first time my action is called after the application boots was giving
me
about 2.6s as the completion time.

After 3 refreshes, the time will fall down to 0.7s.
This difference can be explained by the the first request including
GSP compilation and then subsequent requests getting faster due to the
JVM JIT

I've checked that changing my controller also triggered the issue.

The first time I call the action after touching any source file ("touch
grails-app/controllers/...."), it will take 5s.

The next time onwards it will remain about 3.5s no matter how much times
I
press refresh and how many times I touch my source files (any of them).

Can you see a reason for this behavior?
Only thing I can think is that it is recompiling GSPs on every request

This wouldn't explain the behavior because the first time I call the action
after a "grails run-app", I get 2.6s including GSP compilation and complete
lack of Hibernate Cache (which I think is the main reason for subsequent
requests go from 2.6s to 0.7s).

I mean, even if Hibernate Cache is kept empty after changes and if GSP are
recompiled, shouldn't the requests take about 2.6s instead of 3.5s? Since it
took 5s after some change and then it went down to 3.5s it means some
optimization (probably Hibernate Cache) is still working after reloading,
but somehow the reloading is impacting negatively in the performance in some
permanent way.

Does this help guessing what else could be causing this behavior?
Not really, I suggest you create an example that reproduces the
problem and attach to a JIRA

Okay, I've been trying to create this JIRA since then. Several hours were invested in this JIRA and I did even get an interesting number for it: 8888:

http://jira.grails.org/browse/GRAILS-8888

So, please consider taking a look at it when you're able to.

Thanks in advance,

Rodrigo.

Loading...