我有这个问题:
Select '' as name, ' ' as pid, ' ' as type union all Select distinct instructor.name as name, instructor.Pid as pid, instructor_type as type From sisinfo.dbo.SISCRSI instructor inner join section_info as section on section.sctn_id_code = instructor.sctn_id_code Where section.sctn_term_code in (@Terms) and section.subj_code in (@Subject) order by name
当我在SSMS中运行它时,它在非常接近零的时间内完成.当我为它提供一些值时,它也在SSRS查询设计器中快速运行.但是当我预览报表时,查询至少需要两分钟才能运行.我不确定这里发生了什么,我以前从来没有遇到SSRS这样的问题.
正如评论中所讨论的那样,让我们摆脱参数,看看你的查询是否受到参数嗅探的影响.
为此,我们从头开始构建SQL语句.SSRS中的大多数内容都可以是包含SQL查询的表达式,因此您可以将其构建为字符串.使用参数,我们将使用它们将它们转换为逗号分隔列表JOIN
.
因此,对于您的SQL语句,请进入"数据集属性"对话框(而不是查询编辑器),然后按fx
表达式编辑器按钮将查询表达式编辑为文本,并使其成为表达式,如下所示:
="Select '<ALL>' as name, '<ALL>' as pid, '<ALL>' as type " &"union all " &"Select distinct instructor.name as name, instructor.Pid as pid, instructor_type as type " &"From sisinfo.dbo.SISCRSI instructor " &"inner join section_info as section on section.sctn_id_code = instructor.sctn_id_code " &"Where section.sctn_term_code in (" & Join(Parameters!Terms.Value, ",") & ") " &"and section.subj_code in (" & Join(Parameters!Subject.Value, ",") & ") " &"order by name "
我们在这里做的是将SQL表达式转换为一个字符串,我们将多值参数作为字符串而不是参数提供,从而消除参数嗅探作为查询运行缓慢的原因.
如果你的查询在此之后很慢,你知道这不是参数嗅探问题,但至少你会将其视为原因.