现在有个代码
String sql = ""
for(i = 0;i < 1000; i ++)
{ sql = "select t.ID from tblValue where t.Value = " + i;
Statement stmt = null;
ResultSet result = null;
stmt = con.createStatement();
result = stmt.executeQuery(sql); if(result.next()) {
detail.dayData[i] = result.getString("VALUE");
} else {
detail.dayData[i] = "0";
}
}发现,1000次执行的话,执行的比较慢,如果大于1000会更慢。
请求一条比较执行快的SQL语句。
String sql = ""
for(i = 0;i < 1000; i ++)
{ sql = "select t.ID from tblValue where t.Value = " + i;
Statement stmt = null;
ResultSet result = null;
stmt = con.createStatement();
result = stmt.executeQuery(sql); if(result.next()) {
detail.dayData[i] = result.getString("VALUE");
} else {
detail.dayData[i] = "0";
}
}发现,1000次执行的话,执行的比较慢,如果大于1000会更慢。
请求一条比较执行快的SQL语句。
SQL语句已经很简洁了,执行1000次取1000次数据,当然需要些时间了,这很正常。
因为在一个窗口或页面中需要的数据肯定是有限的,再有可以考虑使用Java中的多线程来解决
这个问题。
Statement stmt = null;
ResultSet result = null;
stmt = con.createStatement();
for(i = 0;i < 1000; i ++)
{
sql = "select t.ID from tblValue where t.Value = " + i;
result = stmt.executeQuery(sql);
if(result.next()) {
detail.dayData[i] = result.getString("VALUE");
} else {
detail.dayData[i] = "0";
}
}
红字部分,这样改试试,没有必要每次执行都定义一遍,连接一般……
2.循环1000次,也就是执行1000次查询,这个很浪费时间,还是写成一个sql效率高
比如:select t.ID from tblValue where t.Value in ('a','b') 这样的。
Statement stmt = null;
ResultSet result = null;
stmt = con.createStatement();
result = stmt.executeQuery(sql);
if(result.next()) {
for(i = 0;i < result.table[0].rows.count ; i ++){
detail.dayData[i] = result.getString("VALUE");
}
} else {
detail.dayData[i] = "0";
}
}
1、测试表结构
create table t_test
(
a int,
b varchar2(32)
)
2、填入缺失编号的数据
begin
for i in 1..12 loop
if (i <> 5) then
insert into t_test values (i,'line' || to_char(i));
end if ;
commit;
end loop;
end;
--记录如下:
1 1 line1
2 2 line2
3 3 line3
4 4 line4
5 6 line6
6 7 line7
7 8 line8
8 9 line9
9 10 line10
10 11 line11
11 12 line123、创建同结构的中间表
create table t_temp as select * from t_test where 1=0;4、执行脚本,补全缺失编码的描述为null value
declare
strSql varchar2(1000) := '';
recordCount number := 0;
begin
for i in 1..100 loop
recordCount := 0;
strSql := 'select count(1) from t_test where a = ' || to_char(i);
execute immediate strSql into recordCount;
if (recordCount = 0) then
strSql := 'insert into t_temp values (' || to_char(i) || ',''null value'')';
else
strSql := 'insert into t_temp select * from t_test where a = ' || to_char(i);
end if;
--dbms_output.put_line(strSql);
execute immediate strSql;
commit;
end loop;
end;5、select * from t_temp order by a
--记录如下:
1 1 line1
2 2 line2
3 3 line3
4 4 line4
5 5 null value
6 6 line6
7 7 line7
8 8 line8
9 9 line9
10 10 line10
11 11 line11
12 12 line12
13 13 null value
14 14 null value
15 15 null value
,你这样的1000次循环,反而比原先还要耗时拉
可以改一下代码啊,试试这样String sql = "" sql = "select t.ID from tblValue where t.Value between 0 and 1000 " ;
Statement stmt = null;
ResultSet result = null;
stmt = con.createStatement();
result = stmt.executeQuery(sql);
for(i = 0;i < 1000; i ++)
{
if(i< result.table[0].rows.count )
{
detail.dayData[i] = result.table[0].rows[i]["VALUE"].toString();
}
else {
detail.dayData[i] = "0";
}
}
}
问题关键是1000次查询,要考虑一个sql完成,用in只是一种实现方式,具体按你的业务需求定。你参考下面的:SQL> WITH tblValue AS (
2 SELECT 't1' tid,'1' tvalue FROM DUAL UNION ALL
3 SELECT 't2' tid,'2' tvalue FROM DUAL UNION ALL
4 SELECT 't3' tid,'3' tvalue FROM DUAL
5 )
6 SELECT t.tid,
7 NVL(t.tvalue, 0) tvalue
8 FROM tblValue t
9 RIGHT JOIN (SELECT RTRIM(REGEXP_SUBSTR('1,2,3,4,5' || ',', '.*?' || ',', 1, LEVEL), ',') tvalue
10 FROM DUAL
11 CONNECT BY LEVEL <=
12 LENGTH(REGEXP_REPLACE('1,2,3,4,5' || ',', '[^' || ',' || ']', NULL))) m
13 ON t.tvalue = m.tvalue
14 ;TID TVALUE
--- ------
t1 1
t2 2
t3 3
0
0上面的'1,2,3,4,5'是你传递的参数,tvalue上建立索引,java端记得使用绑定。
加入 t.Value 在 3 的时候,存在了一个值,那么应该是
detail.dayData[0] = "0";
detail.dayData[1] = "0";
detail.dayData[2] = "3";
detail.dayData[3] = "0";
....按照你这个逻辑,
result.table[0].rows.count = 1detail.dayData[0] = "3"了
单独运行下面的sql看看多少时间??SELECT t.ID,NVL(t.VALUE,0) FROM tblValue t
RIGHT JOIN (
SELECT LEVEL tvalue FROM DUAL CONNECT BY LEVEL < 1001
) m ON t.VALUE = m.tvalue
sql = "select t.ID,t.Value from tblValue where t.Value between 0 and 1000 " ;
Statement stmt = null;
ResultSet result = null;
stmt = con.createStatement();
result = stmt.executeQuery(sql);
for(i = 0;i < 1000; i ++)
{
if(i=result.table[0].rows[i]["VALUE"].toString() )
{
detail.dayData[i] = result.table[0].rows[i]["VALUE"].toString();
}
else
{
detail.dayData[i] = "0";
}
}
String sql = ""
sql = "select t.ID,t.Value from tblValue where t.Value between 0 and 1000 order by t.value desc" ;
Statement stmt = null;
ResultSet result = null;
stmt = con.createStatement();
result = stmt.executeQuery(sql);
for(i = 0;i < 1000; i ++)
{
detail.dayData[i] = "0";
}
for(int i=0;i<result.table[0].rows.count;i++)
{
detail.dayData[result.table[0].rows[i]["VALUE"].toString()] = result.table[0].rows[i]["VALUE"].toString();
}