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
Asked2021-03-31byLuv 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.
Answered2021-03-31byAndy Mallon