> I am currently involved in porting sqlite on mobile phone As an aside, you do realize that most smartphone OSes already have SQLite available? WinCE, iPhone OS, Symbian, PalmOS, Pre, and Android either have SQLite libs built-in to the SDK, or have a version of SQLite that compiles without modification. If you're using one of those environments, there is no reason to re-port SQLite unless you need a cutting-edge feature that just appeared in a newer version.
> And that's why I am interested to see if the first awailable row > can return ASAP. This gets back to the original presumption that if you want it sorted, you want it sorted, and all the wishing and hoping isn't going to change the fact that sorting is often more computationally complex than returning rows in random order. The database engine will do what it takes to return a sorted result. If that takes more time, the database engine doesn't care. The database engine will not return an incorrect unsorted result if you asked for a sorted result, regardless of the API. If you have an extremely tight reset timer you have to deal with, you can just have the rows returned and sort them yourself. The overall time is not likely to be much faster, but if you don't do the sort in one pass, you can keep the phone from resetting. > And if "prepare-step" can not do better than "execute" or "GetTable", As I said before, prepare/step is the only way to get data out of the database. sqlite3_exec() and sqlite3_get_table() call prepare and step internally. exec and get_table are just wrapper functions. There is nothing special about them. You could write identical functions yourself with the public prepare/step API. > then what is meaning to use "prepare-step"? That's a long discussion. I'd suggest you start here: http://sqlite.org/cintro.html A quick summary: 1) The "prepare" process is fairly expensive. Using bound variables, you can prepare a statement once and use it over and over without having to re-prepare it. 2) Prepare/step is required to use bound variables. Bound variables prevent SQL injection attacks and a number of other issues. In general, you should be using bound variables and not doing string manipulations on SQL statements. If it was up to me, functions like sqlite3_exec, sqlite3_get_table, and sqlite3_vmprintf wouldn't be included in the API unless you compiled SQLite with SQLITE_BIG_SECURITY_HOLE or some similar flag. 3) Prepare/step is generally faster and uses less memory. All the other APIs return results as strings, which requires further processing and memory management. 4) The step API allows you to break out of queries mid-way through and allows better processing and flow-control. I'd also point out that the prepare/step paradigm is not unique to SQLite. Most database APIs have a similar construction, even if they use different terms. > if I create a view with clause of "order by" to ask sqlite to sort > on that index. Can I get sorted result by querying the view with > prepare-step? Sure, but it is going to take the same amount of processing as just issuing whatever the underlying query is. Views don't pre-sort or cache results, they're more like named sub-SELECTs. > > sqlite3_step() returns rows as they become available. You're reading > > too deeply into "as they become available", however. The database > > engine is still required to return the correct result set. If the > > query needs to be sorted, it needs to be sorted. That may or may not > > require computing the full result set before returning the first row. > > It depends on the query. But either way, the database will do the > > right thing and return the correct results. > According to your explanation, it seems the sorting prevents > "prepare-step" from returning faster than "execute". If you're talking about "time to first row returned", then yes. The time it takes for the first row to be available via step when using prepare/step and the time it takes for your first callback using exec is going to be the same. exec is actually a pretty thin wrapper. All it does is call prepare on the SQL you've provided, then calls step. Each time it calls step, it extracts the results and calls your callback. The exec callback is called once per row, just like step. Both APIs should provide more or less the same performance in all situations. > the implication is that without using "order by" clause the prep > are-step API would return much quicker than "execute" because it > doesn't have to wait for all result set being available. sqlite3_exec doesn't wait for the whole result set to be available either. Take a closer look at the sqlite3_exec API call. It doesn't return anything other than an error code. You provide exec with a callback function, which is called once for each row in the result set. sqlite3_exec does not have to wait for the whole result to be available in order to call your callback. It can start to call your callback as soon as a row is available, just as step can return as soon as a result is available (which is exactly what it does). sqlite3_get_table is another story. It has to collect the whole result set before it returns. The total time should be about the same as any other API, but the "time until first row" is much different. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
