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.

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

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.