Android Question Speed up searching through an array?

Scotter said:

[NOTE I'm not "merely joining" tables in my query because then I get duplicate data returned, since every product has multiple dates]

Click to expand...

KMatle said:

More important: How is the process the user selects the data?
1. Select product
2. See avaliable date(s)?
I would do it online and a good db-design with indexes on begin/end.

Click to expand...

LucaMs said:

Can't you use anyway a join query with GROUP BY? I think that create a good query should be the best way.
Otherwise you could use a List of custom Type and its SortType. Be careful because the dates in that format will not be ordered well; the format must be "yyyy/MM/dd" or you could convert them to Ticks (long).

Click to expand...

Erel said:

Note that you don't need to add CRLF to log messages.

I think that the indices you wrote in your post are wrong. You probably meant to replace index 1 with 2 and 3.

B4X:

Type Product (Id As Int, Name As String, DatesBegin As List, DatesEnd As List)
...
Dim products As Map
products.Initialize
for i = 0 to IdProductID.length - 1
     Dim p As Product
     p.Initialize
     p.DatesBegin.Initialize
     p.DatesEnd.Initialize
     p.Id = IdProduct(i)
     p.Name = sProduct(i)
next

For i = 0 to IdDateBeginProduct.Length - 1
Dim id As Int = IdDateBeginProduct(i)
Dim dateBegin As String = ...
Dim dateEnd As String = ...
Dim p As Product = products.Get(id)
p.DatesBegin.Add(dateBegin)
p.DatesEnd.Add(dateEnd)
Next

Click to expand...

Erel said:

B4X:

Type Product (Id As Int, Name As String, DatesBegin As List, DatesEnd As List)
...
Dim products As Map
products.Initialize
for i = 0 to IdProductID.length - 1
     Dim p As Product
     p.Initialize
     p.DatesBegin.Initialize
     p.DatesEnd.Initialize
     p.Id = IdProduct(i)
     p.Name = sProduct(i)
next

For i = 0 to IdDateBeginProduct.Length - 1
[INDENT]Dim id As Int = IdDateBeginProduct(i)
Dim dateBegin As String = ...
Dim dateEnd As String = ...
Dim p As Product = products.Get(id)
p.DatesBegin.Add(dateBegin)
p.DatesEnd.Add(dateEnd)[/INDENT]
Next

Click to expand...

Sub ??????
    Type Product (Id As Int, Name As String, DatesBegin As List, DatesEnd As List)
    End Type
End Sub

'If I can get away from this call/wait thing, I would love to. Don't know how.
Using example from
https://www.b4x.com/android/forum/threads/connect-android-to-ms-sql-server-tutorial.13166/

Sub SendSQLforProducts
   'starts an HttpJob and when job complete, calls GetProducts
End Sub

Sub SendSQLforDates
   'starts an HttpJob and when job complete, calls GetDates
End Sub

Sub GetProducts (job as HttpJob)
   'this is where I fill products with data
   Dim parser as JSONParser
   Dim rows As List
   rows = parser.NextArray

   for i = 0 to rows.Size - 1
        Dim p As Product
        Dim m as map
        m = rows.Get(i)
        p.Initialize
        p.Id = m.Get("Id")
        p.Name = m.Get("Name"
        p.DatesBegin.Initialize
        p.DatesEnd.Initialize
   next
End Sub

Sub GetDates
   Dim parser as JSONParser
   Dim rows As List
   rows = parser.NextArray
   Dim products As Map
   products.Initialize
   For i = 0 to IdDateBeginProduct.Length - 1
      Dim id As Int = IdDateBeginProduct(i)
      Dim dateBegin As String = m.Get("dateBegin")
      Dim dateEnd As String = m.Get("dateEnd")
      Dim p As Product = products.Get(id)
      p.DatesBegin.Add(dateBegin)
      p.DatesEnd.Add(dateEnd)
   Next
End Sub

LucaMs said:

Can't you use anyway a join query with GROUP BY? I think that create a good query should be the best way.
Otherwise you could use a List of custom Type and its SortType. Be careful because the dates in that format will not be ordered well; the format must be "yyyy/MM/dd" or you could convert them to Ticks (long).

Click to expand...

Scotter said:

TABLE Products_Categories
Id, ProductId, CategoryId

TABLE Product
Id, Name, ShortDescription

TABLE Dates
Id, ProductId, Caption, EventDateTime, DisplayOrder

Click to expand...

SELECT Product.UPC, Product.Name, Price_h.Price, MAX(Price_h.Date)
FROM Product
INNER JOIN Price_h
   ON Product.Id = Price_h.Product_id
GROUP BY Product.UPC, Product.Name, Price_h.Price

KMatle said:

1. Only fetch the data you really need. Fetching 5000 rows (except you are in an batch environment) looks like a design problem (esp. in an app). Even Amazon does not show that much No user is able to scroll through 5000 categories (is it really 5000?). Think about the internet speed. This will take some time and the users will complain about the huge amount of data to download. On each update the user has to load all the data again. Think about memory issues you might get.
2. Build a pager (Scrollview is nice). Load the first 10 or 20 categories (with LIMIT 20) and if the user wants, load the next 20.
3. Show super-categories (like Amazon: Electronics -> TV's -> LED -> 55")
4. When the user clicks on one category, load the first 20 items/events, etc., then the next 20.
5. When the user clicks on an item/event, load the (available) dates, etc. (maybe preload it)
Of course you can do it with a JOIN like this example

B4X:

SELECT Product.UPC, Product.Name, Price_h.Price, MAX(Price_h.Date)
FROM Product
INNER JOIN Price_h
   ON Product.Id = Price_h.Product_id
GROUP BY Product.UPC, Product.Name, Price_h.Price

but this will take me back to #1: Why 5000 rows?

Click to expand...

KMatle said:

Are you able to zip and send me a db dump of the test environment? I will then build you a proper SQL query including the dates...

Click to expand...