Category Archives: Performance

How we reduced ETL Build Time for our Data Warehouse in SQLServer by Using BCP and Java Based String Concatenation rather than SQLServer’s String Concat mechanism

We had an ETL Build step – that used to take 75-80% of the build time. This step heavily used SQLServer’s String concatenation method and often this used to take about 30-40 hours. It has been widely documented that SQLServer’s String Concatenation is extremely slow and we had also observed that during the time SQLServer does String Concatenation the CPU usage on the machine goes up and stays there for the entire duration.

The article below describes how – I speeded up the above step by getting rid of String concatenation entirely and using an alternate technique to speed up the ETL build times. After the steps below were implemented – the above ETL Step took about 1.5-2.0 hours. What an improvement.  Thanks to the crude raw speed of BCP ( bulk copy protocol ) from SQLServer and Java.

Let me lay the background of what we were trying to do before I describe the steps.

Assume we had a fact table that looked like below – Everything in Dimension1, Dimension2, Dimension3 is shown as a Foreign KeyID to the Dimensional tables.

Dimension1 Dimension2 Dimension3 Product-Market Hierarchy Time Measure1 Measure2
345 435 823 A Jan-2009 145 675
345 435 823 B Jan-2009 329 719
345 435 823 C Jan-2009 146 782
345 435 823 A Mar-2009 435 671
350 432 829 B Mar-2009 1232 5540
350 432 829 A Apr-2009 435 671
350 432 829 C May-2009 567 987
.
355 435 823 A Jan-2009 145 675
355 435 823 B Jan-2009 329 719
355 435 823 A Feb-2009 146 782
365 442 823 A Mar-2009 435 671
365 442 829 C Mar-2009 1232 5540
370 442 829 C Apr-2009 435 671
370 442 829 B Feb-2009 567 987
370 442 829 A Apr-2009 45 25

Table : 1

We needed to create an Aggregate Table as shown below for doing some reporting in our BI Analytic Tool.

Also in our domain – Dimension 1 had a cardinality of about 2 million.

Dim1 Dim2 Dim3 Measure1-Str Measure2-Str
345 435 823 A,Jan-2009,145; B,Jan-2009,329; C,Jan-2009,146; A,Mar-2009,435; A,Jan-2009,675; B,Jan-2009,719; C,Jan-2009,782; A,Mar-2009,671;
350 432 829 B,Mar-2009,1232; A,Apr-2009,435; C,May-2009,567; B,Mar-2009,5540; A,Apr-2009,671; C,May-2009,987;
355 435 823 A,Jan-2009,145; B,Jan-2009,329; A,Feb-2009,146; A,Jan-2009,675; B,Jan-2009,719; A,Feb-2009,782;
365 442 823 A,Mar-2009,435; C,Mar-2009,1232; A,Mar-2009,671; C,Mar-2009,5540;
370 442 829 C,Apr-2009,435; B,Feb-2009,567; A,Apr-2009,45; C,Apr-2009,671; B,Feb-2009,987; A,Apr-2009,25;

Table : 2

What I am going to discuss is – how I dramatically improved the building times of the Agg Table as shown in the above Table.

The above Aggregate table took about 10 hours to be build on a 235 million row fact table – when we used a SQLServer String concatenation approach of using cursors to loop through the rows. I had verified that the slowdown was due to the String Concatenation only and nothing else.

The solution I discuss below reduced the built time to about 1.5 hours.

Please remember that the Aggregate table build time was mainly because SQLServer was very very very very slow doing String Concatenation.

3 Step Process to get 10 time speed improvements in ETL Time to generate the Aggregate Table

1st Step – BCP – Bulk Copy Protocol  in SQLServer

I used the BCP program to get the data out from the SQLServer Tables to a flat file separated by Tabs.

I was at times amazed at the raw speed of BCP in SQLServer to get the data out from a very very large table sometimes almost the size of 250 Million row tables in less than 10-15 min to a flat file and each column value separated by a Tab.

For example I used the command line as shown below :

bcp “SELECT i.Dim1,i.Dim2, i.Dim3, i.Product-Market-Hierarchy, i.Time,  isNull(SUM(i.Measure1), 0) ,  isNull(SUM(i.Measure2), 0)  FROM FactTable i

GROUP BY i.Dim1,i.Dim2, i.Dim3, i.Product-Market-Hier, i.Time

ORDER BY  i.Dim1, i.Dim2, i.Dim3, i.Product-Market-Hier, i.Time”

QUERYOUT    tmp_FactTable_FROM_BCP_OUT.txt -c -Usa -PPassword

2nd Step – Use Java to Do the String Concatenation

I then wrote a java program that reads the above generated Tab separated text file from BCP and uses FileIO to read each line – parse each column separated with Tabs and then generates an output which is a Text file – with each column separated by Tabs and with the columns as shown in Table 2.

It was the Java Program that was doing the String Concatenation.

3rd Step – Take the output from the Java Based step – and BCP in the results back

After that it was easy. Just BCP in the results obtained from 2nd step into the  DB Table 2.

bcp TableName_asTable2

IN  FLatFileGeneratedByJava.txt -c -Usa -Ppassword

Advertisements

