请能帮小弟我优化一下数据库，查询的时候太慢了

请能帮我优化一下数据库，查询的时候太慢了

查询的时候使用的是视图
如下：

SQL code

SELECT a.*, b.ci_name AS leixingname, c.ci_name AS yuyanname,       d.ci_name AS pingtainame, e.ci_name AS huanjingname, f.ui_nameFROM dbo.xj_TeachresInfo a LEFT OUTER JOIN      dbo.xj_CanshuInfo b ON a.ti_leixing = b.id LEFT OUTER JOIN      dbo.xj_CanshuInfo c ON a.ti_yuyan = c.id LEFT OUTER JOIN      dbo.xj_CanshuInfo d ON a.ti_pingtai = d.id LEFT OUTER JOIN      dbo.xj_CanshuInfo e ON a.ti_huanjing = e.id LEFT OUTER JOIN      dbo.xj_UserInfo f ON a.ti_ui_id = f.id

用到的两张数据库表如下

SQL code

CREATE TABLE [xj_TeachresInfo] (    [id] [bigint] IDENTITY (1, 1) NOT NULL ,    [teachres_info] [bit] NULL ,    [ti_biaoti] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_fubiaoti] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_key] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_file] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_filetype] [nvarchar] (7) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_memo] [text] COLLATE Chinese_PRC_CI_AS NULL ,    [ti_banquan] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_laiyuan] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_zuozhe] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_timea] [smalldatetime] NULL ,    [ti_time] [datetime] NULL ,    [ti_ui_id] [int] NULL ,    [ti_lanmu] [int] NULL ,    [ti_leixing] [int] NULL ,    [ti_yuyan] [int] NULL ,    [ti_pingtai] [int] NULL ,    [ti_huanjing] [int] NULL ,    [ti_path0] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_path1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_path2] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_xianshi] [bit] NULL ,    [ti_shenhe] [bit] NULL ,    [ti_bianji] [int] NULL ,    [ti_cs_dianji] [bigint] NULL ,    [ti_cs_xiazai] [bigint] NULL ,    [ti_byte] [bigint] NULL ,    [ti_pingjia] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,    [ti_pingjiatimes] [int] NOT NULL CONSTRAINT [DF_teachres_info_v_ti_pingjiatimes] DEFAULT (0),    [ti_uid] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TABLE [xj_CanshuInfo] (    [id] [int] NOT NULL ,    [canshu_info] [bit] NULL CONSTRAINT [DF_canshu_info_canshu_info] DEFAULT (1),    [ci_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,    [ci_cl_id] [int] NULL CONSTRAINT [DF_canshu_info_ci_cl_id] DEFAULT (0),    [ci_next_ids] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_canshu_info_ci_next_id] DEFAULT ('0'),    [ci_path] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_canshu_info_ci_path] DEFAULT ('0'),    [ci_memo] [text] COLLATE Chinese_PRC_CI_AS NULL ,    CONSTRAINT [PK_canshu_info] PRIMARY KEY  CLUSTERED     (        [id]    )  ON [PRIMARY] ,    CONSTRAINT [FK_canshu_info_canshu_list] FOREIGN KEY     (        [ci_cl_id]    ) REFERENCES [xj_CanshuList] (        [id]    ) ON DELETE CASCADE  ON UPDATE CASCADE ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO

请能帮我优化一下数据库，查询的时候太慢了，谢谢了

[解决办法]
只有视图代码，具体怎么查询？

根据查询的字段建立索引。

[解决办法]
dbo.xj_TeachresInfo a LEFT OUTER JOIN
dbo.xj_CanshuInfo b ON 

这两表之间还应该建立个组合表，
比如
TA
IDA .... ID1 ID2 ID3
A 1 2 3

TB
ID NAME
1 A
2 B
3 C

组合表建立TC
IDA ID
A 1
A 2
A 3 

TA就不需要再存储ID1 这些列，而且以后方便ID4，ID5的扩展

查看更多 下一篇