> A simple "select * from table" query takes twice as longer time in sqlite > compared to sql server express, Both use the same data structure and exactly > the same code except for the connection and command objects.
Everything is the same except providers and principles of how database works. I see the following reasons of possible slowdown: 1) Inefficient implementation of System.Data.Sqlite. Inefficiency could be specifically for your use case, so that if you adjust it to be more SQLite-specific it will work faster. 2) Different data typing principles can lead to lots of type transformations in case of SQLite leading to significant slowdown. 3) SQLite works completely inside your process. Thus when you want to fetch next row you make some function call, it blocks until SQLite make some processing finding the next row. With SQL Server your test effectively works in parallel in 2 processes (and maybe several threads on SQL Server side). So you issue SQL command, block for some time while SQL Server finds first bunch of rows, it gives these rows to you and while you process them SQL Server prepares next rows, so when you need them you don't need to block and get them right away. Pavel On Fri, Aug 12, 2011 at 1:55 PM, Yonnas Beyene <yon...@gmail.com> wrote: > Hi, > We are in the process of migrating our .NET desktop applications database > from sql server express to sqlite (system.data.sqlite provider). As part of > the task we converted one the large client databases to sqlite using an open > source tool and tested some of the common queries. > A simple "select * from table" query takes twice as longer time in sqlite > compared to sql server express, Both use the same data structure and exactly > the same code except for the connection and command objects. This specific > table has just over a million rows and is related with lot of other tables. > The total database size is about 450MB and 130 tables. > My question is does reducing the number of rows (changing the db design) > will help in any way or the query is a function of the total database size > irrespective of number of rows in a table? Related with that is the > performance of a simple direct query affected by the constraints it has in > Sqlite? Is there any way we can optimize the performance? > Thanks, I appreciate your input. > _______________________________________________ > 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
