Android Question [[SOLVED]Loading Photos From SQLite D/Base Takes Time

Big Dave

Member
Licensed User
Longtime User
A am writing an app for myself, to take pictures of my expense receipts, store them in a blob field in an SQLite database and refer to them as needed. When I load the data and photos into a custom list view it takes about 2 seconds to load 4 records. Not sure if it is an issue with the picture size or quality, or the way I am handling the clv loading. I would be happy to store them as photo files on my phone if that is more efficient. ECS10 takes the picture whilst ECS20 loads the clv. I am using a Samsung Galaxy S9+ phone
 

Attachments

  • ExpenseClaimsSystem2.zip
    62.4 KB · Views: 245

Alex_197

Well-Known Member
Licensed User
Longtime User
A am writing an app for myself, to take pictures of my expense receipts, store them in a blob field in an SQLite database and refer to them as needed. When I load the data and photos into a custom list view it takes about 2 seconds to load 4 records. Not sure if it is an issue with the picture size or quality, or the way I am handling the clv loading. I would be happy to store them as photo files on my phone if that is more efficient. ECS10 takes the picture whilst ECS20 loads the clv. I am using a Samsung Galaxy S9+ phone
Just a question - why you store a whole image in blob but not a file name in text field? You can store images separately in a folder and then show them in your in your list. By the way - using web view with HTML is better solution.
 
Upvote 0

Big Dave

Member
Licensed User
Longtime User
Width = 4032 Height = 3024 Somewhat large!! Given that width is larger than height would I be correct in assuming this is why my pictures are displayed rotated by 90 degrees. I have coded to correct this. I am going to change the picture size and see how this improves loading time.
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
I am going to change the picture size and see how this improves loading time.

Occasionally , I do store the Images directly in a DB ... but resized to the maximum required display size at when writing to the db.

In your case the CLV imageview is 120x120. so stored at that size might work / be better.

But in saying that .. (and knowing the source of images) you might need to view the original at some point in time without distortion, so ...

option 1 ... Create a small image to be stored in the db / displayed in clv.... Store the original in a folder with a db path reference should you need to view later.

option 2 ... Create a small image and store this image and original in separate folders with db path reference to both
 
Last edited:
Upvote 0

Big Dave

Member
Licensed User
Longtime User
Erel & mj. I have identified all available picture sizes and reduced the size to its minimum. Trying to set a non available picture size resulted in a parameter error so I went with the minimum. I can increase sizes if I need to but for now loading times are vastly improved. mj, your points about storing the pictures in a folder may come into play down the line when I fully test the app and print the pictures. Thank you for your help both.
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
Trying to set a non available picture size resulted in a parameter error
Unsure where this comes from .. you would need to elaborate if you wish to pursue further ...

when I fully test the app and print the pictures.

This obviously points out that you will want to store a decent sized original for print clarity and small thumbnail for clv display.

Anyway ... glad to see you making headway .
 
Upvote 0

Big Dave

Member
Licensed User
Longtime User
Trying to set a non available picture size resulted in a parameter error

I tried to set a picture size of 370 x 350 equivalent to the panel size in ECS10 where I take the photo. It returned a parameter error so I changed it to one of the available picture sizes as returned from camEx.GetSupportedPicturesSizes.

when I fully test the app and print the pictures.

As I am taking pictures of receipts and invoices the picture quality does not have to be high. When I get to that stage of development I will be looking for a quality/size/speed compromise.

It has been a frustrating couple of days trying to get my head round these new areas of programming but I feel happy that I am making progress and your assistance is greatly appreciated.
 
Upvote 0

Alex_197

Well-Known Member
Licensed User
Longtime User
Erel & mj. I have identified all available picture sizes and reduced the size to its minimum. Trying to set a non available picture size resulted in a parameter error so I went with the minimum. I can increase sizes if I need to but for now loading times are vastly improved. mj, your points about storing the pictures in a folder may come into play down the line when I fully test the app and print the pictures. Thank you for your help both.
here is my code - I'm storing images in a folder and images names in the database. To show them I'm using ListView filled ImageView

Show Photos:
private Sub GetPhoto
    
    Try
        
        Dim MySQL As String
        Dim Cursor1 As Cursor
        Dim FileName As String
        Dim j As Int
        Dim ID As Int
        svList.Panel.RemoveAllViews
        
        ItemHeight=200dip
        
        
        MySQL="select * from tblNotesMediaFiles where PlacementLocalID=? and NoteHour=? and JobDate=? And Shift=? And FileType='Photo'"
        Cursor1=SQL1.ExecQuery2(MySQL,Array As String(Main.SelectedPlacementLocalID,NoteEdit.VMHour,Main.SelectedDOS,Main.SelectedShift))
                
        Cursor1.Position=0
                
        If Cursor1.RowCount=0 Then
            Cursor1.Close
            Return
        End If
        
        For i=0 To Cursor1.RowCount-1
            
            Cursor1.Position=i
            FileName=Cursor1.GetString("FileName")
            ID=Cursor1.GetInt("ID")
        
            Dim IV As ImageView
            Dim bp As Bitmap=GetBitmap(FileName)
        
            If bp=Null Then
                Continue
            End If
        
            j=j+1
        
            IV.Initialize("IV")
            IV.Tag=FileName & "~" & ID
            IV.Bitmap=bp
            
            svList.Panel.AddView(IV,0, ItemHeight * (j-1), svList.Width, ItemHeight - 2dip)
        Next
        
        svList.Panel.Height= ItemHeight *j
        
    Catch
        Log("GetPhoto " & LastException)
        modFun.ShowError("GetPhoto " & LastException)
    End Try
    
End Sub
 
Upvote 0

Big Dave

Member
Licensed User
Longtime User
Alex. Thanks for your coding example. If the loading time becomes an issue I will be saving the pictures as files but at the moment I wish to keep everything together. The app is only for my use and I will be doing some stress testing before I put it into use to see which way to go. Either way, with what I am doing I am learning. Again thanks.
 
Upvote 0

Alex_197

Well-Known Member
Licensed User
Longtime User
Alex. Thanks for your coding example. If the loading time becomes an issue I will be saving the pictures as files but at the moment I wish to keep everything together. The app is only for my use and I will be doing some stress testing before I put it into use to see which way to go. Either way, with what I am doing I am learning. Again thanks.
One more reason to store images in a folder that if your database will be corrupted you will not lose your files.
 
Upvote 0

Big Dave

Member
Licensed User
Longtime User
Alex. I did a quick stress test and it took near 30 seconds to load 1000 records into the custom list view. Also the scrolling in the custom list view was too slow to be effective. Do you happen to know if loading from a file is any quicker?. Your point about database corruption is a good one, and also if I lost my phone, so I will take your suggested route. If scrolling is still slow I will look at other options like batch loading.
 
Upvote 0

Alex_197

Well-Known Member
Licensed User
Longtime User
Alex. I did a quick stress test and it took near 30 seconds to load 1000 records into the custom list view. Also the scrolling in the custom list view was too slow to be effective. Do you happen to know if loading from a file is any quicker?. Your point about database corruption is a good one, and also if I lost my phone, so I will take your suggested route. If scrolling is still slow I will look at other options like batch loading.
I think that from a file should be quicker, you can load a file directly into bitmap
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
Alex. I did a quick stress test and it took near 30 seconds to load 1000 records


@BigDave ... Before you go further , maybe you should think if it is necessary to load 1000 + records immediately , rather than a small batches when needed.

 
Upvote 0

Big Dave

Member
Licensed User
Longtime User
Hi mj. I made the decision yesterday to batch load the details by month with control buttons in the view. I have coded it and it works for me so going to stick with that for now. I have also decided to take the pictures out of the database and store them as files. Then if I need to I can handle them externally. Really appreciated your input and pointers with this.
 
Upvote 0
Top