Month: August 2008 Page 1 of 5

My friend has been asked to optimize the SQL query for a forum application. Basically the Query is used to list the thread as well as the last person who posted on the forum. He and me tried to optimize that and we can fasten up the query up to only 13 secs which is not really well 🙁 . then the sql guru is coming to help him and show him the better way in writing the query and it performs only 1 sec. I thought it’s gonna be a good lesson to be shared it on my blog. There you go:

--Non Optimized Version
SELECT
	ForumTopic.ForumTopicID, ForumTopic.Title, Members.Username AS MemberName,
	ForumTopic.Views, ForumTopic.Replies,
	(
		SELECT TOP 1
			ForumPost.EnteredDate
		FROM
			ForumPost
		WHERE
			ForumPost.ForumTopicID = ForumTopic.ForumTopicID
		ORDER BY
			ForumPost.ForumPostID DESC
	) AS LastPostEnteredDate,
	(
		SELECT TOP 1
			Members.Username
		FROM
			ForumPost
		INNER JOIN
			Members
		ON
			ForumPost.EnteredBy = Members.MemberID
		WHERE
			ForumPost.ForumTopicID = ForumTopic.ForumTopicID
		ORDER BY
			ForumPost.ForumPostID DESC
	) AS LastPostMemberName
FROM
	ForumTopic
INNER JOIN
	Members
ON
	ForumTopic.EnteredBy = Members.MemberID
WHERE
	ForumID = @ForumID
AND
	ForumTopic.Valid = 1
ORDER BY
	LastPostEnteredDate DESC

--optimized version
SELECT
	ForumTopic.ForumTopicID,
	ForumTopic.Title,
	Members.UserName as MemberName,
	ForumTopic.Views,
	ForumTopic.Replies,
	ForumPost.EnteredDate as LastPostEnteredDate,
	Members1.Username as LastPostMemberName
FROM
	(
		SELECT
			ForumTopicID, MAX(ForumPostID) AS ForumPostID
		FROM
			ForumPost
		WHERE
			ForumTopicID IN (
				SELECT
					ForumTopicID
				FROM
					ForumTopic
				WHERE
					ForumTopic.ForumID = @ForumID
				AND
					ForumTopic.Valid = 1)
		GROUP BY
			ForumTopicID
	) RecentPost
INNER JOIN
	ForumPost
ON
	ForumPost.ForumPostID = RecentPost.ForumPostID
INNER JOIN
	ForumTopic
ON
	ForumTopic.ForumTopicID = RecentPost.ForumTopicID
INNER JOIN
	Members
ON
	ForumTopic.EnteredBy = Members.MemberID
INNER JOIN
	Members Members1
ON
	ForumPost.EnteredBy = Members1.MemberID
ORDER BY
	ForumPost.EnteredDate DESC

How to do a recursive query in SQL Server 2005, Recursive in the terms of you got a table where it has foreign key to the other record on the same table. Assume a table employee where a record on that table can be an employee or a manager which is an employee as well, or let’s call it when you have nested structure within one table, call it as a tree structure. I’ve googled and found the way in doing it so I believe it’s worthy for me to share with everyone

Table Schema

How to get the whole structure by using one SQL query, we can utilize CTE(Common table expression) and using UNION ALL to do the recursive and do the INNER JOIN with the CTE it self

--get all contentPageID for the page and its children
WITH dynamicPages(contentPage_id, parentContent_id, contentPageName) AS
	(
		SELECT
			c.contentPage_id, c.parentContent_id, c.ContentPageName
		FROM
			tblContentPage c
		WHERE
			c.contentPage_id = @contentPage_id
		UNION ALL
		SELECT
			c.contentPage_id, c.parentContent_id, c.contentPageName
		FROM
			tblContentPage c
		INNER JOIN
			dynamicPages b
		ON
			c.parentContent_id = b.contentPage_id
	)
SELECT * FROM dynamicPages

this is only a simple trigger and table to monitor the activity on an update activity to “members” table. it tracks the date when the update query is being executed and who execute it, it will be quite useful for a database that has many applications accessing it with different SQL User

