Heck of a way to spend the holiday, HELP

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

Transact-SQL (2000)

Heck of a way to spend the holiday, HELP

Author

Topic

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-07-03 : 06:09:03

I have tried all sorts of things but am spinning my wheels I am sure this can be done.

I have a table called UF2_rt_Friends, it has 3 fields shown below. ID is an Identity field, From_ID and To_ID are both int types. My User_ID# would be in the From_ID field and my friends would be in the To_ID field

Table UF2_rt_Friends
ID, From_ID, To_ID
1,82,10298 'sample data for user 10298 my id is 82
2,82,12738 'sample data for user 12738 my id is 82

Next There is a table called UF2_Comment_Threads which contains the following there are more fields but they are not important. Each photo is considered a thread and comments are linked to the thread_id There is one record per photo in the table.

Table UF2_Comment_Threads
Thread_id int identity
User_ID int 'This field is also the photographer
Image_Active bit

Next there is a table called UF2_Comments which contains the actual comment left by the user, the id of the person leaving the comment (critiquer), and the person receiving the comment(photographer)

Comment_ID int identity
Thread_ID int
User_ID int 'this is the person leaving the comment
Photog_ID int 'this is the recipient of the comment
image_ID int 'this is the image being commented on

The last table is the users table where the actual names and user IDs are stored. Both the photographers and critiquers IDs are containted in the table.

Table UF2_rt_Users
User_ID int identity
User_First_Name varchar
User_Last_Name varchar
User_Active bit

Here is one version of the query the idea is to return to me all of the comments made by friends(UF2_rt_Friends.To_ID) to me (MyID) which is in the UF2_rt_Friends.From_ID field. I can have unlimited friends in the To_ID field. I only want to see the comments directed to me by my friends, and in another query to see all comments made by my friends to me or anyone else.

There are approximatly 5 million records in the comments table and it grows by about 2000 a day, and about 600,000 records in the threads table growing at about 1000 per day, so I really need something that is efficient.

SELECT
dbo.UF2_Comments.comment_id,
dbo.UF2_Comments.thread_id,
dbo.UF2_Comments.user_id,
dbo.UF2_Comments.comment
dbo.UF2_Comments.Image_ID
etc......

FROM ((((UF2_Comments INNER JOIN UF2_rt_Friends AS Friends ON (UF2_Comments.User_id=Friends.To_id))
INNER JOIN UF2_Comment_Threads ON (UF2_Comments.thread_id=UF2_Comment_Threads.thread_id))
INNER JOIN UF2_rt_users AS Photographer ON (Photographer.user_id=UF2_Comments.Photog_id))
INNER JOIN UF2_rt_users AS Critiquer on (Critiquer.user_id=UF2_Comments.User_id))
WHERE (dbo.UF2_Comments.Image_Active = 1) AND (Friends.From_ID = " & MyID & ") AND (UF2_Comments.Photog_ID = " & MyID & ")"

The above thing almost works but it is slow don't have much hair left to pull out so any help would be appreciated.

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

xpherion
Starting Member

4 Posts

Posted - 2005-07-03 : 12:30:20

Do you have index on any of these tables? Are you running any kind of reindexing service against the server? How about using a stored proc and index temp table? Do you have to use inner join? I know this is not exactly what you are looking for. But these are some suggestions.

SQL Help Blog
http://www.xpherion.com/blog.asp

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-07-03 : 15:29:57

Yes, I have created indexes on all things that are indexable, but that is not the problem at the moment. I have further simplified things as this was a carry over from an older version where it was part of a forum and needed the threads, in that since the comments are slaved to images I got rid of the threads as the info was redundant and just added an extra level of joins.

So now the Image_ID is used instead of the thread_ID so here is my new query. It works great on the first page however returns a full page of the same image on each of the following pages.

Here is the new query
SELECT
dbo.UF2_Comments.comment_id,
dbo.UF2_Comments.user_id,
dbo.UF2_Comments.comment,
dbo.UF2_Comments.Image_ID
etc......

FROM (((UF2_Comments INNER JOIN UF2_rt_Friends AS Friends ON (UF2_Comments.User_id=Friends.To_id))
INNER JOIN UF2_rt_users AS Photographer ON (Photographer.user_id=UF2_Comments.Photog_id))
INNER JOIN UF2_rt_users AS Critiquer on (Critiquer.user_id=UF2_Comments.User_id))
WHERE (dbo.UF2_Comments.Image_Active = 1) AND (dbo.UF2_Comments.Comment_Active = 1) AND (Friends.From_ID = " & MyID & ") AND (UF2_Comments.Photog_ID = " & MyID & ")"

