CODEWITHSUNDEEP
sqlsql-serverdatabasesorting
nawlrus
nawlrus

Reputation: 797

SQL Server 2008 Sort is slow

All,

I have a users table with around 250000 records. I have the following query (mind you, I am a novice in SQL) but it is taking a very LONG time to execute. I checked out the execution plan and %60 of the execution time is being taken up by 2 sorts.

SELECT TOP 50
    Flows_Users.UserName,
    Flows_Users.UserID,
    Flows_Users.ImageName,
    Flows_Users.DisplayName,
    Flows_UserBios.bio,
    FlowsCount = (
        SELECT Count(1)
        FROM Flows_Flows
        WHERE UserID = Flows_Users.UserID
            AND Flows_Flows.Active = '1'
    ),
    BeatsCount = (
        SELECT Count(1)
        FROM Flows_Beats
        WHERE UserName_ID = Flows_Users.UserID
            AND Flows_Beats.Active = '1'
    ),
    FollowersCount = (
        SELECT Count(1)
        FROM Flows_Follow
        WHERE FOLLOWING = Flows_Users.UserID
    ),
    FollowingCount = (
        SELECT Count(1)
        FROM Flows_Follow
        WHERE FOLLOWER = Flows_Users.UserID
    ),
    ISNULL(SUM(Flows_Flows_Likes_Dislikes.[Like]), 0) AS Likes,
    ISNULL(SUM(Flows_Flows_Likes_Dislikes.Dislike), 0) AS DisLikes
FROM Flows_Users
LEFT JOIN Flows_Flows
    ON Flows_Users.UserID = Flows_Flows.UserID
LEFT JOIN Flows_UserBios
    ON Flows_Users.UserID = Flows_UserBios.userid
LEFT JOIN Flows_Flows_Likes_Dislikes
    ON Flows_Flows.FlowID = Flows_Flows_Likes_Dislikes.FlowID
WHERE Flows_Users.UserID = Flows_Users.UserID
GROUP BY Flows_Users.UserID,
    Flows_Users.UserName,
    Flows_Users.ImagePath,
    Flows_Users.ImageName,
    Flows_Users.DisplayName,
    Flows_UserBios.bio
ORDER BY
    [Likes] DESC,
    [Dislikes] ASC,
    FlowsCount DESC

I know this is a mess but it got the job done when tables where only in the tens of thousands. Is there any way to make this quicker? Right now it takes up to 5-10 minutes to execute. This is in a stored procedure also and I feel i have the columns that need to be indexed, indexed.

Upvotes: 1

Views: 390

Answers (3)

WKordos
WKordos

Reputation: 2255

as @PreetSanght sugested change

this part 

FlowsCount = (
    SELECT Count(1)
    FROM Flows_Flows
    WHERE UserID = Flows_Users.UserID
        AND Flows_Flows.Active = '1'
),
BeatsCount = (
    SELECT Count(1)
    FROM Flows_Beats
    WHERE UserName_ID = Flows_Users.UserID
        AND Flows_Beats.Active = '1'
),
FollowersCount = (
    SELECT Count(1)
    FROM Flows_Follow
    WHERE FOLLOWING = Flows_Users.UserID
),
FollowingCount = (
    SELECT Count(1)
    FROM Flows_Follow
    WHERE FOLLOWER = Flows_Users.UserID
),

into something like this 

sum(case when Flows_Flows.Active = '1' then 1 else 0 end) 
over (partition by UserID order by Flows_Flows.UserID) as FlowsCount,

sum(case when Flows_Beats.Active = '1' then 1 else 0 end) 
over (partition by UserName_ID order by Flows_Flows.UserID) as BeatsCount,

count(1) over(parition by FOLLOWING order by Flows_Flows.UserID) as FollowersCount,
count(1) over(parition by FOLLOWER order by Flows_Flows.UserID) as FollowingCount,

i think you go the idea
you should read about windowing function and over() clause

if you want entire query post some sample data and tables schema

Upvotes: 1

Captain Kenpachi
Captain Kenpachi

Reputation: 7215

When you've implemented the other suggestions, turn your query into a stored procedure. THat way, the server doesn't have to create an execution plan each time the query is run.

Upvotes: 0

Andrew Jansen
Andrew Jansen

Reputation: 196

Try this alteration, as I dont have those tables, you might need to patch it up. After verifying, check the execution plans and look for table scans. Then add indexes. If the number indexes are large, consider included columns. I personally would modify this to a view and not stored proc but thats my preference.

Someone once told me that counting * is actually slightly better but that was in SQL Server 2000 days and not sure if still relevant.

If possibly, modify left joins to inner joins where you can. IE. I assume that users arent hard deleted so would modify user id joins to inner and filter disabled accounts where neccesary.

    SELECT TOP 50
    Flows_Users.UserName,
    Flows_Users.UserID,
    Flows_Users.ImageName,
    Flows_Users.DisplayName,
    Flows_UserBios.bio,
    a.FlowsCount,
    b.BeatsCount,
    c.FollowersCount,
    d.FollowingCount,
    ISNULL(SUM(Flows_Flows_Likes_Dislikes.[Like]), 0) AS Likes,
    ISNULL(SUM(Flows_Flows_Likes_Dislikes.Dislike), 0) AS DisLikes
FROM Flows_Users
LEFT JOIN     (    SELECT Count(*) FlowCount
        FROM Flows_Flows
        WHERE UserID = Flows_Users.UserID
            AND Flows_Flows.Active = '1') a
LEFT JOIN  (SELECT Count(*) as 
        FROM Flows_Beats
        WHERE UserName_ID = Flows_Users.UserID
            AND Flows_Beats.Active = '1') b
LEFT JOIN  (SELECT Count(*) as FollowersCount
        FROM Flows_Follow
        WHERE FOLLOWING = Flows_Users.UserID) c
LEFT JOIN  (
        SELECT Count(*) as FollowingCount
        FROM Flows_Follow
        WHERE FOLLOWER = Flows_Users.UserID) d
LEFT JOIN Flows_Flows
    ON Flows_Users.UserID = Flows_Flows.UserID
LEFT JOIN Flows_UserBios
    ON Flows_Users.UserID = Flows_UserBios.userid
LEFT JOIN Flows_Flows_Likes_Dislikes
    ON Flows_Flows.FlowID = Flows_Flows_Likes_Dislikes.FlowID
WHERE Flows_Users.UserID = Flows_Users.UserID
GROUP BY Flows_Users.UserID,
    Flows_Users.UserName,
    Flows_Users.ImagePath,
    Flows_Users.ImageName,
    Flows_Users.DisplayName,
    Flows_UserBios.bio
ORDER BY
    [Likes] DESC,
    [Dislikes] ASC,
    FlowsCount DESC

Upvotes: 0

Related Questions