This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
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
asked 2021-03-31 by Luv Gupta
Answer
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.
answered 2021-03-31 by Andy Mallon