On Mon, Sep 21, 2015 at 5:48 AM, Richard Hipp <drh at sqlite.org> wrote:
> On 9/21/15, Dominique Devienne <ddevienne at gmail.com> wrote: > > On Sun, Sep 20, 2015 at 4:10 PM, Igor Tandetnik <igor at tandetnik.org> > wrote: > > > >> On 9/20/2015 9:55 AM, gwenn wrote: > >> > >>> If there is no way to know that the statement has been recompiled, I > >>> guess that the column count should not be cached... > >>> > >> > >> You could use sqlite3_prepare (no _v2), then you'd get an error on > schema > >> change. You would then re-prepare the statement and update your caches. > > > > > > Could perhaps also use the change counter > > https://www.sqlite.org/fileformat2.html#chngctr, if you're not using WAL > > mode. > > You'd get false positives I guess, since both DML and DDL changes would > > increment it, and I'm not sure that's any different from checking the > > column_count anyway, but just in case it's useful. --DD > > PRAGMA schema_version > (https://www.sqlite.org/pragma.html#pragma_schema_version) does what > you want. > > But here the thing: It is probably far more expensive to run PRAGMA > schema_version than it is to just rerun sqlite3_column_count(). I > think this whole conversation is an exercise in premature > optimization. Has anybody actually *measured* a performance problem > with sqlite3_column_count()? > I have not, and I don't have the environment to check. The original premise as I understand it was that a function call was not expensive because the API function itself was expensive, but transitions through the language binding in use might be too expensive. I indicated the same thought last night, that this might be premature optimization, but even if it is not, I can't imagine any way to get this information (that the schema changed so discard cached column count) without making *some* api call, and all api calls will have the same problem: an expensive language binding transition. One person did suggest using sqlite3_prepare instead of v2, since it will return a schema change error code. My gut instinct is that it'll be easier / at least as performant to just continue to use v2 and not cache column count after finishing stepping / resetting a statement. But as indicated, I can't check that. The only reason I jumped into this however is that several responses weren't answering the asked question, assuring the OP that what was being asked couldn't happen. It can and does by design. In order to cache info beyond a statement reset, there needs to be a way to invalidate it. In any case, I think the knowledge on the list has exhausted its ability to answer. To summarize: 1. Checking the schema version will result in at least one language binding transition (two if the version changed), whereas checking the column count will always only be one transition. Might as well just check the column count. 2. Using prepare vs prepare_v2 would return a schema error, at which point the caller could finalize / re-prepare the statement and update his cached values. This still involves extra language binding transitions to handle the schema change that would have been automatically dealt with by prepare_v2. OP would have to test to see if it benefits his use case, though it seems unlikely to be any faster than using the easier v2 interface and calling column count after each first step. -- Scott Robison
