Hello All,

I want to bring all the records of one entity which are not present in another entity.

What is the easiest and efficient way to do that?

I want to use timer for the same. Therefore, any pointer to get records in efficient way would be helpful.


Thanks & Regards

Prajakta Roshankhede


Hi Prajakta,


is there any relation between this entity or not. can you provide images of your entity. based on this provide sql or solution.

 and if you are using timer than you have using another entity to store those records or may be create structure for the same and save this on location so you can get after when you login application.


Regards

Rahul Sahu

Rahul Sahu wrote:

Hi Prajakta,


is there any relation between this entity or not. can you provide images of your entity. based on this provide sql or solution.

 and if you are using timer than you have using another entity to store those records or may be create structure for the same and save this on location so you can get after when you login application.


Regards

Rahul Sahu

Hello Rahul,

I can not share images of entity, but yeah I can tell you more about the case.

In my scenario I want to delete records from outsystems db if it is deleted from external db. Right now i am checking it by using aggregate and for each loop, but it is taking much time. I want efficient solution for the same.

Thanks & Regards

Prajakta Roshankhede


Hi Prajakta,

Since these are in seperate databases and you cannot join them, you need to create some kind of compare functionality. Generally speaking though, ideally you would have access to a journal, or an update timestamp (assuming soft deletes) or the like so you could more easily determine which records have been deleted.

If that's not available, and the dataset is really large, you have a very bad, non-scalable situation that will break sooner or later if the amount of data becomes too big, and you're probably comparing 99.99% of the records for nothing, as they stay the same.

On the other hand, if the data sets are relatively small (say a few 1000 records max), I'd just query both tables using the same sorting order (ideally on Id/external Id or some other unique characteristic), and just work yourself down one record at a time, keeping track of where you are in both lists.


Kilian Hekhuis wrote:

Hi Prajakta,

Since these are in seperate databases and you cannot join them, you need to create some kind of compare functionality. Generally speaking though, ideally you would have access to a journal, or an update timestamp (assuming soft deletes) or the like so you could more easily determine which records have been deleted.

If that's not available, and the dataset is really large, you have a very bad, non-scalable situation that will break sooner or later if the amount of data becomes too big, and you're probably comparing 99.99% of the records for nothing, as they stay the same.

On the other hand, if the data sets are relatively small (say a few 1000 records max), I'd just query both tables using the same sorting order (ideally on Id/external Id or some other unique characteristic), and just work yourself down one record at a time, keeping track of where you are in both lists.


Hello Kilian,

I already applied soft delete functionality for outsystms server db and local db sync process. But unfortunately there is no indication available in external db.


- Prajakta   


In that case there's little you can do besides what I suggested: read both Entities, and do a linear search through both lists, comparing items as you go. This gets you an O(n) algorithm, which is as fast as you can get.

Kilian Hekhuis wrote:

In that case there's little you can do besides what I suggested: read both Entities, and do a linear search through both lists, comparing items as you go. This gets you an O(n) algorithm, which is as fast as you can get.


Hi  Prajakta,


On top of Killian's suggestion using O(n) algorithm. Assuming External DB entity wont have any records that are not present in OutSystems DB then skip the search if record count match on both entities. That way search can be avoided when not needed. 

Vijay Selvaraj wrote:

Kilian Hekhuis wrote:

In that case there's little you can do besides what I suggested: read both Entities, and do a linear search through both lists, comparing items as you go. This gets you an O(n) algorithm, which is as fast as you can get.


Hi  Prajakta,


On top of Killian's suggestion using O(n) algorithm. Assuming External DB entity wont have any records that are not present in OutSystems DB then skip the search if record count match on both entities. That way search can be avoided when not needed. 

Hello Vijay,

But there is possibility that old record is deleted and some other new record will be added. In that case this solution may fail.

Thanks for the suggestion.



Prajakta Roshankhede wrote:

Rahul Sahu wrote:

Hi Prajakta,


is there any relation between this entity or not. can you provide images of your entity. based on this provide sql or solution.

 and if you are using timer than you have using another entity to store those records or may be create structure for the same and save this on location so you can get after when you login application.


Regards

Rahul Sahu

Hello Rahul,

I can not share images of entity, but yeah I can tell you more about the case.

In my scenario I want to delete records from outsystems db if it is deleted from external db. Right now i am checking it by using aggregate and for each loop, but it is taking much time. I want efficient solution for the same.

Thanks & Regards

Prajakta Roshankhede


If you are comparing names or numbers, sorting the lists and adding logic might wield quicker conclusions.

For example, if the outsystems and external Lists are ordered alphabetically and you are comparing names:

(You can also convert numbers to text and use alphabetical ordering)

The first entity is named Ana, once you see that you've iterated all external entities whose name starts with 'A' and no match for Ana was found, you can conclude that Ana is not in the external list and remove Ana from the outsystems list right away.

Also once you are done with all the 'A' names in the outsystems list you can remove all the 'A' names in the external list in the first 'B' iteration, shortening the external list as you go.

Hope this is helpful


Filipe,

Your proposal would add extra logic, which makes it actually slower. Simple compares are imho the best way, you can't get it faster than O(n).

Hi,

Do you have access of external db and can do some programming there. If yes, then create a trigger/event there in external db or server that is connected to db. At same time expose one API in outsystems application. So whenever there is delete in external db, trigger will call the API exposed by the Outsystems. In this API, you can write your logic to delete the record.

Thanks.

Kilian Hekhuis wrote:

Filipe,

Your proposal would add extra logic, which makes it actually slower. Simple compares are imho the best way, you can't get it faster than O(n).

But the logic shortens the list, if the data size justifies it it may end up making less comparisons to solve the same problem.

In the example I posted, once you iterate all the 'A' names in the outsystems list and remove the 'A' names from the external list, all the 'B' names will be the first elements in the both lists, you can match them right away instead of comparing a 'B' name potentially N times, where N is the size of the external list.

By guaranteeing early matches this way you are likely to make less comparisons in total and finish early, even though the logic is in fact slower.

This can only be done if both lists are sorted alphabetically to begin with.

Hi Filipe,

There's no magic way to remove all "A" names from the list, you'd need to compare anyway. Furthermore, to notice that the 2nd list has "B" while the first list has "A" is another compare. Instead, my much simpler proposal needs just a single compare. You are really overcomplicating things.

Hi Prajakta,

I assume that 

   a. you have some unique identifier to map records from External DB to your OutSystems DB.

   b. External systems has the records physically deleted.

With that assumption in mind:

1. Get list of Ids from External DB 

SELECT Id FROM EXTERNAL_DB

2. Get list of Ids from OutSystems DB

SELECT Id FROM EXTERNAL_DB

3. For each Id in OutSystems DB

    if( ListIndexOf(ExternalDB List, ExternalDB.Id = OutSystems DB.Id)) = -1

      Add id to a new list, say DeletedIdList


4. String_Join DeletedIdList with comma separated delimiter and store in a variable, say DeletedIds (string type)


5. Write a SQL with input parameter DeletedIds 

DELETE FROM {OutSystems DB} WHERE ID IN (@DeletedIds)


This should delete all records in your OutSystems DB with all the selected ids in single delete operation. Your looping in STEP 3 should only be doing the filter for the items to delete.


Let me know if this helps.

Regards,

Saugat