System.Data.SQLite

Performance of SQLite provider could improve a lot, I just want to point out couple of things hre, although I am not a C++ guy so I could be very wrong

1) P/invoke calls should be reduce as much as possible. All columns data for a particular row could be read at once on each Read() [sqlite3_step call of DataReader, calling through pinvoke for every column to retrieve value is very slow.

2) I heard that there are ways to improve marshalling ...

3) Convert.ChangeType should be avoid. Introduce more TypeInfinities like TypeInfinity.Int32 since sqlite3_column_int function was already available. So ' _typecodeAffinities' array at SQLiteConverter should be corrected and GetValue(SQLiteStatement stmt, int index, SQLiteType typ) of SQLite2 should cater all infinities.

I have tested same amount of data loading of identical schema tables between SQL Server 2008 and SQLite. Being the fact that SQLite was on my machine and I was just running Select * from table. SQLite was 3 times slower. I think SQLite could perform better because when I put filters in the query which reduces the result set to just hundreds of rows, SQLite was performing better. I believe the reason why SQLite is slower is due to massive P/Inoke calls (for each column).

1.  I don't read the contents of a column in a row until you ask for it, this is an optimization to prevent too many p/invoke calls.  Nobody should be calling the GetXXX() functions more than once per column per row -- that's an optimization I leave to the consumer to make.

2.  If you find any ways to improve the marshaling, please let me know.  It'll have to work on both the full and compact frameworks though.

3.  I'll look into this one.

1.Yes you are right. but here is the senerio; I am writing an ORM and like many other it loads all columns data to make an object. Making an object in other provider doesn't take much time what ever they are doing internally seem to be efficient enough. I am afraid that I cannot modify it for myself because you have done a great job and I don't want to spoil it (specially handling datatype mapping). So if it could be posible to provide a flag that load columns at once if set than that would be great (I mean a function in interop libray that returns all columns rather calling get column data iteratively). 

2. Following are the starting points, For quick improvement just put SuppressUnmanagedCodeSecurity attribute. Try to use bittable types like char[ instead of string or StringBuilder if you have used it. Also see IJW I think it is same as C++/CLI Interop. Man missing C++ now ... :)

a) http://social.msdn.microsoft.com/Forums/en-US/vcgeneral/thread/c73bc623-f74c-4e70-b312-4e9df94c1a58/

b) http://msdn.microsoft.com/en-us/library/ky8kkddw.aspx 

3) Thanks

4) I observed that mostly a lazy loading approach is used, for example if Affinity == UnInitialized then initialize SQLiteType.... How ever in my opinion this check for every call to read next data row is overhead. If you could loads type at the time of DataReader creation. than this check could be remove. if somebody call ExecuteDataReader than offcourse he is going to iterate it atleast once. 

Thank you for great library!