sluggish android app...how do I limit a sql query

Question

An app that I'm working on has started to get sluggish. One of the reasons its getting sluggish is that part of the app is storing gps coordinates. I looked up how to limit the number of records in a sql query. I found this. When I enter the command limit into my code I get an error. Here is the code I've tried:

return mDb.query(DATABASE_TABLE, new String[]{KEY_ROWID, KEY_TITLE, 
                KEY_BODY, KEY_LAT, KEY_LONGI, KEY_DATE}, null, null, null, null,LIMIT 1000);

Just in case you're wondering, the `limit` SQL clause would look like this in an actual query: `select * from table_name where active = true limit 1000;`. (It might make it easier to understand and to solve if you understand how it works in reality.) — Naftuli Kay, Nov 27 '11 at 23:47

score 8 · Answer 1 · answered Oct 18 '12 at 07:04

Since this question still is not provided with a proper solution I want to clear things up.

Much of what was said by user658042 and Jan Dragsbaek is correct. There is an overloaded method, providing the 8th parameter for your limit clause and it also has to be a string.

Since LIMIT can be used either with or without an offset, it is possible to write it in SQL as

LIMIT 1000 // limit to 1000 entries

or with offset

LIMIT 5000, 1000 // limit to 1000 entries with an offset of 5000

Mainly, that is the reason why the limit parameter in the query method must be passed as string.

To conclude, just omit the text "LIMIT" in your limit parameter is the way to go :)

// limit to 1000 entries
return mDb.query(DATABASE_TABLE, 
                 new String[]{ KEY_ROWID, KEY_TITLE, KEY_BODY, KEY_LAT, 
                 KEY_LONGI, KEY_DATE}, 
                 null, null, null, null, null, "1000"); 

// limit to 1000 entries with an offset of 5000
return mDb.query(DATABASE_TABLE, 
                 new String[]{ KEY_ROWID, KEY_TITLE, KEY_BODY, KEY_LAT, 
                 KEY_LONGI, KEY_DATE}, 
                 null, null, null, null, null, "5000, 1000");

Jan Dragsbaek · Answer 2 · 2011-11-27T23:07:09.510

1

By putting your last argument in quotes.

return mDb.query(DATABASE_TABLE, new String[]{KEY_ROWID, KEY_TITLE, KEY_BODY, KEY_LAT, KEY_LONGI, KEY_DATE}, null, null, null, null,"LIMIT 1000");

edited Nov 27 '11 at 23:07

answered Nov 27 '11 at 22:57

Jan Dragsbaek

8,078
2
26
46

thats because it was broke into two lines, i updated the post with it being on one line. – Jan Dragsbaek Nov 27 '11 at 23:07

score 0 · Answer 3 · answered Nov 27 '11 at 23:40

You use a query() method with 7 arguments. The only query-method that has 7 arguments doesn't have one for a limit clause. Chances are that you want to use this one instead, which means you have to add another null before the limit clause:

return mDb.query(DATABASE_TABLE, 
                 new String[]{ KEY_ROWID, KEY_TITLE, KEY_BODY, KEY_LAT, 
                               KEY_LONGI, KEY_DATE}, 
                 null, null, null, null, null, "LIMIT 1000");

The answer from Jan Dragsbaek is also correct, the limit clause is a string, so you have to put it into quotes as well.

"LIMIT 1000" is throwing a invalid LIMIT clauses error. Am I going down the right path to limit the query to 1000 records? — Loren Zimmer, Nov 27 '11 at 23:49

PH7 · Answer 4 · 2011-11-28T13:40:20.553

The problem is with the structure of query method. It requires 7 arguments. The last argument is order by clause. When you put a string "LIMIT 1000" at order by clause place. query method put in for you ORDER BY words. Thus, make SQL Exception.

So, to fix it, you need to put your query this way.

return mDb.query(DATABASE_TABLE, new String[]{KEY_ROWID, KEY_TITLE, 
                KEY_BODY, KEY_LAT, KEY_LONGI, KEY_DATE}, null, null, null, null,"null LIMIT 1000");

A little research pointed me to query method with 8 arguments. Extra one argument being the limit clause. This should solve your problem.

sluggish android app...how do I limit a sql query

4 Answers4