architect for precalc results for faster queries

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

General SQL Server Forums

Database Design and Application Architecture

architect for precalc results for faster queries

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-19 : 17:18:32

Hey Guys,

I have a pretty huge table (204,000,000 rows and growing), the structure is posted below.

The queries on this table are running quite slow, and I want to try and find a way to speed them up. Every time a user looks at another users profile (social networking) it logs the view. To make the insert as fast as possible it records duplicates etc.

The only way we query this table is to query the "last 20 users to look at me". I was thinking of having a job that prepopulates another table of the same structure, but only allows for 20 rows per user, instead of the virtually unlimted in the current table.

With 500k users this would reduce the table size 95% down to 10 million in theory, and the queries should at least execute quicker.

Any thoughts on this approach ? Is this just a patch solution ? Am I going about this the wrong way ?

Any critisms are much appreciated, I'm not so sure this is the right way myself.. I am very open to any ideas..

Thanks again!
mike123

CREATE TABLE [dbo].[tblProfileViews_Users](
[viewID] [int] IDENTITY(1,1) NOT NULL,
[viewerUserID] [int] NOT NULL,
[profileUserID] [int] NOT NULL,
[viewDate] [smalldatetime] NOT NULL
) ON [PRIMARY]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-20 : 02:36:27

Or you can have the stored procedure which insert the records, automatically delete all but last 19 records for the user, and then insert the new record?

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-20 : 03:40:14

Hey Peso,

Originally I avoided this in the attempt of making the SP as fast as possible, so yes it would have been possible then. However I forgot to mention a point and there is another way we query this table. We have a query for users thats "users I have not seen", which must keep all views in this table for life. Of course we only need 1 record per unique user, so we could still reduce alot of rows by getting rid of duplicates.

Thanks for your analysis! :)

mike123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-20 : 04:08:21

or maybe you can have another table that keep user and view user with a flag seen or not seen and this table is updated in that stored procedure that insert records into tblProfileViews_Users

KH
[spoiler]Time is always against us[/spoiler]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-22 : 05:47:53

Hey Guys,

Just want to clear things up a bit. I'm not sure if a 1 table or 2 table solution is best. I have been recommended to change my INSERT statement to the one below. It looks like this will work quite well for making sure I just have the top 20 records in the table, however it presents a problem.

I have to keep a record of all unique views in this table for life, because there is another query that runs, that lets users know if they have viewed the profile before. Because of this I can't delete any records.

Would it be best to have 2 seperate tables for this situation ?

1 where I could run the recommended query below, and keep the table tight with just allowing 20 records and not to grow beyond that.

The second table would be just queried for lifetime views.

Would this be a good approach ?

Any thoughts appreciated !

Thanks again,
Mike123

DELETE f
FROM (
 SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS recID
FROM Table1
WHERE UserID = @UserID
) AS f
WHERE recID > 19

INSERT Table1
VALUES (new record)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 05:53:25

Are you using SQL Server 2008?

CREATE TABLE	dbo.tblHolyGrail
		(
			viewID INT IDENTITY(1, 1) NOT NULL,
			viewerUserID INT NOT NULL,
			profileUserID INT NOT NULL,
			viewDate SMALLDATETIME NOT NULL,
			isTop20 TINYINT NOT NULL
		)

CREATE INDEX	IX_IsTop20
ON		dbo.tblHolyGrail
		(
			profileUserID,
			viewDate
		)
WHERE		isTop20 = 1

UPDATE	f
SET	isTop20 = CASE WHEN recID >= 20 THEN 0 ELSE 1 END
FROM	(
		SELECT	isTop20,
			ROW_NUMBER() OVER (ORDER BY Date DESC) AS recID
		FROM	dbo.tblHolyGrail
		WHERE	UserID = @UserID
			AND isTop20 = 1
	)

INSERT	dbo.tblHolyGrail
VALUES	(new record with 1 as top 20 value)

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-22 : 05:59:50

Hey Peso,

I am not using SQL2008, but may upgrade in the next month or so.

Does this last post of your require 2008 ?

Thanks again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 06:01:12

No, not actually.
The only "SQL Server 2008" specific is the filtered index.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources