0
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

2
  • It's exactly the same, except to alias a table in an update you need a FROM. I would discourage the use of the ancient , join syntax, and you can't do cardinality hints like that. Mar 31 at 10:54
  • Shared Oracle query I can't modified, As its been shared by client. Related to using cardinality and parallel functions hint for SQL, It does not exist in SQL server, so I was hoping for a workaround for the same
    – Luv Gupta
    Mar 31 at 11:07
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).

6
  • @Charlieface Yes thanks! Fixed, my early morning brain slips up sometimes. :)
    – J.D.
    Mar 31 at 22:40
  • Thanks, @J.D. for sharing the details, But the client has reported a performance issue in both Oracle and SQL DBs. The client shared the fix for oracle, and by using the shared query their performance has improved for the oracle. Now they are asking for the same fix for MS-SQL. For Parallel execution I can try with suggested Maxdop, Is there any way around for Cardinality (Like using TOP and OPTIMIZE FOR OPTION), Thanks!
    – Luv Gupta
    Apr 1 at 10:33
  • @LuvGupta Nope, there is no equivalent hint for that in Microsoft SQL Server. If needed they can try Updating Statistics, probably with the FULLSCAN option, but I bet their underlying issue isn't actually being fixed by these query hints, rather it just helped mask the problem in their Oracle database. I'm sure you'll still see the root issue in the Microsoft SQL Server instance (even with the above suggestions) because I don't think you have a cardinality estimate issue...
    – J.D.
    Apr 1 at 12:23
  • ...that's due to just your query, because of how simple it is, and not even due to the statistics on the server. (The statistics vary from one server to another, and will surely be different in SQL Server vs Oracle anyway.) To solve the root problem your client is seeing, you need to provide the actual execution plan of the slow query which will likely point us to the root issue. Then we can solve the problem the correct way.
    – J.D.
    Apr 1 at 12:25
  • Thanks for the detailed explanation, I have this MS-SQL query- UPDATE MARKPOSTED_TMP SET ARCHIVE_FLAG = 'N' WHERE MARKPOSTEDID in (SELECT MARKPOSTEDID FROM SHFASGNMKPS WHERE SHIFTID not in (SELECT SHIFTID FROM SHIFT_TMP WHERE ARCHIVE_FLAG = 'Y')); I want a performance improved version of the above query in MS-SQL, the same query performance improved modified oracle version I have shared already in the ticket
    – Luv Gupta
    Apr 2 at 7:55
5

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.

3
  • Thanks, @Amtwo for the explanation and for sharing the details, I got your point that for doing the same in SQL I need to run it on SQL to observe the behavior, but due to the limitations, I can't run it. But the client has reported a performance issue in both Oracle and SQL DBs. The client shared the fix for oracle, by using the shared query their performance has improved for the oracle. Now they are asking for the same fix for MS-SQL.
    – Luv Gupta
    Apr 1 at 10:27
  • Unfortunately, the solution that fixes a query on Oracle likely isn't the same fix for SQL Server. To tune the query in SQL Server, you'll need to look at the table & index definitions, the execution plan, and go from there.
    – AMtwo
    Apr 1 at 12:43
  • Thanks for the detailed explanation, I have this MS-SQL query- UPDATE MARKPOSTED_TMP SET ARCHIVE_FLAG = 'N' WHERE MARKPOSTEDID in (SELECT MARKPOSTEDID FROM SHFASGNMKPS WHERE SHIFTID not in (SELECT SHIFTID FROM SHIFT_TMP WHERE ARCHIVE_FLAG = 'Y')); I want a performance improved version of the above query in MS-SQL, the same query performance improved oracle version I have shared already in the ticket, Thanks a lot!
    – Luv Gupta
    Apr 2 at 7:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.