Snitz Forums database design with SP's

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Snitz Forums database design with SP's

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-27 : 23:23:08

Has anybody tackled the task of writing some of snitz's pages mainly (forum.asp and topic.asp) with stored proc's. Any opinions on how much of a performance gain it would be?
Anybody know of any good articles defining the database design? I am currently using snitz on a high volume messageboard and would need to increase performance as much as possible. It looks like its no easy task.

Thanks,
mike

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-27 : 23:33:20

I have done something similar, it is not too hard.

I haven't looked too closely at the snitz code, but from what I understand there are a BUNCH of queries going on for those pages, so you could probably speed it up a bit if you tried. With snitz I think they sacrificed a little bit of performance for the sake of compatibility with different databases.

That being said, how "high volume" are you talking ? This site has a "bunch" of traffic running on snitz. It holds up pretty well, although Graz has put a bit of time into getting the indexing just right. If you look around the snitz site you will find a bunch of perfomance tuning posts by Graz.

Hope that helps you a bit.

Damian

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-27 : 23:37:24

I would estimate about 1.5 million page views per month.
I made the front page static which seemed to help a decent amount, I've looked at the code -- most of it is dynamic queries which I can handle in ASP, but dynamic SP's scare me ..

thanks,
mike

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-27 : 23:44:54

I would suggest doing some searches of snitz support site for stored procs. Also, like I said before, look for Graz's posts on tuning and indexing. They should help you a bunch.

Damian

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-28 : 01:35:41

thanks found an excellent article by graz

http://forum.snitz.com/forum/topic.asp?TOPIC_ID=24226

a novice question here ... can somebody tell me where the beginning and end of the Sp's are .. I tried it different ways and they both worked..

I have always started my SP's with CREATE PROC as the first line .. all this ANSI stuff is new to me :)

Thanks
mike

Edited by - mike123 on 03/28/2002 01:36:22

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-28 : 01:38:13

below is how I copied them, is this correct?
--------------------------------------------->>

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE function fn_SQLDateToSnitz (@DateIn datetime)
Returns char(14)
AS
Begin
Declare @DateOut char(14)
Declare @TimeIn char(20)
Set @TimeIn = convert(varchar, @DateIn, 114)
Set @DateOut = convert(char(8), @DateIn, 112) + left(@TimeIn, 2) + substring(@TimeIn, 4,2) + substring(@TimeIn, 7, 2)
Return (@DateOut)
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC SnitzTopicNew01
(@Forum_ID int,
@Subject nvarchar(100) = '',
@Author int,
@IP char(15) = '',
@Message text = '')
AS
Begin
set nocount on
Declare @Cat_ID int,
@GetDate char(14),
@Topic_ID int,
@Error int
If @Forum_ID IS NULL or @Author IS NULL return -7
Select @GetDate = fn_SQLDateToSnitz ( GetDate() )
If @@Error <> 0 Return -2
Select @Cat_ID = CAT_ID FROM FORUM_FORUM WHERE FORUM_ID = @Forum_ID
if @Cat_ID IS NULL Return -1

-- Insert the topic
INSERT INTO FORUM_TOPICS ( FORUM_ID, CAT_ID, T_SUBJECT, T_MESSAGE, T_AUTHOR, T_LAST_POST, T_LAST_POST_AUTHOR, T_DATE, T_STATUS, T_IP, T_ARCHIVE_FLAG )
Values ( @Forum_ID, @Cat_ID, @Subject, @Message, @Author, @GetDate, @Author, @GetDate, 1, @IP, 1 )

Select @Topic_ID = @@IDENTITY, @Error = @@ERROR
If @@Error <> 0 Return -3
-- Update the forum totals
UPDATE FORUM_FORUM SET F_LAST_POST = @GetDate, F_TOPICS = F_TOPICS + 1, F_COUNT = F_COUNT + 1, F_LAST_POST_AUTHOR = @Author WHERE FORUM_ID = @Forum_ID
If @@Error <> 0 Return -4
-- Update the Grand Totals
UPDATE FORUM_TOTALS SET T_COUNT = T_COUNT + 1, P_COUNT = P_COUNT + 1
If @@Error <> 0 Return -5
-- Update the Member Table
UPDATE FORUM_MEMBERS SET M_POSTS = M_POSTS + 1, M_LASTPOSTDATE = @GetDate WHERE Member_ID = @Author
If @@Error <> 0 Return -6

Return @Topic_ID
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------------->

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Proc SnitzReplyNew01
(@Topic_ID int,
@Author int,
@IP char(15) = '',
@Message text = '')
AS
BEGIN
SET NOCOUNT ON
Declare @Cat_ID int, @Forum_ID int, @GetDate char(14), @Error int, @Reply_ID int
If @Topic_ID IS NULL or @Author IS NULL return -1
Select @GetDate = fn_SQLDateToSnitz ( GetDate() )
If @@Error <> 0 Return -2
Select @Cat_ID = CAT_ID, @Forum_ID = FORUM_ID FROM FORUM_TOPICS WHERE TOPIC_ID = @Topic_ID
if @Cat_ID IS NULL Return -1
-- Insert the Reply
INSERT INTO FORUM_REPLY (TOPIC_ID, FORUM_ID, CAT_ID, R_AUTHOR, R_DATE, R_IP, R_STATUS, R_MESSAGE)
Values (@Topic_ID, @Forum_ID, @Cat_ID, @Author, @GetDate, @IP, 1, @Message)
Select @Reply_ID = @@IDENTITY, @Error = @@ERROR
If @@Error <> 0 Return -3
-- Update the topic
UPDATE FORUM_TOPICS SET T_LAST_POST = @GetDate, T_REPLIES = T_REPLIES + 1, T_VIEW_COUNT = T_VIEW_COUNT + 1, T_LAST_POST_AUTHOR = @Author WHERE TOPIC_ID = @Topic_ID

If @@Error <> 0 Return -3
-- Update the forum
UPDATE FORUM_FORUM SET F_LAST_POST = @GetDate, F_LAST_POST_AUTHOR = @Author, F_COUNT = F_COUNT + 1 WHERE FORUM_ID = @Forum_ID
If @@Error <> 0 Return -3
-- Update the forum totals
UPDATE FORUM_TOTALS SET P_COUNT = P_COUNT + 1
If @@Error <> 0 Return -3
-- Update the members table
UPDATE FORUM_MEMBERS SET M_POSTS = M_POSTS + 1, M_LASTPOSTDATE = @GetDate WHERE MEMBER_ID = @Author
If @@Error <> 0 Return -3
Return @Reply_ID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-28 : 09:07:06

The ANSI and QUOTED IDENTIFIER settings are set at the time of SP creation. These settings affect how certain commands operate in a procedure, some operations won't work correctly if ANSI_NULLS or ANSI_WARNINGS are off, for example. They MUST appear before the CREATE PROCEDURE command, setting them inside the sproc won't make any difference. If in doubt, keep these settings in your script files, they can't hurt.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources