MDX: Query Performance ORDER using Val

One of my colleague working on a different project had an issue with performance of a query. The query looked something like this

Key Points: It has an ORDER BY clause, the member used for ordering converts the string value to Int using Val.

SELECT  {[Measures].[Answer Count]} on 0, NONEMPTY( [Location].[Region].[Region] * ORDER ( [Location].[Retailer Store Number].[Retailer Store Number], Val( [Location].[Retailer Store Number].CURRENTMEMBER. MEMBER_CAPTION ) ) ,[Measures].[Answer Count]) on 1 FROM  cdw  WHERE [Visit Start Date].[Last 1 Week].&[Current Week]

Limitations 

One does not have permission to change the structure of the cube as client has not given permission. Data type for store number is String. The store number stores integer values but yet the data type is String. Hence ORDER was @MDX

Initially I suggested the following

WITH SET OrderedStoreNum AS Order (     NonEmpty     (         [Location].[Retailer Store Number].[Retailer Store Number]         ,[Measures].[Answer Count]     )     ,Cint([Location].[Retailer Store Number].CurrentMember.Member_Caption)     ,BASC ) SELECT {     [Measures].[Answer Count] } ON 0 ,NonEmpty (     [Location].[Region].[Region] * OrderedStoreNum     ,[Measures].[Answer Count] ) ON 1 FROM [cdw];

Reasoning for above suggestion was as follow

It is better to do an "order by" on a set independent of the current context and then do    
cross join.

           Additionally filter out Nulls before doing order by and reduce the size of set

 Use CInt instead of Val directly and not let AS decided the base data type it needs to  
 convert to under the hood. (Val does not throw an error if value is a string value whereas 
 CInt will throw an error  Type Mismatch.)

 Resort to BDESC rather than DESC. BDESC does a simple sort and not a hierarchical sort

Additionally I landed up in this article:- .http://sqlblog.com/blogs/mosha/archive/2008/10/22/optimizing-mdx-aggregation-functions.aspx which speaks about not using the NonEmpty Clause within the aggregate function. But in our case this does not apply as the order by is not an aggregate function.

To substantiate this and dig more into it, found some interesting stuff. Hrvoje helped me in this as he had the required volume of dataset.

So here were the readings

The fact table contains around 3M rows. The dimension contains around 249,607 members: [Dim1].[Attr1].[Attr1]. Dim1].[Attr2].[Attr2] contains around 160 members. [Dim1].[Attr1] is an hierarchy that has 7 levels.

a:- this is the time (mm:ss) for query created using set

b:- this is the time (mm:ss) for query created using ORDER directly in the cross join.

Query 1: 

a) Time 07:51

b) Time 07:51 

WITH SET setL AS ORDER (       [Dim1].[Attr1].[Attr1]       ,Val([Dim1].[Attr1].CurrentMember.MEMBER_Key) -- Key is SK of int type       ,DESC ) SELECT       [Measures].[M1] ON 0,       NON EMPTY [Dim1].[Attr2].[Attr2] * setL ON 1 FROM       [Cube1]

Query 2: 

a) Time 03:57  

b) Time 03:58 

WITH SET setL AS ORDER (       NonEmpty([Dim1].[Attr1].[Attr1], [Measures].[M1])       , Val([Dim1].[Attr1].CurrentMember.MEMBER_Key)       ,DESC ) SELECT       [Measures].[M1] ON 0,       NON EMPTY [Dim1].[Attr2].[Attr2] * setL ON 1 FROM [Cube1]

Query 3:

a) Time 01:06

b) Time 01:04

WITH SET setL AS ORDER (       NonEmpty([Dim1].[Attr1].[Attr1], [Measures].[M1])       ,Int([Dim1].[Attr1].CurrentMember.MEMBER_Key)       ,DESC ) SELECT       [Measures].[M1] ON 0,       NON EMPTY [Dim1].[Attr2].[Attr2] * setL ON 1 FROM [Cube1]

Query 4:

a) Time 00:46

b) Time 00:39

WITH SET setL AS ORDER (       NonEmpty([Dim1].[Attr1].[Attr1], [Measures].[M1])       ,[Dim1].[Attr1].CurrentMember.MEMBER_Caption       ,DESC ) SELECT       [Measures].[M1] ON 0,       NON EMPTY [Dim1].[Attr2].[Attr2] * setL ON 1 FROM [Cube1]

Conclusion

1. Using Val is a big culprit. Int is better than Val. But changing the source data type is the best.

2. It is better to use NonEmpty within ORDER().

3. Though there is no considerable difference between the creating a set versus using ORDER() directly in cross join, i think it is better practice to do so.

4. The observation is when both the attributes are belonging to same dimension. I assume that the behavior would be same when we use attributes of different dimension in a cross join

5. I could not do much analysis on BDESC versus DESC.

Note: This query difference may not be substantial if the dimension and measure data is small.

Windows versus Sql collation

There was a question posted by my friend as to which collation to select for a column, whether the “SQL_Latin1_General_Cp1_CI_AS” or the “Latin1_General_CI_AS”.

My first reaction was oops….we normally use the defaults by the SQL server i.e. SQL_Latin1_General_Cp1_CI_AS mostly. The other option is SQL_Latin1_General_Cp1_CS_AS (Case Sensitive) where “ABS” and “abs” are two different values. But trying to find answer to the question I realized that it became a choice between SQL collation or the windows collation.

Here is some dig.

A “collation” specifies how strings are compared and sorted, and what character set is used for non-Unicode data. SQL Server supports two types of collations:

SQL collation: Example: “SQL_Latin1_General_Cp1_CI_AS”

Windows collation: Example: “Latin1_General_CI_AS”

For a Windows collation, a comparison of non-Unicode data is implemented by using the same algorithm as Unicode data. Both Unicode and non-Unicode sorting are compatible with string comparison rules in a particular version of Windows.

In a SQL collation, SQL Server defines different comparison semantics for non-Unicode data.

A SQL collation’s rules for sorting non-Unicode data are incompatible with any sort routine that is provided by the Microsoft Windows operating system; however, the sorting of Unicode data is compatible with a particular version of the Windows sorting rules. Because the comparison rules for non-Unicode and Unicode data are different, when you use a SQL collation you might see different results for comparisons of the same characters, depending on the underlying data type.

Lets do some hands on here.

Example: Create 2 tables namely tblSQLCol and tblWinCol

        CREATE TABLE tblSQLCol (a1 varchar(50), na1 nvarchar(50)) 
        //Here the first column is a non UNICODE data type and second column is a UNICODE data type

[Note: this will create the columns with SQL_Latin1_General_CP1_CI_AS.  This is because the Setup program does not set the instance default collation to the Windows collation Latin1_General_CI_AS if the computer is using the U.S. English locale. Instead, it sets the instance default collation to the SQL collation SQL_Latin1_General_Cp1_CI_AS. So since the server instance is set with SQL.. collation, when creating table, it takes the collation set at server level. Here CP1 means Code Page [1=1252 the default code page], CI means Case insensitive and AS meaning Accent Sensitive]

        CREATE TABLE tblWinCol (a1 varchar(50) COLLATE  Latin1_General_CI_AS,na1 nvarchar(50) COLLATE          
        Latin1_General_CI_AS) 
        //Here the first column is a non UNICODE data type and second column is a UNICODE data type

[Note here the columns will be set with the specified collation]

Insert the data into the table in sql collation

