Android Question Speed up searching through an array?

[Scotter]

So if I have some arrays (basically, a bunch of data pulled from a few different database tables), simplified:
IdProduct(1) = 359
sProduct(1)="Garage Sale"
IdProduct(2) = 368
sProduct(2) = "Concert"
IdProduct(3) = 399
sProduct(3) = "Wedding"

Created from different read of the database, so the array order is different:
[NOTE I'm not "merely joining" tables in my query because then I get duplicate data returned, since every product has multiple dates]

IdDateBeginProduct(1) = 368
dDateBegin(1) = "1/3/2017"
dDateEnd(1) = "1/3/2017"
IdDateBeginProduct(2) = 359
dDateBegin(2) = "1/19/2017"
dDateEnd(2) = "1/20/2017"
IdDateBeginProduct(3) = 399
dDateBegin(3) = "2/5/2017"
dDateEnd(3) = "2/5/2017"

OK so what I'm looking at is that I'm looking through the products, and FOR EACH PRODUCT, I want to search through the date arrays to find begin and end dates, based on the Product ID:

for i = 0 to IdProductID.length
     IdProduct=IdProduct(i)
     for x = 0 to IDDateBeginProduct.length
          if (IdDateBeginProduct(x)=IDProduct) then
               iThisOne=x
               exit
          end if
     next
     log(sProduct(i) & CRLF)
     log(dDateBegin(iThisOne) & CRLF)
     log(dDateEnd(iThisOne) & CRLF)
next

So... THAT seems like a cumbersome way to do it.
Is there a way to index or otherwise search through arrays?
Or if I use Types, would that make it more efficient?

 

[LucaMs]

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

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).

 

[Erel]

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.

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

 

[KMatle]

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.

 

[Scotter]

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.

Thanks. The data is pulled via a spinner loaded first with [product category], not [product]. The database is highly normalized (many tables).
The recordset might return 5000 records and I don't want to step through each one (product) and then send a query asking for all dates. <-- which I know no one is suggesting, hah.
Someone above mentioned join and group. I understand how join and group work. I had joined the products and dates before but then I could either add "distinct" to get only one date with each product (which was fine for testing but not production). Without distinct, I'd get multiples of product info (name, description, etc.) with each date. Grouping in this case... hmmm... I feel like I'm missing something. Ideas?

 

[Scotter]

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).

Ooh thanks for the tip to sort table BEFORE convert function on date!!!

 

[Scotter]

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.

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

YES you were right that I had indices wrong in my post. Fixed.
This solution seems to fit best my situation. For now, at least.
THANK YOU!

 

[Scotter]

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

Apology for newb question:
It's very important I fill products and dates in two separate subs. Hoping possible.
Can I Type this Product in a global way and in two different subs fill in product info (Id, Name, etc) and date info (DatesBegin, DatesEnd)?
If yes, in what sub do I put the initial Type code? And what does that code look like as far as...

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

 

[Erel]

Custom types should be declared in Sub Process_Globals.

 

[Scotter]

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).

I would LOVE to do this with just one query! I didn't think it was possible. Maybe I'm wrong. I hope so. There are a few tables with many fields but I'll simplify so you can see what I'm doing and maybe you will have an idea:

TABLE Products_Categories
Id, ProductId, CategoryId

TABLE Product
Id, Name, ShortDescription

TABLE Dates
Id, ProductId, Caption, EventDateTime, DisplayOrder

Initially was doing SELECT and RIGHT JOINS for Table Products_Categories, Table Product, and Table Dates after knowing Category ID
Which was returning duplicate product for every product date:
Big Banana (Product name) | Yellow (Product Description) | Start Date (Caption) | 12/2/2016 (EventDateTime)
Big Banana (Product name) | Yellow (Product Description) | End Date (Caption) | 12/3/2016 (EventDateTime)
[If I can't figure out a better way to do this stuff, I'll probably revert back to this way and in my read-database-rows-loop merely look for same product Id and know product is repeat]

Then added DISTINCT to get only first date that goes with each product:
Big Banana (Product name) | Yellow (Product Description) | Start Date (Caption) | 12/2/2016 (EventDateTime)

Is it really possible to write SQL that gets something like this:
Big Banana (Product name) | Yellow (Product Description) | Start Date (Caption) | 12/2/2016 (EventDateTime) | End Date (Caption) | 12/3/2016 (EventDateTime)

 

[KMatle]

TABLE Products_Categories
Id, ProductId, CategoryId

TABLE Product
Id, Name, ShortDescription

TABLE Dates
Id, ProductId, Caption, EventDateTime, DisplayOrder

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

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?

 

[Scotter]

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

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?

I probably shouldn't have mentioned the 5000. A few things about that:
(1) It's actually over 5000 and it's products not categories. I mentioned it only so it was known how impractical it would be to do various methods that might be okay if number of products was like 10. Choosing category from the dropdown narrows it down but yeah some categories have over 5000 products. Yeah, great idea with subcategories. It already exists in the web site but for now, I only want the phone app to show a few very specific categories. That all said, this part is irrelevant to the problem I'm wanting to solve.
(2) I intend, once I get this issue with multiple dates per product, to paginate.
(3) Number of rows really isn't the issue here. The issue is, again, multiple dates per product.
Thanks for caring and sharing your expertise!

 

[KMatle]

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...

 

[Scotter]

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...

THANK YOU! Here's what I'm betting you need.
See attached image and...

SELECT DISTINCT
Product_Category_Mapping.CategoryId
, Product.Id
, Product.Name
, Product.ShortDescription
, Product.Published
, Pvl_AdDates.Caption
, Pvl_AdDates.EventDateTime
, Pvl_AdDates.DisplayOrder
, Pvl_Urls.Caption as CaptionUrl
, Pvl_Urls.Url
FROM Pvl_Urls
RIGHT JOIN (Pvl_AdDates
RIGHT JOIN (Product_Category_Mapping
LEFT JOIN Product ON Product_Category_Mapping.ProductId = Product.Id)
ON Pvl_AdDates.ProductId = Product.Id)
ON Pvl_Urls.ProductId = Product.Id
WHERE (((Product_Category_Mapping.CategoryId)=5) AND ((Product.Published)=True))
ORDER BY
Pvl_AdDates.EventDateTime DESC
, Pvl_AdDates.DisplayOrder;​

 

[Scotter]

Here's the data dump from running that query