Re: [sqlite] Manifest Typing performance impact?
On Aug 28, 2008, at 3:19 AM, [EMAIL PROTECTED] wrote: >> Is there a performance hit assosiated with manifest typing? > > I'm not sure about performance, but I assume that static typing > might reduce memory usage (especially with small caches). Internally > every type in sqlite is stored in structure that takes 64 bytes > (little more on 64 bit systems). Different structure for every type > or even union would make the size smaller. > The structure that holds a variable in SQLite is 40 bytes on a 32-bit system and 56 bytes on 64-bit. The number of such structures required for an SQLite statement depends on the complexity of the statement, but averages around 13. Assuming the same structure in a rigidly typed system requires 8 bytes (the size of a 64-bit integer) then your total savings will be about 624 bytes on a 64-bit system - less on a 32-bit system. You can save more memory by reducing the size of your disk cache from 2000 to 1999 pages. Note also that the extra code complexity required to support a rigid type system would likely swell the size of the library by several hundred kilobytes. So you would be trading perhaps 250KB of code space for a heap space savings of less than 1KB. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
> Is there a performance hit assosiated with manifest typing? I'm not sure about performance, but I assume that static typing might reduce memory usage (especially with small caches). Internally every type in sqlite is stored in structure that takes 64 bytes (little more on 64 bit systems). Different structure for every type or even union would make the size smaller. -- >> Sprawdz, czy do siebie pasujecie! >> http://link.interia.pl/f1eea ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
On Wed, Aug 27, 2008 at 03:50:56PM +0100, Hardy, Andrew wrote: > Is there any way to log conversions to highlite any issues that would > have been hilighted by failure with strict typing? If the conversions are nicely isolated into functions (static and otherwise) could use the DTrace 'pid' provider on Solaris, and probably on FreeBSD/MacOS X as well. If the conversions are not nicely isolated into functions then SQLite3 could be modified to define DTrace USDT probes that could then be used on Solaris/FreeBSD/MacOS X. I think it'd be cool to modify SQLite3 to define [unstable] USDT probes for all VM opcodes, at the very least, and stable USDT probes probes for statement compilation, execution, ... (If I needed these I'd contribute the code. But I don't, and don't have the time.) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
The Sqlite manifest typing integrates nicely with scripting languages which use similar strategies. Where such integration is not required and there is a well defined application such as an embedded system, particularly with a slower processor, a static typing model would be advantageous in many cases. It would integrate cleanly with a strongly typed language. In a strongly typed environment type checking at run time is avoided and much overhead abolished. It would be Sqextralite to be embedded in the Algol/Pascal or C family languages. Dennis Cote wrote: > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
When I modified Sqlite to block type conversions my memory of it is that the changes were quite simple and easy to implement. You could log the change events with a handful of patches to Sqlite. It would be an interesting exercise to analyze the impact of the conversions. On an aside I discovered a long time ago that analyzing radix changes in commercial type software indicated an enormous overhead which was fairly easily removed by holding numbers in display format whereever possible. Now faster processors make such optimizations less significant. Hardy, Andrew wrote: > Is there any way to log conversions to highlite any issues that would > have been hilighted by failure with strict typing? > > It is my plan to match the column type & data stored type, but clearly > if any conversions are occurring I will be unaware. > > Kind Regards > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote > Sent: 27 August 2008 15:45 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Manifest Typing performance impact? > > Hardy, Andrew wrote: > >> >>Is there a performance hit assosiated with manifest typing? >> > > > I'm sure there is since sqlite must track the type of each data item as > well as its value. But in reality this overhead is quite small. > > >>Is it right that although info on the sqlite site suggests there is an > > >>avilable mode that supports strict typring, this is not infact the > > case? > >> > > > That is correct, the strict affinity mode does not exist. > > >>If there is a performance hit, what are the best ways to minimise > > this? > >>And is there any way to at least log conversions to highlite any >>issues that would have been hilighted by failure with strict typing? >> > > > The best way to minimize the conversion overhead is to store the data in > suitably typed columns (i.e. that match the type of the data stored in > the column). This will avoid any unnecessary conversions when storing, > loading, or comparing the values. > > See http://www.sqlite.org/datatype3.html for the column type affinity > deduction rules. > > HTH > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
I made some changes to Sqlite to stop the automatic type conversions. The performance improvement was not significant. My reason was not to avoid the performance overhead but to prevent the use of floating point where it would raise precision problems. My suggestion would be not to worry about manifest typing as a perfomance drag. Dan wrote: > On Aug 27, 2008, at 4:03 PM, Hardy, Andrew wrote: > > >>Is there a performance hit assosiated with manifest typing? > > > Yes. No. Possibly. Difficult to tell unless somebody creates > and optimizes a version of sqlite that does not do manifest > typing. > > >>Is it right that although info on the sqlite site suggests there is an >>avilable mode that supports strict typring, this is not infact the >>case? > > > Correct. > > Dan. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
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
Re: [sqlite] Manifest Typing performance impact?
Is there any way to log conversions to highlite any issues that would have been hilighted by failure with strict typing? It is my plan to match the column type & data stored type, but clearly if any conversions are occurring I will be unaware. Kind Regards -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: 27 August 2008 15:45 To: General Discussion of SQLite Database Subject: Re: [sqlite] Manifest Typing performance impact? Hardy, Andrew wrote: > > Is there a performance hit assosiated with manifest typing? > I'm sure there is since sqlite must track the type of each data item as well as its value. But in reality this overhead is quite small. > Is it right that although info on the sqlite site suggests there is an > avilable mode that supports strict typring, this is not infact the case? > That is correct, the strict affinity mode does not exist. > If there is a performance hit, what are the best ways to minimise this? > And is there any way to at least log conversions to highlite any > issues that would have been hilighted by failure with strict typing? > The best way to minimize the conversion overhead is to store the data in suitably typed columns (i.e. that match the type of the data stored in the column). This will avoid any unnecessary conversions when storing, loading, or comparing the values. See http://www.sqlite.org/datatype3.html for the column type affinity deduction rules. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
On Aug 27, 2008, at 10:45 AM, Dennis Cote wrote: > Hardy, Andrew wrote: >> >> Is there a performance hit assosiated with manifest typing? >> > > I'm sure there is since sqlite must track the type of each data item > as > well as its value. But in reality this overhead is quite small. >> 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.) 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. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
Hardy, Andrew wrote: > Is there any way to log conversions to highlite any issues that would > have been hilighted by failure with strict typing? > Not that I am aware of. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
Hardy, Andrew wrote: > > Is there a performance hit assosiated with manifest typing? > I'm sure there is since sqlite must track the type of each data item as well as its value. But in reality this overhead is quite small. > Is it right that although info on the sqlite site suggests there is an > avilable mode that supports strict typring, this is not infact the case? > That is correct, the strict affinity mode does not exist. > If there is a performance hit, what are the best ways to minimise this? > And is there any way to at least log conversions to highlite any issues > that would have been hilighted by failure with strict typing? > The best way to minimize the conversion overhead is to store the data in suitably typed columns (i.e. that match the type of the data stored in the column). This will avoid any unnecessary conversions when storing, loading, or comparing the values. See http://www.sqlite.org/datatype3.html for the column type affinity deduction rules. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
On Aug 27, 2008, at 4:03 PM, Hardy, Andrew wrote: > Is there a performance hit assosiated with manifest typing? Yes. No. Possibly. Difficult to tell unless somebody creates and optimizes a version of sqlite that does not do manifest typing. > Is it right that although info on the sqlite site suggests there is an > avilable mode that supports strict typring, this is not infact the > case? Correct. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
