Web Counter : Is there a faster way to do it? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

Web Counter : Is there a faster way to do it?

Author

Topic

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2008-04-15 : 12:49:23

Our webcounter, is beginnign to run more slowly, its basic and homemade and runs in sql2000 sp4 at the moment.

How can I do this faster?
This is the DDL, I wasnt sure what options to select.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Counters]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Counters]
GO

CREATE TABLE [dbo].[Counters] (
[counterID] uniqueidentifier ROWGUIDCOL NULL ,
[SiteName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PageName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastHit] [datetime] NULL ,
[Hits] [int] NULL
) ON [PRIMARY]
GO

THIS is the code that increments the counters in the table.
Right now every page on our webservers have a row in this table, each time a page is hit, that should increment.

Set rstCounter = conCounter.Execute("select * from counters where [SiteName]='" & tSiteName & "' and [PageName]='" & tPageName & "'")

If rstCounter.EOF Then 'Not in the table
conCounter.Execute
"insert into Counters (SiteName, PageName, Hits, LastHit) values('" & tSiteName & "','" & tPageName & "',1,'" & Now() & "')"

Else
conCounter.Execute
"update Counters set Hits=Hits+1, LastHit='" & Now() & "' where [SiteName]='" & tSiteName & "' and PageName='" & tPageName & "'"
End If

I believe I put the SQL Code in bold. I will clarify as best I can if necessary.

-Trip

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 16:10:05

You should use a stored procedure for this.
Then all you need is a small call to the database.

CREATE PROCEDURE dbo.uspCounterIncrement
(
	@SiteName VARCHAR(500),
	@PageName VARCHAR(500)
)
AS

SET NOCOUNT ON

UPDATE	Counters
SET	Hits = Hits + 1,
	LastHit = GETDATE()
WHERE	SiteName = @SiteName
	AND PageName = @PageName

IF @@ROWCOUNT = 0
	INSERT	Counters
		(
			SiteName,
			PageName,
			Hits,
			LastHit
		)
	VALUES	(
			@SiteName,
			@PageName,
			1,
			GETDATE()
		)

****************************************************************************************************************


   With cmd
      .Parameters.Append .CreateParameter("@SiteName", adVarChar, adParamInput, 500, "MySiteNameHere")
      .Parameters.Append .CreateParameter("@PageName", adVarChar, adParamInput, 500, "MyPageNameHere")
      .Execute , , adExecuteNoRecords
   End With

E 12°55'05.25"
N 56°04'39.16"

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2008-04-15 : 17:15:02

So then... the webserver only talked to the database a single time?
ouhhhhh

sexy.
I will give it to the dba and say "told you so"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-15 : 17:28:03

be aware that neither scenario is bullet proof for high frequency pages.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2008-04-15 : 17:47:07

I was not aware of that at all, what could cause it to fail?
And what would you suggest to make it bullet proof.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-15 : 17:57:22

http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-15 : 18:38:13

or, just skip all this and just use google analytics.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-16 : 04:40:42

ROTFLOL!

true true!!!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-04-16 : 05:02:42

I was wondering the same thing ? Why weigh your server down.

There are many stats tools out there apart from google. I use http://www.smartertools.com/

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-04-16 : 05:04:04

And also we monitor site traffic and ranking using www.alexa.com ( Its owned by amazon) and sometimes www.netcraft.co.uk

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2008-04-22 : 16:33:58

Some of our sites do use the analytics. But that part isnt really my thing. We have web people to do that....

Im just trying to make the darn server use less cpu / network traffic.

The stored procdure works like a charm. Too bad I dont have a way to benchmark it (in my own limited knowledge)

We do also use this to keep track of how many times an image or pdf, or any object is accessed on the server.

One day, google may not be there.
and MS analytics will need at least 1 service pack before its good to go. lol

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-22 : 16:37:29

quote:
Originally posted by tripodal

One day, google may not be there.

you think your company will outlast google? where do you work? IRS?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-22 : 16:41:15

sure you can bechmark the sproc.
declare @dtStart datetime
select @dtStart = getdate()
-- other code here

select datediff(ms, @dtStart, getdate()) as diff

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2008-04-22 : 18:45:25

So long as I have any control, We will outlast Google and Microsoft.
Chrysler nearl died. :)
And I pray that we outlast the IRS. There has to be a better less scarey way.

Spirit1,
Thats awesome, :-D we will give it a whirl and see where it comes out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources