Performance advice

[Rigoberto12]

I want to optimize a query in my application involving the loading of blobs, which are the most important data for the main functionality of the application. Currently, the query is slow due to the time it takes to load blobs from the database. I need a solution that significantly improves the performance of this query, ensuring that blobs are loaded efficiently and quickly.

This query uses flow in kotlin to return a list. It is about 40k records and each record has a blob, it takes around 16-17sec.

I don’t know if I’m making a mistake with my query, but this is how I access the blob.

 val queryBuilder = QueryBuilder.select(
                    SelectResult.expression(Meta.id).`as`("metaId"),
                    SelectResult.property("type"),
                    SelectResult.property("template")
                ).from(DataSource.collection(db.defaultCollection!!).`as`("item"))
                    .where(Expression.property("type")
                        .`in`(*types.filterNotNull().map { Expression.intValue(it) }
                            .toTypedArray()))


                val results = queryBuilder.execute().map { row ->
                   RegistryT(
                        type = row.getInt("type"),
                        template = row.getBlob("template")?.content,
                        metaId = row.getString("metaId")
                    )
                }

I identify that the amount of time it is taking is due to this. I also understand that the blobs are stored in a different section of the database. But this data is needed.

     template = row.getBlob("template")?.content,

I would like to know if there is any way I can improve this or if I’m wrong.

Issues:

1. Slow Performance: The current query takes a long time due to blob loading.
2. Importance of Blobs: Blobs are critical for the main functionality of the application, so improving their loading is essential.
3. Need for Speed: It is crucial that the query is fast, as efficiency in data loading affects user experience and overall application performance.

[Rigoberto12]

I think I can sacrifice these seconds to load all the blobs, it’s really not a lot of time. I will prepare all the data in the splash screen with a loading status. So, users won’t notice it.

[jens]

Loading all the blobs at once probably isn’t a good idea for RAM usage or performance. If you skip the .content I believe you’ll just get the Blob objects, which are very lightweight; then you can iterate over them getting their contents.

Also, is there a good reason for storing the critical app data in blobs, and needing them all up-front? I don’t know what these blobs are, but if you don’t need all the data of each blob up-front, can you add the necessary bits as properties of the document and use those?