I am working on an application that downloads data from Snowflake, then loads to the application's internal database. The download from Snowflake takes a negligible amount time, but the load to the app's database takes several seconds (for example, 8-10 seconds for 81 records). I have a hard time understanding why pulling data from across the network is so much faster than loading to the app's database. Is there a way to optimize the database insert? I removed the Id field as it is not necessary for my use case.
Hi @Vikram Navale ,
I see you are using a loop and calling Create<Entity> for each record, then the time is probably coming from multiple database roundtrips and transaction overhead, not from the data itself. In ODC, for this type of scenario, you should try using the bulk entity actiony CreateOrUpdateSome .
Also, I see that this logic is currently being handled in a Client Action. I would recommend moving the data retrieval and record creation logic into a Server Action.
The issue is absolutely on you doing it client side. You have several warnings in Studio telling you that.
For those 80 records you are doing 82 sequential connections to the server. Even if it is 0.1 seconds each, it adds up.
If it takes only one second, why to do you need 4 feedback messages telling you the step it is in? Just ask the server to do the delete, the download, and the CreateAndUpdateSome as Mihai said.
You wont even have time to measure how long it takes.
If you're sure that Get External Data is fast,
then the problem is that it's taking too long because you're looping to update. As Mihai mentioned, you must to use bulk update for this.
Additionally, you cannot use client actions to call multi-server actions.
it is the client side execution, much much more than the looping.
Okay, wow, loading on the server side made a dramatic difference in execution time. Thanks, everyone.
@Mihai Melencu The only entity actions on that table are Create and DeleteAll. ODC removed the rest after I deleted the Id, and throws an error during publishing when I add a field and try to set that as the identifier.
@Nuno Reis I added the feedback messages in order to determine where was the bottleneck. I will remove them before showing this to the users.
The easiest solution is to create a new entity with the updated attribute changes and replace the old entity with the new one.
Mihai said everything.
Just to explain what is going on:
When you don't have an Id you can't find the record to update/delete, that's why you have less Entity Actions.
That error is because it is trying to create an attribute but internally it still remembers the old one (in case you want to rollback to it).
Tried that, didn't work, don't remember why.
@Nuno Reis I understand databases well enough to figure that out. This screen is intended only for displaying data. No update or deletion of individual records--no need for Id.
You should have an Id nonetheless, I don't see any reason of not having the Id, even if you dont want to manually edit that data.