急 我想問一下如何提高我的sql語句執行的速度, 貼上我的源碼.(50分)

這是我的源碼 ;
procedure TForm1.BitBtn1Click(Sender: TObject);
var
sqlstr :string;
time1 ,time2 : string;
i : Integer ;
begin

Series1.Clear;

time1 :=DateToStr(DateTimePicker1.Date)+' '+timetostr(DateTimePicker2.time);
time2 :=DateToStr(DateTimePicker3.Date)+' '+timetostr(DateTimePicker4.time);

with ADOQuery1 do
begin
close;
sql.Clear;
sqlstr :='select process Stations,count(distinct unit_id) InputQty from unit_status ';
sqlstr :=sqlstr +' where serial_number in (select track_id from TRACK_MASTER where front_end_job_id=''81001789'') and ' ;
sqlstr :=sqlstr +' modified_date>=to_date(''' + time1 + ''',''yyyy/mm/dd hh24:mi:ss'') and modified_date<=to_date(''' + time2 + ''',''yyyy/mm/dd hh24:mi:ss'') and ';
//sqlstr :=sqlstr +' modified_date between :a and :b and ';
sqlstr :=sqlStr + ' process in (''CMT'',''RFC'',''MIT'',''RFT'') group by process ' ;
sql.Add(sqlstr);
Open;

好象没什么办法优化,只能把select track_id from TRACK_MASTER where front_end_job_id=''81001789''事先作成视图,并对unit_status 表做个process in (''CMT'',''RFC'',''MIT'',''RFT'')的视图,尽量利用oracle自己的检索性能.并且建议modified_date>=to_date(''' + time1 + ''',''yyyy/mm/dd hh24:mi:ss'') and modified_date<=to_date(''' + time2 + ''',''yyyy/mm/dd hh24:mi:ss'')改成to_char,我日常使用发现to_char要比to_date快点.并且建议使用dbexpress连接数据库,使用起来检索速度个人感觉快过ADO.

現在源碼有點改變了; 我貼出來給大家看看!
procedure TForm1.BitBtn1Click(Sender: TObject);
var
sqlstr,sqlstr1 :string;
time1 ,time2 : string;
i : Integer ;
begin

Series1.Clear;

time1 :=DateToStr(DateTimePicker1.Date)+' '+timetostr(DateTimePicker2.time);
time2 :=DateToStr(DateTimePicker3.Date)+' '+timetostr(DateTimePicker4.time);

if Trim(cbb1.Text) <> 'all' then
sqlstr1 := ' and serial_number in (select track_id from TRACK_MASTER where front_end_part_ver_code ='''+cbb1.Text+''') '
else
sqlstr1 := '';



with ADOQuery1 do
begin
close;
sql.Clear;
sqlstr :='select process Stations,count(distinct unit_id) InputQty from unit_status ';
// sqlstr :=sqlstr +' where serial_number in (select track_id from TRACK_MASTER where front_end_job_id=''81001789'') and ' ;
sqlstr :=sqlstr +' where modified_date>=to_date(''' + time1 + ''',''yyyy/mm/dd hh24:mi:ss'') and modified_date<=to_date(''' + time2 + ''',''yyyy/mm/dd hh24:mi:ss'') ';
sqlstr :=sqlstr + sqlstr1;
// sqlstr :=sqlstr +' modified_date>=to_date(''' + time1 + ''',''yyyy/mm/dd hh24:mi:ss'') and modified_date<=to_date(''' + time2 + ''',''yyyy/mm/dd hh24:mi:ss'') and ';
// sqlstr :=sqlstr +' modified_date between :a and :b and ';
sqlstr :=sqlStr + ' and process in (''CMT'',''RFC'',''MIT'',''RFT'') group by process ' ;
// parameters.ParamByName('a').Value:=time1;
// parameters.ParamByName('b').Value:=time2;
sql.Add(sqlstr);
ShowMessage(sql.text);
Open;

是不是使用to_date和to_char 都會使查詢的速度變慢阿,有沒有甚麼方法能替換之類甚麼的阿?

是用in的原因，in是子查询，Oracle尽量不要用in，用关联查。

to : leadyli 但是不用 in 代碼該怎麼寫阿
serial_number in (select track_id from TRACK_MASTER where
front_end_part_ver_code ='''+cbb1.Text+''')
serial_number 有多條數據

等待中啊 怎么没有什么回答啊！

帮顶！

╭=========================================╮

80G海量源代码,控件，书籍全免费狂下不停！

http://www.source520.com

个人网站站长开发推广同盟，让所有人知道你的世界！

http://www.source520.com/search/search.asp

╰=========================================╯

用exists
select ...from unit_status a
where exists (select track_id from TRACK_MASTER b where
front_end_part_ver_code ='''+cbb1.Text+''' and a.serial_number=b.track_id )

我把注释去掉这样好看一些。
procedure TForm1.BitBtn1Click(Sender: TObject);
var
sqlstr,sqlstr1 :string;
time1 ,time2 : string;
i : Integer ;
begin

Series1.Clear;

time1 :=DateToStr(DateTimePicker1.Date)+' '+timetostr(DateTimePicker2.time);
time2 :=DateToStr(DateTimePicker3.Date)+' '+timetostr(DateTimePicker4.time);

if Trim(cbb1.Text) <> 'all' then
sqlstr1 := ' and serial_number in (select track_id from TRACK_MASTER where front_end_part_ver_code ='''+cbb1.Text+''') '
else
sqlstr1 := '';



with ADOQuery1 do
begin
close;
sql.Clear;
sqlstr :='select process Stations,count(distinct unit_id) InputQty from unit_status ';
sqlstr :=sqlstr +' where modified_date>=to_date(''' + time1 + ''',''yyyy/mm/dd hh24:mi:ss'') and modified_date<=to_date(''' + time2 + ''',''yyyy/mm/dd hh24:mi:ss'') ';
sqlstr :=sqlstr + sqlstr1;
sqlstr :=sqlStr + ' and process in (''CMT'',''RFC'',''MIT'',''RFT'') group by process ' ;
sql.Add(sqlstr);
ShowMessage(sql.text);
Open;

to :tohh79 我用的是oracle 的数据库 好象不行啊.

尽量在sql中先塞选出少的记录，在查

1. 最好不要用 in
2. 你可以用, select ..... from 表1 a, 表2 b where (a.??=B???) and (a.???=B??) 的形式
3.还要注意一下,.表1 一定是主表记录数多的,表2 最好是 记录数少的,表要少,
4. 最好查有限的数据量,
5. ADOQuery1 的缓存, 及其它相关



查看sql語句執行的速度或者效率请用 (把SQL语句考到查询分析器里,然后执行,再 Ctrl+L ) 就可以看到 sql語句執行的速度或者效率

這麼長的SQL語句，應該放到視圖或存儲過程裡執行。
其最大的特點是減少網絡傳輸流量。

多人接受答案了。

謝了 這兩天太忙沒有上網.