Last 10 links clicked - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Last 10 links clicked

Author

Topic

cornall
Posting Yak Master

148 Posts

Posted - 2010-05-26 : 05:42:16

Hi,

I was wondering if anyone had a good design pattern for creating a table to store the last 10 links that a user clicked?

If I was doing this in C# I would just use a list and push a new item in and pop the oldest item out.

I need the table to hold the last 10 links clicked for a given user ordered by date so if the user clicks link 5 then it replaces link 1.

So far my table has a primary key of username and link plus a datetime stamp.

Before I start designing the stored proc to manage the data I wondered if anyone has a best practice example or any comments on my table design.

Cheers D

cornall
Posting Yak Master

148 Posts

Posted - 2010-05-26 : 06:01:47

This is my solution


ALTER PROCEDURE [dbo].[sp_UpdateUserRecentProjects] 
	@UserAdAccount VARCHAR(255),
	@ProjectNumber INT
AS
BEGIN
	DECLARE @NoOfRecords INT
	SET @NoOfRecords = (SELECT COUNT(*) FROM UserRecentProjects WHERE userAdAccount = @UserAdAccount)
	
	DECLARE @AlreadyExists INT
	SET @AlreadyExists = (SELECT COUNT(*) FROM UserRecentProjects WHERE userAdAccount = @UserAdAccount AND projectNumber = @ProjectNumber)
	
	IF @AlreadyExists > 0
	BEGIN
		UPDATE UserRecentProjects SET accessedDateTime = GETDATE() 
		WHERE userAdAccount = @UserAdAccount AND projectNumber = @ProjectNumber
	END
	ELSE
	BEGIN	
		IF @NoOfRecords >= 15
		BEGIN
			DELETE FROM UserRecentProjects 
			WHERE userAdAccount = @UserAdAccount 
			AND accessedDateTime = (SELECT MIN(accessedDateTime) FROM UserRecentProjects WHERE userAdAccount = @UserAdAccount)	
		END
		
		INSERT INTO UserRecentProjects VALUES (@UserAdAccount,@ProjectNumber,GETDATE())

	END
	
END

Kristen
Test

22859 Posts

Posted - 2010-05-26 : 07:01:43

Some thoughts:

Don't name your SProcs starting with "sp_" - system SProcs using that naming convention and the effect is to cause SQL to spend extra CPU looking in MASTER etc before deciding that the Sproc is actually in the current database / schema.

@AlreadyExists is only needed for the IF, so doesn't need a working variable, but either way better to use EXISTS test rather than COUNT(*)


IF EXISTS (SELECT * FROM UserRecentProjects WHERE userAdAccount = @UserAdAccount AND projectNumber = @ProjectNumber)
BEGIN
    UPDATE UserRecentProjects SET ...

you might be able to get smarted using the MERGE command for this job.

Don't do your COUNT(*) for @NoOfRecords until you know you have to insert a row - currently that CPU effort is wasted if it then turns out that the row exists. Again, you can do the COUNT(*) as part of the IF rather than separately:


IF (SELECT COUNT(*) FROM UserRecentProjects WHERE userAdAccount = @UserAdAccount AND projectNumber = @ProjectNumber) >= 15
BEGIN
	DELETE ...

If @NoOfRecords > MAX_REQUIRED then you could UPDATE the oldest, rather than DELETING / INSERT. Depending on what indexes you have on that table an UPDATE might be more efficient. You will still have to do an INSERT if you have less than MAX records

What is the widest actual value in [userAdAccount]? Do you really need varchar(255) for that column? (Seems quite a lot to me for an Account ID!)

You may want to put SET NOCOUNT ON at the top of the SProc (immediately after AS) so that you don't get "noise" recordsets returned to the application.

cornall
Posting Yak Master

148 Posts

Posted - 2010-05-26 : 07:18:53

Don't name your SProcs starting with "sp_" - system SProcs using that naming convention and the effect is to cause SQL to spend extra CPU looking in MASTER etc before deciding that the Sproc is actually in the current database / schema.

Very good point forgot that one will sort my naming ocnvention. Thankfuly I only have 3 stored procs to fix!

@AlreadyExists is only needed for the IF, so doesn't need a working variable, but either way better to use EXISTS test rather than COUNT(*)

you might be able to get smarted using the MERGE command for this job.

Don't do your COUNT(*) for @NoOfRecords until you know you have to insert a row - currently that CPU effort is wasted if it then turns out that the row exists. Again, you can do the COUNT(*) as part of the IF rather than separately:

If @NoOfRecords > MAX_REQUIRED then you could UPDATE the oldest, rather than DELETING / INSERT. Depending on what indexes you have on that table an UPDATE might be more efficient. You will still have to do an INSERT if you have less than MAX records

Thanks for these hints I will look into implimenting them

You may want to put SET NOCOUNT ON at the top of the SProc (immediately after AS) so that you don't get "noise" recordsets returned to the application.

Could you explain this a bit more what is a "noise" record set? Don't spend long if this is complex I can doa search

What is the widest actual value in [userAdAccount]? Do you really need varchar(255) for that column? (Seems quite a lot to me for an Account ID!)

This is mapped to Active Directory and although extreamly unlikely I have used the allowed length from the AD schema on samAccountName

Kristen
Test

22859 Posts

Posted - 2010-05-26 : 07:24:13

If you do "SELECT ... FROM ..." or "UPDATE ... WHERE ..." and ditto INSERT / DELETE then SQL sends a "10 row(s) effected" type resultset to the application. If the application is expecting real RecordSets this may upset it! and in any event ifs traffic between client-server than can avoided if it is not needed (it rarely is with Sprocs - using SET NOCOUNT ON will turn those "noise" resultsets off. (I can't think of a single SProc that we have where we do NOT use SET NOCOUNT ON (there! managed to get a treble-negative into the sentence!)

Ah [userAdAccount] "AD" refers to Active Directory ... I thought it was an advertising system. As you were then ...

cornall
Posting Yak Master

148 Posts

Posted - 2010-05-26 : 09:41:39

Hi Kristen,

Thanks so much for taking the time to look at my code and for your suggestions. I work in so many different languages I like to check I am using best practice as sometimes I forget thing like the sp_ no no.

D

cornall
Posting Yak Master

148 Posts

Posted - 2010-05-26 : 09:59:55

I have another question for you :-) With regards to my table I probably will only haev 100 users with 10 rows each so 1000 rows is it worth indexing on username and project number or is the overhead not worth while!

Kristen
Test

22859 Posts

Posted - 2010-05-26 : 10:14:19

Assuming Read:Write ration is 100:1, or higher, I would definitely add an index.

cornall
Posting Yak Master

148 Posts

Posted - 2010-05-26 : 12:17:20

Again thanks the Read:Write ratio is probably closer to 25:1. I am assuming an update is counted as a write?

Its basicaly a bit like the last file used list in word but it is a list of the last 15 SharePoint sites connected to.

So there will be alot of updates to the accessed date field.

Kristen
Test

22859 Posts

Posted - 2010-05-26 : 17:31:22

Yes, an UPDATE is a "write" in my example.

Well, suggest you try it and see what difference it makes. Its easy enough to DROP the index if it turns out to be too much of a resource-hog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources