B
baoyun
Unregistered / Unconfirmed
GUEST, unregistred user!
[?]刚学数据库,以下查询写得很笨,查询的数据情况:总数据量超过千万,所要查询的是,不到100个的 Station_ID,只查询24小时内(每小时)的雨量数据,再合计,可是也要2~3秒时间,不知道该怎么优化。劳烦高人指点,感激非常!
T1:=FormatDateTime('yyyy-MM-dd',DateTimePicker1.Date) + ' 20:30:00' ;
T2:=FormatDateTime('yyyy-MM-dd',DateTimePicker1.Date-1) + ' 20:30:00' ;
strslect:='Select Hour_Rainfall '; //DISTINCT Date_Time , Hour_Rainfall
for i:=1 to num do strfrom:='from Surface_Udometer where Station_ID='''+sta+ '''and Date_Time>'''+T2+ '''and Date_Time<'''+T1+''' ORDER BY Date_Time' ;
Image1.Canvas.Font.Style:=[fsBold];
for i:=1 to num do
begin
try
with QueryRain do
begin
close;
sql.Clear ;
sql.add(strslect);
sql.add(strfrom);
open;
end;
except
//ShowMessage('数据库链接错误!');
close;
end;
rain:=0;//(ADOQuery1.fieldbyname('Hour_Rainfall') as TNumericField).DisplayFormat := '0.#';
SetBKMode(Image1.Canvas.Handle,TRANSPARENT); //设置文字背景透明(2)
while not QueryRain.eof do
begin
if QueryRain.FieldByName('Hour_Rainfall').AsFloat <>8888 then
rain:=rain+QueryRain.FieldByName('Hour_Rainfall').AsFloat;
QueryRain.next;
end;
if (rain>= 0) and (rain<25) then Image1.Canvas.Font.Color :=clBlue;
if (rain>= 25) and (rain<50) then Image1.Canvas.Font.Color :=clFuchsia;
if (rain>=50) and (rain<100) then Image1.Canvas.Font.Color :=clRed;
if rain>=100 then Image1.Canvas.Font.Color :=clBlack;
strrain:= formatfloat('0.0',rain);
if rain <>0 then Image1.Canvas.TextOut(x,y,strrain);
ProgressBar1.StepIt ;
end;
T1:=FormatDateTime('yyyy-MM-dd',DateTimePicker1.Date) + ' 20:30:00' ;
T2:=FormatDateTime('yyyy-MM-dd',DateTimePicker1.Date-1) + ' 20:30:00' ;
strslect:='Select Hour_Rainfall '; //DISTINCT Date_Time , Hour_Rainfall
for i:=1 to num do strfrom:='from Surface_Udometer where Station_ID='''+sta+ '''and Date_Time>'''+T2+ '''and Date_Time<'''+T1+''' ORDER BY Date_Time' ;
Image1.Canvas.Font.Style:=[fsBold];
for i:=1 to num do
begin
try
with QueryRain do
begin
close;
sql.Clear ;
sql.add(strslect);
sql.add(strfrom);
open;
end;
except
//ShowMessage('数据库链接错误!');
close;
end;
rain:=0;//(ADOQuery1.fieldbyname('Hour_Rainfall') as TNumericField).DisplayFormat := '0.#';
SetBKMode(Image1.Canvas.Handle,TRANSPARENT); //设置文字背景透明(2)
while not QueryRain.eof do
begin
if QueryRain.FieldByName('Hour_Rainfall').AsFloat <>8888 then
rain:=rain+QueryRain.FieldByName('Hour_Rainfall').AsFloat;
QueryRain.next;
end;
if (rain>= 0) and (rain<25) then Image1.Canvas.Font.Color :=clBlue;
if (rain>= 25) and (rain<50) then Image1.Canvas.Font.Color :=clFuchsia;
if (rain>=50) and (rain<100) then Image1.Canvas.Font.Color :=clRed;
if rain>=100 then Image1.Canvas.Font.Color :=clBlack;
strrain:= formatfloat('0.0',rain);
if rain <>0 then Image1.Canvas.TextOut(x,y,strrain);
ProgressBar1.StepIt ;
end;