Cardinality , Parallel Hint alternative for MS-SQL

Question

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

Answer 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).

Answer 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.