Holding table :

CREATE TABLE [dbo].[MemberUpdateTracking](
	[MemberTrackingID] [int] IDENTITY(1,1) NOT NULL,
	[MemberID] [int] NULL,
	[BeforeMemberCode] [varchar](50) NULL,
	[BeforeFirstName] [varchar](255) NULL,
	[BeforeSurname] [varchar](255) NULL,
	[BeforeUserName] [varchar](255) NULL,
	[BeforePassword] [varchar](255) NULL,
	[BeforeEmailAddress] [varchar](255) NULL,
	[AfterMemberCode] [varchar](50) NULL,
	[AfterFirstName] [varchar](255) NULL,
	[AfterSurname] [varchar](255) NULL,
	[AfterUserName] [varchar](255) NULL,
	[AfterPassword] [varchar](255) NULL,
	[AfterEmailAddress] [varchar](255) NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[SQLUser] [varchar](255) NOT NULL,
 CONSTRAINT [PK_MemberUpdateTracking] PRIMARY KEY CLUSTERED
(
	[MemberTrackingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Triggers:

CREATE TRIGGER [dbo].[tr_Member_UPDATE]
	ON [dbo].[Members]
	FOR UPDATE
AS
BEGIN
	SET	NOCOUNT ON

	DECLARE @MemberID			int
	DECLARE @AfterFirstName		varchar(255)
	DECLARE @AfterSurname		varchar(255)
	DECLARE @AfterMemberCode	varchar(50)
	DECLARE @AfterUserName		varchar(255)
	DECLARE @AfterPassword		varchar(255)
	DECLARE @AfterEmailAddress	varchar(255)

	DECLARE @BeforeFirstName	varchar(255)
	DECLARE @BeforeSurname		varchar(255)
	DECLARE @BeforeMemberCode	varchar(50)
	DECLARE @BeforeUserName		varchar(255)
	DECLARE @BeforePassword		varchar(255)
	DECLARE @BeforeEmailAddress	varchar(255)


	--get information what will been inserted
	SELECT @MemberID = MemberID,
		   @AfterFirstName = FirstName,
		   @AfterSurname = Surname,
		   @AfterUserName = Username,
		   @AfterPassword = [Password],
		   @AfterMemberCode = MemberCode,
		   @AfterEmailAddress = EmailAddress
	FROM
		Inserted

	--only need for strange behaviour issue that we had before otherwise not necessary
	IF (@AfterFirstName = '' OR @AfterSurname = '' OR @AfterFirstName IS NULL OR @AfterSurname IS NULL)
	BEGIN
		--get the original information
		SELECT  @BeforeFirstName = FirstName,
				@BeforeSurname = Surname,
				@BeforeUserName = Username,
				@BeforePassword = [Password],
				@BeforeMemberCode = MemberCode,
				@BeforeEmailAddress = EmailAddress
		FROM
			Deleted

		--track the changes
		INSERT INTO MemberUpdateTracking(MemberID, BeforeMemberCode, BeforeFirstName,
					BeforeSurname, BeforeUserName, BeforePassword, BeforeEmailAddress,
					AfterMemberCode, AfterFirstName, AfterSurname, AfterUserName, AfterPassword,
					AfterEmailAddress, ModifiedDate, SQLUser)
		VALUES
			(@MemberID, @BeforeMemberCode, @BeforeFirstName, @BeforeSurname,
					@BeforeUserName, @BeforePassword, @BeforeEmailAddress, @AfterMemberCode,
					@AfterFirstName, @AfterSurname,
					@AfterUserName, @AfterPassword, @AfterEmailAddress,
					GETDATE(), SYSTEM_USER)
	END
END

I was having a problem when adding a stored procedure into table adapter. The error was “Invalid object” of my temporary table name inside stored procedure.

It’s a bit strange since what i thought the table adapter only care about the results being returned but what it is actually checking up the existence of the table it self. FMTONLY is used to “Returns only metadata to the client. Can be used to test the format of the response without actually running the query”.

--uncomment this to regenerate it in table adapter
--comment it once you are done with generating the table adapter
SET FMTONLY OFF