Help Please , Select the first 3 messages for each

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

New to SQL Server Programming

Help Please , Select the first 3 messages for each

Author

Topic

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-07-18 : 09:11:12

I have a website with a DB table of messages sent by users.
I want to select the first 3 messages for each unique user.

I found a way to accomplish this by pulling all of the messages from the sql and filtering the results via ASP.net code but it seems to be really really slow.

I hope one of you guys can help me accomplish this via sql .

Messages Table

Mid Username SendDate
1 100 16/07/06 13:00
2 100 17/07/06 14:00
3 101 17/07/06 14:30
4 102 17/07/06 15:00
5 103 18/07/06 16:00
6 100 18/07/06 17:00
7 102 18/07/06 18:01
8 103 19/07/06 18:02
9 110 19/07/06 19:03
10 102 19/07/06 19:04
11 100 21/07/06 20:00
12 103 21/07/06 20:00
13 102 21/07/06 20:00

Query result:

Mid
1
2
6
3
4
7
10
5
8
12
9

THANK you guys!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-18 : 09:31:42

[code]select Mid
from Messages m
where Mid in (select top 3 Mid from Messages x where x.Username = m.Username)
order by Username[/code]

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-07-18 : 10:13:54

Thank you Thank you Thank you :)

it seem to be so simple when u have the answer.

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-07-24 : 08:32:45

[code]
SELECT mid AS Id
FROM Messages m
WHERE (
mid IN (SELECT TOP 5 mid FROM Messages x WHERE (x.username = m.username) ORDER BY mid )
AND
m.senddate>= @FromDate AND m.senddate<= @ToDate
)
[/code]
I'm using the code khtan suggested and it works.
BUT i have a major performace problem.
Messages table has more than 100k of rows.

When I run this query on a date range larger than a few days , the query takes more than 40 sec to complete or it gets stuck with this error:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. "

ANY ideas on how to make this more efficient in terms of perfomance?

Thank You!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-07-24 : 08:55:56

try this way:
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 2.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 09:55:13

Try this approach and see if it is fast enough

-- Prepare test data
declare @messages table (mid int, username int, senddate datetime)

set dateformat dmy

insert	@messages
select	1, 100, '16/07/06 13:00' union all
select	2, 100, '17/07/06 14:00' union all
select	3, 101, '17/07/06 14:30' union all
select	4, 102, '17/07/06 15:00' union all
select	5, 103, '18/07/06 16:00' union all
select	6, 100, '18/07/06 17:00' union all
select	7, 102, '18/07/06 18:01' union all
select	8, 103, '19/07/06 18:02' union all
select	9, 110, '19/07/06 19:03' union all
select	10, 102, '19/07/06 19:04' union all
select	11, 100, '21/07/06 20:00' union all
select	12, 103, '21/07/06 20:00' union all
select	13, 102, '21/07/06 20:00'

-- Do the work (this is the code you run on your computer).
select		b0.i * isnull(min(m0.mid), 0) +
		b1.i * isnull(min(m1.mid), 0) +
		b2.i * isnull(min(m2.mid), 0) mids
from		(
			select		username,
					min(mid) mid
			from		@messages
			group by	username
		) m0
left join	@messages m1 on m1.username = m0.username and m1.mid > m0.mid
left join	@messages m2 on m2.username = m1.username and m2.mid > m1.mid
cross join 	(select 0 i union all select 1) b0
cross join 	(select 0 i union all select 1) b1
cross join 	(select 0 i union all select 1) b2
where		b0.i + b1.i + b2.i = 1
group by	m0.username,
		b0.i,
		b1.i,
		b2.i
having		b0.i * isnull(min(m0.mid), 0) +
		b1.i * isnull(min(m1.mid), 0) +
		b2.i * isnull(min(m2.mid), 0) > 0
order by	m0.username,
		b0.i * isnull(min(m0.mid), 0) +
		b1.i * isnull(min(m1.mid), 0) +
		b2.i * isnull(min(m2.mid), 0)

Output is

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-24 : 10:49:17

Also, try this one...

SELECT mid AS Id
FROM messages a
WHERE (SELECT count(*) FROM messages WHERE username = a.username and mid < a.mid) < 3
ORDER BY username, mid

EDIT: I just realised this is the same method as spirit1 suggested. My apologies spirit1

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-24 : 10:54:59

Oh, and of course, make sure you have an index on 'mid' (presumably you have anyway), and if you're adding criteria on senddate, you should have an index on that too.

These articles should help you analyse the execution plans and optimise for the queries that have been suggested...

http://www.sqlservercentral.com/columnists/jsack/sevenshowplanredflags.asp
http://www.sql-server-performance.com/query_execution_plan_analysis.asp

And don't forget to let us know the results...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 11:28:53

I try to stay out of subqueries as much as I can due to performance ratio. But sometimes you can't leave without. I wonder though how my approach compares to subquery when having 100K+ rows?

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-07-24 : 11:54:13

maybe this is a case where a cursor might be faster...
it is in running totals.

No appologiy needed, Ryan.

On the other hand... HOW DARE YOU??!???!??

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources