> > > 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. > Actually we are planning to use "qualcomm" solution for CDMA which wraps sqlite in its sdk. > > 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. > BTW, I just happened to read documents about "progress-callback" in sqlite website(http://www.sqlite.org/c3ref/progress_handler.html) and it seems this is the best solution for embedded system which gives chances to kick the watch dog periodically without reset the system. Any comment on this callback? > > 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. > agree. > > 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. > understand. > 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. > Interesting. > 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. > good. > 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. > Yes, is there similar ones in MySql? not very sure. > > 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. > This is very helpful. > > > 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. good. > > 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. This is most helpful and most surprise to me. As the name "exec" reminds me some database like oracle, API's. That is why I expect it returns all query result in local and then starting calls the user callback. So, according to your explanation, the "first row returning time" of exec is almost same as prepare/step. This is really a useful message. Thank you. > > > 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). > Thanks again. > 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 > _________________________________________________________________ Attention all humans. We are your photos. Free us. http://go.microsoft.com/?linkid=9666047 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
