Comments (8)
Why did you comment out offset: pageNum * perPage
? Isn't that required for pagination?
To the actual issue, nothing obvious stands out. Are you using foreign keys for originalId
and roomId
? Did you enable foreign keys with a pragma? That would generate some indexes which might make this query faster.
Alternatively, you could add an index on the originalId
column to find replies more efficiently - but I don't know if that's the actual bottleneck. If you use the profiler from the DevTools, doe calls in the sqlite3
package stand out to be particularly slow? (if we spend a second on the query, it should be visible in the profiler)
from drift.
@simolus3 the reason why I not using pageNation, but simple just query 20, 40, 60, 80 etc, because I can not judge whether the data was last batch, the UI side so complicated to judge, so simple query them all.
But this is not the question, since the laggy happen only first batch, which is only 25 element queried out.
I didn't using foregin key, the originalId is just a table field of messages, not foregin key design.
To be more specically:
- the slow only happens when one room messages more than 500 so, if just 100-200 messgaes, it's fast and blink to query out;
Alternatively, you could add an index on the originalId column to find replies more efficiently
will this more faster?
I think roughly this code can work, but I wanna it be a little bit more faster,
from drift.
the slow only happens when one room messages more than 500 so, if just 100-200 messgaes, it's fast and blink to query out;
This sounds like the runtime query might be quadratic with the amount of messages looked up. A missing index on originalId
is one possible explanation for this, since for every message found, sqlite3 will have to iterate over all messages again just to check whether they are a response.
So you can try adding an index on originalId
and see if it improves things. You can also pull the database file from the device and run the query locally (or use EXPLAIN
) to see what sqlite3 is doing.
from drift.
@simolus3 thank u sir.
A missing index on originalId is one possible explanation for this
Seems no index on originalId can caused repeatly query, make it quadratic.
Do u know how can I add such index in my table? Need to do migrate?
from drift.
Also, I thought a little bit, this line could cause very serious repeated query, no matter what first batch I query, this will query through all data:
final selectReplies = selectOnly(replies)
..addColumns([
FunctionCallExpression('json_group_array', [replies.id])
])
..where(replies.originalId.equalsExp(outerMessages.id));
from drift.
Do u know how can I add such index in my table?
Add a @TableIndex
annotation to your Messages
table class, like
@TableIndex(name: 'messages_original_id', columns: {#originalId})
class Messages extends Table {
Need to do migrate?
Yes, you need to increment the schema version and run m.create(messagesOriginalId)
to create the index after adding it to the table.
Also, I thought a little bit, this line could cause very serious repeated query, no matter what first batch I query, this will query through all data:
Databases apply a bunch of optimizations to the query. When this subquery is executed, the outerMessages.id
value is already known. So the database shouldn't have to go through all messages, just the one with a matching originalId
. But since there is no index, it doesn't have a mechanism of just looking up matching messages. So my assumption is that, after adding an index, that part of the query will be much faster.
But it's important to know that this is just an assumption. To analyze seemingly slow queries, you need to run an EXPLAIN
statement to see whether the assumptions about the missing index are actually true. The easiest way to do that is to use the sqlite3
command line tool on the database in the app.
from drift.
@simolus3 Hi, I ended with created a new table which stores message_id and original_id (for current message replys and the messages replied to current message).
the speed is fast now.
However, my solution seems not same as yours. Possiably your solution maybe even more faster?
from drift.
I don't think so, it's likely that your solution sped it up because sqlite3 will create indices automatically for foreign keys. So your solution is essentially the same as mine.
from drift.
Related Issues (20)
- Cannot test migrations using modular approach with store_date_time_values_as_text: true HOT 4
- Unable to override `runs_before` in `build.yaml` due to `Unrecognized keys` exception HOT 2
- Support remote SQLite databases HOT 8
- drift_flutter package doesn't exist HOT 3
- WEB build db queries returns nothing from a prefilled db HOT 5
- Migration Error when altered same table twice. HOT 1
- Missing `extensions` Import in Generated Code HOT 1
- Add Code Generation for json_serializable HOT 2
- android::register_android_database_SQLiteConnection Android Crash HOT 2
- insertOnConflictUpdate behaves as an insert HOT 8
- Adding unique key that is case-insensitive HOT 1
- `pragma legacy_alter_table = 1` not supported in Turso HOT 4
- [WEB] Can't run a transaction inside `exclusively` HOT 1
- v2.19.0 introduced a breaking change HOT 3
- 404 link in QueryExecutor class documentation HOT 1
- Renew DriftDatabase usage example HOT 1
- feat: Declare column on table with `late final`
- Unhandled Exception: Bad state: Tried to send Request ... over isolate channel, but the connection was closed! HOT 3
- DevTools extension not working in 2.19.1
- [Android web] wasm issue HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from drift.