怎么加快查询速度(50分)

C

cnbobo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #1

我有两个数据库表“包厢，餐饮预订”，在 listview1导入包厢名时要完成这样的功能：
包厢被预订：NewItem.ImageIndex:=2；
没被预订：NewItem.ImageIndex:=1；
下面这段程序虽然完成了这功能，但是速度很慢慢，没有效率，请大家帮忙！！！[]
procedure TForm3.SpeedButton1Click(Sender: TObject);
var lc:integer;
NewItem:TListItem;
begin
with form1.adoquery1 do
begin
Close;
sql.Clear; // ,餐饮预订
sql.Add('select * from 包厢 ');
// sql.Add('where 包厢.包厢名=餐饮预订.包厢 ');
sql.Add('order by 包厢名');
open;
first;
end;
rc:=form1.ADOQuery1.RecordCount;
for lc:=0 to rc-1 do
begin
NewItem:=ListView1.Items.Add;
//listview1.Items.Add;
listview1.Items.Item[lc].Caption:=form1.ADOQuery1.fieldbyname('包厢名').AsString;
/////////////////////////////////////////////////////////
with adoquery1 do
begin
Close;
sql.Clear;
sql.Add('select * from 餐饮预订 ');
sql.Add('where 包厢='+''''+listview1.Items.Item[lc].Caption+'''');
open;
end;
////////////////////////////////////////////////////////
if adoquery1.RecordCount>0 then
NewItem.ImageIndex:=2
else
NewItem.ImageIndex:=1;
form1.ADOQuery1.Next;
end;
end;

 

M

mdc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #2

关注。

 

K

kunmeng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #3

语句头部加上 ListView1.Items.BeginUpdate;
尾部加上 ListView1.Items.EndUpdate;
//如果库中的记录量大，数据库建立对应关键子索引

 

A

Aiirii

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #4

你查询前要用disablecontrol;
open后用 enablecontrol;啊！

 

C

charles_zou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #5

建立存储过程，很快的

 

C

cnbobo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #6

to kunmeng:
我的数据量不多，加了这两句没什么用。我想问题主要出在循环里的查询，不知如何解决.

 

C

cnbobo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #7

大家能不能说的详细点

 

C

cnbobo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #8

to charles_zou:怎么建立存储过程

 

Z

zhtx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #9

你能不能先生成包含包厢预定信息的库,然后再统一显示,免得循环调用SQL语句.

 

H

honor_53

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #10

以包厢名做一个索引会打打的加快速度的！
写一个递推函数吧

 

C

cnbobo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #11

to honor_53L:
怎么写。我搞不定，……

 

沸

沸腾一百

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #12

加快查询速度
和你建立的表结构有很大的关系，
表结构一定要建立主键和索引，否则，会很慢

 

P

pengjinlongex

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #13

先写一个存储过程，我用的是SQL SERVER
create procedure proc
select d1.包厢名,case when exists (select * from 餐饮预订 d2 where d2.包厢=d1.包厢名) then 1
else 0
end as bool
from 包厢 d1
order by d1.包厢名
在程序里写如下代码
procedure TForm3.SpeedButton1Click(Sender: TObject);
var lc:integer;
NewItem:TListItem;
begin
with form1.adoquery1 do
begin
Close;
sql.Clear; // ,餐饮预订
sql.Add('exec proc ');
open;
first;
end;
rc:=form1.ADOQuery1.RecordCount;
for lc:=0 to rc-1 do
begin
NewItem:=ListView1.Items.Add;
listview1.Items.Item[lc].Caption:=form1.ADOQuery1.fieldbyname('包厢名').AsString;
if form1.ADOQuery1.fieldbyname('包厢名').value=0 then
NewItem.ImageIndex:=1
else
NewItem.ImageIndex:=2;
end;
end;

 

雪

雪上霜

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #14

关注中。。。

 

F

fshrq1

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #15

不用存储过程那么麻烦呀

 

Z

zslg1

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-07

• #16

first耗时

 

C

chji

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-08

• #17

//慢在这里! 你其实打开了rc次(adoquery1)数据集
with adoquery1 do
begin
Close;
sql.Clear;
sql.Add('select * from 餐饮预订 ');
sql.Add('where 包厢='+''''+listview1.Items.Item[lc].Caption+'''');
open;
end;

解决办法(适用于sqlserver数据库与access数据库 )
procedure TForm3.SpeedButton1Click(Sender: TObject);
var NewItem:TListItem;
begin
with form1.adoquery1 do
begin
Close;
sql.text:='SELECT count(餐饮预订.包厢 ) AS 是否预定, 包厢.包厢名'
+' FROM 包厢 LEFT JOIN 餐饮预订 ON 包厢.包厢名= 餐饮预订.包厢'
+' GROUP BY 包厢.包厢名';
open;
end;

while not form1.adoquery1.eof do
begin
NewItem:=ListView1.Items.Add;
NewItem.Caption:=form1.ADOQuery1.fieldbyname('包厢名').AsString;
if form1.ADOQuery1.fieldbyname('是否预定').asinteger=0 then
NewItem.ImageIndex:=1
else
NewItem.ImageIndex:=2;
end;

 

X

xlkjok

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-08

• #18

with adoquery1 do
begin
Close;
sql.Clear;
sql.Add('select * from 包厢 ');
open;
end;
with adoquery2 do
begin
Close;
sql.Clear;
sql.Add('select * from 餐饮预订 ');
open;
end;

with ADOquery1 do
begin
while not Eof do
begin
NewItem:=ListView1.Items.Add;
NewItem.Caption:=ADOQuery1.fieldbyname('包厢名').AsString;
if ADOQuery1.Locate('包厢名',ADOQuery1.fieldbyname('包厢名').AsString,[]) then
NewItem.ImageIndex:=1
else
NewItem.ImageIndex:=2;
Next;
end;
end;
要不在'包厢'表中加一个"是/否"预订字段

 

C

cnbobo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-09-09

• #19

谢谢大家，我已搞定！！代码如下:

procedure TForm3.SpeedButton1Click(Sender: TObject);
var lc:integer;
NewItem:TListItem;
begin
rj:=datetostr(DateTimePicker1.Date);
with adoquery1 do
begin
Close;
sql.Clear;
sql.Add('SELECT dbo.包厢.包厢名, dbo.餐饮预订.x FROM dbo.包厢 LEFT OUTER JOIN dbo.餐饮预订 ON dbo.餐饮预订.包厢名 = dbo.包厢.包厢名 and dbo.餐饮预订.预订日期 ='+''''+rj+''''+'and dbo.餐饮预订.时间='+''''+sj+''''+'order by dbo.包厢.包厢名' );
open;
end;
rc:=ADOQuery1.RecordCount;
for lc:=0 to rc-1 do
begin
NewItem:=ListView1.Items.Add;
//listview1.Items.Add;
listview1.Items.Item[lc].Caption:=ADOQuery1.fieldbyname('包厢名').AsString;
if ADOQuery1.FieldByName('x').AsString='0' then
NewItem.ImageIndex:=0
else
if ADOQuery1.FieldByName('x').AsString='1' then
NewItem.ImageIndex:=1
else
if ADOQuery1.FieldByName('x').AsString='2' then
NewItem.ImageIndex:=2;
ADOQuery1.Next;
end;
end;