sql语句优化
我的数据量如果达到几十万的话 程序反应很慢跟死了一样
具体如下：
//得到查询后所有未定制该栏目客户的总数
public List getCustomerCount(String customerName,String mobile,int groupId,int proId,String whe){
init();
List list = new ArrayList();
StringBuffer sb = new StringBuffer( "select a.customerid from b_customer a " +
" where a.customerid not in(select customerid from b_customer_program_relation where proid= "+proId+ ") "+whe+ " ");
if(groupId!=0){
sb.delete(0, sb.length());
sb.append( "select a.customerid from b_customer a, b_customer_group_relation b, "
+ " b_customer_group c where a.customerid not in(select customerid from b_customer_program_relation where proid= "+proId+ ") and b.GroupID= "+groupId+ " "
+ " and b.GroupID=c.GroupID and b.CustomerID=a.CustomerID "
+ " "+whe+ " ");
}
if(!customerName.equals( " ") && customerName!=null){
sb.append( "and a.name like '% "+customerName+ "% ' ");
}
if(!mobile.equals( " ") && mobile!=null){
sb.append( " and a.mobile= "+mobile+ " ");
}
try{
pstmt=con.prepareStatement(sb.toString());
rs=pstmt.executeQuery();
while(rs.next()){
list.add(String.valueOf(rs.getInt(1)));
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
rs.close();
pstmt.close();
close();
}catch(Exception ex){
ex.printStackTrace();
}
}
return list;
}
哪为朋友帮忙优化下 一旦可以提高工作效率马上给分谢谢．
[解决办法]
因为不知道表的结构,所以...无从考虑.
[解决办法]
首先要给大家讲明白你要干什么，看着头大
[解决办法]
select a.customerid
from b_customer a
where not exists
(
select 1
from b_customer_program_relation b
where a. customerid = b.customerid
and b.proid = ?
)
[解决办法]
select distinct a.customerid from b_customer A left outer join b_customer_program_relation B on A.customerid = B.customerid and B.proid !=1
[解决办法]
千万不要in 、not in!!!都改exsit
[解决办法]
不知道您用的是ＯＲＡＣＬＥ还是ＤＢ２所以把两种都写了一下．
Oracle:
select
a.customerid
from
b_customer a，b_customer_program_relation b
where
a.customerid = b.customerid
and
b.proid!=1
ＤＢ２：
select
a.customerid
from
b_customer a
inner join
b_customer_program_relation b
on
where
a.customerid = b.customerid
and
proid!=1
[解决办法]
youbin_()如果还不能解决的话，就只能写存储过程了。
mysql5.0支持存储过程
[解决办法]
在编写SQL语句时，如果要实现一张表有而另一张表没有的数据库时，通常第一直觉的写法就是：
select * from table1 where table1.id not in (select id from table2)，这种方法虽然很直观，但是in及not in的写法经常会影响其执行的效率，对于大数据量时，这个原因经常是性能的瓶颈。可以通过左连接的方法来解决，其替代写法如下：
select a.* from table1 a left join table2 b on a.id=b.id where b.id is null
同理，这个方法也适用于in的情况。
[解决办法]
不用not in的话用not exit