Anyone see anything in the JOINS that i am doing wrong?

THanks,

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-03 : 16:05:59

Post the text execution and IO results. Before you run the query, put in:

SET STATISTICS IO ON
SET STATISTICS PROFILE ON

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-07-03 : 17:59:40

Here it is Kind of a mess to read though

1 1 SELECT dbo.UF2_Comments.comment_id, dbo.UF2_Comments.user_id, dbo.UF2_Comments.comment_active, dbo.UF2_Comments.comment_length_flag, dbo.UF2_Comments.Image_Active, dbo.UF2_Comments.Image_ID, Critiquer.User_First_Name AS Critiquer_First_Name, Criti 1 1 0 NULL NULL NULL NULL 7.2474766 NULL NULL NULL 0.81995147 NULL NULL SELECT 0 NULL
1 1 |--Nested Loops(Inner Join) 1 3 1 Nested Loops Inner Join NULL NULL 7.2474766 0.0 3.0294452E-5 3207 0.8199507 [UF2_Comments].[comment_id], [UF2_Comments].[user_id], [UF2_Comments].[comment_active], [UF2_Comments].[comment_length_flag], [UF2_Comments].[Image_Active], [UF2_Comments].[Image_ID], [Photographer].[user_first_name], [Photographer].[user_last_name], [Phot NULL PLAN_ROW 0 1.0
1 1 |--Clustered Index Seek(OBJECT:([UseFilm].[dbo].[UF2_rt_users].[PK_UF2_rt_users] AS [Photographer]), SEEK:([Photographer].[user_id]=82) ORDERED FORWARD) 1 4 3 Clustered Index Seek Clustered Index Seek OBJECT:([UseFilm].[dbo].[UF2_rt_users].[PK_UF2_rt_users] AS [Photographer]), SEEK:([Photographer].[user_id]=82) ORDERED FORWARD [Photographer].[user_first_name], [Photographer].[user_last_name], [Photographer].[user_id], [Photographer].[user_donor], [Photographer].[user_karma] 1.0 3.2034749E-3 7.9603E-5 1573 3.2830781E-3 [Photographer].[user_first_name], [Photographer].[user_last_name], [Photographer].[user_id], [Photographer].[user_donor], [Photographer].[user_karma] NULL PLAN_ROW 0 1.0
1 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Friends].[to_id])) 1 5 3 Nested Loops Inner Join OUTER REFERENCES:([Friends].[to_id]) NULL 7.2474766 0.0 3.0294452E-5 1643 0.81663734 [UF2_Comments].[comment_id], [UF2_Comments].[user_id], [UF2_Comments].[comment_active], [UF2_Comments].[comment_length_flag], [UF2_Comments].[Image_Active], [UF2_Comments].[Image_ID], [Critiquer].[user_first_name], [Critiquer].[user_last_name], [Critiquer] NULL PLAN_ROW 0 1.0
1 1 |--Filter(WHERE:([Friends].[from_id]=82)) 1 6 5 Filter Filter WHERE:([Friends].[from_id]=82) NULL 7.2474766 0.0 3.3300227E-4 79 0.81245065 [UF2_Comments].[comment_id], [UF2_Comments].[user_id], [UF2_Comments].[comment_active], [UF2_Comments].[comment_length_flag], [UF2_Comments].[Image_Active], [UF2_Comments].[Image_ID], [Friends].[to_id] NULL PLAN_ROW 0 1.0
16796 1 | |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([UseFilm].[dbo].[UF2_rt_friends] AS [Friends])) 1 7 6 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1002]), OBJECT:([UseFilm].[dbo].[UF2_rt_friends] AS [Friends]) [Friends].[to_id], [Friends].[from_id] 693.75476 0.41874665 7.6313026E-4 79 0.81211764 [UF2_Comments].[comment_id], [UF2_Comments].[user_id], [UF2_Comments].[comment_active], [UF2_Comments].[comment_length_flag], [UF2_Comments].[Image_Active], [UF2_Comments].[Image_ID], [Friends].[to_id], [Friends].[from_id] NULL PLAN_ROW 0 1.0
16796 1 | |--Nested Loops(Inner Join, OUTER REFERENCES:([UF2_Comments].[user_id]) WITH PREFETCH) 1 9 7 Nested Loops Inner Join OUTER REFERENCES:([UF2_Comments].[user_id]) WITH PREFETCH NULL 693.75476 0.0 2.899895E-3 75 0.39260784 [UF2_Comments].[comment_id], [UF2_Comments].[user_id], [UF2_Comments].[comment_active], [UF2_Comments].[comment_length_flag], [UF2_Comments].[Image_Active], [UF2_Comments].[Image_ID], [Bmk1002] NULL PLAN_ROW 0 1.0
676 1 | |--Filter(WHERE:(Convert([UF2_Comments].[Image_Active])=1 AND Convert([UF2_Comments].[comment_active])=1)) 1 11 9 Filter Filter WHERE:(Convert([UF2_Comments].[Image_Active])=1 AND Convert([UF2_Comments].[comment_active])=1) NULL 107.01357 0.0 1.285335E-4 51 0.37241974 [UF2_Comments].[comment_id], [UF2_Comments].[user_id], [UF2_Comments].[comment_active], [UF2_Comments].[comment_length_flag], [UF2_Comments].[Image_Active], [UF2_Comments].[Image_ID] NULL PLAN_ROW 0 1.0
738 1 | | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([UseFilm].[dbo].[UF2_Comments])) 1 12 11 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1000]), OBJECT:([UseFilm].[dbo].[UF2_Comments]) [UF2_Comments].[user_id], [UF2_Comments].[comment_active], [UF2_Comments].[comment_length_flag], [UF2_Comments].[Image_Active], [UF2_Comments].[Image_ID] 119.0125 0.36874706 1.3091374E-4 51 0.37229121 [UF2_Comments].[comment_id], [UF2_Comments].[user_id], [UF2_Comments].[comment_active], [UF2_Comments].[comment_length_flag], [UF2_Comments].[Image_Active], [UF2_Comments].[Image_ID] NULL PLAN_ROW 0 1.0
738 1 | | |--Index Seek(OBJECT:([UseFilm].[dbo].[UF2_Comments].[IX_UF2_Comments_Photographers]), SEEK:([UF2_Comments].[Photog_ID]=82) ORDERED FORWARD) 1 14 12 Index Seek Index Seek OBJECT:([UseFilm].[dbo].[UF2_Comments].[IX_UF2_Comments_Photographers]), SEEK:([UF2_Comments].[Photog_ID]=82) ORDERED FORWARD [Bmk1000], [UF2_Comments].[comment_id] 119.0125 3.2034749E-3 2.0977079E-4 33 3.4132458E-3 [Bmk1000], [UF2_Comments].[comment_id] NULL PLAN_ROW 0 1.0
16796 676 | |--Index Seek(OBJECT:([UseFilm].[dbo].[UF2_rt_friends].[IX_UF2_rt_friends_to] AS [Friends]), SEEK:([Friends].[to_id]=[UF2_Comments].[user_id]) ORDERED FORWARD) 1 24 9 Index Seek Index Seek OBJECT:([UseFilm].[dbo].[UF2_rt_friends].[IX_UF2_rt_friends_to] AS [Friends]), SEEK:([Friends].[to_id]=[UF2_Comments].[user_id]) ORDERED FORWARD [Bmk1002] 6.4828668 3.2034749E-3 8.5650601E-5 33 1.7288212E-2 [Bmk1002] NULL PLAN_ROW 0 107.01357
1 1 |--Clustered Index Seek(OBJECT:([UseFilm].[dbo].[UF2_rt_users].[PK_UF2_rt_users] AS [Critiquer]), SEEK:([Critiquer].[user_id]=[Friends].[to_id]) ORDERED FORWARD) 1 28 5 Clustered Index Seek Clustered Index Seek OBJECT:([UseFilm].[dbo].[UF2_rt_users].[PK_UF2_rt_users] AS [Critiquer]), SEEK:([Critiquer].[user_id]=[Friends].[to_id]) ORDERED FORWARD [Critiquer].[user_first_name], [Critiquer].[user_last_name], [Critiquer].[user_id], [Critiquer].[user_donor], [Critiquer].[user_karma], [Critiquer].[user_charisma] 1.0 3.2034749E-3 7.9603E-5 1573 4.156427E-3 [Critiquer].[user_first_name], [Critiquer].[user_last_name], [Critiquer].[user_id], [Critiquer].[user_donor], [Critiquer].[user_karma], [Critiquer].[user_charisma] NULL PLAN_ROW 0 7.2474766

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-03 : 21:24:26

It looks like your biggest problem is with the UseFilm table. Try putting a composite index on that table with the most selective columns first in the index. In other words, look at all the columns from that table that are in the WHERE and FROM clause. Create an index with all of them, putting the most selective column first.

See if that helps. If it doesn't, repost the information you just gave us with the new plan.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources