NH-3820 - Support for Queryhints like merge,hash,loop join, with Index etc...

[nhibernate-bot]

Oğuzhan Soykan created an issue — 25th August 2015, 6:37:32:

Why we are use QueryOver including these options ? It would be very pretty feature.

---

Oğuzhan Soykan added a comment — 25th August 2015, 6:44:57:

I'm doing this by Interceptor but it is cost for the query preparing.

While putting hints;

• Define your Table aliases which will be marked hint
• Search Sql string which started "SELECT" word.
• Locate aliases/tables
• Calculate text and find right place for put the hint

In my opinion, This approach wouldn't work under large systems, that's why hints are should be written while query for the optimization.

---

Ricardo Peres added a comment — 25th August 2015, 16:29:23:

OK, but do you imagine this API? For example, what changes do you propose to:

• HQL
• LINQ
• Criteria API
• Query Over

---

Oğuzhan Soykan added a comment — 25th August 2015, 17:02:46:

Ok, Let me tell you:

I use a structure similar to this I wrote it myself. It works through Interceptor. But as i mentioned above comment, it is costly.

It is should be definetly on QueryOver and It may be on Fetch,ThenFetch etc.. strategies, this will pretty good.

var query = session.QueryOver<User>()
                          .JoinAlias(x => x.UserDepartments, () => userDepartmentAlias).ForceJoin(JoinType.Merge).WithIndex("IX_Something").WithNolock()
                          .JoinAlias(() => userDepartmentAlias.Department, () => departmentAlias).ForceJoin(JoinType.Hash).WithNolock()
                          .JoinAlias(x => x.UserNotifications, () => userNotificationAlias)
                          .Where(x => x.StatusType != StatusType.Deleted && departmentAlias.Id == department.Id &&  userNotificationAlias.UserNotificationType == userNotificationType);

return query.List();

---

Oğuzhan Soykan added a comment — 1st September 2015, 13:03:02:

<~rjperes> Do you have any comment ? Is that possible or sense ? :)

---

Ricardo Peres added a comment — 1st September 2015, 13:16:40:

I'm all for it! :-)
The problem is that I see things a little further ahead, and I thing it's going to be complicated... for once, hints can go anywhere in a SQL sentence, and each provider supports different hints, so it would probably need changes at the Dialect level.
Something to consider, though.

---

Oğuzhan Soykan added a comment — 1st September 2015, 13:42:40:

Great ! , I'm looking forward your latest commit :)

Yes, you are right. Each provider may wants different hint sentences and when it's thought, some challenges could expose.

Do you considering implementation about only on SELECT statements?

---

Oğuzhan Soykan added a comment — 2nd September 2015, 21:00:14:

<~rjperes> I thought and a few thing came to my mind.

For ex;

You should consider hints moreover custom type.

For instance;

session.QueryOver<User>().JoinAlias(x => x.UserDepartments, () => userDepartmentAlias).With("TABLOCKX","UPDLOCK")

And after generated sql shown like this;

 SELECT *
 FROM   dbo.Temp AS T WITH (TABLOCKX, UPDLOCK)

or

session.QueryOver<User>().JoinAlias(x => x.UserDepartments, () => userDepartmentAlias).With("SERIALIZABLE")

SELECT *
FROM   dbo.Temp AS T WITH (SERIALIZABLE)

or

session.QueryOver<User>().JoinAlias(x => x.UserDepartments, () => userDepartmentAlias).With("TABLOCKX").WithIndex("IX_Something")

SELECT *
FROM   dbo.Temp AS T WITH (TABLOCKX,INDEX('IX_Something'))

also;

..queryover().With("TABLOCKX","UPDLOCK","AnotherHINT","AnotherHINT",...) statement can grow.

---

Alexander Zaytsev added a comment — 3rd September 2015, 1:17:33:

I would love to go with extension of a dialect API to solve this issue.

---

Oğuzhan Soykan added a comment — 12th November 2015, 13:05:20:

<~rjperes> I was just wondering the process :) How does it go ? :) At the moment, I'm really looking for this feature :(

---

Oğuzhan Soykan added a comment — 8th January 2016, 7:45:04:

<~hazzik> you said "dialect api" in source code or as external in my code ? Where should i write this code ?

---

Sebastian Leopold added a comment — 21st February 2017, 20:00:31:

Is there any progress on this feature? Otherwise I try to implement it. Is there any plan for release of NHibernate 4.2 soon?

---

Alexander Zaytsev added a comment — 21st February 2017, 20:15:20:

<~SLeopold> give it a go. There were not much of progress recently.

Not sure about 4.2, the plan was to skip it and release 5.0 sooner.

---

Sebastian Leopold added a comment — 21st February 2017, 20:20:06:

Okay my main problem is that the company i am working for ist not willig to maintain a custom branch of NHibernate. But that is an internal discussion I have to live with.

I would do it slightly different than described in this post. I would add it like the "TakeMaxResults" Methods. So the implementation would be dialect specific. Would that be a way to go? How to Implement a Test for that new Feature? Mainly I can't develop the Test first because the Query will work all the time against the Database. The query hint is a completly optional part.

---

Alexander Zaytsev added a comment — 21st February 2017, 20:22:03:

You can check that SQL has the required parts with SqlLogSpy (the test helper class). Also, if it is possible write an unit test.

---

Oğuzhan Soykan added a comment — 21st February 2017, 21:56:46:

<_{SLeopold> [}hazzik] There was a PR that i have opened, just fyi.

---

Sebastian Leopold added a comment — 21st February 2017, 22:15:24:

For me it looks like you have only added the "nolock" option. I have a query where parameter sniffing is causing the selection of a bad query execution plan on MSSQL Server. So I have to add something like this to my query:

SQL: SELECT * FROM TABLE_X OPTION(RECOMPILE)

My Intention was to simply do the following:

I would extend the IQuery Interface by a Method called: SetHint(string hints). The implementation than goes equal to the SetTimeout-Method. So I could call:

IQuery q = session....
q.SetHint("RECOMPILE");

In the Query generator I will append a method which will call the dialect to append the hint in the right fashion to the select. The Dialect is responsible for parsing the hint string in a correct way. Maybe by this way not all cases are hit, but it is a simple and working way for the main use cases I can think of. Also the LINQ Provider could easy extended.

What do you think?

---

Oğuzhan Soykan added a comment — 21st February 2017, 22:35:43:

I think this behaviour could be provided as

session.QueryOver<User>().WithOption("RECOMPILE")

to only main Entity that would be "FROM" statement.

---

Sebastian Leopold added a comment — 21st February 2017, 22:38:13:

Okay, so I will name the IQuery Method also "SetOption". Than I will extend QueryOver to.

---

Sebastian Leopold added a comment — 22nd February 2017, 18:12:16:

Okay, I have pushed the changes from me to my branch. Test is missing so far but the Linq Provider and the Query API is finished.

https://github.com/IQS-Leopold/nhibernate-core