2 Replies Latest reply on Jun 4, 2015 1:08 PM by m60freeman

    Queries in a Grail/Hibernate environment


      Does anyone have good solutions to an issue I'm experiencing with using DPA in and environment where the use of Grail/Hibernate by the developers is causing the queries to appear in DPA as different queries.  This make it difficult to see trends.   The queries are treated as different in DPA, since the SQL text is different.


      The root of the problem is that with Grail/Hibernate, the query can apparently have changing aliases for the column name.


      I found this old feature request on Thwack.




      Does anyone have any other experiences?   Is there another way to deal with this?    What terminology should I use when talking to the developer and asking them if they have the option to tell Grail/Hibernate not to do this.?



        • Re: Queries in a Grail/Hibernate environment

          Unfortunately, it's not us seeing the statement as different, it's the database engine that's assigning a different hash/handle that we rely on.

          If they are using dynamic SQL generation, what they may want to do is look into using bind variables (that's more from a generic development perspective).  I'm not familiar with Grail/Hibernate, so perhaps others can chime in?

            • Re: Queries in a Grail/Hibernate environment

              The issue isn't one of parameters or bind variables. Grails/Hibernate seems to randomly generate SQL with different column aliases for the same query. For example:


              (@P0 bigint)

              SELECT setracking0_.OrderNum as OrderNum9_1_, 

                 setracking0_.AutoNumber as AutoNumber1_, 

                 setracking0_.AutoNumber as AutoNumber2_0_, 

                 setracking0_.Carrier as Carrier2_0_, 

                 setracking0_.OrderNum as OrderNum2_0_, 

                 setracking0_.TrackingId as TrackingId2_0_ 

              FROM SETrackingView setracking0_ 

              WHERE setracking0_.OrderNum=@P0 


              Tomorrow, "OrderNum9_1_" could be "OrderNum4_1_" (likewise with the other aliases) for no reason that I understand. (Dont' even get me started about why the same column is requested multiple times i the same SELECT with different aliases.)

              I'm a DBA, not a Grails/Hibernate developer. It would be unreasonable to expect that DPA will ever be able to automatically identify these variants as the same query on its own, but we do really need the ability to group them ourselves as they turn up.