Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [hints]

The tag has no usage guidance.

2
votes
1answer
87 views

Does using a read committed isolation level and a NOLOCK query hint impact performance?

Can having both the SET TRANSACTION ISOLATION LEVEL READ COMMITTED and WITH (NOLOCK) on all tables in SP cause conflicts and downgrade performance on the SP? I just noticed some changes that a dev ...
0
votes
1answer
32 views

How do I force a Nested Loop join on Materialized View and Table in Oracle 12c

There is a third party app which generates a high-cost query that has a join on multiple tables. I created a materialized view on some of the tables using the same join that is originated in the ...
7
votes
0answers
91 views

What cases benefit from the Reduce, Replicate, and Redistribute join hints?

The From Clause Documentation starting with SQL Server 2008 briefly mention 3 join hints and their basic mechanisms: Reduce Replicate Redistribute However there does not seem to be much information ...
2
votes
0answers
115 views

What is the difference between “OPTION (OPTIMIZE FOR UNKNOWN)” and “OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'))”

Can you tell me what is difference between OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING')) and OPTION (OPTIMIZE FOR UNKNOWN)?
2
votes
1answer
227 views

Is it possible to ignore query hints in SQL Server 2012?

I know this sounds a bit odd... the thing is we have a piece of critical code in production which issues a query with a Hash Join hint which is causing high CPU usage on SQL Server. Owing to some ...
10
votes
1answer
423 views

Why does the READPAST hint cause indexed views to be ignored?

I am investigating using the READPAST hint to reduce resource locking in our application's financial subsystem. It seemed like a good way to go because financial transaction records are only ever ...
1
vote
1answer
661 views

Hinting to Redshift how to distribute a join?

If I am joining between two tables, A and B, with multiple equality constraints, is there any way that I can hint to Redshift which it should use for distributing the join? For example, in: FROM A ...
0
votes
0answers
21 views

Will hotfixing parameter sniffing with optimize for xy help?

I figured out a parameter sniffing issue and after doing experiments have decided to introduce a code (query) change in app code that uses optimize for unknown hint. Now, I need to make sure the ...
0
votes
2answers
2k views

Oracle “insert /*+APPEND*/” into empty table

I have following code in script: execute immediate 'truncate table tab_a drop storage'; insert /*+APPEND*/ into tab_a select (...) I am wondering if this APPEND hint can cause any performance ...
0
votes
1answer
490 views

Stored Procedure with HINT “WITH RECOMPILE” loses performance, solved with DBCC FLUSHPROCINDB [duplicate]

I have a Stored Procedure with the hint WITH RECOMPILE, so the Stored Procedure do not store in the cache a Query Plan for the Stored Procedure. The average time the Stored Procedure resolve the Query ...
0
votes
1answer
196 views

Inheritance and performance in database [closed]

I have 3 table linked by an "inheritance" with Entity Framework. Sadly, I have poor performance with those query because I can't make any good index. Here is the tables : CREATE TABLE [dbo].[...
7
votes
2answers
1k views

SQL Server INNER REMOTE JOIN returns more rows than INNER JOIN

I'm trying to join a few rows from a remote view to a local table. The view has about 300 million rows, so I want to use the REMOTE query hint so that all 3 million rows don't have to be transferred ...
27
votes
3answers
28k views

What does OPTION FAST in SELECT statement do?

I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and am still confused on it. According to MSDN: Specifies that the query is optimized for fast ...
1
vote
0answers
42 views

Very different query plans with and without LIMIT in PostgreSQL.

I have the following query which I am using to create vector tiles for a web map. I'm pulling together just the geometries within the selected tile using a CTE, then joining it to get additional ...
6
votes
2answers
2k views

Fixed query plan for a stored procedure

