powered by Jive Software

About get roster list''s SQL

Hi,My jive server is using MS SQL Server 2000,and I found one problem.It’'s so slow when my clients request for roster list.

Then I used SQL profile to monitor the SQL statements,I found something below:

SELECT jid, rosterID, sub, ask, recv, nick FROM jiveRoster WHERE username=N’‘tangrh’’

SELECT groupName FROM jiveRosterGroups WHERE rosterID=10114

SELECT name, email, creationDate, modificationDate FROM jiveUser WHERE username=N’‘wangly’’

SELECT name, propValue FROM jiveUserProp WHERE username=N’‘wangly’’

I know that all of the roster data are stored in jiveRoster and jiveRosterGroup,and if the current roster is at the same server,you may want to get his/her info from jiveUser.I don’‘t know what you do,but I think my SQL is more efficiency than that I got from profile.It’'s below:

SELECT a.jid, a.rosterID, a.sub, a.ask, a.recv, a.nick,b.groupName

FROM jive.jiveRoster a inner join jive.jiveRosterGroups b

on a.rosterID=b.rosterID

WHERE a.username=’‘tangrh’’

order by a.rosterID

This statement gets all rosters in one recordset.

And if the current roster is at the same server,there’'s another efficiency way to solve it.

I hope that it is useful for you!

Thanks for your cool project and hard work.

every user has 100+ rosters,and then there are 10000+ lines in jiveRoster and jiveRosterGroup.It takes me 4+ minutes to get my roster list.

Anybody there?