D. Richard Hipp wrote:> > I was going to guess the opposite - that manifest typing reduces > overhead. (But as Dan pointed out - nobody will know until somebody > generates a version of SQLite that uses static typing and compares the > performance.) >
I agree with Dan on this point. > The reason I think static typing would make things slower is that with > static typing, there has to be a bunch of checking during processing > to verify the specified datatype is in use. With the current database > file format, this checking must be done at query run-time. And there > is no savings in not having to track the types of each data item at > run-time because the current file format allows dynamic typing. So > any "strict affinity" mode would likely be slower than the current > SQLite. > > If you designed a new file format that did not allow dynamic typing at > the file format layer, then you could perhaps do away with tracking of > types at query run-time. But if you go with a completely new file > format, you really wouldn't be dealing with SQLite any more. So I'm > not sure the comparison would be valid. > > Note that if you really, really want to do static typing in SQLite you > can implement it using CHECK constraints: > > CREATE TABLE ex(a INTEGER CHECK( typeof(a)='integer' )); > > A "strict affinity" mode in SQLite would amount to adding these check > constraints automatically. If you look at it from this point of view, > it seems likely that strict affinity would slow down performance due > to the added cost of checking type constraints at each step. > I think the benefit of a static typing system is that those checks are not done at all at run time. They are done once when the statement is compiled. After that the code can be execute many millions of times (i.e. for millions of rows) without the need for any type checking at runtime because the compiler did the necessary checks. There is no need for a check constraint as you have shown, since the compiler would only generate code to insert integer values into integer columns. If only integer values can be inserted, there is no need to check the type of the values when they are retrieved (even if the file format supports dynamic typing). Now, data pulled from the tables can be assumed to be of the expected type and used directly. This may simplify subsequent processing. The trade off is that the compiler may become more complex and the compilation step may take longer. There may still be a net benefit if the compilation time is only a small percentage of the statement's execution time (i.e complex long running queries on large tables). Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users