为什么读数据狂慢？

[pangu2000]

我用sturts+sqlserver2000做了一个网站，现在在线人数多了，发现读取数据很慢，有时就无法读取，后台无异常，但是重新启动tomcat就没问题了，请问这是何原因？是不是sqlserver2000是盗版的不支持多用户访问呢？请专家解答一下。

[8992026]

游标方式

[pangu2000]

楼上那位朋友能不能说具体一点？这么肯定？！

[klan]

你是怎么实现你的读取数据的?
具体一点

[tky111]

数据优化呀

[pangu2000]

谢谢楼上的几位！
我是执行的是一个查询的动作
public ArrayList getFriend_datum(int comm_id) throws Exception
{
ArrayList arraylist = null;
Connection con = null;
Statement stmt = null,state=null,st=null;
ResultSet rs = null,rst=null,rss=null;
DataSource dataSource=null;
C_community_memberBean userbean = null;
arraylist = new ArrayList();
try
{
ServletContext context = servlet.getServletContext();
DataSource datasource=(DataSource)context.getAttribute(Globals.DATA_SOURCE_KEY);
con = datasource.getConnection();

String numofFriend="0";
String user_name=null;

stmt = con.createStatement();
rs = stmt.executeQuery("select TOP 24 member_id from community_member_table where comm_id='"+comm_id+"' and is_confirm ='true' order by join_time desc");
while(rs.next())
{
userbean = new C_community_memberBean();
userbean.setMember_id(rs.getInt("member_id"));

state = con.createStatement();
rst=state.executeQuery("select user_id,user_name,user_photo from user_table where user_id='"+rs.getInt("member_id")+"'");
if(rst.next())
{
userbean.setUser_id(rst.getInt("user_id"));
user_name=rst.getString("user_name");
if(user_name.length()>8)
{
user_name=user_name.substring(0,8)+"...";
}
userbean.setUser_name(user_name);
userbean.setUser_photo(rst.getString("user_photo"));
//存放下一度朋友个数
st = con.createStatement();
rss=st.executeQuery("select count(*) as total_friend from friend_table where user_id='"+rst.getInt("user_id")+"'");
if(rss.next())
{
if(rss.getInt("total_friend")!=0)
{
numofFriend=String.valueOf(rss.getInt("total_friend"));
}
userbean.setTotal_friend(numofFriend);
}
//////////////////////////
if (rss != null) {
rss.close();
rss=null;
}
if (st != null) {
st.close();
st=null;
}
//////////////////////////

}
///////////////////////////////////

if (rst != null) {
rst.close();
rst=null;
}
if (state != null) {
state.close();
state=null;
}
/////////////////////////////////

arraylist.add(userbean);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
finally {


if (rs != null) {
rs.close();
rs=null;
}


if (stmt != null) {
stmt.close();
stmt=null;
}
if (con!= null) {
con.close();
con=null;
}
}
return arraylist;
}

[klan]

大概看了一下，你好像就是先去id，然后根据id再去取相关的数据

你这样好了，写一个view，然后数据从view中取，程序中就不要rs里面套rs了

下面的sql你自己再优化一下吧

参考：
create view v_abc
as
select a.user_id,a.user_name,a.user_photo from user_table a
left join (select count(*),user_id as total_friend from friend_table group by user_id)b on a.user_id=b.user_id
where a.user_id in (select TOP 24 member_id from community_member_table
where comm_id='*****' and is_confirm ='true' order by join_time desc)

[pangu2000]

谢谢楼上的朋友！请问大家怎么给分呀！

[Wonny]

可能是您的程序有问题。