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

Active Member
Licensed User
Longtime User
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..
 
Last edited:

Albert Kallal

Active Member
Licensed User
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:

B4X:
    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
 
Last edited:
Upvote 0

Peter Simpson

Expert
Licensed User
Longtime User
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 '.
 
Last edited:
Upvote 0

beelze69

Active Member
Licensed User
Longtime User
Hi Albert !

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

beelze69

Active Member
Licensed User
Longtime User
Thanks Peter !

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

Peter Simpson

Expert
Licensed User
Longtime User
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...

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

Enjoy...
 
Upvote 0

Makumbi

Well-Known Member
Licensed User
These examples are good i wish Peter simpson could convert all them to the latest B4xpages
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
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
 
Upvote 0

Albert Kallal

Active Member
Licensed User
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
 
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…