Skip to main content

Development

Using Grouping and Cursor to improve performance

Summary:

In BI testing, we create views for large amounts of data, but performance issues can occur when we need to pivot a column and make it a string.

To improve the performance, we divide this column into two groups first, one is for the unique value that doesn’t need any combination, and another one is for the multi values that need combination. Then use Cursor to combine the multi values.

Here is the example:

There are three tables in database: CLAIM, REMARKS and REMARKCODES.

Table CLAIM contains electronic claim information;

Table REMARKS contains the remark code for each claim, and each claim has at least one remark code;

Table REMARKCODES contains the remark description for each code, and each code has only one description.

And there is the Velocity Mapping:

 

So according to the velocity mapping, the expected result is to combine all of the remark codes and description for each claim.

For example, CODE=CD01,CD13

and DESCRIPTION =the description of CD01, the description of CD13

To improve the performance, we will divide claim into two groups first:

One is for the claim has only one remark code, so we don’t need any combination;

Another one for the claim has more than one remark codes, we need do combination.

Then use cursor to combine the remark codes and description for the claim with more than one remark codes.

Here are the detail steps we used:

  1. Create two temp tables to calculate the count of remark code for each claim.

Claims_Temp:

 

Claims_Temp_Count:

2. Create a function (QA_DESCRIPT) to combine the remark codes and description for the claim that has more than one remark codes.

3.Create 3.  Create a view to get the transformed data.

Here use “UNION ALL” to union the claim that has only one remark code and others have more than one remark codes.

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow Us