跨越7个表查询，不知道连接的表是不是太多，查询效率不高，由于不能帖图，所以把SQL代码帖出来算了。/********************************************************************************
* 农场表,一个农场N个林队，一个林队M个林段
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_nongchang')
DROP TABLE rubsys_tbl_nongchang
GO
CREATE TABLE rubsys_tbl_nongchang --农场表
(
id smallint PRIMARY KEY, --索引id
name varchar(50) NOT NULL, --农场名
note varchar(100), --描述
)
/********************************************************************************
* 林队表
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_lindui')
DROP TABLE rubsys_tbl_lindui
GO
CREATE TABLE rubsys_tbl_lindui --林队表
(
id smallint PRIMARY KEY, --索引id
farm smallint NOT NULL, --农场索引
name varchar(50) NOT NULL, --林队名
note varchar(100), --描述
CONSTRAINT FK_nongchang_lindui FOREIGN KEY (farm) REFERENCES rubsys_tbl_nongchang(id)
)
GO
/********************************************************************************
* 林段表
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_linduan')
DROP TABLE rubsys_tbl_linduan
GO
CREATE TABLE rubsys_tbl_linduan --林段表
(
id smallint PRIMARY KEY, --索引id
lindui smallint NOT NULL, --林队id
name varchar(50) NOT NULL, --林段名
note varchar(100), --描述
CONSTRAINT FK_lindui_linduan FOREIGN KEY (lindui) REFERENCES rubsys_tbl_lindui(id)
)/********************************************************************************
* 属性代码表
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_shuxindaima')
DROP TABLE rubsys_tbl_shuxindaima
GO
CREATE TABLE rubsys_tbl_shuxindaima --属性代码表
(
id smallint PRIMARY KEY, --索引id
viewid smallint NOT NULL, --视图索引，有很多视图，如[生长数据]的[植被结构]就是一个视图
name varchar(50) NOT NULL, --属性名称
unit varchar(25), --属性单位
)
INSERT INTO rubsys_tbl_shuxindaima
--//[生长数据],这里示例3个属性。
--植被结构
SELECT 1, 0, '叶面积指数','' UNION ALL
SELECT 2, 0, '叶面积密度','m^2' UNION ALL
SELECT 3, 0, '枝干生物量','Kg'
--大气数据
--...
--灾害数据
-- ...
/********************************************************************************
* 数据组号表，代表那几行数据为一组。一组计算处理后的数据可对应N个原始测量数据。
* 如果groupidx为-1，表示该组数据存的为计算处理的原始数据，如果不为-1，则该组
* 为原始数据。
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_groupindex')
DROP TABLE rubsys_tbl_groupindex
GO
CREATE TABLE rubsys_tbl_groupindex --组号索引
(
id int PRIMARY KEY, --索引
groupidx int NOT NULL, --默认为－1
)
/********************************************************************************
* 数据表，表很大，数据是在每个林段测量的，所以每个数据都有一个林段号。
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_data')
DROP TABLE rubsys_tbl_data
GO
CREATE TABLE rubsys_tbl_data --数据记录表,存放所有关键数据
(
id int PRIMARY KEY, --索引id
linduan smallint NOT NULL, --林段id
t smalldatetime NOT NULL, --测量时间
daima smallint NOT NULL, --属性代码Id
groupno int NOT NULL, --测量组号
val numeric(18,6) NOT NULL, --属性值
--林段约束
CONSTRAINT FK_linduan_data FOREIGN KEY (linduan) REFERENCES rubsys_tbl_linduan(id),
--属性代码约束
CONSTRAINT FK_shuxindaima_data FOREIGN KEY (daima) REFERENCES rubsys_tbl_shuxindaima(id),
--组号约束
CONSTRAINT FK_groupindex_data FOREIGN KEY (groupno) REFERENCES rubsys_tbl_groupindex(id),)/********************************************************************************
* 备注表，并不是每一个记录都有备注，因此提出来单独存储
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_note')
DROP TABLE rubsys_tbl_note
GO
CREATE TABLE rubsys_tbl_note --数据记录备注表
(
id int PRIMARY KEY, --索引id
groupno int NOT NULL, --数据组号
note varchar(100) NOT NULL --备注
CONSTRAINT FK_groupindex_note FOREIGN KEY (groupno) REFERENCES rubsys_tbl_groupindex(id)
)
--需要显示的结果方式，以[生长数据]的[植被结构]视图为例，这里显示的是计算处理后数据。
/*----------------------------------------------------------------------------------------------
索引 林段名称 所属林队 所属农场 测量时间 叶面积指数 叶面积密度(m^2) 枝干生物量(Kg) 备注
1 6-1 1队 西联农场 2008-10-11 6.501 12.115 21.342 XXXX
2 7-3 3队 立才农场 2008-10-14 8.461 13.345 18.334 NULL
...
--索引号为1的数据是根据几十个原始数据计算得来。在程序中可以显示这种关系。原始数据与计算处理后的数据
--都是存在rubsys_tbl_data中的。根据groupno对应rubsys_tbl_groupindex中的groupidx来区分。
*/--我的查询方法，在程序会根据当前的视图生成类似的SQL代码：SELECT a.groupno AS 索引号,b.林段名称,b.林队名称,b.农场名称,CONVERT(varchar(10),a.t,120) AS 测量时间,a.val AS 叶面积指数,
c.val AS 叶面积密度,d.val AS 枝干生物量,(CASE e.note WHEN '' THEN NULL ELSE e.note END) AS 备注 FROM
(SELECT rubsys_tbl_linduan.id,rubsys_tbl_linduan.name AS 林段名称,rubsys_tbl_lindui.name AS 林队名称,rubsys_tbl_nongchang.name AS 农场名称 FROM rubsys_tbl_linduan
INNER JOIN rubsys_tbl_lindui ON rubsys_tbl_linduan.lindui = rubsys_tbl_lindui.id
INNER JOIN rubsys_tbl_nongchang ON rubsys_tbl_lindui.farm = rubsys_tbl_nongchang.id) AS b
LEFT JOIN (SELECT linduan,t,groupno,val FROM rubsys_tbl_data WHERE daima=1) AS a ON a.linduan=b.id
LEFT JOIN(SELECT groupno,val FROM rubsys_tbl_data WHERE daima=2) AS c ON a.groupno=c.groupno
LEFT JOIN(SELECT groupno,val FROM rubsys_tbl_data WHERE daima=3) AS d ON a.groupno=d.groupno
LEFT JOIN (SELECT groupno,note FROM rubsys_tbl_note) AS e ON a.groupno = e.groupno
JOIN (SELECT id FROM rubsys_tbl_groupindex WHERE groupidx = -1) AS f ON a.groupno = f.id ORDER BY a.groupno
--使用LEFT JOIN是因为后面可能为每个视图增加新的属性，不使用左连接那么原来的有部分数据就会查询不到。
当rubsys_tbl_data中记录不是很大时，运行没有什么问题。测试中rubsys_tbl_data中数据记录增加到130万个记录时，并且表中95%的数据都为[生长数据]的[植被结构]视图的数据时，查询该表就很慢了，查询并显示需要6-7秒的样子，返回的行数为40万左右(40万*3=120万)，较大。如果再加个WHERE条件只查询一两个记录，速度上倒还是可以。请问各位高手有什么好的优化方法？或者我的设计有没有重大的缺陷？尽管仍砖头，呵呵，初次设计数据库，没多少经验。真诚的感谢您的指点与帮助!
* 农场表,一个农场N个林队，一个林队M个林段
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_nongchang')
DROP TABLE rubsys_tbl_nongchang
GO
CREATE TABLE rubsys_tbl_nongchang --农场表
(
id smallint PRIMARY KEY, --索引id
name varchar(50) NOT NULL, --农场名
note varchar(100), --描述
)
/********************************************************************************
* 林队表
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_lindui')
DROP TABLE rubsys_tbl_lindui
GO
CREATE TABLE rubsys_tbl_lindui --林队表
(
id smallint PRIMARY KEY, --索引id
farm smallint NOT NULL, --农场索引
name varchar(50) NOT NULL, --林队名
note varchar(100), --描述
CONSTRAINT FK_nongchang_lindui FOREIGN KEY (farm) REFERENCES rubsys_tbl_nongchang(id)
)
GO
/********************************************************************************
* 林段表
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_linduan')
DROP TABLE rubsys_tbl_linduan
GO
CREATE TABLE rubsys_tbl_linduan --林段表
(
id smallint PRIMARY KEY, --索引id
lindui smallint NOT NULL, --林队id
name varchar(50) NOT NULL, --林段名
note varchar(100), --描述
CONSTRAINT FK_lindui_linduan FOREIGN KEY (lindui) REFERENCES rubsys_tbl_lindui(id)
)/********************************************************************************
* 属性代码表
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_shuxindaima')
DROP TABLE rubsys_tbl_shuxindaima
GO
CREATE TABLE rubsys_tbl_shuxindaima --属性代码表
(
id smallint PRIMARY KEY, --索引id
viewid smallint NOT NULL, --视图索引，有很多视图，如[生长数据]的[植被结构]就是一个视图
name varchar(50) NOT NULL, --属性名称
unit varchar(25), --属性单位
)
INSERT INTO rubsys_tbl_shuxindaima
--//[生长数据],这里示例3个属性。
--植被结构
SELECT 1, 0, '叶面积指数','' UNION ALL
SELECT 2, 0, '叶面积密度','m^2' UNION ALL
SELECT 3, 0, '枝干生物量','Kg'
--大气数据
--...
--灾害数据
-- ...
/********************************************************************************
* 数据组号表，代表那几行数据为一组。一组计算处理后的数据可对应N个原始测量数据。
* 如果groupidx为-1，表示该组数据存的为计算处理的原始数据，如果不为-1，则该组
* 为原始数据。
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_groupindex')
DROP TABLE rubsys_tbl_groupindex
GO
CREATE TABLE rubsys_tbl_groupindex --组号索引
(
id int PRIMARY KEY, --索引
groupidx int NOT NULL, --默认为－1
)
/********************************************************************************
* 数据表，表很大，数据是在每个林段测量的，所以每个数据都有一个林段号。
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_data')
DROP TABLE rubsys_tbl_data
GO
CREATE TABLE rubsys_tbl_data --数据记录表,存放所有关键数据
(
id int PRIMARY KEY, --索引id
linduan smallint NOT NULL, --林段id
t smalldatetime NOT NULL, --测量时间
daima smallint NOT NULL, --属性代码Id
groupno int NOT NULL, --测量组号
val numeric(18,6) NOT NULL, --属性值
--林段约束
CONSTRAINT FK_linduan_data FOREIGN KEY (linduan) REFERENCES rubsys_tbl_linduan(id),
--属性代码约束
CONSTRAINT FK_shuxindaima_data FOREIGN KEY (daima) REFERENCES rubsys_tbl_shuxindaima(id),
--组号约束
CONSTRAINT FK_groupindex_data FOREIGN KEY (groupno) REFERENCES rubsys_tbl_groupindex(id),)/********************************************************************************
* 备注表，并不是每一个记录都有备注，因此提出来单独存储
*********************************************************************************/
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'rubsys_tbl_note')
DROP TABLE rubsys_tbl_note
GO
CREATE TABLE rubsys_tbl_note --数据记录备注表
(
id int PRIMARY KEY, --索引id
groupno int NOT NULL, --数据组号
note varchar(100) NOT NULL --备注
CONSTRAINT FK_groupindex_note FOREIGN KEY (groupno) REFERENCES rubsys_tbl_groupindex(id)
)
--需要显示的结果方式，以[生长数据]的[植被结构]视图为例，这里显示的是计算处理后数据。
/*----------------------------------------------------------------------------------------------
索引 林段名称 所属林队 所属农场 测量时间 叶面积指数 叶面积密度(m^2) 枝干生物量(Kg) 备注
1 6-1 1队 西联农场 2008-10-11 6.501 12.115 21.342 XXXX
2 7-3 3队 立才农场 2008-10-14 8.461 13.345 18.334 NULL
...
--索引号为1的数据是根据几十个原始数据计算得来。在程序中可以显示这种关系。原始数据与计算处理后的数据
--都是存在rubsys_tbl_data中的。根据groupno对应rubsys_tbl_groupindex中的groupidx来区分。
*/--我的查询方法，在程序会根据当前的视图生成类似的SQL代码：SELECT a.groupno AS 索引号,b.林段名称,b.林队名称,b.农场名称,CONVERT(varchar(10),a.t,120) AS 测量时间,a.val AS 叶面积指数,
c.val AS 叶面积密度,d.val AS 枝干生物量,(CASE e.note WHEN '' THEN NULL ELSE e.note END) AS 备注 FROM
(SELECT rubsys_tbl_linduan.id,rubsys_tbl_linduan.name AS 林段名称,rubsys_tbl_lindui.name AS 林队名称,rubsys_tbl_nongchang.name AS 农场名称 FROM rubsys_tbl_linduan
INNER JOIN rubsys_tbl_lindui ON rubsys_tbl_linduan.lindui = rubsys_tbl_lindui.id
INNER JOIN rubsys_tbl_nongchang ON rubsys_tbl_lindui.farm = rubsys_tbl_nongchang.id) AS b
LEFT JOIN (SELECT linduan,t,groupno,val FROM rubsys_tbl_data WHERE daima=1) AS a ON a.linduan=b.id
LEFT JOIN(SELECT groupno,val FROM rubsys_tbl_data WHERE daima=2) AS c ON a.groupno=c.groupno
LEFT JOIN(SELECT groupno,val FROM rubsys_tbl_data WHERE daima=3) AS d ON a.groupno=d.groupno
LEFT JOIN (SELECT groupno,note FROM rubsys_tbl_note) AS e ON a.groupno = e.groupno
JOIN (SELECT id FROM rubsys_tbl_groupindex WHERE groupidx = -1) AS f ON a.groupno = f.id ORDER BY a.groupno
--使用LEFT JOIN是因为后面可能为每个视图增加新的属性，不使用左连接那么原来的有部分数据就会查询不到。
当rubsys_tbl_data中记录不是很大时，运行没有什么问题。测试中rubsys_tbl_data中数据记录增加到130万个记录时，并且表中95%的数据都为[生长数据]的[植被结构]视图的数据时，查询该表就很慢了，查询并显示需要6-7秒的样子，返回的行数为40万左右(40万*3=120万)，较大。如果再加个WHERE条件只查询一两个记录，速度上倒还是可以。请问各位高手有什么好的优化方法？或者我的设计有没有重大的缺陷？尽管仍砖头，呵呵，初次设计数据库，没多少经验。真诚的感谢您的指点与帮助!
解决方案 »
免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货