Hi, I have a query like the following statement. My Problem with TOP 100 PERCENT takes this query 12 Seconds time, but if I remove TOP 100 PERCENT I don't know how long time it take. BECAUSE I had to cancel it after 2 Hours. Is there any hint or any reason, what could cause these huge difference. If I run only these statement (B) there is no difference between time, but when I use as subquery, there is enorm problem and I don't want to use actually TOP 100 PERCENT in the statement. Thanks SELECT DISTINCT A.Col1 FROM (SELECT Col1, Col2, Col3 FROM A) A INNER JOIN (SELECT TOP 100 PERCENT Col1, Col2, Col3, Col4 FROM B) B ON A.Col1=B.Col1 INNER JOIN (SELECT Col1, Col2, Col3 FROM C) C ON B.Col1=C.Col1
pls, can you post the execution plan for the two cases can you try: SELECT DISTINCT A.Col1 FROM A INNER JOIN B ON A.Col1=B.Col1 INNER JOIN C ON B.Col1=C.Col1
This is not original query. It's more complicated than this. But in a subquery I use TOP 100 PERCENT. How can I copy the execution plan here. I copied SHOWPLAN_TEXT here but it was ununderstandable.
i simulated a query to find the effect of top 100 oercent as: select h.* from (select * from Sales.SalesOrderHeader) h inner join ( select * from Sales.SalesOrderDetail )d on h.SalesOrderId = d.SalesOrderId go -------------------------- select h.* from (select * from Sales.SalesOrderHeader) h inner join ( select top 100 percent * from Sales.SalesOrderDetail )d on h.SalesOrderId = d.SalesOrderId i created the execution plan , and i found the performance is the same (typical execution plan) i think that top 100 perecent has no effect on performance - check that all joining fields has an index - rebuild statistics of the tables involved in the query
Why are you using DISTINCT in the begining? If that has anything to do with your inner query, Try using DISTINCT within the inner query. I have seen that at times, it has improved performance as Sort operation happens at the bigining. It won't guarantee an improvement, but there is a possibility. You need to compare both execution plans. If you can't use Text plan, use SET Statistics PROFILE ON and see. You will get the results in tabular format, and you can get only the plan and copy here. (You may have to do some adjustments in formatting)