Cardinality , Parallel Hint alternative for MS-SQL

All we need is an easy explanation of the problem, so here it is.

UPDATE MARK M SET ARCHIVE_FLAG = 'N' WHERE EXISTS 
(SELECT /*+ cardinality(S1, 10) parallel(S1,8)*/ 1 FROM SHFASG S, SHIFT S1 
WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y');

This is the oracle query that I have, I want to create similar query for my MS SQL DB, Any help please

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Out of the box, you don’t need be using query hints in SQL Server until you need to and probably even true in your case with Oracle, by the looks of that query. The cardinality hint in Oracle is for when you have predicates that are too complex for Oracle’s Cardinality Estimator, but your query looks rather simple.

Anyway, there’s not exactly a similar hint in SQL Server to Oracle’s cardinality hint, and regarding the parallel query hint, you can specify that in SQL Server with the MAXDOP hint like so:

UPDATE M 
SET M.ARCHIVE_FLAG = 'N'
FROM MARK M
WHERE EXISTS 
(
    SELECT 1 
    FROM SHFASG S, SHIFT S1 
    WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y'
)
OPTION (MAXDOP 8);

But again, you should try running the query without any hints first, and only use them when they’re necessary. Specifying a parallelism hint only limits the performance capabilities of the query, and can make it run slower (at the tradeoff of it consuming less resources from your server when it runs).

Method 2

Just remove the hint for SQL Server.

The cardinality and parallel hints are designed to help control performance when you "know better" than the DBMS–they don’t affect the results in any way. You can’t know if you’ll get acceptable performance in SQL Server until you run the query.

The different platforms will almost certainly need tuning in different places, using different tools. For example, parallelism works differently in SQL Server, so the related query tuning is approached differently. If you DO need to control parallelism, using a hint has different semantics and happens in different scenarios.

So just remove those hints (or leave them. SQL Server will just interpret them as comments), and watch performance.

Tune the queries that need it: First by making sure there are indexes and statistics so that the optimizer has the data it needs to get a good plan, then by exploring alternate ways to write your query, and only last by exploring hints.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply