A question of performance ...

Results 1 to 2 of 2

Thread: A question of performance ...

  1. #1
    Join Date
    Dec 1969

    Default A question of performance ...

    Using SQL Server 97 and well and truly in the world of Stored Procedures, I&#039;ve now hit the stage of trying to find the most logical and quickest way to do things.<BR><BR>This is a good example:<BR>Two tables; Header and Detail. Both have an integer unique key.<BR><BR>By counting the Header I know how many user threads have been created, by counting the details for each headerID I know how many responses have been made to each post.<BR><BR>The detail table is linked to a members table (for name and email address) and a couple of other reference tables.<BR><BR>These are the two ways I&#039;ve obtained information from this structure and their comparable speeds:<BR>--------------------------------------<BR>Method 1:<BR>qryPost Count: Obtains detail count per ThreadID<BR>qryThread COunt: Obtains total number of header rows<BR>qryThreads: Obtains header information for every leading posting, including members name AND referencing the first two queries.<BR>A Select from qryPosts in an .ASP page using typical ADO and a WHERE clause. ("Select from qryPosts where PostDate &#062; etc, etc")<BR><BR>At around 3000-8000 posts, this runs quite swiftly ... the page typically renders within a second or two.<BR><BR>HOWEVER ...<BR>------------------------------------<BR>Method Two:<BR>Believing the first method to be old and clunky, and with it slowing drastically around 18,000 posts, I upgraded everything to a stored procedure:<BR><BR>Because we&#039;re in an SP, I thought I&#039;d be *clever* and store ThreadID&#039;s in a temp table first ... this having a unique key which will allow me to select just the records I want and count on those.<BR><BR>The trouble is, this procedure runs - easily - 10 or 20 times SLOWER than the old, clunky method ... so I am definately doing something wrong.<BR><BR>I&#039;ve reached a point in my career where sql performance is important to me (what a geek!), but I just don&#039;t understand *WHY* certain ways out perform others.<BR><BR>So, your input in this is invaluable ..<BR><BR>Method 2: Super slow ... why?<BR>-----------------------------<BR>(3000 records, 15+ seconds)<BR><BR><BR>CREATE PROCEDURE [dbo].[spViewThreads]<BR> @MemberID int = 0,<BR> @intFORUMid int = 1,<BR> @intCURSOR int = 0,<BR> @strAction varchar(10) = &#039;&#039;,<BR> @intNumPosts int = 0 OUTPUT,<BR> @intPageSize int = 40 OUTPUT,<BR> @intNumThreads int = 0 OUTPUT<BR><BR><BR>AS<BR><BR>SET NOCOUNT ON<BR><BR><BR><BR>-- MARK FORUM AS READ<BR>IF @strAction = &#039;allRead&#039;<BR> BEGIN<BR> EXEC spMark_Forum_Read @MemberID<BR><BR> END<BR><BR><BR>-- NUMBER OF THREADS<BR>SELECT @intNumThreads = COUNT(ThreadID) FROM dbo.tblThread_Header<BR><BR><BR>-- NUMBER OF POSTS FOR THIS FORUM<BR>SELECT<BR> @intNumPosts = COUNT(dbo.tblThread_Detail.Thread_DetailID)<BR>FRO M<BR> dbo.tblThread_Header<BR> INNER JOIN<BR> dbo.tblThread_Detail ON dbo.tblThread_Header.ThreadID = dbo.tblThread_Detail.ThreadID<BR>GROUP BY dbo.tblThread_Header.ForumID<BR>HAVING dbo.tblThread_Header.ForumID = @intFORUMid<BR><BR><BR><BR>-- PAGE SIZE<BR>DECLARE @intEnd int<BR><BR>SELECT<BR> @intEnd = CONVERT(INT, dbo.ref_tblPageSize.PageSize_DESC)<BR>FROM<BR> dbo.tblPreferences INNER JOIN dbo.ref_tblPageSize ON dbo.tblPreferences.Message_Page_Size = dbo.ref_tblPageSize.PageSizeID<BR>WHERE (dbo.tblPreferences.MemberID = @MemberID)<BR><BR><BR>SET @intEND = ISNULL(@intEND + @intCURSOR, 40)<BR><BR><BR><BR><BR>-- TEMP TABLE TO HOLD THREADS<BR>CREATE TABLE [dbo].[#tmpThreadView] (<BR> [CursorID] [smallint] IDENTITY (1, 1) NOT NULL ,<BR> [ThreadID] [int] NOT NULL,<BR> [NumPosts] [smallint] NOT NULL,<BR> [PostDate] [DATETIME] NOT NULL<BR>) ON [PRIMARY]<BR><BR><BR>INSERT INTO #tmpThreadView<BR> (<BR> ThreadID,<BR> NumPosts,<BR> PostDate<BR> )<BR>SELECT TOP 100 PERCENT<BR> ThreadID,<BR> COUNT(ThreadID),<BR> MAX(PostDate)<BR>FROM<BR> dbo.tblThread_Detail<BR>GROUP BY<BR> ThreadID<BR>ORDER BY MAX(PostDate) DESC<BR><BR><BR><BR><BR>-- CURRENT THREADS<BR>SELECT TOP 100 PERCENT<BR> dbo.#tmpThreadView.CursorID,<BR> dbo.#tmpThreadView.ThreadID,<BR> dbo.tblThread_Header.ForumID,<BR> dbo.tblThread_Header.theTitle,<BR> dbo.tblMembers.MemberID, <BR> dbo.tblMembers.MemberTypeID,<BR> dbo.tblMembers.Handle,<BR> dbo.tblMembers.Email,<BR> dbo.#tmpThreadView.NumPosts,<BR><BR> ISNULL( (<BR> SELECT<BR> AmtRead<BR> FROM dbo.tblMemHistory<BR> WHERE MemberID = @MemberID AND ThreadID = dbo.tblThread_Header.ThreadID<BR> ), 0),<BR><BR> dbo.#tmpThreadView.PostDate,<BR> CONVERT(INT, dbo.tblThread_Header.Locked) AS isLocked,<BR> CONVERT(INT, dbo.tblThread_Header.FixedPos) AS isFixedPos<BR>FROM<BR> dbo.tblThread_Header<BR> INNER JOIN<BR> dbo.tblMembers ON dbo.tblThread_Header.Originator = dbo.tblMembers.MemberID<BR> INNER JOIN<BR> dbo.tblThread_Detail ON dbo.tblThread_Header.ThreadID = dbo.tblThread_Detail.ThreadID<BR> INNER JOIN<BR> dbo.#tmpThreadView ON dbo.tblThread_Header.ThreadID = dbo.#tmpThreadView.ThreadID<BR>WHERE<BR> dbo.#tmpThreadView.CursorID BETWEEN @intCURSOR AND @intEND<BR>GROUP BY<BR> dbo.#tmpThreadView.CursorID,<BR> dbo.#tmpThreadView.ThreadID,<BR> dbo.#tmpThreadView.PostDate,<BR> dbo.#tmpThreadView.NumPosts,<BR> dbo.tblThread_Header.ForumID,<BR> dbo.tblThread_Header.theTitle,<BR> dbo.tblMembers.MemberID,<BR> dbo.tblMembers.MemberTypeID, <BR> dbo.tblMembers.Handle,<BR> dbo.tblMembers.Email,<BR> dbo.tblThread_Header.ThreadID,<BR> CONVERT(INT, dbo.tblThread_Header.Locked),<BR> CONVERT(INT, dbo.tblThread_Header.FixedPos)<BR>ORDER BY<BR> CONVERT(INT, dbo.tblThread_Header.FixedPos) DESC,<BR> dbo.#tmpThreadView.PostDate<BR>GO<BR>

  2. #2
    Join Date
    Dec 1969

    Default What's the deal w/ that TOP 100 PERCENT?

    That sounds like you are selecting everything from the table, but I would guess that SQL Server needs to compare ALL of the rows to figure out what the top 100% is going to be.<BR><BR>-Doug

Posting Permissions

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