MDX performance compared to T-SQL

Question

I have a database that contains tables with over 600 million records and a set of stored procedures that do complex searches in the database. The performance of stored procedures is so slow, even with suitable indexes in tables. Database design is a normal db relational design. I want to change the database design to be multi-dimensional and use MDX queries instead of traditional T-SQL queries, but the question is: Is the MDX query better than the traditional T-SQL query in terms of performance? and if so, to what extent will this improve query performance?

Thanks for any help.

Answer 1

Apples and Oranges: OLAP Cube Analysis Services is a fundamentally different type of storage than the SQL Server database, and they are designed for different purposes. Technically, MDX is not "faster" than T-SQL, or vice versa - it's just languages, but designed for different needs.

Having said that, a cube is usually best suited for numerical analysis of static data, such as aggregating a large number of sales / transactions / any records over time. In contrast, a traditional relational database usually works very well if the schema and indexes are well constructed for search. An easy way to judge: if your SQL queries should do a lot

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

then a cube can help (it is intended for aggregate mathematical functions - sum () and group by). OTOH if your requests do a lot

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

then the cube probably won’t help, and I would instead focus on setting up the schema, queries, and indexing, and possibly paging, if the data can be appropriately divided.

Do you have a clustered index and spanning non-clustered indexes matching queries?

Answer 2

MS SSAS OLAP cube can be used in several storage modes:

• Relational (OLAP) - data and metadata remain in your database and several materialized representations are added. It may or may not be faster.

• Hybrid (HOLAP) - metadata and (pre-computed) aggregations are stored on a new server running an instance of SSAS. This should speed up all queries using aggregations, such as “total hours of work over the past year by month,” but queries that can go through certain records can be as before.

• Multidimensional OLAP (MOLAP), where all your data, as well as metadata and aggregations are copied to the SSAS server. This is usually the fastest, but duplicate repository.

Before you begin, you should consider optimizing the layout of the tables for reports and analytics, in other words, use the data warehouse (DW) - put your data in the Kimball stars table and fact tables. Then you periodically download the DW using ETL (SSIS) and point your reporting and analytics to the DW. You may not need to use SSAS at all - SQL queries executed against star table layouts are usually significantly faster than for a normal DB database. If it is still too slow, create SSAS cubes on top of the DW. After you start downloading DW, you can delete entries from your operating base, which will make it faster for daily use.

To summarize, my thumb rule is:
1. Create a DW and configure the ETL process
2. Try T-SQL versus DW reports; this can be quite good.
3. If still slow, create SSAS cubes (on top of DW) in HOLAP mode and use MDX to query them.

Answer 3

"Stored procedure performance is so slow, even with suitable indexes."

I would be surprised if the stored procedure is a real problem, perhaps the way the procedures are used is slow, but the stored procedure by definition does not slow down. Did you find out that your procedures are slow? Did you profile them? I would have looked at this route for a very long time before redesigning my database. Are multidimensional databases for OLAP - is it your database, is it strictly an OLAP database, or is it a hybrid of OLAP and OLTP? Perhaps you need to de-normalize and replicate the data in your OLTP project to a de-normalize d structure? 600 million records in the table are by no means huge, this is not small, but it does not lead me to the idea that dumping stored procedures will magically speed up work. Profile your saved processes and see where the performance bottlenecks are before moving to a larger project to fix the problem.

Answer 4

Did you consider PowerPivot (Excel add-on)? It uses vertical compression to compress data by about 95% locally, so you can analyze your heart content.

http://technet.microsoft.com/en-us/library/ee210692.aspx