Ok, so I'm trying to do some performance tuning. I have a query that runs really long. To simplify the problem, we'll talk about the part of it that's causing the problem...

In my mapping documents, I map using a formula that selects from another table and then does a GROUP BY on the column being returned. The GROUP BY is essential for ensuring that only one item is returned to fit in the column. The results of the query always GROUP to one item.

On analysis (outside of Hibernate), I determined that the GROUP BY was significantly slowing my query and that putting FETCH FIRST ROW ONLY instead significantly increases the speed of the query (as in 1 second instead of 10).

So I attempted to put the FETCH FIRST ROW ONLY into my mapping file (again using a formula with a subselect). However, it seems that hibernate attempts to interpret each word as a column in the table. So I get tableAlias.FETCH tableAlias.FIRST tableAlias.ROW tableAlias.ONLY. As you can imagine, not what I expected. Any suggestions on how to get around this?

Thanks in advance,
Susan Alexander

Hibernate version:3.0

Name and version of the database you are using: DB2 v8.1.0.16 FixPak 2

Instead of using HQL, try using createSQLQuery(..)

But how do I do that in a mapping file? This code is in my .hbm.xml file...

You can use eiter custom SQL loading (http://www.hibernate.org/hib_docs/v3/re ... rysql-load) or query substituties (http://www.hibernate.org/hib_docs/v3/re ... bstitution).

I guess I really need to show some of my mapping file and then the generated SQL...

excerpt...

...

end excerpt...

Generated SQL for the "relatedInfo" portion:



All I really want to do is put FETCH FIRST ROW ONLY instead of GROUP BY infoTable.info.

I tried putting the following in my hibernate.properties:

hibernate.query.substitutions fetch=FETCH
hibernate.query.substitutions first=FIRST
hibernate.query.substitutions row=ROW
hibernate.query.substitutions only=ONLY

and putting "fetch first row only" at the end of my query, but hibernate still attempted to interpret the words:



I'm not sure how in this case I could even use custom sql for loading... I'm only getting back a string...

Thanks for trying to help, but I really am still stuck. Maybe you could explain how I might use one or the other. Or maybe I am just really approaching this whole thing wrong...

Note:

I did try the following instead in my mapping file. It did not work either. Hibernate clearly does not support the idea I'm trying at here:



Hibernate errors out that it cannot work with the property like that.

It seems to me though, that I should be able to put FETCH FIRST ROW ONLY into my queries and hibernate should know that those words are SQL keywords and not attempt to interpret them - just like it behaves with WHERE or FROM or SELECT or any of the other keywords...

Always good idea. :-)



If you only want this, then you can try:

hibernate.query.substitutions 'GROUP BY infoTable.info' 'FETCH FIRST ROW ONLY'

Not sure how Hibernate works with substituties with spaces.

As alternative you can consider using some kind of stored procedure or view.

Ok, just tried that. Looking at the generated SQL, Hibernate did not substitute...

I can't do anything to this DB because it is a legacy DB, so anything DB internal is out of the question. All I want to do is speed up my query. I know from analysis that replacing the GROUP BY with a FETCH FIRST ROW ONLY will seriously speed up my query times (as in 3 seconds per query instead of 10).

It still seems to me I should be able to just write FETCH FIRST ROW ONLY and hibernate should not attempt to interpret any of those words as columns.... There has to be some way to do this!! It's driving me crazy!

Yes, I had looked into source code of Hibernate - it's doesn't support substituties with spaces.



Lets look at this by another angle - why you are need these keywords at all? Is info_code field not unique? If yes, then which one 'info' you are want to retrieve?



I would not hope for it

Remember, as I said before, this is a legacy DB. I cannot change how the data is stored. Believe me, if I could, this would all be so much easier.

Yes, in fact, this query can match more than one row, however, the column I am looking at will have exactly the same value in both matches. It is another, unrelated column that can be duplicated. I don't care one bit about the other column and I don't care which row I get, since they will have the same data, as far as I'm concerned.

All I care about is that DB2 takes 10 seconds to process the query with a GROUP BY and 3 seconds with a FETCH FIRST ROW ONLY.

I am aware that FETCH FIRST ROW ONLY is not preferred DB usage. However, I believe the tradeoff is worth it to get my users' time back, especially considering that, knowing the data, I know that they will always be getting back useful information anyways.

Ok, so substitution ignores spaces. Then why won't it substitute when I put the following in my properties file?



For the above substitutions, this is the matching query portion of my .hbm.xml file:



I don't understand why this is such an "out there" thing to do. Surely other people have needed FETCH FIRST ROW ONLY or FETCH FIRST ? ROWS and still needed the flexibility of an HQL query...

I just try to understang reasons. :-)



So, if values are same, then you can use another aggregate function besides GROUP BY. How about using MAX/MIN functions? For example:

Or what about mapping a second class, that's read-only, that excludes the column that can be duplicated and you don't care about, and use DISTINCT ?

The SQL parser Hibernate uses for processing formulas is not aware of "fetch first row(s) only". You would need to patch org.hibernate.sql.Template to do this. (A trivial patch.)

I did try DISTINCT - it's even slower than GROUP BY - something like 13 seconds...

Thanks for the suggestion on max - I tried that and it brought the query down to around 4-5 seconds. Still too slow in my opinion, but a little more doable. Thanks for suggestion!

Still, if someone knows how to get it to do FETCH FIRST ROW ONLY, I'd appreciate it!

Thanks Gavin - good to know. I'll see if we can live with the query times with max for now. If not, I might think about making a patch. I'd give rate you a helpful on this, but I've apparently given away all the helpful points for this topic...