Same query taking 0.2 sec in Oracle database but 4 mins in SQL Server
There's a chance that the SQL server table or index statistics are out of date, aren't as accurate as they could be (both of which can affect how the SQL query plan is put together) or SQL could be reusing an old query plan which was based on a smaller data set.

If the statistics are out of date, or aren't accurate, then SQL will generate the query plan based on flawed data, so you'll get poorly performing code (looking specifically at your SQL Query plan and the 98% on the nested loop, it might just be a single index, or one of the tables affected by that part of the query plan).

If the statistics are up to date and you're still getting poor performance and the query plan is pushing everything through a Nested Loop, you could try forcing it to use something else like using an OPTION query hint, although try it out on a dev system first.
MSDN - Query Hints article

This quote posted on SQLServerCentral by Kevin Boles is a something to keep in mind when looking at inefficient query plans.


  One thing to watch out for here is when the optimizer THINKS there will be few rows (check the estimated rows in the popup for the estimated query plan graphic) but in reality there are LOTS of rows (thousands or even millions).


Specifically regarding the nested joins, Gail Shaw wrote this article on execution plan operations joins, which is a good reference on the physical join types and where/how they're normally used.