26

I am developing one android database application . I just want to know which code execute faster and what is the difference between them?

Query - 1 =

db.rawQuery("select * from user_table where user_id =" + userId, null);

Query - 2 = 

db.query(USER_TABLE_NAME, ALL_COLUMNS, "user_id = " + userId, null, null, null, null);
Chirag
  • 56,621
  • 29
  • 151
  • 198
  • ...try it and see? I'm going to guess "the same" for all intents and purposes. But, *please use placeholders in either case*. –  Jan 12 '12 at 05:58
  • If you have a build system already set up, do some benchmarks on it. Also, what kind of workload are you expecting and what margins in speed are you looking for? – Shawn Buckley Jan 12 '12 at 06:09

2 Answers2

18

Use query.

Android precompile queries and using too many different raw queries may cause performance issue.

Use rawQuery only when absolutely needed.

Also, both your example have a MAJOR security issue of SQL Injection.

You should sanitize the userId.

The most obvious thing to do is let Android do it by using:

db.query(USER_TABLE_NAME, ALL_COLUMNS, "user_id = ?", new String[] {userId}, null, null, null);
Daniele Segato
  • 12,314
  • 6
  • 62
  • 88
  • 4
    RE precompiled queries: Those only apply when using PreparedStatement. Query() just builds the string and passes it to rawQuery(). – Jon Adams Aug 13 '14 at 23:07
  • Well, PreparedStatement are meant to run the same query multiple time, true, but I think some kind of optimization is also performed by the driver. I've encountered some error when using multiple different queries of android complaining too many statement was provided. :) – Daniele Segato Aug 15 '14 at 20:25
  • @JonAdams Please help me understand your comment, 'only apply when using preparedstatement', where do we use preparedstatement while same can be achieved by rawQuery? (ie you can put '?' , and pass arguments?) – Darpan Apr 24 '15 at 05:54
  • @Darpan Yes, you can still put parameters in all query types. I was just clarifying that the parts about performance in this answer weren't quite correct. – Jon Adams Apr 24 '15 at 12:40
  • @Darpan, more info about prepared statements: http://stackoverflow.com/a/29797229/3681880 – Suragch Apr 27 '15 at 11:53
  • Thank you @Suragch :) this explain what I was referring to talking about performance far better then how I could explain myself – Daniele Segato Apr 29 '15 at 07:25
  • I realize I'm digging up an old thread here, but how is Android automatically sanitizing user_ind in your code? Does this argument in the query() method automatically get sanitized? – intA Oct 25 '16 at 02:11
  • if you use the ? + parameters it just sql escape the parameters – Daniele Segato Oct 25 '16 at 06:58
  • by the way, stumbling here after a lot of time and rereading the comments. @JonAdams was right, precompiled queries only apply to `PreparedStatements`. Query DOES build the query string and execute raw string looking at the code. But I remember stumbling on some kind of error, using `query()` when i used too many different queries string and so I went for the assumption that the framework was performing some kind of optimization. I don't know for sure but I think it would make sense if it has it. – Daniele Segato Jan 29 '18 at 12:06
  • Regardless the performance difference between building the query string and not building it is negligible compared to the actual query so I would still advice for using the cleaner API, the `query()` unless you need to build the raw query programmatically. And definally use positional parameters! (`?`) to avoid SQL injection. – Daniele Segato Jan 29 '18 at 12:07
8

Looking at SQLiteDatabase.java in the android source shows that the query(..) ends up calling the QueryBuilder to build the query as a single string and then it essentially calls rawQuery(). They should be roughly equivalent, assuming that you also did the same work to build your own statement.

dangVarmit
  • 5,641
  • 2
  • 22
  • 24