I am exploring the plan guides topic. Sounds easy, as far as we go through the examples in documentation. The problem is that I cannot find any working example (nor did I manage to figure it out by ...
5
votes
2answers
554 views

Can we execute an optimal plan instead of a generic one in the first execution of a PL/pgSQL function?

I have a really busy function that I need to optimize the best way I can. This function is just a nested select statement that is requested several times a sec by a legacy application. The indexes ...
0
votes
1answer
506 views

Append hint with autotrace

I'm doing a direct-path insert which executes successfully. When I try to autotrace the execution, I get the ORA-12838: cannot read/modify an object after modifying it in parallel. SQL> insert ...
10
votes
3answers
2k views

NOLOCK hint changes order of records returned

There is a clustered index on a table Client field LastName. When I simply dump all records from the table, they appear in the alphabetical order unless (nolock) hint is used as in the query in ...
3
votes
1answer
171 views

Problem trying to force the use of a filtered index

I was trying to do some testing on filtered indexes yesterday and created a fairly simple situation: CREATE TABLE IndexTest ( ID INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_IndexTest PRIMARY KEY, ...
0
votes
1answer
400 views

Using of OPTIMIZE FOR hints in a sql query

as I know, OPTIMIZE FOR hint helps to avoid parameter sniffing. For such a stored procedure: SELECT * FROM Tabele WHERE param=@param1 We have two parameter S1 with high selectivity and s2 with low ...
0
votes
1answer
68 views

Using oracle hints

I am trying the following query to list the first 10 rows from fisgen table: SELECT /*+FIRST_ROWS(10)*/ salute, gender, fid FROM fisgen; The hint is not executed. All the rows from the table are ...
7
votes
1answer
3k views

Grant users privilege to use QUERYTRACEON 9481

Microsoft suggests using OPTION (QUERYTRACEON 9481) for queries that have "degraded" performance against SQL Server 2014 databases running under compatibility level 120. In my situation, "degraded" ...
1
vote
1answer
701 views

Why would I not specify ROWLOCK to get rid of page locks?

I have a query that goes something like this: SELECT TOP (1) * FROM MyTable WITH (UPDLOCK, READPAST) ORDER BY SomeColumn ASC; This answer says that it's hard to say for sure but it will probably ...
2
votes
1answer
485 views

Making MySQL use primary index without hinting or forcing

I have the following db structure: CREATE TABLE `question` ( `id` int(11) NOT NULL AUTO_INCREMENT, `level_id` int(11) NOT NULL, `video_id` int(11) NOT NULL, `is_active` tinyint(1) NOT NULL, ...
2
votes
1answer
3k views

How to speed up looped INSERT INTO statements?

I currently use the following statement, for 10,000 rows it takes about 150 seconds. I tried removing the index on the target table, but this didn't help. Running the loop without the INSERT INTO ...
2
votes
2answers
1k views

Reducing deadlocks with update query hint or other method

I'm getting 20-30 deadlocks per day on the same table. (2 mil rows) Profiler DeadlockGraphs show KEY LOCK and PAGE LOCK (See attched jpg) Total datatype size per row = 114 bytes We have multiple ...
1
vote
3answers
2k views

SELECT lock only for inserts [closed]

I need to get the MAX aggregate of certain field in a transaction. I use this value further inside the transaction. I want to avoid inserts in the entire table from which I'm getting the MAX while the ...
4
votes
1answer
320 views

Is there a behavior change between SQL Server 2000 and SQL Server 2008 R2 OPTION (FAST 1)?

This is question 2 of 2 related to the FAST query hint. Background Information (as an aside, I'm not trying to garner more rep, I'm trying to be true to only asking one question per question). Our ...
7
votes
1answer
5k views

How does OPTION (FAST 1) actually interact with a client?

This is question 1 of 2 related to OPTION (FAST 1); We've just upgraded our ERP database from SQL 2000 EE to 2008 R2 EE and we've been noticing increased blocking in the database. I've narrowed it ...
3
votes
3answers
219 views

Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

We have an unusual database structure (I leave the reasoning for another question), that in its current form requires a regular truncation and recreation of a lot of rows (about 1 million). We're ...
0
votes
1answer
933 views

Query performance and join hints, plan cost, and duration

Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases. Background I have a reporting database using ...
2
votes
5answers
10k views

Combine Table Hints INDEX and FORCESEEK with Two Joins Not On PK

See first query below. Can NOT combine table hint Index and forceseek with two joins and the joins are not on the PK. How to make the first query compile? Interesting - if just one join or the other ...
9
votes
1answer
14k views

OPTION FORCE ORDER improves performance until rows are deleted

I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds. ...
5
votes
4answers
12k views

SQL Server 2008R2 - Why is my index not used

I have a table defined in the following way: CREATE TABLE [dbo].[MyTable] ( [MyTable_ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_WITH_DATA] [varchar](128) NOT NULL, [COLUMN_A] [varchar](128) ...
6
votes
2answers
863 views

How to use merge hints to isolate complex queries in SQL Server

I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
1
vote
0answers
218 views

What overhead is there if I MERGE row by row?

I have a target table of about 2000 rows and I want to be able to MERGE (with HOLDLOCK) about 5-10 rows at a time into the target table. The target table doesn't get used heavily, with only 1-2 users ...
1
vote
1answer
258 views

Should I use table hints with a process-keyed table?

I read on stackoverflow that locking can be a problem when using process-keyed tables, and I was wondering if you had any suggestions on how to minimize or avoid unnecessary contention (e.g. table ...
4
votes
1answer
644 views

Locking, deadlocking workaround while process data at the same table

My database has a table: tableX. Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate. At the ...
3
votes
2answers
57k views

Where all can the /*+ PARALLEL */ hint be used?

It works with SELECT and UPDATE statements. Are there some more statements with which it works? I was able to use it with create, but am not sure if the hint was actually considered. SQL> create ...