2. ASPRunner.NET General questions
  3. [SOLVED]  Fixing a slow query with a HINT
This topic is locked
[SOLVED]

 Fixing a slow query with a HINT

 		2/21/2020 2:16:40 PM
ASPRunner.NET General questions
T
 Tim author

Hello,
I have an SQL view as a data source. My query is like so:



SELECT Field1,

Field2,

Field3,

FROM vMyView

ORDER BY Field2 DESC


When I run this query in management studio it runs fine, but the web page is really slow (and sometimes it times-out when doing a search). I displayed the queries on the web page via "debug" post value and I see this is the actual query at run time:



SELECT top 20 Field1,

Field2,

Field3,

FROM vMyView

ORDER BY 2 DESC, 1 Asc


This makes sense (although it looks like ASPR replaces field names in the "order by" with ordinal values, which , according to Google searches, people seem to think is bad practice). If I take this query to management studio I can reproduce the slowness. It seems to be related to the "top 20". I found one solution to the problem (for SQL Server 2016+ anyway) is to add this HINT to the end of my query:



SELECT top 20 Field1,

Field2,

Field3,

FROM vMyView

ORDER BY 2 DESC, 1 Asc
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))


This works in management studio and speeds my query up (like from 3 min.s to 3 sec.). But it looks like I can't just add this to the end of my query in ASPR. It complains about the order by fields not being contained in the select. The project does build but it drops the order by statement all together at run time.
I realize that the real issue is likely with the view's underlying query, but I have attempted all the optimizing I know to do and I can't get it to run faster. And this HINT really does fix it.
Is there a way to add the HINT to my query? Or does anyone have any other ideas?
Thanks,

Tim
N
 		Nir Frumer 2/21/2020

hi

creating an index on your "order by" expression may solve this issue,
hope it helps,
T
 		Tim author 2/21/2020

Thanks for the reply. I do have an index that covers both of the order by fields and are sorted in the same direction.
Admin 2/21/2020

There must be some sort of a native solution to this. If vMyView is a view in SQL Server you can either optimize it (indexes) or at the very least add that OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL')) hint to the view.
T
 		Tim author 2/21/2020

Thanks. It is a view, but the OPTION clause can't be used in views.
https://www.sqlservercentral.com/forums/topic/views-and-hints
It has to do with the query optimizer making a wrong assumption about a loop join being more efficient than a hash join (I think).
https://blogs.msdn.microsoft.com/bartd/2012/03/14/row-goals-gone-rogue/
And this is where I got the tip about using the OPTION clause:
https://dba.stackexchange.com/questions/188704/why-is-select-faster-than-select-top-500-in-sql-server?noredirect=1&lq=1
Again, it does seem like I could maybe solve this by tweaking my view's query, but I haven't been able to figure it out and this HINT solution is a quick solution if I could get it added to the query.
Thanks,

Tim
T
 		Tim author 2/21/2020

Wow, I think I may have fixed my query! Based on this forum post:
http://geekswithblogs.net/Martinez/archive/2013/01/30/why-sql-top-may-slow-down-your-query-and-how.aspx
I changed one of the joins in my view's query to force it to do a hash join.
Select a.F1

,b.F2

FROM a

INNER HASH JOIN b on a.ID = b.ID
I didn't even know that was a thing. Thanks to you both for your input.
Tim