Query on lot of records

StxLog · 2017-09-13 12:06:10

Hi mORMot community,

First of, I'm sorry if this as already been answered, I didn't knew what to type to find such topic.

I have a table with around 800.000 record in it, and between 5 to 8 fields. What would be the best way to query all those records at once?
We are currently experiencing timeout in a local configuration with a simple ExecuteList(aTable, 'SELECT * FROM MyTable');
The timeout fire before the server can even end processing the request.

We haven't cached anything explicitly for now, and we haven't done any optimization or else as it is the first time we are experiencing slowdown with this great framework.

Thanks for you help.

ab · 2017-09-13 12:31:10

First of all, never write a SELECT in your ORM!

The proper way of doing it is by using paging in your query.

There is no automated paging in the framework, since paging will be highly depending on the backend database, and cursors tend to consume a lot of resources on the server side.
You could try https://synopse.info/forum/viewtopic.ph … 308#p12308 for brute force paging.

What we currently use for paging is to define a parametrized query, with a LIMIT clause and a WHERE condition with an ID or a display field (the order by clause). This is for instance what the sample MVC 30 does, and I also proposed in https://synopse.info/forum/viewtopic.ph … 516#p12516

StxLog · 2017-09-14 14:48:16

I'm slowly switching my mind to ORM but it take time

I've been able to introduce the paging functionnality based on your answer and it works great, thanks!

As for optimization, what should I prefer?

FormatUTF8('ID >= ? AND ID < ? ORDER BY ID', [],
          [aLastID, aLastID+C_MAX_LOADING]));

or

FormatUTF8('ID >= ? ORDER BY ID LIMIT ?', [],
          [aLastID, C_MAX_LOADING]));

What would be the fastest (if there is any difference) in your opinion? (I know it isn't exactly the same result I'm going to have but that's not a problem).

And as of right now, I'm using RetrieveListJSON to execute my SELECT query, and consolidating the resulting JSON in a way you wouldn't aprove (for testing purpose). Do you have any advice for consolidating all the resulting JSON array together?

Thanks for your fast reply, as usual.

ab · 2017-09-14 19:52:50

Limit using id will be as fast as the other, I suppose.
Validate using explain SQL command.
With external db, using only ID comparison without any limit may be the way to go.

oz · 2017-09-15 06:00:42

Imho it is no option to use id>=? and id<? statements for paging functionality because of deleted records. Imagine there are 100 records in your table. Paging should load 10 records for each page. Loading page 3 results in a query like: id>=20 and id<30. Everything will be ok until records will be deleted. If record 20 to 30 are delted then you will get an empty result. If record 23 and 24 are deleted then this query will contain only 8 records instead of 10.
So, go for LIMIT statements.

StxLog · 2017-09-15 08:47:52

@oz I totaly agree with you for a normal paging functionality, but the way I'm using it it wasn't a true problem.

BUT as far as I understand the EXPLAIN command, the LIMIT way seem more fast or at least equal so yes, go for the LIMIT as it would avoid the headaches as stated by @oz.

No advices on a proper-(mORMot)-way to append two JSON array or two TSQLTableJSON?

edwinsn · 2017-09-15 16:00:49

To work around the situation oz described, I think only "ID > ?" should be used with the LIMIT keyword, instead of using "id>=? AND id<?".

Reference: http://sqlite.org/rowvalue.html#scrolli … ow_queries

mORMot Open Source

#1 2017-09-13 12:06:10

Query on lot of records

#2 2017-09-13 12:31:10

Re: Query on lot of records

#3 2017-09-14 14:48:16

Re: Query on lot of records

#4 2017-09-14 19:52:50

Re: Query on lot of records

#5 2017-09-15 06:00:42

Re: Query on lot of records

#6 2017-09-15 08:47:52

Re: Query on lot of records

#7 2017-09-15 16:00:49

Re: Query on lot of records

Board footer