一个数据库设计问题

试题(id,CONTENT,xxxx)
关系表(试题ID,属性ID)
属性表(id,属性名,排序规则)--排序规则你可以指定，比如创建日期的为1，星级为2等SELECT *
FROM 试题 a INNER JOIN 关系表 b ON a.id=b.试题ID
INNER JOIN 属性表 c ON b.属性ID=c.id
ORDER BY c.排序规则

视图实际上还是sql语句，慢有慢的原因，你贴出执行计划来看看，ctrl+m，然后执行你的语句，下面就会有一个图

b_examination 如果不能再加where条件的话，建个索引吧，把你的语句贴出来看看

我用的是视图视图语句如下
SELECT     dbo.B_Course.CourseName, dbo.B_Examination.ExaminationID, dbo.B_Examination.CourseID, dbo.B_Examination.ExaminationType,
                      dbo.B_Examination.ExaminationName, dbo.B_Examination.ExaminationContent, dbo.B_Examination.ExaminationResult,
                      dbo.B_Examination.ExaminationRe, dbo.B_Examination.IsDisable, dbo.B_Examination.ContentItem, dbo.B_Examination.ExaminationSource,
                      dbo.B_Examination.IsCheck, dbo.B_Examination.Difficulty, dbo.B_Examination.AnswerTime, dbo.B_Examination.CreateTime,
                      dbo.B_Examination.TeacherID, dbo.B_Examination.LastOpertaor, dbo.B_Examination.IdentID, dbo.B_Examination.UserCount,
                      dbo.B_Examination.UpdateTime, dbo.B_Examination.IncludeLabel, dbo.B_Examination.AverageStarLevel
FROM         dbo.B_Course INNER JOIN
                      dbo.B_Examination ON dbo.B_Course.CourseID = dbo.B_Examination.CourseID

这样会不会好点？不过我觉得还是要加where条件为好SELECT  dbo.B_Course.CourseName ,
        dbo.B_Examination.ExaminationID ,
        dbo.B_Examination.CourseID ,
        dbo.B_Examination.ExaminationType ,
        dbo.B_Examination.ExaminationName ,
        dbo.B_Examination.ExaminationContent ,
        dbo.B_Examination.ExaminationResult ,
        dbo.B_Examination.ExaminationRe ,
        dbo.B_Examination.IsDisable ,
        dbo.B_Examination.ContentItem ,
        dbo.B_Examination.ExaminationSource ,
        dbo.B_Examination.IsCheck ,
        dbo.B_Examination.Difficulty ,
        dbo.B_Examination.AnswerTime ,
        dbo.B_Examination.CreateTime ,
        dbo.B_Examination.TeacherID ,
        dbo.B_Examination.LastOpertaor ,
        dbo.B_Examination.IdentID ,
        dbo.B_Examination.UserCount ,
        dbo.B_Examination.UpdateTime ,
        dbo.B_Examination.IncludeLabel ,
        dbo.B_Examination.AverageStarLevel
FROM    dbo.B_Course
        INNER HASH JOIN dbo.B_Examination ON dbo.B_Course.CourseID = dbo.B_Examination.CourseID

可以啊，建议加上where条件，不然你这是全表扫描，性能好不了哪里去

SELECT top 1000  dbo.B_Course.CourseName ,
        dbo.B_Examination.ExaminationID ,
        dbo.B_Examination.CourseID ,
        dbo.B_Examination.ExaminationType ,
        dbo.B_Examination.ExaminationName ,
        dbo.B_Examination.ExaminationContent ,
        dbo.B_Examination.ExaminationResult ,
        dbo.B_Examination.ExaminationRe ,
        dbo.B_Examination.IsDisable ,
        dbo.B_Examination.ContentItem ,
        dbo.B_Examination.ExaminationSource ,
        dbo.B_Examination.IsCheck ,
        dbo.B_Examination.Difficulty ,
        dbo.B_Examination.AnswerTime ,
        dbo.B_Examination.CreateTime ,
        dbo.B_Examination.TeacherID ,
        dbo.B_Examination.LastOpertaor ,
        dbo.B_Examination.IdentID ,
        dbo.B_Examination.UserCount ,
        dbo.B_Examination.UpdateTime ,
        dbo.B_Examination.IncludeLabel ,
        dbo.B_Examination.AverageStarLevel
FROM    dbo.B_Course
        INNER HASH JOIN dbo.B_Examination ON dbo.B_Course.CourseID = dbo.B_Examination.CourseID
        where B_Examination.CourseID=5条件不能这样加吗加上会爆一个错误
由于此查询中定义了提示，查询处理器未能生成查询计划。请重新提交查询，并且不要在查询中指定任何提示，也不要使用 SET FORCEPLAN。

先执行第一条建索引的语句，然后执行下面的SELECT语句，如果还慢，再贴执行计划给我看看CREATE CLUSTERED INDEX IX_B_Examination ON B_Examination(CourseID)SELECT TOP 1000
        dbo.B_Course.CourseName ,
        dbo.B_Examination.ExaminationID ,
        dbo.B_Examination.CourseID ,
        dbo.B_Examination.ExaminationType ,
        dbo.B_Examination.ExaminationName ,
        dbo.B_Examination.ExaminationContent ,
        dbo.B_Examination.ExaminationResult ,
        dbo.B_Examination.ExaminationRe ,
        dbo.B_Examination.IsDisable ,
        dbo.B_Examination.ContentItem ,
        dbo.B_Examination.ExaminationSource ,
        dbo.B_Examination.IsCheck ,
        dbo.B_Examination.Difficulty ,
        dbo.B_Examination.AnswerTime ,
        dbo.B_Examination.CreateTime ,
        dbo.B_Examination.TeacherID ,
        dbo.B_Examination.LastOpertaor ,
        dbo.B_Examination.IdentID ,
        dbo.B_Examination.UserCount ,
        dbo.B_Examination.UpdateTime ,
        dbo.B_Examination.IncludeLabel ,
        dbo.B_Examination.AverageStarLevel
FROM    dbo.B_Course
        INNER  JOIN dbo.B_Examination ON dbo.B_Course.CourseID = dbo.B_Examination.CourseID
WHERE   B_Examination.CourseID = 5

调试易

一个数据库设计问题

解决方案 »