我的数据量如果达到几十万的话 程序反应很慢跟死了一样
具体如下:
//得到查询后所有未定制该栏目客户的总数
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;
}哪为朋友帮忙优化下 一旦可以提高工作效率马上给分谢谢.
具体如下:
//得到查询后所有未定制该栏目客户的总数
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;
}哪为朋友帮忙优化下 一旦可以提高工作效率马上给分谢谢.
解决方案 »
- java抓取网页程序的问题
- JAVA中能不能定义这样的二维数组int [][] play = new int [num][];?
- java 变量的使用
- java非英文字符串截取的问题(包括简繁中文,日本等双字节编码)
- 在文件并不真实存在的情况下,怎么根据扩展名来获得系统图标?
- 小妹请问thinking in java 和Core Java 2这两本名书对有一点java基础的初学者来书哪本先看为好??
- 如何格式化float数据?
- applet动态编辑,请教
- 浏览器不能浏览JAVA2写的Applet,那里可有升级包?
- 有谁会做dns的mx查询
- 急需解答,关于用GC花动态类似心电图那种曲线图
- 说得简单通俗点,介绍一下Struts和Spring
`UserID` int(11) NOT NULL,
PRIMARY KEY (`CustomerID`)
b_customer_program_relation 表字段:`ProID` int(11) NOT NULL,
`CustomerID` int(11) NOT NULL
我个人觉得我的sql语句中含有not in,大家有没有什么好的办法替换not in的功能语句.if语句里的sql都不需要考虑,就是头一条sql优化下.
from b_customer a
where not exists
(
select 1
from b_customer_program_relation b
where a. customerid = b.customerid
and b.proid = ?
)
这个,上面写错了
Oracle:
select
a.customerid
from
b_customer a,b_customer_program_relation b
where
a.customerid = b.customerid
and
b.proid!=1
DB2:
select
a.customerid
from
b_customer a
inner join
b_customer_program_relation b
on
where
a.customerid = b.customerid
and
proid!=1
不知道您用的是ORACLE还是DB2所以把两种都写了一下.
Oracle:
select
a.customerid
from
b_customer a,b_customer_program_relation b
where
a.customerid = b.customerid
and
b.proid!=1
DB2:
select
a.customerid
from
b_customer a
inner join
b_customer_program_relation b
where
a.customerid = b.customerid
on
proid!=1
我的b_customer_program_relation表是多对多的,如果有proid!=1,但在别的proid里肯定有不在proid!=1的customerid. 结贴!
mysql5.0支持存储过程
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的情况。
比如说吧 我表里有b_customer 有三条记录 customerid分别为1,2,3 在表b_customer_program_relation有记录1,1和1,2 和2,1和 2,2和2,3
当proid!=1的时候我只要查出customerid为3. 不要用 not in 和 not exists语句.在线等待!
在大数据量下not in 和 not exists的效率差别是很大的!!