Databases
StackOverflow2013
Postsdbo
POSQL Server 2008 Sort is slow
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server 2008 Sort is slow
    primarykey
    data
    text
    <p>All,</p> <p>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.</p> <pre><code>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 </code></pre> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmvp
    1. USnawlrus
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COMy suggestion. 1. Replace the subselects with joins or windowing operations, and then 2. look at indexes. 1. Should help you reduce the query to manageable bits, and 2 will help speed them up.
      singulars
      1. POSQL Server 2008 Sort is slow
      1. USPreet Sangha
    2. COCan you tell us **what indexes** are on these tables??
      singulars
      1. POSQL Server 2008 Sort is slow
      1. USmarc_s
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload