In various SQL implementations, a hint is a description added to the SQL standard that instructs and forces a database engine to execute a query in a specific way that is specified by a user even if there is a better way to execute the query. For example a hint may tell the engine which indexes are used or whether to use an index at all. Implementation: Different database engines such as MySQL or Oracle have implemented hints using different ways.
Questions tagged [query-hints]
70 questions
229
votes
6 answers
OPTION (RECOMPILE) is Always Faster; Why?
I encountered an odd situation where appending OPTION (RECOMPILE) to my query causes it to run in half a second, while omitting it causes the query to take well over five minutes.
This is the case when the query is executed from Query Analyzer or…
Chad Decker
- 5,997
- 8
- 27
- 31
24
votes
1 answer
SELECT TOP is slow, regardless of ORDER BY
I have a fairly complex query in SQL Server running against a view, in the form:
SELECT *
FROM myview, foo, bar
WHERE shared=1 AND [joins and other stuff]
ORDER BY sortcode;
The query plan as shown above shows a Sort operation just before…
richardtallent
- 34,724
- 14
- 83
- 123
24
votes
2 answers
Adding a query hint when calling Table-Valued Function
I'm calling a Table-Valued Function from entity framework and need to be able to add the option (recompile) to it because the execution plan it picks up is not optimal. Running the query in SQL Server Management Studio, it would look something like…
Matt Burland
- 44,552
- 18
- 99
- 171
9
votes
1 answer
Using Oracle hint "FIRST_ROWS" to improve Oracle database performances
I have a statement that runs on Oracle database server. The statement has about 5 joins and there is nothing unusual there. It looks pretty much like below:
SELECT field1, field2, field3, ...
FROM table1, table2, table3, table4, table5
WHERE…
bobetko
- 5,019
- 14
- 58
- 85
8
votes
1 answer
Get SQL Server to use index seek + key lookup instead of clustered index scan, without WITH (FORCESEEK)
Version: SQL Server 2008 R2
Database: AdventureWorks 2008R2 from http://msftdbprodsamples.codeplex.com/releases/view/55926
Query:
SELECT TOP 10
*
FROM
Person.Person --WITH (FORCESEEK)
WHERE
LastName like 'Max%'
OR EXISTS (
…
sayap
- 6,169
- 2
- 36
- 40
7
votes
2 answers
How do I control parameter sniffing and/or query hints in entity framework?
Update: I've created a suggestion to implement hint control in a future version of EF. Go here to vote for it.
I have a problem where one of my Entity Framework (EF) queries is taking a very long time to execute in Sql Server, although when I…
Mike
- 7,500
- 8
- 44
- 62
7
votes
2 answers
How to use the NOEXPAND hint with Linq to SQL?
I have an indexed view that I need to specify the noexpand hint for in order for it to perform reasonably. Unfortunately as seen with regard to modifying the Linq to SQL generated T-SQL query from the NOLOCK hint it appears that there is no easy way…
jpierson
- 16,435
- 14
- 105
- 149
6
votes
4 answers
Oracle multi insert statement
In my application I have to add many records. I am using the following construct:
INSERT /*+ append parallel(t1, 4) parallel(t2, 4) */ ALL
INTO t1 (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
INTO t2 (col1, col2, col3) VALUES…
Eduardo Mauro
- 1,515
- 1
- 26
- 38
6
votes
3 answers
Should I use Query Hint Fast number_rows / FASTFIRSTROW?
I was reading over the documentation for query hints:
http://msdn.microsoft.com/en-us/library/ms181714(SQL.90).aspx
And noticed this:
FAST number_rows
Specifies that the query is optimized for fast retrieval of the first number_rows. This is a…
Will Shaver
- 12,471
- 5
- 49
- 64
5
votes
1 answer
Put query hint (OPTION) into view in SQL Server
I have an SQL query on a view using several joins that is occasionally running really slow - a lot slower than normal, making the query nearly unusable.
I copied the query out of the view and experimented and found a solution at…
Andreas Reiff
- 7,961
- 10
- 50
- 104
5
votes
3 answers
Can we assign custom Query Hints to JPA NamedQueries
We are required to append query numbers to each and every query our application executes.
EX: SELECT * FROM ... WHERE ... QUERYNO 123456;
OpenJPA supports query hints, but only for specific hints on specific implementations.
...
Query q =…
Eddie
- 9,696
- 4
- 45
- 58
5
votes
5 answers
Oracle LEADING hint -- why is this required?
Suddenly (but unfortunately I don't know when "suddenly" was; I know it ran fine at some point in the past) one of my queries started taking 7+ seconds instead of milliseconds to execute. I have 1 local table and 3 tables being accessed via a DB…
aw crud
- 8,791
- 19
- 71
- 115
5
votes
1 answer
SQL Developer stripping inline hints
I'm using SQL Developer 3.1.07. I ran a query to Oracle with an inline hint --+ hint in it but SQL Developer seems to have stripped the hint before submitting it to the database. Multi-line hints /*+ hint */ didn't get stripped. Is there an option…
Alex
- 9,250
- 11
- 70
- 81
5
votes
2 answers
Query faster with top attribute
Why is this query faster in SQL Server 2008 R2 (Version 10.50.2806.0)
SELECT
MAX(AtDate1),
MIN(AtDate2)
FROM
(
SELECT TOP 1000000000000
at.Date1 AS AtDate1,
at.Date2 AS AtDate2
…
JJANSSEN
- 171
- 1
- 6
4
votes
1 answer
Add Query Hint inside of JPA Specification
I'm looking for a way to add a QueryHint inside of a Specifications toPredicate method. As I'm only having access to Root, CriteriaQuery and CriteriaBuilder instances I'm wondering if this is even possible.
I'm also okay with setting a global…
cmdjulian
- 123
- 1
- 1
- 6