SQL查询性能的简单结合。

I have a simple query tuning question, it is

我有一个简单的查询调优问题

can We improve the performance of a view which have definition as

我们可以改进具有as定义的视图的性能吗

SELECT * FROM A UNION ALL SELECT * FROM B

从一个联合中选择*，所有从B中选择*

and this is performing so poorly that it is taking 12 seconds for 6.5k Records

它的表现非常糟糕，6。5千米的记录需要12秒

Any help is appreciated.

任何帮助都是感激。

3 个解决方案

#1

The problem with a union view like that is that the original columns of the tables are not accessible to the optimizer via the view, so when you use the view like this:

这样的union视图的问题是，优化器无法通过视图访问表的原始列，所以当您使用这样的视图时:

select * from myview where foo = 5

the where clause is a filter on the entire rowset delivered by the view, so all the rows from both tables are processed after the union, so no indexes will be used.

where子句是视图传递的整个行集中的一个过滤器，因此两个表中的所有行都在union之后进行处理，因此不会使用索引。

To have any hope at performance, you have to somehow get the condition you want inside the view and applied to each table, but keep it variable so you can apply different criteria when you use it.

要对性能有任何希望，您必须以某种方式在视图中获得您想要的条件并应用于每个表，但是要保持它的变量，以便在使用它时可以应用不同的条件。

I found a work-around that does this. It's a little bit hacky, and doesn't work for concurrent use, but it works! Try this:

我找到了一个解决方法。这有点陈腐，不适合并发使用，但它确实有效!试试这个:

 create table myview_criteria(val int);
 insert into myview_criteria values (0); -- it should have exactly one row

 create view myview as
 SELECT * FROM A
 WHERE foo = (select val from myview_criteria)
 UNION ALL
 SELECT * FROM B
 WHERE foo = (select val from myview_criteria);

then to use it:

然后使用它:

update myview_criteria set val = 5;
select * from myview;

Assuming there's an index on foo, that index will be used.

假设有一个关于foo的索引，该索引将被使用。

Caution here, because obviously this technique won't work when multiple executions are done concurrently.

这里要注意，因为当同时执行多个执行时，这种技术显然不能工作。

#2

is there a reason u keep these two not in the same table, no matter what you do, eventually it will be horrible. If possible consider a migration to one table. If not, you can always "materialize a view" by inserting them all into the same thing.

你为什么不把这两件事放在一张桌子上，不管你做什么，最终都是很可怕的。如果可能，考虑迁移到一个表。如果不是，您可以通过将所有视图插入到相同的内容中来“具体化视图”。

with that being said, you are not going to be selecting * on a union, if there are specific conditions on top of the view, performance can be improve by the indexing that column.

有了这样的说法，您就不会在一个联合中选择*，如果视图的顶部有特定的条件，那么可以通过索引该列来提高性能。

It would help if you tell everyone what specific db this is for.

如果你告诉每个人这是为了什么目的，这将会有所帮助。

#3

My best advice is to limit the number or records that are returned from the source tables to only what you really need and making sure your data is properly indexed.

我最好的建议是将源表返回的数量或记录限制为您真正需要的，并确保您的数据被正确索引。

Don't use * unless its an ad-hoc script (even then it would still be better to list the columns) include only the columns that you really need for the task at hand.

不要使用*，除非它是一个特别的脚本(即便如此，列出这些列也更好)只包含您手头任务真正需要的列。

Use proper indexes based on the most frequently used queries. Remove any unused indexes. Remove any duplicate indexes.

根据最常用的查询使用适当的索引。删除任何未使用索引。删除任何重复的索引。

Pluralsight has a wonderful course on MS SQL Server query performance tuning. http://www.pluralsight.com/training/Courses/TableOfContents/query-tuning-introduction

Pluralsight提供了关于MS SQL Server查询性能调优的精彩课程。http://www.pluralsight.com/training/Courses/TableOfContents/query-tuning-introduction

The course is presented by Vinod Kumar and Pinal Dave who are two very brilliant individuals.

本课程由维诺德·库马尔和皮纳尔·戴夫讲授他们是两位非常杰出的人。

You could also check out Pinal's article on SQL Server Performance to help point out areas that you may not have thought about.

您还可以阅读Pinal关于SQL Server性能的文章，以帮助指出您可能没有考虑过的领域。

http://blog.sqlauthority.com/sql-server-performance-tuning/

#1