Member Search by Name Very Slow in AS2005 compared to AS2000

[Steve G]

We have a dimension which contains 400,000 members at the lowest (key) level.
Each member has a numeric key and a name (long string - 70 chars long). We
have code which returns the keys of all members whose name matches a
particular string, as follows:

with member [Measures].[TheID] as '[Ini Cust Cls
Curr].CurrentMember.Properties("Key")'
SELECT Head(Filter([Ini Cust Cls Curr].[Ini Cust Cls Curr_Name].Members,
InStr(1,[Ini Cust Cls Curr].CurrentMember.Name,"bloggs") <> 0), 500)
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON Columns, {[Measures].[TheID] }
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON ROWS
FROM [fxdistribution]

.. That's an example which searches for a member with a name containing the
text 'bloggs'. It should be very easy to create a dimension against which to
demonstrate this problem.

I have run this code on AS2000 and AS2005 on the same server, against the
same dimension with the same number of members. It runs much faster on AS2000
than on AS2005 to the point where the search on AS2005 is unacceptably slow.
In AS2000 it takes 22 seconds, in AS2005 it takes 300 seconds - a factor of
about 9 times slower !

We'd like to enquire as to why the code is so slow on AS2005 i.e. Is it
normal to suffer this 900% slow-down when 'upgrading' (?!) to AS2005 ? Can
the MDX be 'sped-up' ? If it is not possible to speed this MDX up, then is
there another way to perform such a search (xmla?) ?

Steve G

[Jéjé]

does [Ini Cust Cls Curr].[Ini Cust Cls Curr_Name] is an attribute?
if yes, try to change
filter([Ini Cust Cls Curr].[Ini Cust Cls Curr_Name].members, ....
to
filter([Ini Cust Cls Curr].[Ini Cust Cls Curr_Name].[Ini Cust Cls
Curr_Name].members, ....

"Steve G" <Ste...@discussions.microsoft.com> wrote in message
news:FC07C114-EABD-401B...@microsoft.com...

[Steve G]

Well, I changed the syntax as you suggested but it made no difference to the
performance. I noticed that executing this MDX causes the server to fully
utilize 1 CPU for an entire 5 minutes. That seems way too slow because
searching 400,000 text strings is not a massive operation.

Steve G

[Steve G]

On the same subject I was wondering whether an XMLA DISCOVER query could be
used with a wild-card to do this search. I can't see how to do that. Anyone
tried it ?

[Jéjé]

have you try this formula:

with member [Measures].[TheID] as '[Ini Cust Cls

Curr].[Ini Cust Cls Curr_Name].CurrentMember.membervalue'
SELECT Head(Filter([Ini Cust Cls Curr].[Ini Cust Cls Curr_Name].[Ini Cust
Cls Curr_Name].Members,
InStr([Ini Cust Cls Curr].[Ini Cust Cls Curr_Name].CurrentMember.Properties
("
Member_Caption","bloggs") <> 0), 500)
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON Rows,

{[Measures].[TheID] } ON Columns
FROM [fxdistribution]

the rows & columns are been switched
the membervalue is used instead of the "Propreties("key")" formula
the search is in the member_caption and not the name.
the properties are retrieved only for the rows, not the columns.

do you scan a parent-child dimension?

"Steve G" <Ste...@discussions.microsoft.com> wrote in message
news:FC07C114-EABD-401B...@microsoft.com...

[Akshai Mirchandani [MS]]

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
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Steve G" <Ste...@discussions.microsoft.com> wrote in message

news:571734AA-0A62-4CB6...@microsoft.com...