MDX性能与T-SQL [英] MDX performance vs. T-SQL

问题描述

我有一个数据库，其中包含具有6亿多条记录的表以及一组对数据库进行复杂搜索操作的存储过程. 即使表上有合适的索引，存储过程的性能也是如此缓慢. 数据库的设计是常规的关系数据库设计. 我想将数据库设计更改为多维数据库，并使用MDX查询而不是传统的T-SQL查询，但问题是: 在性能方面，MDX查询是否比传统的T-SQL查询更好? 如果可以，在何种程度上可以提高查询的性能?

I have a database containing tables with more than 600 million records and a set of stored procedures that make complex search operations on the database. The performance of the stored procedures is so slow even with suitable indexes on the tables. The design of the database is a normal relational db design. I want to change the database design to be multidimensional and use the MDX queries instead of the traditional T-SQL queries but the question is: Is the MDX query better than the traditional T-SQL query with regard to performance? and if yes, to what extent will that improve the performance of the queries?

感谢您的帮助.

推荐答案

苹果和桔子:分析服务OLAP多维数据集与SQL Server数据库在根本上是一种不同的存储类型，并且它们旨在执行不同的操作.从技术上讲，MDX并不比T-SQL更快"，反之亦然-它们只是语言，而是为不同的需求而设计的.

Apples and oranges: An analysis services OLAP cube is a fundamentally different type of storage than a SQL Server database, and they are designed to do different things. Technically MDX is not "faster" than T-SQL, or vice versa -- they are just languages, but designed for different needs.

话虽如此，多维数据集通常是对静态数据进行数字分析的最佳方法，例如对一段时间内的大量销售/交易/任何记录进行汇总.相反，如果架构和索引构建良好，则传统的关系数据库通常可以很好地进行搜索.一种简单的判断方法:您的SQL查询是否必须做很多

Having said that, a cube is usually what works best for doing numeric analysis of static data, such as aggregating large numbers of sales/transactions/whatever records over time. In contrast, a traditional relational database generally works just fine, if the schema and indexes are well constructed, for search. A simple way to judge: if your SQL queries have to do a lot of

select grock, sum/min/max/avg( foo ) 
from bar 
group by grock -- Ideal Analysis Services problem

然后，一个多维数据集可能会有所帮助(它是为汇总数学函数而设计的-sum()和group by). OTOH，如果您的查询做了很多

then a cube may help (it's designed for aggregate math functions - sum() and group by). OTOH if your queries do a lot of

select cols 
from foo 
where <complicated search> -- Not so much

然后，多维数据集可能无济于事，而我将重点放在调整架构，查询和索引以及表分区(如果可以对数据进行适当的分区)上.

then a cube probably will not help, and I would focus instead on tuning the schema, the queries and indexing, and perhaps table partitioning if the data can be suitably partitioned.

您是否具有聚集索引并且涵盖了与查询匹配的非聚集索引?

Do you have a clustered index and covering non-clustered indexes that match the queries?

这篇关于MDX性能与T-SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！