How I solved the Slow Login Problem with Liferay

We had been using the Liferay Portal Server for our CMS and UserManagement on a Tomcat server. When the new systems were deployed initially there was no problem initially for the 1st 3 months or so after which we started slow trickling comments from users that the logins were getting slower and slower with each passing month.

The initial logging time was < 5 sec (which is slow but still quite acceptable). Then over period of months – login times started deteriorating to 30 sec and in some cases for some users to 90 sec – which was outrageous.

Liferay is known to be slow (see JIRA ticket LEP-2182, http://support.liferay.com/browse/LEP-2182 )

Especially when using Microsoft SQL Server (see  https://www.liferay.com/web/guest/community/forums/message_boards/message/71332)

And indeed we were using SQLServer as the repository for Liferay.

On further investigation I found out that – it was the Liferay permissions checker algorithm that was causing the slowdown in Login response time.

I found that over a period of time as the resources (which in our case is Analytic Charts that users create over a period of time ) increased – Liferay’s permissions checking Algorithm did not scale up well to handle the response time for checking if the User who is trying to login had permissions to Read/Write/Clone/Migrate all the resource in the system.

So the crux of the slowdown was the – inability of Liferay to scale – checking of different types of permissions across numerous resources. At one point in time we had more than 100,000 resources ( charts in our case ) and Liferay became slower and slower in handling the necessity to check across all the resources in the system.

Solution  :

I found that internally Liferay was using Hibernate’s auto generated SQL ( HQL ) to do the queries to search for the resources the User would be able to Modify/Clone/Migrate.

I found out the ER Diagram of the Liferay DB Tables and how they are interrelated and formulated my own SQL query ( which is given below ) to get back the resources for a given User who belongs to a given Customer. Then I intercepted the Hibernate Calls made from Liferay APIs and injected our own SQL to get back the results using the optimized SQL. The SQL query was simple Union across multiple Tables to check how a user can get permissions – based on Group Permission/Roles. The whole thing now works in < 2 sec response time.

Modified SQL Query to get the Permissions for a given user for a given company

 select distinct r.primKey r, r.companyid c
from user_ u , role_ ro, Users_Roles ur, resource_ r
where ro.name='Administrator' and
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet' and
r.scope = 'individual' and
r.typeid = 'class' and
ur.userId=u.userId and
ur.roleId = ro.roleId and
r.companyId = u.companyId
and u.userId = 'cbergh.agn'
and  r.companyid = 'ALLERGAN'
union
-- Get All Resources that the User can access directly : just by itself
select distinct  r.primKey r, r.companyid c
from resource_ r, permission_ p, users_permissions up, user_ u
where
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet' and
r.scope = 'individual' and
r.typeid = 'class' and
r.resourceId = p.resourceId and
r.CompanyId = p.CompanyId and
p.permissionID = up.permissionId and
up.userId = u.userId and u.userId = 'cbergh.agn'
and  r.companyid = 'ALLERGAN'
union
-- Get All Resources that the User can access from the group
select distinct  r.primKey r , r.companyid c
from resource_ r, permission_ p, groups_permissions gp, group_ g, users_groups ug
where
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet' and
r.scope = 'individual' and
r.typeid = 'class' and
r.resourceId = p.resourceId and
p.permissionID = gp.permissionId and
gp.groupId = g.groupId and
g.groupId = ug.groupId and ug.userId = 'cbergh.agn'
and  r.companyid = 'ALLERGAN'
union
-- Get all the resources the user can access through the Roles
select distinct r.primKey r, r.companyid c
from resource_ r, permission_ p, Roles_Permissions rp, Role_ ro, Users_Roles ur
where
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet' and
r.scope = 'individual' and
r.typeid = 'class' and
r.resourceId = p.resourceId and
p.permissionID = rp.permissionId and
rp.roleId = ro.roleId and
ro.roleId = ur.roleId and ur.userId = 'cbergh.agn'
and  r.companyid = 'ALLERGAN'
union
-- Get all the resources the user can access through the group Roles
select distinct r.primKey r, r.companyid c
from resource_ r, permission_ p, Roles_Permissions rp, Role_ ro, groups_roles gr, Users_groups ug, group_ g
where
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet' and
r.scope = 'individual' and
r.typeid = 'class' and
r.resourceId = p.resourceId and
p.permissionID = rp.permissionId and
rp.roleId = ro.roleId and
ro.roleId = gr.roleId and
gr.groupid = g.groupId and
g.groupId = ug.groupId and
ug.userId = 'cbergh.agn'
and  r.companyid = 'ALLERGAN'
union
select distinct primKey, null, null, null from resource_ r where resourceID in
(
select resourceID from permission_ where permissionId in
(
select permissionID from groups_permissions where groupId in
(
select groupId from group_ where className like 'com.liferay.portal.model.UserGroup' and companyId = 'ALLERGAN'
and classPK in ( select u1.userGroupId from userGroup u1, Users_UserGroups u2  where companyId =  'ALLERGAN' and
u1.usergroupId = u2.userGroupId and u2.userId = 'cbergh.agn'
)
)
)
)
and
r.companyid = 'ALLERGAN'
and
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet'
and r.scope = 'individual'
r.typeid = 'class'