Android Question How to give Messages that would flash only if a particular SQL Operation has not yielded a result within a 'threshold time'

[beelze69]

Hi,

I have a b4A Client application that uses JRDC2 to fetch data from an RDBMS Backend and places the output in a xCustomListView.

I would like to do the following:

i) Assuming that all SQL Queries fired are 'Select queries', some sql queries would take longer time to complete processing and show the result in the xCustomListView control and some queries would display the result faster.

ii) In the b4A Front-end , I would like to set a 'threshold time in seconds' and if the data populating has 'not commenced populating' in the xCustomListView (meaning query is not completed) within that threshold time , the application should show an 'asynchronous wait message like 'Ps. wait..Query processing is on'... for say 3 seconds (by ascnchronous I mean whatever processing goes to complete the query and fetch the records should not be halted for displaying this wait message.

iii) Taking ii) a little further, I would like to show this wait message not for just 3 seconds but TILL the xCustomListView 'commences'(not completes) population of the result of the query / query returns no records (as the case may be)...

How can this be done ?

iv) Any help on pulling out the approximate completion time of an RDBMS query BEFORE its actual execution (in particular Oracle, which is my RDBMS in the present case) will also be very useful

Please help.

Thanks..

 

[Albert Kallal]

You can quite easy do this. I have a sqlite sync routine (using jtds/jdbc to sql server).
And when I start a sync, I display the "indetermine" progress bar (and I HIGH recommend you do this). In ohter words, I would not wait 3 seconds.

The only real issue then is do you want some progress notification WHILE you filling out the xcustomListview, but you HAVE the data. You can do this, since you do now have a row count from the query (once it done). But lets deal with the first part.

You could/would simply use a timer.

thus you do this:

    ProgressBar1.Visible = True
    MyTimer.Initialize("TShow",3000)
    MyTimer.Enabled = True
 
    ' My sql query with wait for goes here.

    ' after sql query is done, we turn off the timer like this:

    MyTimer.Enabled = False
    ProgressBar1.Visible = False
    EditText1.Visible = False

'' and our message display event on "tick" would be this:
Sub Tshow_Tick

    ' ok 3 seconds - show the progress text or message wait buton
    'ToastMessageShow("3 seconds - waiting",True)
    EditText1.Text = "Loading data - please wait"
    EditText1.Visible = True
     MyTimer.Enabled = false    ' no need to have this code fire every 3 seconds.
 
End Sub

Now in above, I commented out the toastmessage - but you could fire one of those - pulling data please wait!
(I like them, as they don't take up UI space - and they just fade in/ then out).

But, regardless, the Tick event only fires after 3 seconds, and then displays our message (hidden text box in this example).

but, if the whole operation occurs in less then 3 seconds, then our code that follows the query + wait for turns off the timer, and that 3 second message of course never displays.

As noted, I tended to always display the spinning circle during the sync process (that's the indeterminate progress bar). As for a progress bar? As noted, you could start/show/display one AFTER the query, since often the xCustomList view load up time is RATHER expensive and time consuming (much more then the query pull). And you do have the row count at that point in time . But then again, this assumes and suggests we keep the CLV down to 100 max range.

Larger lists suggests a different UI to reduce the amount of scrolling required to deal with 100+ rows.

So, I can't see much need for the CLV loading time, since if it too large and a noticeable wait time exists, then we already messed up and are overloading the users ability to work with 100 rows of data anyway.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[Peter Simpson]

Hello,
Just how many rows of data (about) are you expecting your database query to return???

You can use xCLV with lazy loading, this allows you to display the loaded SQL query data much quicker than trying to load all the query results into the xCLV at the same time. If you believe that using lazy loading with xCLV will still not be fast enough for you when it comes to populating your xCLV, then you should use PreoptimizedCLV. Using PreoptimizedCLV is an excellent solution when it comes to populating xCLV with large amounts of data from databases. In both cases above you should not need to use any wait messages to let users know that your app is loading data.

Something else to look at when querying your database is to make sure that you optimise your SQL query as much as possible and to only return the exact columns of data that are necessary. If you are retrieving data from 2 or more tables to populate your xCLV, then you will probably find it beneficial to use stored procedures. One more thing that you should definitely do is to index the columns of data in your tables that the WHERE clause is used (for the most part).

Basically, if your app is going to return a lot of rows of data from your database tables, you should seriously take into consideration using PreoptimizedCLV or at the very least xCLV with lazy loading.

Btw most users do not scroll down long lists of data reading each and every single row, that is why having custom search filters in your applications can be extremely beneficial and important to integrate.

Search the forum for 'Lazy Loading' or 'PreoptimizedCLV '.

 

[beelze69]

You can quite easy do this. I have a sqlite sync routine (using jtds/jdbc to sql server).
And when I start a sync, I display the "indetermine" progress bar (and I HIGH recommend you do this). In ohter words, I would not wait 3 seconds.

The only real issue then is do you want some progress notification WHILE you filling out the xcustomListview, but you HAVE the data. You can do this, since you do now have a row count from the query (once it done). But lets deal with the first part.

You could/would simply use a timer.

thus you do this:

    ProgressBar1.Visible = True
    MyTimer.Initialize("TShow",3000)
    MyTimer.Enabled = True

    ' My sql query with wait for goes here.

    ' after sql query is done, we turn off the timer like this:

    MyTimer.Enabled = False
    ProgressBar1.Visible = False
    EditText1.Visible = False

'' and our message display event on "tick" would be this:
Sub Tshow_Tick

    ' ok 3 seconds - show the progress text or message wait buton
    'ToastMessageShow("3 seconds - waiting",True)
    EditText1.Text = "Loading data - please wait"
    EditText1.Visible = True
     MyTimer.Enabled = false    ' no need to have this code fire every 3 seconds.

End Sub

Now in above, I commented out the toastmessage - but you could fire one of those - pulling data please wait!
(I like them, as they don't take up UI space - and they just fade in/ then out).

But, regardless, the Tick event only fires after 3 seconds, and then displays our message (hidden text box in this example).

but, if the whole operation occurs in less then 3 seconds, then our code that follows the query + wait for turns off the timer, and that 3 second message of course never displays.

As noted, I tended to always display the spinning circle during the sync process (that's the indeterminate progress bar). As for a progress bar? As noted, you could start/show/display one AFTER the query, since often the xCustomList view load up time is RATHER expensive and time consuming (much more then the query pull). And you do have the row count at that point in time . But then again, this assumes and suggests we keep the CLV down to 100 max range.

Larger lists suggests a different UI to reduce the amount of scrolling required to deal with 100+ rows.

So, I can't see much need for the CLV loading time, since if it too large and a noticeable wait time exists, then we already messed up and are overloading the users ability to work with 100 rows of data anyway.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

Hi Albert !

Thanks a lot !! .. Will try this out .. Worked with VB6 timers but never in b4A.. Very nice explanation you have provided...

 

[beelze69]

Hello,
Just how many rows of data (about) are you expecting your database query to return???

You can use xCLV with lazy loading, this allows you to display the loaded SQL query data much quicker than trying to load all the query results into the xCLV at the same time. If you believe that using lazy loading with xCLV will still not be fast enough for you when it comes to populating your xCLV, then you should use PreoptimizedCLV. Using PreoptimizedCLV is an excellent solution when it comes to populating xCLV with large amounts of data from databases. In both cases above you should not need to use any wait messages to let users know that your app is loading data.

Something else to look at when querying your database is to make sure that you optimise your SQL query as much as possible and to only return the exact columns of data that are necessary. If you are retrieving data from 2 or more tables to populate your xCLV, then you will probably find it beneficial to use stored procedures. One more thing that you should definitely do is to index the columns of data in your tables that the WHERE clause is used (for the most part).

Basically, if your app is going to return a lot of rows of data from your database tables, you should seriously take into consideration using PreoptimizedCLV or at the very least xCLV with lazy loading.

Btw most users do not scroll down long lists of data reading each and every single row, that is why having custom search filters in your applications can be extremely beneficial and important to integrate.

Search the forum for 'Lazy Loading' or 'PreoptimizedCLV '.

Thanks Peter !

Will try Lazy Loading with xCustomListView... Maximum I expect around 5000 records for output,.,so it is heavy

 

[Peter Simpson]

Maximum I expect around 5000 records for output,.,so it is heavy

Is that all, that's nowhere near heavy, in that case look at the link below. The code and database has changed from when I originally posted it, I removed my lazyness...

Spoiler: Populate airport list

List population time = 0.64 seconds to populate 7698 airport names
List population time = 0.01 seconds to populate 20 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 20 airport names
List population time = 0.47 seconds to populate 7698 airport names
List population time = 0.01 seconds to populate 20 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 20 airport names
List population time = 0.63 seconds to populate 7698 airport names

💡 Part 2 Examples - Creating long lists using xCustomListView with Lazy Loading - Newer developers

Hello All, If you're not experienced with using xCustomListView (xCLV) with Lazy Loading (LL) when it comes to loading long lists of data, then I strongly suggest that you read the following post first >>>💡 Part 1 Basics > XcustomListView

www.b4x.com

Enjoy...

 

[Makumbi]

Is that all, that's nowhere near heavy, in that case look at the link below. The code and database has changed from when I originally posted it, I removed my lazyness...

Spoiler: Populate airport list

List population time = 0.64 seconds to populate 7698 airport names
List population time = 0.01 seconds to populate 20 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 20 airport names
List population time = 0.47 seconds to populate 7698 airport names
List population time = 0.01 seconds to populate 20 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 2 airport names
List population time = 0.01 seconds to populate 20 airport names
List population time = 0.63 seconds to populate 7698 airport names

💡 Part 2 Examples - Creating long lists using xCustomListView with Lazy Loading - Newer developers

Hello All, If you're not experienced with using xCustomListView (xCLV) with Lazy Loading (LL) when it comes to loading long lists of data, then I strongly suggest that you read the following post first >>>💡 Part 1 Basics > XcustomListView

www.b4x.com

Enjoy...

These examples are good i wish Peter simpson could convert all them to the latest B4xpages

 

[Mahares]

i wish Peter simpson could convert all them to the latest B4xpages

Actually the following link has a very nice example by Peter Simpson illustrating B4Xpages with xClv, lazy loading, PreoptimizedCLV, all in one place

💡 [B4X] [XUI] [B4XPages] B4A PreoptimizedCLV lazy loading from SQLite and CSV - Newer developers

Hello to you all, Welcome to Sheldon Coopers and Amy Farrah Fowlers fun with flags ;) Here is a simple example on using B4XPages with both an SQLite database and also a CSV file with multiple PreoptimizedCLV to retrieve country population data and flag images. Using a PreoptimizedCLV to...

www.b4x.com

 

[Albert Kallal]

Thanks Peter !

Will try Lazy Loading with xCustomListView... Maximum I expect around 5000 records for output,.,so it is heavy

As noted, the issue more is a problem that a scrolling list of about 100 is the limit for end users - anything else is a torture for the end user. It not practical to scroll though long lists - it takes too much time and is too frustrating for the end user. That's why I am noting that you probably will not need some type of progress bar or wait notification, since you probably not be wanting to load up much more then 100 records into such a scroll list. As noted with some type of filter system + lazy type of loading, you can go well beyond say 100 rows. The problem then is not one of speed - but that of having too long of a list to scroll through - that's just going to be a form of "torture" on your end users - regardless of performance issues.
R
Albert