August 22, 2024 at 9:15 am
I am debugging an NHibernate generated SQL_query for performance problem and came up with something strange.
The query is a SELECT statement that reads from a view. I've tracked the problem to this part of the query:
AND (myView.A LIKE (@p7+'%') OR myView.B=@p8 OR myView.C=@p9)
If I run the query with only condition at a time, no matter which one, the query goes very fast, almost no database-reads. However, when all three are used (or two), things starts to crawl and database reads goes beyond 2,000,000.
Anyone got any ideas what might be going on?
August 22, 2024 at 9:46 am
Have a look at https://www.sommarskog.se/query-plan-mysteries.html
Slow in app / fast in ssms symptom
Like when debugging you may have setted the variable to literals which sql server can sniff for a better plan
August 22, 2024 at 11:02 am
The query is slow (when using all three conditions) no matter what I use, SSMS or app.
August 22, 2024 at 11:21 am
can you give us the actual explain plan with both the 3 conditions and just 1 condition - as well as the definition of the view.
likely this is joining multiple tables and in order to satisfy the 3 conditions it needs to read a significant part of the underlying tables before filter can be applied.
likely your solution here is do do the 3 queries as a union (to remove dups, but be aware this can lead to removal of needed rows) with 1 condition each
August 22, 2024 at 11:30 am
I can't share the executionplans (without massive censoring) since it belongs to the place I work sadly.
It would be quite easy try running the query 3 times but since this is NHibernate compiled SQL and not a stored procedure it's not that simple. :/
August 22, 2024 at 12:07 pm
use PlanExplorer to scramble the names - https://www.solarwinds.com/free-tools/plan-explorer
that will replace any object name (db, table, column) with object1, object2 and so on - but it will leave intact the remaining info that is what we need.
August 22, 2024 at 1:00 pm
Of course, splitting the query and using UNION ALL to put it all together may be an option too.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 27, 2024 at 8:11 am
No I can't use UNION since this is NHibernate generated.. I really prefer good old hand-coded SQL though.. 🙂
I made a simpler query than the actual one but the problem is still there.
SELECT Object1.Column1, Object2.Column1
FROM Object3 Object1
LEFT JOIN Object4 Object2 on Object2.Column1 = Object1.Column2
WHERE (Object2.Column3 = Variable1 OR Object1.Column4=Variable2) AND Object1.Column5=Variable3
AND Object1.Column6 = ?
ORDER BY Object1.Column7 ASC
OFFSET Variable4 ROWS FETCH FIRST Variable5 ROWS ONLY
The problem is:
WHERE (Object2.Column3 = Variable1 OR Object1.Column4=Variable2)
If both of these two are active, it's causing massive amounts of database reads. If only one of them, anyone, is active, things goes very fast.
Executionplan is attached if you're curious..
August 27, 2024 at 8:22 am
I asked Copilot how to overrule an Nhibernate generated query:
To overrule or customize an NHibernate-generated query, you can use native SQL queries or HQL (Hibernate Query Language). Here are a few methods:
Using Native SQL Queries:
You can directly write SQL queries using the ISQLQuery interface. This allows you to leverage database-specific features and have more control over the query.
var sqlQuery = session.CreateSQLQuery("SELECT * FROM YourTable WHERE YourCondition");
var result = sqlQuery.List();
This method is useful for complex queries or when you need to use specific SQL features1.
Using HQL (Hibernate Query Language):
HQL is similar to SQL but operates on the object model rather than the database model. You can write HQL queries to fetch data in a more object-oriented way.
var hqlQuery = session.CreateQuery("FROM YourEntity WHERE YourCondition");
var result = hqlQuery.List();
HQL provides a more abstract way to interact with the database, making it easier to work with entities2.
Using Criteria API:
The Criteria API allows you to build queries programmatically. This can be useful for dynamic queries where the criteria might change based on user input or other factors.
var criteria = session.CreateCriteria<YourEntity>()
.Add(Restrictions.Eq("YourProperty", YourValue));
var result = criteria.List();
This method is flexible and allows for complex query building2.
Overriding Fetch Strategies:
You can override the default fetch strategies for specific transactions using left join fetch in HQL. This can help optimize performance by reducing the number of SQL queries generated.
var hqlQuery = session.CreateQuery("FROM YourEntity e LEFT JOIN FETCH e.YourAssociation WHERE e.YourCondition");
var result = hqlQuery.List();
This approach ensures that associations are fetched eagerly in the initial query3.
These methods give you control over the queries generated by NHibernate, allowing you to optimize performance and tailor the queries to your specific needs.
Is there a particular scenario or query you’re working on that you’d like more detailed help with?
Document your exception in Nhibernate as well !! (Why did you use it, what are the gains )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 29, 2024 at 6:03 am
There's no way for me to override the NHibernate stuff in the solution I'm working in. It's way too complex and even if I manage to do it, people will have strong opinions why I'm going for native SQL.
August 29, 2024 at 6:30 am
That are indeed valid arguments to take into account.
Our world is mainly about being able to cooperate !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 29, 2024 at 7:11 am
Might have discovered a piece of the puzzle..
When problematic query looks like this.. WHERE param=@p1 it might run fine if I add a relevant index in SSMS, but if I hardcode it instead, WHERE param='p1' (same value as @p1) I get poor performance (just like the app).
August 29, 2024 at 8:07 am
That's odd, I would expect better performance because it can sniff the hardcoded value and use it for the query plan.
Perhaps the hardcoded value is not the same datatype as the database causing a table scan?
August 29, 2024 at 8:33 am
They are all NVARCHAR's so that shouldn't be the problem.
August 29, 2024 at 8:48 am
use PlanExplorer to scramble the names - https://www.solarwinds.com/free-tools/plan-explorer
that will replace any object name (db, table, column) with object1, object2 and so on - but it will leave intact the remaining info that is what we need.
can you do as I mentioned above. nothing on the resulting file will identify db, table or even column names, neither any parameter supplied.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply