Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> SQL Server >> SQL Server Analysis Services

MDX "Filter" function Performance?

There is about 3,000,000 records in the cube. And the members amount of [DIM FACT].[GOODS DESC] is almost 3 Million.

I use the MDX as following:

SELECT
[Measures].[Price] ON 0,
FILTER(NONEMPTY([DIM FACT].[GOODS DESC].MEMBERS),
iif( InStr(1,[DIM FACT].[GOODS DESC].CurrentMember.NAME,"milk") <> 0, [DIM FACT].[GOODS DESC].currentmember,null)) on 1

FROM TestCube

It costs about10 Minutes. Is it too slow? or it's normal?

thanks,

[590 byte] By [ivanw] at [2007-12-22]

«« Details regarding UndoEngine Class in .NET
»» Power consumption class?

# 1

try to replace

InStr(1,[DIM FACT].[GOODS DESC].CurrentMember.NAME,"milk") <> 0

with

[DIM FACT].[GOODS DESC].CurrentMember.NAME >= "milk"

YuriAbele at 2007-8-30 >

top of Msdn Tech,SQL Server,SQL Server Analysis Services...

# 2

thanks Yuri,

but I must list all members that including string 'milk'. (it means like '%milk%' ).

ivanw at 2007-8-30 >

# 3

The iif() logic looks suspect - presumably, you just want to test the current member name; but if it includes "milk", the value of the current measure will then be compared to 0. You could try this:

SELECT
[Measures].[Price] ON 0,
FILTER(NONEMPTY([DIM FACT].[GOODS DESC].MEMBERS),
InStr(1,[DIM FACT].[GOODS DESC].CurrentMember.NAME,"milk")) on 1

FROM TestCube
>>

DeepakPuri at 2007-8-30 >

# 4

thank Deepak,

The iif() is really redundant. I try this MDX to query my cube.

But the response time still costs 10 minutes.

So it seems like iif() is not the critical problem.

Any other idea?

ivanw at 2007-8-30 >

# 5

Only thing that I can think of is to take a different view of the problem - is there a certain set of keywords like "milk", which is established beforehand, or is this a completely ad-hoc query? In the latter case, you may be better off using the text search capabilities of relational SQL Server

DeepakPuri at 2007-8-30 >

# 6

Deepak Puri wrote:
Only thing that I can think of is to take a different view of the problem - is there a certain set of keywords like "milk", which is established beforehand, or is this a completely ad-hoc query? In the latter case, you may be better off using the text search capabilities of relational SQL Server

Actually, it's a ad-hoc query.

By this case, It means MDX Filter is not suit for search lot of members?

ivanw at 2007-8-30 >

# 7

This recent thread from the public SQL Server OLAP Newgroup provides some insight into the issue:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/7216a54031fa6fd2

microsoft.public.sqlserver.olap > Member Search by Name Very Slow in AS2005 compared to AS2000

From: Akshai Mirchandani [MS] - view profile
Date: Fri, Jul 28 2006 4:24 pm
Email: "Akshai Mirchandani [MS]" <aksh...@online.microsoft.com>
Groups: microsoft.public.sqlserver.olap

Rating:
s

Unfortunately I guess the issue here is the InStr function which is
expensive because it is implemented in a VBA managed assembly and causes a
lot of interop between native and managed code...

You could try to write a custom COM UDF assembly that essentially implements
InStr and call that instead and see if it helps.

And in case you are wondering, the reason the managed VBA assembly is used
is because the standard COM VBA DLL is not available on 64-bit platforms.

Thanks,
Akshai

DeepakPuri at 2007-8-30 >

# 8

thanks very much.

ivanw at 2007-8-30 >

SQL Server

SQL Server Analysis Services

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech