ASSP.StringFilters and MDX.SubSet combine optimization

Topics: Developer Forum, Project Management Forum
Nov 26, 2012 at 10:32 PM
Edited Nov 28, 2012 at 8:55 PM

Hello!

I have dimension with millions of items, and need to have quick search mechanism.

I use queries like this (to fill first page with top 100 results):

 

select
{ } on columns,
SubSet (
	ASSP.Like (
		 [Accounts].[Account].Members
		,"%SearchExpression%"
		,[Accounts].[Account].CurrentMember.Name
		,true
	)
	,0,100
) dimension properties member_unique_name
on rows
from [MyCubeName]

 

The query for the first page and queries for other pages elapsed for the same time.

I have a suggestion to add 2 optional paremeters to StringFilters functions to avoid SubSet wrapping:

1. Start - the position of the first tuple to be returned.

2. Count - the number of tuples to be returned.

It will reduce the execution time for first pages.

Can I wait such a release?

Or should I do it myself in codeplex? If so, please, tell me what to do to became a developer of this solution?

Or should I do it myself with the help of redgate reflector?

Thank you!

Coordinator
Nov 26, 2012 at 11:16 PM

This is an open source project. You can download the full source from the Source Code tab.

If you want to submit your change back to the project you can do this via the Upload Patch button on the Source Code tab.

Nov 26, 2012 at 11:29 PM
Edited Nov 26, 2012 at 11:29 PM

Thank you!

I already did it... and msmgdsrv.dll reference missing.

I investigate the code to change - it's very simple. A more intensive optimization can be done.

SQL Server 2008 R2 x64 installed, VS 2012, I opened ASStoredProcs2008 solution.

I find this file localy in 3 different locations:

C:\Program File (x86)\Microsoft Analysis Services\AS OLEDB\10\ (8,26 Mb)

C:\Program File\Microsoft Analysis Services\AS OLEDB\10\ (12,0 Mb)

C:\Program File\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\ (12,0 Mb)

Start testing from the last one...



Nov 26, 2012 at 11:51 PM

The first one ok.

Coordinator
Nov 27, 2012 at 1:35 AM

That assembly reference is machine dependant, so it can break when you open the project on a new machine. It's just a case of pointing it to the desired file.

You should not have any issues at runtime as that assembly will be pre-loaded by SSAS, this is a Visual Studio only issue.

Nov 27, 2012 at 1:44 AM
Edited Nov 27, 2012 at 2:51 AM

Thank you a lot!

I've just written the enhancements (solution rebuilding is ok), I'm about to begin testing of it...

I also replaced regex-based search in like-methods by custom parsing algorithm - very interesting is there will be any perfomance impact...

Nov 27, 2012 at 2:50 AM
Edited Nov 27, 2012 at 2:56 AM

First results: in general, on my data, native "Like" realization minimum 2 times faster (in some tests it was 5 times), then RegEx one. And it doesn't use cache (opposite RegEx one) at all. The RegEx cache locks some memory on each pattern instance until MSAS restarded (GC can't help, as each instance compiled).