Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    26

    Unanswered: Speed problem with SP

    Code:
    SELECT     top 10 tp.fldHeadline AS fldHeadline, tblForums.fldName, tp.fldPostsID, tblUsers.fldAlias,
                              (SELECT     TOP 1 fldDate
                                FROM          tblPost
                                WHERE      fldPostsID =
                                                           (SELECT     TOP 1 fldPostsID
                                                             FROM          tblPosts
                                                             WHERE      isnull(fldParentID, fldPostsID) = tp.fldPostsID
                                                             ORDER BY fldPostsID DESC)
                                ORDER BY fldPostID DESC) AS fldDate, tblUsers.fldUserID,
                              (SELECT     fldAlias
                                FROM          tblUsers
                                WHERE      fldUserID =
                                                           (SELECT     TOP 1 fldUserID
                                                             FROM          tblPosts
                                                             WHERE      isnull(fldParentID, fldPostsID) = tp.fldPostsID
                                                             ORDER BY fldPostsID DESC)) AS fldLastAliasPost,
                              (SELECT     fldUserID
                                FROM          tblUsers
                                WHERE      fldUserID =
                                                           (SELECT     TOP 1 fldUserID
                                                             FROM          tblPosts
                                                             WHERE      isnull(fldParentID, fldPostsID) = tp.fldPostsID
                                                             ORDER BY fldPostsID DESC)) AS fldLastAliasIDPost, tblForums.fldForumsID, tp.fldPostID
    FROM         tblPosts tps INNER JOIN
                          tblForums ON tps.fldForumID = tblForums.fldForumsID INNER JOIN
                          tblUsers ON tps.fldUserID = tblUsers.fldUserID INNER JOIN
                          tblPost tp ON tps.fldPostsID = tp.fldPostsID
    WHERE     (tps.fldParentID IS NULL) AND (tp.fldPostID IN
                              (SELECT     MAX(fldPostID)
                                FROM          tblPost
                                WHERE      fldPostsID = tp.fldPostsID)) AND (ISNULL(tblForums.fldSecurity, 0) <= 300)
    ORDER BY tblUsers.fldDate DESC
    When I dont use the order by statement, it goes very fast, but else it takse like 0.9 seconds. I have set up indexes.

    How can I optimize this question to do the same thing?
    When I order by for example tps.fldPostsID it goes very fast, but that is not what I want. Since I want to order by the latest post in a thread. But I want to show the threadname and everything.

    Do I need to split the question in some way?

    Please help!

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    /*
    If you run your query without order by, only first random 10 results are processed.
    If you run your query with order by, all is processed and top 10 results are returned.
    I think, query written this way cannot use indexes at the top level.

    As I see, you try to select top 10 replied questions from the most current users.
    By the way - why don't you use time from posts?
    Well, what about adding this reducing join to you query:
    */

    join (
    select top 10
    tpsx.fldPostsID,fldPostID=max(tpsx.fldPostID)
    from dbo.tblPosts tpsx
    join dbo.tblUsers usrx on tpsx.fldUserID = usrx.fldUserID and tpsx.fldParentID is NULL
    join tblForums frmx on tpsx.fldForumID = frmx.fldForumsID and (ISNULL(frmx.fldSecurity, 0) <= 300)
    group by tpsx.fldPostsID
    order by max(usrx.fldDate)
    ) XXX on tp.ldPostsID=XXX.fldPostsID and tps.fldPostID=XXX.fldPostID

    /*
    Now you can optimize more.
    P.S. Not tested. Post create table scripts next time.
    */

  3. #3
    Join Date
    Jan 2003
    Posts
    26
    Here you go! The script to create the tables, but also my SP

    What I want to return is this:

    forumname - the forumname that the thread was posted in
    threadname - the headline of the thread (notice that all posts can have headlines, but you know that it is the first post when fldParentID in tblPosts is null)
    createtBy - I want to know the name of the user who started the thread
    lastAnswerBy - I want to know the name of the user who gave the last answer.

    You should also notice that in the table tblPost there can be several postst with the same fldPostsID, and that is because instead of editing a post I want to have a restorelist when someone edit their post. So I create new posts with same ID, and then we have to check for the latest fldPostID of those who have the same fldPostsID, to get the current post.


    In a shorter text, all I want to get is the 10 latest active threads. I dont want the 10 latest threads, since that will not put a thread at the top when someone write a reply in a thread.

    Thanks for your help ispaleny, but I didnt get your code to work. I hope you maybe can help me solve something out now.

    Thanks!

    ///Brimba

    Code:
    CREATE          PROCEDURE proc_getLatestThreads
    ( 
    	@lngSecurity	INT = 0
    ) 
    AS 
    
    SET NOCOUNT ON 
    
    
    SELECT     TOP 10 tp.fldHeadline AS fldHeadline, tblForums.fldName, tp.fldPostsID, tblUsers.fldAlias,
                              (SELECT     TOP 1 fldDate
                                FROM          tblPost
                                WHERE      fldPostsID =
                                                           (SELECT     TOP 1 fldPostsID
                                                             FROM          tblPosts
                                                             WHERE      isnull(fldParentID, fldPostsID) = tp.fldPostsID
                                                             ORDER BY fldPostsID DESC)
                                ORDER BY fldPostID DESC) AS fldDate, tblUsers.fldUserID,
                              (SELECT     fldAlias
                                FROM          tblUsers
                                WHERE      fldUserID =
                                                           (SELECT     TOP 1 fldUserID
                                                             FROM          tblPosts
                                                             WHERE      isnull(fldParentID, fldPostsID) = tp.fldPostsID
                                                             ORDER BY fldPostsID DESC)) AS fldLastAliasPost,
                              (SELECT     fldUserID
                                FROM          tblUsers
                                WHERE      fldUserID =
                                                           (SELECT     TOP 1 fldUserID
                                                             FROM          tblPosts
                                                             WHERE      isnull(fldParentID, fldPostsID) = tp.fldPostsID
                                                             ORDER BY fldPostsID DESC)) AS fldLastAliasIDPost, tblForums.fldForumsID, tp.fldPostID
    FROM         tblPosts tps INNER JOIN
                          tblForums ON tps.fldForumID = tblForums.fldForumsID INNER JOIN
                          tblUsers ON tps.fldUserID = tblUsers.fldUserID INNER JOIN
                          tblPost tp ON tps.fldPostsID = tp.fldPostsID
    WHERE     (tps.fldParentID IS NULL) AND (tp.fldPostID IN
                              (SELECT     MAX(fldPostID)
                                FROM          tblPost
                                WHERE      fldPostsID = tp.fldPostsID)) AND (ISNULL(tblForums.fldSecurity, 0) <= @lngSecurity)
    ORDER BY fldDate DESC
    SET NOCOUNT OFF 
    RETURN 0
    
    GO

    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblMods_tblForums]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[tblMods] DROP CONSTRAINT FK_tblMods_tblForums
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPosts_tblForums]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[tblPosts] DROP CONSTRAINT FK_tblPosts_tblForums
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblModPosts_tblPost]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[tblModPosts] DROP CONSTRAINT FK_tblModPosts_tblPost
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPost_tblPosts]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[tblPost] DROP CONSTRAINT FK_tblPost_tblPosts
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblModPosts_tblUsers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[tblModPosts] DROP CONSTRAINT FK_tblModPosts_tblUsers
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblMods_tblUsers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[tblMods] DROP CONSTRAINT FK_tblMods_tblUsers
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPosts_tblUsers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[tblPosts] DROP CONSTRAINT FK_tblPosts_tblUsers
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblForums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tblForums]
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPost]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tblPost]
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPosts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tblPosts]
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tblUsers]
    GO
    
    CREATE TABLE [dbo].[tblForums] (
    	[fldForumsID] [int] IDENTITY (1, 1) NOT NULL ,
    	[fldParentID] [int] NULL ,
    	[fldName] [nvarchar] (50) NOT NULL ,
    	[fldSort] [int] NOT NULL ,
    	[fldSecurity] [int] NULL 
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[tblPost] (
    	[fldPostID] [int] IDENTITY (1, 1) NOT NULL ,
    	[fldHeadline] [nvarchar] (100) NOT NULL ,
    	[fldText] [ntext] NOT NULL ,
    	[fldDate] [smalldatetime] NOT NULL ,
    	[fldPostModID] [int] NULL ,
    	[fldPostsID] [int] NOT NULL 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[tblPosts] (
    	[fldPostsID] [int] IDENTITY (1, 1) NOT NULL ,
    	[fldParentID] [int] NULL ,
    	[fldForumID] [int] NOT NULL ,
    	[fldUserID] [int] NOT NULL 
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[tblUsers] (
    	[fldUserID] [int] IDENTITY (1, 1) NOT NULL ,
    	[fldEmail] [nvarchar] (50) NOT NULL ,
    	[fldPassword] [nvarchar] (50) NOT NULL ,
    	[fldAlias] [nvarchar] (50) NOT NULL ,
    	[fldRealname] [nvarchar] (50) NULL ,
    	[fldLocation] [nvarchar] (50) NULL ,
    	[fldSignature] [nvarchar] (255) NULL ,
    	[fldICQ] [nvarchar] (50) NULL ,
    	[fldSecurity] [int] NULL ,
    	[fldDate] [smalldatetime] NOT NULL ,
    	[fldSortOrder] [nvarchar] (50) NULL ,
    	[fldLastVisit] [smalldatetime] NULL 
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[tblForums] WITH NOCHECK ADD 
    	CONSTRAINT [PK_tblForums] PRIMARY KEY  NONCLUSTERED 
    	(
    		[fldForumsID]
    	)  ON [PRIMARY] 
    GO
    
    ALTER TABLE [dbo].[tblPost] WITH NOCHECK ADD 
    	CONSTRAINT [DF_tblPost_fldDate] DEFAULT (getdate()) FOR [fldDate],
    	CONSTRAINT [PK_tblPost] PRIMARY KEY  NONCLUSTERED 
    	(
    		[fldPostID]
    	)  ON [PRIMARY] 
    GO
    
    ALTER TABLE [dbo].[tblPosts] WITH NOCHECK ADD 
    	CONSTRAINT [PK_tblPosts] PRIMARY KEY  NONCLUSTERED 
    	(
    		[fldPostsID]
    	)  ON [PRIMARY] 
    GO
    
    ALTER TABLE [dbo].[tblUsers] WITH NOCHECK ADD 
    	CONSTRAINT [DF_tblUsers_fldDate] DEFAULT (getdate()) FOR [fldDate],
    	CONSTRAINT [PK_tblUsers] PRIMARY KEY  NONCLUSTERED 
    	(
    		[fldUserID]
    	)  ON [PRIMARY] 
    GO
    
     CREATE  UNIQUE  INDEX [tblForumsIndex] ON [dbo].[tblForums]([fldForumsID]) ON [PRIMARY]
    GO
    
     CREATE  UNIQUE  INDEX [tblPostIndex] ON [dbo].[tblPost]([fldPostID], [fldPostsID]) ON [PRIMARY]
    GO
    
     CREATE  UNIQUE  INDEX [tblPostsIndex] ON [dbo].[tblPosts]([fldPostsID], [fldParentID], [fldForumID], [fldUserID]) ON [PRIMARY]
    GO
    
     CREATE  UNIQUE  INDEX [tblUsersIndex] ON [dbo].[tblUsers]([fldUserID]) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[tblPost] ADD 
    	CONSTRAINT [FK_tblPost_tblPosts] FOREIGN KEY 
    	(
    		[fldPostsID]
    	) REFERENCES [dbo].[tblPosts] (
    		[fldPostsID]
    	)
    GO
    
    ALTER TABLE [dbo].[tblPosts] ADD 
    	CONSTRAINT [FK_tblPosts_tblForums] FOREIGN KEY 
    	(
    		[fldForumID]
    	) REFERENCES [dbo].[tblForums] (
    		[fldForumsID]
    	),
    	CONSTRAINT [FK_tblPosts_tblUsers] FOREIGN KEY 
    	(
    		[fldUserID]
    	) REFERENCES [dbo].[tblUsers] (
    		[fldUserID]
    	)
    GO
    Last edited by Brimba; 01-03-03 at 06:27.

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Consider redesign of your database
    Moving old records to tblPostVersions (PK fldpostSid,fldpostid)
    -> tblPost (PK fldpostSid)+TR(I,U) audit to tblPostVersions (1:N)
    .(than you can).>>> move tblPost into tblPosts (1:1) + rewrite TR
    .(or).>>> you can leave it vertically splitted to accel tblPosts.

    Code:
    --query for both replied old posts and nonreplied new posts in current db
    select p3.fldHeadline,f3.fldName,p3.fldPostsId,u3.fldAlias,p2.fldDate,u3.fldUserId
          ,fldLastAliasPost=u2.fldAlias,fldLastAliasIdPost=u2.fldUserId,f3.fldForumsId,p3.fldPostId
     from tblPost  p2  
     join
     (
      select top 10 fldPostId=max(p1.fldPostId)
      from tblPosts ps1
      join tblPost   p1  on ps1.fldPostsId=p1.fldPostsId
      join tblForums f  on ps1.fldForumID=f.fldForumsID and (ISNULL(f.fldSecurity, 0) <= 300)
      group by ISNULL(ps1.fldParentID, ps1.fldPostsID)
      order by max(p1.fldDate) DESC
     ) X on p2.fldPostID=X.fldPostID
     join tblPosts ps2 on p2.fldPostsID=ps2.fldPostsID
     join tblPosts ps3 on ISNULL(ps2.fldParentID, ps2.fldPostsID)=ps3.fldPostsId  
     join tblForums f3 on ps3.fldForumID=f3.fldForumsID
     --the newest version of starting post
     join tblPost  p3  on ps3.fldPostsId=p3.fldPostsId 
                      and p3.fldPostId=(select max(p4.fldPostId) from tblPost p4 where p3.fldPostsId=p4.fldPostsId)
     join tblUsers u2  on ps2.fldUserId=u2.fldUserId
     join tblUsers u3  on ps3.fldUserId=u3.fldUserId
     order by p2.fldDate 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •