Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Ok, so my test machine isn't fast. Actually it's really slow. It 1.6 ghz,n 1 gb of ram, No multiple disks, just a single (read slow) disk for sql server, os, and extras.</p> <p>I created your tables with primary and foreign keys defined. Inserted 2 categories, 500 random users, and 250000 random stories.</p> <p>Running the first query above takes 16 seconds (no plan cache either). If I index the LastActivityAt column I get results in under a second (no plan cache here either).</p> <p>Here's the script I used to do all of this.</p> <pre><code> --Categories table -- Create table Categories ( [ID] [int] IDENTITY(1,1) primary key NOT NULL, [ShortName] [nvarchar](8) NOT NULL, [Name] [nvarchar](64) NOT NULL) --Users table -- Create table Users( [ID] [int] IDENTITY(1,1) primary key NOT NULL, [Username] [nvarchar](32) NOT NULL, [Password] [nvarchar](64) NOT NULL, [Email] [nvarchar](320) NOT NULL, [CreatedAt] [datetime] NOT NULL, [LastActivityAt] [datetime] NOT NULL ) go -- Stories table -- Create table Stories( [ID] [int] IDENTITY(1,1) primary key NOT NULL, [UserID] [int] NOT NULL references Users , [CategoryID] [int] NOT NULL references Categories, [VoteCount] [int] NOT NULL, [CommentCount] [int] NOT NULL, [Title] [nvarchar](96) NOT NULL, [Description] [nvarchar](1024) NOT NULL, [CreatedAt] [datetime] NOT NULL, [UniqueName] [nvarchar](96) NOT NULL, [Url] [nvarchar](512) NOT NULL, [LastActivityAt] [datetime] NOT NULL) Insert into Categories (ShortName, Name) Values ('cat1', 'Test Category One') Insert into Categories (ShortName, Name) Values ('cat2', 'Test Category Two') --Dummy Users Insert into Users Select top 500 UserName=left(SO.name+SC.name, 32) , Password=left(reverse(SC.name+SO.name), 64) , Email=Left(SO.name, 128)+'@'+left(SC.name, 123)+'.com' , CreatedAt='1899-12-31' , LastActivityAt=GETDATE() from sysobjects SO Inner Join syscolumns SC on SO.id=SC.id go --dummy stories! -- A Count is given every 10000 record inserts (could be faster) -- RBAR method! set nocount on Declare @count as bigint Set @count = 0 begin transaction while @count&lt;=250000 begin Insert into Stories Select USERID=floor(((500 + 1) - 1) * RAND() + 1) , CategoryID=floor(((2 + 1) - 1) * RAND() + 1) , votecount=floor(((10 + 1) - 1) * RAND() + 1) , commentcount=floor(((8 + 1) - 1) * RAND() + 1) , Title=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36)) , Description=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36)) , CreatedAt='1899-12-31' , UniqueName=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36)) , Url=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36)) , LastActivityAt=Dateadd(day, -floor(((600 + 1) - 1) * RAND() + 1), GETDATE()) If @count % 10000=0 Begin Print @count Commit begin transaction End Set @count=@count+1 end set nocount off go --returns in 16 seconds DBCC DROPCLEANBUFFERS SELECT TOP(10) * FROM Stories INNER JOIN Categories ON Categories.ID = Stories.CategoryID INNER JOIN Users ON Users.ID = Stories.UserID ORDER BY Stories.LastActivityAt go --Now create an index Create index IX_LastADate on Stories (LastActivityAt asc) go --With an index returns in less than a second DBCC DROPCLEANBUFFERS SELECT TOP(10) * FROM Stories INNER JOIN Categories ON Categories.ID = Stories.CategoryID INNER JOIN Users ON Users.ID = Stories.UserID ORDER BY Stories.LastActivityAt go </code></pre> <p>The sort is definitely where your slow down is occuring. Sorting mainly gets done in the tempdb and a large table will cause LOTS to be added. Having an index on this column will definitely improve performance on an order by.</p> <p>Also, defining your Primary and Foreign Keys helps SQL Server immensly</p> <p>Your method that is listed in your code is elegant, and basically the same response that cdonner wrote except in c# and not sql. Tuning the db will probably give even better results!</p> <p>--Kris</p>
    singulars
    1. This table or related slice is empty.
    1. POQuery against 250k rows taking 53 seconds
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USKSimons
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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