CODEWITHSUNDEEP
sql-servert-sql
st_stefanov
st_stefanov

Reputation: 1186

T-SQL SELECT * is Fast, SELECT TOP 50 is Slow

(Deleted previous post, may be it wasn't asked the right way, I'll try again)

    -- 300Ms
    SELECT AppId FROM Application ap
    LEFT OUTER JOIN MissingThings mt on mt.AppId = ap.AppId
    WHERE mt.AppId is NULL
    ORDER BY mt.Id

-- 1.5s
SELECT TOP 50 FROM (TheSame)

    -- 100Ms
    SELECT TOP 50 AppId FROM Application ap
    LEFT OUTER JOIN MissingThings mt on mt.AppId = ap.AppId
    --WHERE mt.AppId is NULL
    ORDER BY mt.Id

If I apply TOP on the original query, it gets slow. If no TOP, it returns all 1000 records fast.

If I remove the WHERE clause and have the TOP 50, it is again fast.

Also tried as other posts suggest to replace the WHERE with NOT EXISTS. Didn't help.

Are there any general possible causes or it is really specific to my database? If it is specific I guess, I can't expect help here, because I am not allowed to share Execution Plans.

EDIT:

There were 2 other conditions in the WHERE clause, which I thought were really neglect-able,but once I commented those, the original query returns in 50ms!!!!!!!

SELECT TOP 50 AppId FROM Application ap
        LEFT OUTER JOIN MissingThings mt on mt.AppId = ap.AppId
        WHERE mt.AppId is NULL
AND ap.IsOrderFinished = 1
AND ap.IsAssigned = 2
        ORDER BY mt.Id

Added non-clustered index on IsOrderFinished and on IsAssigned - no help with Both or either one of the indexes.

More results:

Descending order by is fast (50ms!) ORDER BY mt.Id DESC (go figure)

Upvotes: 0

Views: 1807

Answers (1)

Marc Guillot
Marc Guillot

Reputation: 6455

Some times I can't make work optimally a full query, but I can make optimal their parts. Then instead of keep trying to optimize the full query, I just move the parts to a temporal table and use them to construct the full query.

This should execute in 300ms instead of 1.5s

SELECT AppId 
into #Application
FROM Application ap
     LEFT OUTER JOIN MissingThings mt on mt.AppId = ap.AppId
WHERE mt.AppId is NULL
ORDER BY ap.AppId 

SELECT TOP 50 * FROM #Application

DROP TABLE #Application

We cam also try to speed it up a bit, using your fastest query. This should execute in closer to 100ms.

CREATE TABLE #Application (AppId INT, MissingAppId INT)
CREATE CLUSTERED INDEX #IX_Application ON #Application (MissingAppId, AppId)

INSERT INTO #Application (AppId, MissingAppId)
SELECT ap.AppId, mt.AppId
FROM Application ap
     LEFT OUTER JOIN MissingThings mt on mt.AppId = ap.AppId

SELECT TOP 50 * 
FROM #Application
WHERE MissingAppId IS NULL
ORDER BY AppId

DROP TABLE #Application

Upvotes: 1

Related Questions