How to restrict number of retrieved records from DataSource

[former_member203185]

Hello experts,

occasionally dealing with VC I am got surprised about the tool's abilities for data managing.

Now again new challenge appeared.

One DataSource (kind of custom BPM) has already accumulated too much data that retrieving process takes too long time.

And now am wondering if there is any way how I can restrict number of retrieving records coming from DS?

So the scenarion is pagination. At first time I load 100 records, then user clicks button and other 100 records are loaded an so forth...

I have an idea about usage of  WD component but I suppose that it will take too much time to send data from DS to WD component's context.

I hope enyone would help me.

Thank you in advance.

Sincerely,

Anton

[former_member202465 · Accepted solution]

Hi Anton,

I will try to answer. This text is relevant if loading a lot of records in the UI is the bottleneck.

I tried to create a super simple example.

The runtime:

Step 1:

Step 2 - after pressing Show Data:

Step 3: after pressing Next:

... and so on pressing Next.

The modeling:

1. Have a global Data Share to contain the current records index and the step (jump) for each Next press.

2. Add a Data Share and connect the Service output to it.

3. Filter the results when catching the 'update' event - we will soon model its triggering. Here we filter to show the results of the current step, for example - records 5 to 8 as seen in the runtime snapshot above:

4. In the output table add a button (Show Data) in the Toolbar for showing the first records:

  - Add an Assign Action to reset the index value

  - Trigger the "update" event (Custom Action) to run the filter.

5. Add another button (Next) to perform two actions:

  - The Assign Action to update the global index of presented records to the next step.

  - Trigger the "update" event to rerun the Filter.

That's it!

Remarks on the logic:

- All the data gets loaded into the Data Share (memory) once

- The Filter is run each time - button press -  and shows the relevant records according to the current step.

Gaps and prerequisites:

- We depend on the record number to exist in the Data Service out port. This seems harsh, but should be easy to achieve for example in an SQL Query.

- I didn't add Previous button, nor did I check the limits for exceeding the number of records so... in case this model is a good direction - To Be Continued.

Thanks,

Udi

[former_member203185 · Accepted solution]

Hi Ehud,

glad to hear you

your example is SUPER.

As I have noticed youк solution is based on just one simple field - NUM. It stores sequence of numbers.

Does the field comes from your Data Service or you generate fields values somehow?

Personally I don't have such field in Data Source and now I wonder if I can generate field NUM1 of Data Share during data is loading into the Data Share?

One idean came into my mind: if I could calculate count of records that are stored in datashare at the moment (during data loading) I would get such digits.

Without the field I see solution wich Java WD component usage. But I hope there is simpler way...

Thanks

[former_member202465 · Accepted solution]

Hi Anton,

You are correct, we need the Data Service output to have this field. Our backend could get the Row Number in as SQL query, for example see in SQL Server: http://technet.microsoft.com/en-us/library/ms186734.aspx

Or -

SELECT

    ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,

    FirstName,

    LastName

FROM

    Sales.vSalesPerson

Another option - Paging on the database level:

- The SQL statement is returning a subset of rows, according to given parameters

- VC calls the Data Service for each Next press with the updated parameters, to get the next X rows.

In the solution we have more calls to the backend - each Next press - but we hold less memory at a time.

See on the same link - Returning a Subset of Rows

Or -

WITH OrderedOrders AS

(

  SELECT

     SalesOrderID,

     OrderDate,

     ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber

  FROM Sales.SalesOrderHeader

)

SELECT

    SalesOrderID,

    OrderDate,

    RowNumber 

FROM

    OrderedOrders

WHERE

    RowNumber BETWEEN 50 AND 60;

See a more detailed example: Tutorial 25: Efficiently Paging Through Large Amounts of Data

The Combined Way

According to optimization needs - you might need to combine both methods:

• Get chunks of max 1000 rows into memory (Data Share)
• Page in VC using method 1 - the Filter Operator - to show the next 50 rows each Next press.
• When exceeding the limit of what we have in memory, the Next press triggers another backend call to get the next 1000 rows - method 2.

Thanks,

Udi

[former_member203185 · Accepted solution]

Hi Ehud,

thanks for such clear and deep answer.

But there is one thing that doesn't allow me to follow your recommendations.

I don't have any data services and SQL/Oracle server in direct access.

I've been dealing with BPM Data Sources on SAP  NW CE 7.2. As far as I know it doesn't provide any tools for SQL using. Even there is no BPM DataSource API on this version. Very sad story...

Sincerely

[former_member202465 · Accepted solution]

Hi Anton,

I hope I can help the following way:

I Coded this super-simple Web Service, to consume in VC. It gets the Bank List, and returns the same with one additional field: index - as in the numbering of each row.

So we have to services:

1. The one we can't change - here the R3
2. The Web Service to add the numbering

I could then go on with the modeling using the index and get the paging:

Of course, there are more copying of the data this way, but the UI Table paging is made possible

Thanks,

Udi