INSERT INTO tblSQLCol VALUES(‘a-c’,'a-c’)

INSERT INTO tblSQLCol VALUES(‘ab’,'ab’)

Now insert the data into the table with windows collation

INSERT INTO tblWinCol VALUES(‘a-c’,'a-c’)

INSERT INTO tblWinCol VALUES(‘ab’,'ab’)

Now lets see the difference and affects of SQL and windows collation

select a1,’sql’ from tblSQLCol order by 1 asc  //SORT ASCENDING on first column (non Unicode) retrieved using the SQL collation table

RESULT:

select na1,’sql’ from tblSQLCol order by 1 asc //SORT ASCENDING on first column (Unicode) retrieved using the SQL collation table

select a1,’latin’ from tblWinCol order by 1 asc //SORT ASCENDING on first column (non Unicode) retrieved using the Windows collation table

select na1,’latin’ from tblWinCol order by 1 asc //SORT ASCENDING on first column (Unicode) retrieved using the Windows collation table

NOTE: if you see the result, one can observe that for windows collation, in both cases (Unicode and non Unicode) same sorting and comparison logic is used. In SQL collation two different logics are used.

Now lets us see how any sorting algorithm happens at the .NET level.

Lets test the default sorting order

ArrayList arrlst = new ArrayList(2);

arrlst.Add(2);

arrlst.Add(1);

arrlst.Add(3);

arrlst.Sort();

RESULT: {1,2,3}

ArrayList arrlst = new ArrayList(2);

arrlst.Add(“a-c”);

arrlst.Add(“ab”);

arrlst.Sort();

RESULT: {“ab,”a-c”}

Now lets us test with the some code which we generally might do at .NET

SqlConnection conn = new SqlConnection(“server=SERVERNAME; user 
id=sa;password=sa;database=DBNAME”);

DataSet ds = new DataSet();

SqlDataAdapter adapter = new SqlDataAdapter();

adapter.SelectCommand = new SqlCommand(“select a1 from test”, conn);

adapter.Fill(ds, “test”);

DataRow[] rw = ds.Tables[0].Select(“”,”a1 ASC”);

foreach(DataRow r1 in rw){ MessageBox.Show(r1[0].ToString()); }

Same is the result with adapter.SelectCommand = newSqlCommand(“select na1 from test”, conn); and also with test2 as the table

RESULT: {“ab”,”a-c”}

This clearly shows that with SQL collation and non Unicode data type, at windows application layer different sort logic is applied and at the SQL layer a different logic is applied.

So probably to keep the sorting logic constant across both layers, one can have a Windows collation like the Latin1_General_CI_AS toSQL_Latin1_General_CP1_CI_AS

Regarding conversion of a collation of a column fromSQL_Latin1_General_CP1_CI_AS  to Latin1_General_CI_AS , The column of interest if changed should be checked if it is used in any join conditions. If the joining column is not changed then we will get an error

Example: select * from tblWinCol inner join tblSQLCol on tblWinCol.a1 = tblSQLCol.a1 will give error “Cannot resolve collation conflict for equal to operation.” Same should also be checked with any temp table joins. Not sure if any data losses will occur. Ideally it should not occur

This was done on SQL 2005 and was a nice learning that i thought should be shared.

mdx: query to override parent value with last child value

This query overrides the value of parent with the value of last child.

WITH   MEMBER LevelOrdinal AS        [Date].[Calendar].currentmember.level.ordinal MEMBER LevelName AS        [Date].[Calendar].currentmember.level.name        MEMBER [Measures].[ResellerOrderCountLastPeriod] AS        (               CLOSINGPERIOD(                      [Date].[Calendar].[Month],                      ANCESTOR(                            [Date].[Calendar].CURRENTMEMBER,0                      )               )               ,[Measures].[Reseller Order Count]        ), Back_Color =        case when [Date].[Calendar].CURRENTMEMBER.level.ordinal=1 then RGB(0,255,255)                when [Date].[Calendar].CURRENTMEMBER.level.ordinal=2 then RGB(125,255,255)                when [Date].[Calendar].CURRENTMEMBER.level.ordinal=3 then RGB(200,255,255)        else NULL        end               SELECT {        LevelOrdinal,        LevelName,        [Measures].[Reseller Order Count],        [Measures].[ResellerOrderCountLastPeriod] } ON 0, NONEMPTY (        EXCEPT(               DESCENDANTS([Date].[Calendar].[Calendar Year],,SELF_AND_AFTER),               [Date].[Calendar].[Date]        )        ,[Measures].[ResellerOrderCountLastPeriod] ) ON 1 FROM        [Adventure Works] --WHERE        --[Date].[Calendar Year].&[2001] cell properties formatted_value, back_color --I have added except to exclude the date level.

Result

ssis lookup transfomation memory/performance issues

Suddenly today the package stopped responding. As usual I quote "It worked in the morning and this time it was connected to different server". The issue soon became a high priority as the system went on to throw a Low virtual memory error. The system had to be restarted couple of times and this was a pain.

Investigation resulted in narrowing down the root cause to be one of the lookups in the package.

The first thing that flashed to my mind was "Why are we not using Cache?". So I decided to create a Cache File using Cache Transform in a separate package. Looking into the Execution Result showed that the SSIS printed the following message

"Information: The buffer manager detected that system was low in virtual memory, but was unable to swap out any buffers to relieve memory pressure. 8 buffers were considered and 8 were locked. Either not enough memory

is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked."

"[Cache Transform [28]] Information: The component "Cache Transform" (28) processed 1748563 rows in the cache. The processing time was 224.813 seconds. The cache used 14439633254 bytes of memory.

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

"

I began to search for information on the maximum buffer size and found some information here http://technet.microsoft.com/en-us/library/cc966529.aspx. By default the max rows for data flow task in 10000 rows and default buffer size is 10485760 bytes (~10 MB). This can be increased to 104857600. (~100 MB).  

The package behaved the same even after increasing the size. But this time the OLEDB source and the cache transform task within the data flow task did show green status but however the data flow task never got completed. The Execution result flushed out the following message this time (Infact both the time but with some additional messages this time).

"[Cache Transform [28]] Information: The component "Cache Transform" (28) processed 1748563 rows in the cache. The processing time was 224.813 seconds. The cache used 14439633254 bytes of memory.

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

"

So began to think why a simple query returning 2 million rows result in 14 GB of data. At this point I thought it is worth looking @ the query and other data type settings done by SSIS on the query. So here was the query

SELECT Number=REPLACE(REPLACE(LTRIM(REPLACE(rtrim(Number), '0', ' ')), ' ', '0'),'-',''), Cusip= ltrim(rtrim(cusip)),SponsorId, AccountId FROM Account

The data type for the number and cusip column in the database was set as VARCHAR(20) and Id's as Int. Then I checked how the SSIS is setting the data length in the output and inputs properties (Click Advanced Properties) for OLEDB source.  To my horror found that the data length set was STRING 8000. So the SSIS was not able to decide the proper length when REPLACE or any other string manipulation function was used within the SQL query. So I changed the query to use CAST.

SELECT Number=CAST(REPLACE(REPLACE(LTRIM(REPLACE(rtrim(Number), '0', ' ')), ' ', '0'),'-','') as VARCHAR(20)), Cusip= CAST(ltrim(rtrim(cusip)) AS VARCHAR(20)),SponsorId, AccountId FROM Account

The SSIS this time set the data length as STRING 20.  I ran the package again and it worked like a charm. The size of cache memory drastically reduced to 171359174 bytes (i.e. 160 MB). This was N times less than 14GB.  The package ran smoothly even without the idea of creating cache file.

"[Cache Transform [28]] Information: The component "Cache Transform" (28) processed 1748563 rows in the cache. The processing time was 0.531 seconds. The cache used 171359174 bytes of memory."

Wow. what a difference and what an impact.

We had two lookups using the same query and with the query change the package ran smoothly (Even without using the Cache). But just for curiosity, observed that each of these lookups used 160 MB of cache memory each (i.e. 320 MB) during execution.  Using the cache in the lookup made the package to use only 160 MB of cache memory as the package loaded the cache only once (the cache file itself was loaded separately in another package).

Post changes in query, the package ran even without having to increase the default buffer size (10MB) of data flow task. The lookup now took 160 MB of cache memory which was higher than the 100 MB max buffer size as mentioned in post which is an area of still for investigation.

Anyways for now, works great!

Summary:

·  Do have an eye on the query that you write and the data length set by SSIS on the columns of SSIS in lookups. Remember STRING is not same as VARCHAR in terms of memory allocation.

·  Do have an eye of the Max buffer size of Data Flow Task versus Total Cache memory consumed by data flow task

·  Do not get tricked by the amount of free space in the server. You still can run into the memory issue because of the limits set on individual tasks.

·  Save all the work before your try as sometime this experimentation results in system restart.