SQL Server Performance

TOP 100 PERCENT Performance

Discussion in 'T-SQL Performance Tuning for Developers' started by muko67, Sep 5, 2008.

  1. muko67 New Member

    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
  2. moh_hassan20 New Member

    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
  3. muko67 New Member

    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.
  4. moh_hassan20 New Member

    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
  5. preethi Member

    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)

Share This Page