Q using Flow to write to an Azure SQL DB

[Anonymous]

Hi, I'm building an app that needs to write a number of text inputs to records in an Azure SQL DB. I'm finding that the current design of having a gallery with a text input in each card is very slow when writing to the database, to the tune of 5 seconds per card

it was mentioned to me to look at using flow as it should be able to do them in parllel.  I have created a flow for adding records to the database, however I also need to ad a condition where if the text inputs are a New record, that gets created, however if the record already exists in the database, it is simply updated with the new fields.

Is this possible with a single flow?

[Meneghino]

Hi, why not use the Patch or Collect functions coupled with ForAlll? It seems that flow would add an unnecessary complication.

[Anonymous]

Hi, sorry I didn't include the details.  Right now I'm using a forall statement wrapped around a patch. It works, but is slow when there are up to 14 text input boxes to save. Somewhere in the neighbourhood of 2 mins to save. For my users this would be unacceptable so looking for alternatives.

thanks

Dave

[Meneghino]

Hi @Anonymous

Just one more clarification: are the 14 text input boxes to populate:

1. 14 fields of one record, or
2. 1 field of 14 records, or
3. a combination

If you are having performance issues then I imagine it is the second, but just wanted to make sure.

If you are creating new records, then performance should be much improved by doing a bulk operation using the Collect function instead of a Patch.  Something like:

Collect(MyDataSource, ShowColumns(AddColumns( MyGallery.AllItems, "MySQLColumName", MyTextInput.Text), "MySQLColumnName")))

Even if you are editing existing records, then you can use the Patch function in bulk mode, I think.  You just need to Fitler the records to be edited in the gallery and use those as the second argument.  Something like this:

Patch(MyDataSource,
     ShowColumns(Filter(MyGallery.AllItems, IsExisting), IDcolumn),
     RenameColumns(Filter(MyGallery.AllItems, IsExisting), etc. etc.)
     )

It would help if you could post your existing patch expression that works, albeit slowly, so that I can be more specific.

[Anonymous]

thanks again for the reply.

Your assumption of scenario 2 is valid, 14 gallery items each with a single text input field.

I tried converting the Patch statement over to the collect as you suggested, & it definetly is much faster, however it appears that because my data is in a gallery only the last number in the gallery is actually saved in the end with the new record. In my case, I need to get all 14 saved (each to their own new record).

Here is a condensed (3 gallery items instead of 14) version fo the code I'm using....

ForAll(ItemData.AllItems,
    Patch('[dbo].[TestDB]',
        If(IsBlank(LookUp('[dbo].[TestDB]', ItemName = Item && Text(SelectedDate, "[$-en-US]mm/dd/yyyy") = Date)), 
             Defaults('[dbo].[TestDB]'), 
             LookUp('[dbo].[TestDB]', ItemName = Item && Text(SelectedDate, "[$-en-US]mm/dd/yyyy") = Date)),
        {
        Date: Upper(Text(SelectedDate, "[$-en-US]mm/dd/yyyy")),
        Item: label_ItemName.Text, 
        Quantity: If(IsBlank(ti_Quantity.Text) || !IsNumeric((ti_Quantity.Text)),"0",ti_Quantity.Text), 
        }
        )
    )

[Meneghino]

Hi @Anonymous, thanks for clarifying.

The code you use is inefficient because:

1) There is one LookUp to TestDB for each item to see if item is already present

2) There is one call to Defaults for every new item, or another LookUp to TestDB for any existing item

3) The items are written one by one instead of in bulk

Here is the strategy to make this more efficient:

1) Do the LookUp as to whether the item is existing not when you write to the db, but in the gallery.  The exact way to do this will depend on what the primary key of table TestDB is.  What is it?

2) In the OnStart property of the first screen place this:

Set(TestDB_defaults, Defaults('[dbo].[TestDB]'))

You will need to restart the app for this to be loaded, but then you can substitute TestDB_defaults wherever you use the Defaults function.  Try it and see, this should already improve performance.

For existing items, the answer is linked to 1) above

3) We can do this last, once 1) and 2) are resolved.

One last comment, you say that you have 14 items (reduced to 3 in the example), but from the code it seems that it is 3 (or 14) columns, not items.  Can you please clarify?  Let's use the term item to indicate one entry/record in the database and column/field to indicate the elements of that one entry.

[Anonymous]

First off, thankyou very much for your patience & time; greatly appreciated.

Currently I populate the ti_Quantity text input box in the gallery by doing a lookup in the Default property of the text input. If I could set a variable in the same Default property, I could use it later as a flag to tell whether ti_Quantity was blank (indicating it should be a new record). Or I could also check the Key value (RecordID column in this case) to see if it is blank or not. I'm just not sure if it's possible to set a variable in the Default parameter or not.

So to answer your Strategy #1 question, I do have a key column, called RecordID, which I populate when a new record is created (sorry should have left that in the last snipit). 

I've implemented your Set code for Strategy #2 point now, thankyou. Luckily, this is a new app, so I can wipe the DB clean for a few more days & only have test data I need.

I'm hoping that the following structure will clairify what I'm hoping the database will look like for 2 days of data recording (in some cases data from multiple days might be entered on the same day).....

Item                        Qty              Date                                  RecordedEnteredDate            RecordID

Apples                    5                  7/17/2017                          7/18/2017                              1234

Oranges                 0                  7/17/2017                          7/18/2017                              1235

Pears                     4                  7/17/2017                          7/18/2017                              1236

Oranges                 3                  7/17/2017                          7/18/2017                              1237

..... Other 10 items.......

Apples                    3                  7/18/2017                          7/18/2017                              1248

Oranges                 4                  7/18/2017                          7/18/2017                              1249

Pears                     6                  7/18/2017                          7/18/2017                              1250

Oranges                 9                  7/18/2017                          7/18/2017                              1251

..... Other 10 items.......

Appologies for misuse of some terms, hopefully the above will help.

[Meneghino · Accepted solution]

Hi @Anonymous

Yes, you can use variables for the Default property of text inputs, but you cannot set a variable as this is an action.  You could do that in the OnChange property of the same text input for example. 

In any case, the discussion is really a little bit wider, in that the solution really depends on what you are trying to do.  I get the impression that the way you organise your data is also relevant.  The solution space is so large that it is difficult to be more precise without a clearer understanding of your business needs.  For example what determines the list of items: are the 'fruits' from a predetermined set that rarely changes, or can it be something completely different every day?  Does the user have to go through the complete list every day, or pick and choose?  Depending on this and other factors you could use a dropdown instead a text input, or just produce the list in labels.

However, I am confident that you can speed up the app performance.

[Anonymous]

Hi,

Just wanted to say thankyou for your guidance. I did rebuild my design, with better use of lookups and filters, not to mention flows. My app is working much faster now.

thanks again

Tomcat