SQL服务器中的位置和视图的查询执行顺序 [英] Order of query execution on where and view in SQL server

问题描述

大家好，



我有一个复杂的视图，它有6个表内连接和最大功能。



在6个表中，只有一个表有1亿个普通数据（没有LOB）。



我正在运行一个查询，就像

Hi All,

I have a complex view which is having 6 tables inner joins and max function on it.

Out of 6 tables,only one table is having 100 millions of plain data (no LOBs).

I am running one query which is like

Select * 
  from ComplexView 
  where id in (Select id from AnotherView where dt between 'somedate' to 'someotherdate')

我验证了ComplexView中使用的所有基表的索引和统计数据，并且所有基表的平均碎片都小于10％。



当我运行此查询时，需要超过2小时。但是，如果我重建索引并更新统计数据，

它会在10秒内给出结果。一段时间后（在产品中）它再次花费2小时。



ComplexView：



从tab1 .... tab6（所有r内部联接）中选择id，max（case）



group by id





通过查看执行计划，我很困惑是先执行条件还是查看逻辑。



In一般情况下，首先考虑Where子句或首先查看执行情况？



任何人都可以建议可能导致性能问题的原因。没有改变视图而没有每2小时重建一次索引。??



提前谢谢



我有什么尝试过：



出于测试目的，我只需要工具

I verified indexes and stats for all the base tables which is used in ComplexView and all of them are less than 10% avg fragmentation.

When I run this query, taking more than 2hrs. But if I rebuild the indexes and update the stats,
its giving the result in 10 secs. Some how,after some time (in prod) its again taking to 2 hrs.

ComplexView:

Select id,max(case )
from tab1....tab6 (all r inner joins)
group by id


By seeing the execution plan,am getting confused whether first its executing where condition or view logic.

In Generally,Where clause will considered first or view execution first?

Can any one please suggest what might be causing the perf prob..With out altering the view and with out rebuilding the indexes for every 2hrs.??

Thanks in advance

What I have tried:

For the testing purpose,I just tool the result set of

Select id from AnotherView where dt between 'somedate' to 'someotherdate'

导入临时表并运行以下查询

into temp table and run the below query

Select * from ComplexView where id in (select id from #tmp) 

这几乎运行了30秒。

this was running almost 30 Sec.

推荐答案

你说重建之后需要的索引和统计数据一段时间返回查询的2小时执行。有时多久了？小时，天，周？



听起来你需要的是为你的DB创建一个维护计划。这包括索引重建等。如果这是一个包含大量写入/更新/删除的事务表，则会有一个超过100万行的表。索引可以快速分段。



在阅读您的评论后，但不知道该系统的用途，您可能需要考虑建立一个报告/数据仓库系统。这可以帮助减轻您在交易系统上完成的工作。

You say after rebuild the indexes and stats it takes "some time" to return to the 2 hour execution for your query. How long is sometime? Hours, days, weeks?

It sounds like what you need is to create a maintenance plan for you DB. This includes index rebuilding, etc. With a table of 100+ million row if this is a transaction table with lots of write/update/deletes the indexes can get fragmented quickly.

After reading your comment, but without knowing what this system is being used for you might want to consider sitting up a reporting/data warehouse system. This could help off load the work being done on your transaction system.

这篇关于SQL服务器中的位置和视图的查询执行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！