Re: [sqlite] Slow performance
> 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 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
Re: [sqlite] Slow performance
On 12 Aug 2011, at 6:55pm, Yonnas Beyene wrote: > 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. There are many differences between the ways the two systems work. For instance, SSE caches temporary indexes it makes up to answer queries. But SQLite gets far faster if you define suitable indexes for your queries because every query is considered separately. On the other hand, SQLite doesn't get much faster when you use multi-thread/multi-process code because access to the disk is a bottleneck, whereas in SSE the server process is the bottleneck. So you can make some minor changes to your database, like create an index or three, which will dramatically speed up SQLite. I recommend you read the SQLite documentation yourself, or put together a short description of any timing you find puzzlingly slow. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance
Well, I am not an expert on this, but just want to touch some base things 1. Did you set indexes ? Sometime conversion tools didn't do that stuff properly. 2. Is Sql Server reside on same desktop or on LAN ? if it is on LAN probably the server machine is better and hence the comparison is irrelevant. 3. Sqlite in .NET as I see runs in same Memory as application where as Sql Server is obviously has its own process. Having said that if your application is not PARALLEL processing support, you might have little low response for obvious reason. I don't think splitting DB is real good idea, but yeah they do make performance better. I would first try to optimize my query, make sure Datatypes are quite relevant to data, different database has different understanding on them. Hope those help. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Yonnas Beyene Sent: 12 August 2011 23:26 To: sqlite-users@sqlite.org Subject: [sqlite] Slow performance 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
[sqlite] Slow performance
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
Re: [sqlite] Slow performance with Sum function
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Trainor, Chris Sent: Wednesday, March 11, 2009 5:31 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Slow performance with Sum function > Do not be tempted by the incremental vacuum feature. Incremental > vacuum will reduce the database size as content is deleted, but it > will not reduce fragmentation. In fact, incremental vacuum will > likely increase fragmentation. Incremental vacuum is just a variation > on auto_vacuum. It is designed for flash memory with zero seek latency. > D. Richard Hipp > d...@hwaci.com Thanks for the reply, but I am confused again. Is incremental vacuum different from the vacuum command? It seems like vacuum would defragment the database according to the description here: http://www.sqlite.org/lang_vacuum.html = No, the auto_vacuum command differs from the regular vacuum command. The auto_vacuum command does not reduce (and may increase fragmentation). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
> Do not be tempted by the incremental vacuum feature. Incremental > vacuum will reduce the database size as content is deleted, but it > will not reduce fragmentation. In fact, incremental vacuum will > likely increase fragmentation. Incremental vacuum is just a variation > on auto_vacuum. It is designed for flash memory with zero seek latency. > D. Richard Hipp > d...@hwaci.com Thanks for the reply, but I am confused again. Is incremental vacuum different from the vacuum command? It seems like vacuum would defragment the database according to the description here: http://www.sqlite.org/lang_vacuum.html "The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure." If incremental vacuum and vacuum are the same, then I am still uncertain of what to do about my original problem. Any ideas on why the sum function is slow on my existing table, but it is fast on a copy of the table? Also, after calling vacuum, sum is fast on the original table. Here's my original question: I am trying to use the Sum function on a column in a table with ~450K rows in it. Select sum(Col4) from Table1 Where Table1 looks like this: Create TABLE Table1 ( Col1 INTEGER NOT NULL, Col2 INTEGER NOT NULL, Col3 INTEGER NOT NULL, Col4 BIGINT NOT NULL, Col5 BIGINT NOT NULL, Col6 BLOB NOT NULL, Col7 CHAR(1) DEFAULT '0', Col8 NUMERIC(2) NOT NULL, Col9 NUMERIC(2) NOT NULL, Col10 INTEGER NOT NULL, Col11 INTEGER NOT NULL, CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC) ); It takes over 2 minutes to execute when using the original table. I created an exact copy of the table with the same indices and constraints and inserted all the data from the original table into it. Summing that column on the copied table only takes a few seconds. I am guessing that using the copied table is faster because it has all of its data arranged contiguously, but that is just a guess. Can anyone shed some light on this? Making a copy of the table is not an option, so is there anything I can do to get better performance from the original table? Thanks The information contained in this email message and its attachments is intended only for the private and confidential use of the recipient(s) named above, unless the sender expressly agrees otherwise. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws by means of email or in an attachment to email you must select a more secure alternate means of transmittal that supports your obligations to protect such personal data. If the reader of this message is not the intended recipient and/or you have received this email in error, you must take no action based on the information in this email and you are hereby notified that any dissemination, misuse, copying, or disclosure of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email and delete the original message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
Hello! On Wednesday 04 March 2009 17:19:09 Jim Wilcoxson wrote: > Have you tried changing the page size to 4096 or 8192? Doing this > with my SQLite application and increasing the transaction size > decreased runtime from over 4 hours to 75 minutes. The runtime for > my app writing the same amount of data to flat files was 55 minutes, > so the time penalty for building a database was about 35%, which > seemed reasonable. > > I haven't tried changing the cache size yet, because I like that my > app uses a small amount of memory. I have my own build of SQLite with default page size 4096 and increased caches for server applications. For huge databases and SAS disks i'm use page size 16384. Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
Have you tried changing the page size to 4096 or 8192? Doing this with my SQLite application and increasing the transaction size decreased runtime from over 4 hours to 75 minutes.The runtime for my app writing the same amount of data to flat files was 55 minutes, so the time penalty for building a database was about 35%, which seemed reasonable. I haven't tried changing the cache size yet, because I like that my app uses a small amount of memory. Good luck! Jim On 3/4/09, Alexey Pechnikov wrote: > Can enough cache size prevent fragmentation? And how to calculate degree of > fragmentation and when is needed make vacuum of database? > > Best regards. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
Hello! On Wednesday 04 March 2009 04:44:05 D. Richard Hipp wrote: > One could envision future versions > of SQLite that allowed you to preallocate a large database files such > that the database always stayed less than 80% full. Then we could use > filesystem techniques to keep fragmentation down. The penalty, of > course, is that your database file is larger. Probably much larger. > And just to be clear: SQLite does not have that capability at this time. Can enough cache size prevent fragmentation? And how to calculate degree of fragmentation and when is needed make vacuum of database? Best regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
On Mar 3, 2009, at 8:01 PM, Trainor, Chris wrote: > I'm not sure how much we can do about preventing adds and deletes. > It *may* be possible to replace them with updates, but I am not sure > yet. These adds and deletes are happening in a different table than > the one being summed. This other table contains a large blob > column. Would changing to updates help or will updates fragment the > database as much as adds and deletes? SQLite implements an UPDATE by first deleting the old row then inserting a new one in its place. So I don't think changing DELETE/ INSERT pairs into UPDATEs will help much with fragmentation. And, besides, deleting and inserting does not really cause much fragmentation, as long as the data inserted is roughly the same size as the data deleted. Fragmentation occurs for many reasons, but one important reason is that two or more b-trees within the database file are growing at the same time. As each b-tree grows, it needs to allocate new pages. New pages are allocated from the end of the database file (unless there were previously deleted pages that can be reused). Imagine that you have (say) 10 b-trees all growing at roughly the same rate. As the b- trees all grow, they will each allocates pages off the end of the file as they need time. And you will end up with pages of the 10 b-tress all interleaved rather than being bunched together. Note that there is one b-tree for each table and for each index. So if you have a single SQL table with 3 unique columns (there is one implied index for each UNIQUE constraint) and 2 explicit indices, you will have 1+3+2=6 b-trees. As you insert new information into this table, all 6 b-trees are updated together, so there iwill be some interleaving and hence fragmentation. When you run the VACUUM command, it rebuilds each b-tree one by one, so all the pages for a single b-tree are bunched together in the file. Note that using auto_vacuum does *not* help with fragmentation. In fact, auto_vacuum makes fragmentation worse. Auto_vacuum is designed for used on small flash-memory drives (such as found on cell-phones) that have low capacity and zero seek latency. Auto_vacuum is a very helpful feature for the right problem, but fragmentation is not the right problem. When there are free pages in the database file and new pages are needed by a growing b-tree, an attempt is made to reuse free pages that are as close as possible to the rest of the b-tree. But typically the free list is short and the choices are limited, so it does not often happen that the chosen free page is immediately adjacent to the growing b-tree. Decades of experience with filesystems have taught us that various heuristics can prevent filesystem fragmentation, as long as the filesystem is less than about 80% or 90% full. Once a filesystem gets close to being full, fragmentation is inevitable. To transfer this experience to SQLite, recognize that SQLite attempts to keep its database file as small as possible. In other words, SQLite tries to keep itself 100% full at all times. Hence, fragmentation of data in SQLite is pretty much inevitable. One could envision future versions of SQLite that allowed you to preallocate a large database files such that the database always stayed less than 80% full. Then we could use filesystem techniques to keep fragmentation down. The penalty, of course, is that your database file is larger. Probably much larger. And just to be clear: SQLite does not have that capability at this time. > > > The second option is the one I am considering. It looks like there > might be a good time to run vacuum. I need to do some more timings > to tell for sure. Do not be tempted by the incremental vacuum feature. Incremental vacuum will reduce the database size as content is deleted, but it will not reduce fragmentation. In fact, incremental vacuum will likely increase fragmentation. Incremental vacuum is just a variation on auto_vacuum. It is designed for flash memory with zero seek latency. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
The blob is fairly small but is referenced very often, so it wouldn't be feasible to move it to another table. Does the blob make a difference in this case? I thought that since the blob column is to the right of the column being summed that it would never get read during the summation. Thanks -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin Sent: Tuesday, March 03, 2009 7:53 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Slow performance with Sum function On 4/03/2009 5:52 AM, Trainor, Chris wrote: > I am trying to use the Sum function on a column in a table with ~450K > rows in it. > > Select sum(Col4) from Table1 > > Where Table1 looks like this: > > Create TABLE Table1 ( > Col1 INTEGER NOT NULL, > Col2 INTEGER NOT NULL, > Col3 INTEGER NOT NULL, > Col4 BIGINT NOT NULL, > Col5 BIGINT NOT NULL, > Col6 BLOB NOT NULL, What is the min/max/average size of this blob and how often do you need to access it? If the answer tends towards "huge and rarely", consider putting it in a separate table. > Col7 CHAR(1) DEFAULT '0', > Col8 NUMERIC(2) NOT NULL, > Col9 NUMERIC(2) NOT NULL, > Col10 INTEGER NOT NULL, > Col11 INTEGER NOT NULL, > CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1) > ON DELETE CASCADE > ON UPDATE CASCADE, > CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC) > ); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users The information contained in this email message and its attachments is intended only for the private and confidential use of the recipient(s) named above, unless the sender expressly agrees otherwise. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws by means of email or in an attachment to email you must select a more secure alternate means of transmittal that supports your obligations to protect such personal data. If the reader of this message is not the intended recipient and/or you have received this email in error, you must take no action based on the information in this email and you are hereby notified that any dissemination, misuse, copying, or disclosure of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email and delete the original message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
I'm not sure how much we can do about preventing adds and deletes. It *may* be possible to replace them with updates, but I am not sure yet. These adds and deletes are happening in a different table than the one being summed. This other table contains a large blob column. Would changing to updates help or will updates fragment the database as much as adds and deletes? The second option is the one I am considering. It looks like there might be a good time to run vacuum. I need to do some more timings to tell for sure. Thanks for the suggestions. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Tuesday, March 03, 2009 7:41 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Slow performance with Sum function On Tue, Mar 3, 2009 at 6:36 PM, Greg Palmer wrote: > Trainor, Chris wrote: >> After running vacuum, sum is fast on the original table. However, >> running vacuum took a long time, so I'm not sure if that is a feasible >> solution. Is there any way to prevent fragmentation in the first place? >> If not for the whole database, then for a specific table? (e.g. is it >> possible to preallocate space for a table?) >> >> Thanks >> > I'm not an expert on SQLite but generally speaking fragmentation in a > database is usually a result of records being added and deleted. Are you > doing a lot of these and if so, can you change your algorithm to cut > down on this activity? exactly the right approach. Even better, make your application do the vaccuming when your users are away, much like housekeeping in a hotel. > > Regards, > Greg > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users The information contained in this email message and its attachments is intended only for the private and confidential use of the recipient(s) named above, unless the sender expressly agrees otherwise. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws by means of email or in an attachment to email you must select a more secure alternate means of transmittal that supports your obligations to protect such personal data. If the reader of this message is not the intended recipient and/or you have received this email in error, you must take no action based on the information in this email and you are hereby notified that any dissemination, misuse, copying, or disclosure of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email and delete the original message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
On 4/03/2009 5:52 AM, Trainor, Chris wrote: > I am trying to use the Sum function on a column in a table with ~450K > rows in it. > > Select sum(Col4) from Table1 > > Where Table1 looks like this: > > Create TABLE Table1 ( > Col1 INTEGER NOT NULL, > Col2 INTEGER NOT NULL, > Col3 INTEGER NOT NULL, > Col4 BIGINT NOT NULL, > Col5 BIGINT NOT NULL, > Col6 BLOB NOT NULL, What is the min/max/average size of this blob and how often do you need to access it? If the answer tends towards "huge and rarely", consider putting it in a separate table. > Col7 CHAR(1) DEFAULT '0', > Col8 NUMERIC(2) NOT NULL, > Col9 NUMERIC(2) NOT NULL, > Col10 INTEGER NOT NULL, > Col11 INTEGER NOT NULL, > CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1) > ON DELETE CASCADE > ON UPDATE CASCADE, > CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC) > ); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
On Tue, Mar 3, 2009 at 6:36 PM, Greg Palmer wrote: > Trainor, Chris wrote: >> After running vacuum, sum is fast on the original table. However, >> running vacuum took a long time, so I'm not sure if that is a feasible >> solution. Is there any way to prevent fragmentation in the first place? >> If not for the whole database, then for a specific table? (e.g. is it >> possible to preallocate space for a table?) >> >> Thanks >> > I'm not an expert on SQLite but generally speaking fragmentation in a > database is usually a result of records being added and deleted. Are you > doing a lot of these and if so, can you change your algorithm to cut > down on this activity? exactly the right approach. Even better, make your application do the vaccuming when your users are away, much like housekeeping in a hotel. > > Regards, > Greg > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
Trainor, Chris wrote: > After running vacuum, sum is fast on the original table. However, > running vacuum took a long time, so I'm not sure if that is a feasible > solution. Is there any way to prevent fragmentation in the first place? > If not for the whole database, then for a specific table? (e.g. is it > possible to preallocate space for a table?) > > Thanks > I'm not an expert on SQLite but generally speaking fragmentation in a database is usually a result of records being added and deleted. Are you doing a lot of these and if so, can you change your algorithm to cut down on this activity? Regards, Greg ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
After running vacuum, sum is fast on the original table. However, running vacuum took a long time, so I'm not sure if that is a feasible solution. Is there any way to prevent fragmentation in the first place? If not for the whole database, then for a specific table? (e.g. is it possible to preallocate space for a table?) Thanks -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Tuesday, March 03, 2009 2:00 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Slow performance with Sum function Trainor, Chris wrote: > I am trying to use the Sum function on a column in a table with ~450K > rows in it. > > Select sum(Col4) from Table1 > > It takes over 2 minutes to execute when using the original table. I > created an exact copy of the table with the same indices and > constraints and inserted all the data from the original table into > it. Summing that column on the copied table only takes a few seconds. Try running VACUUM on your database. Your original table is probably badly fragmented and results in excessive disk seeking. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users The information contained in this email message and its attachments is intended only for the private and confidential use of the recipient(s) named above, unless the sender expressly agrees otherwise. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws by means of email or in an attachment to email you must select a more secure alternate means of transmittal that supports your obligations to protect such personal data. If the reader of this message is not the intended recipient and/or you have received this email in error, you must take no action based on the information in this email and you are hereby notified that any dissemination, misuse, copying, or disclosure of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email and delete the original message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
Trainor, Chris wrote: > I am trying to use the Sum function on a column in a table with ~450K > rows in it. > > Select sum(Col4) from Table1 > > It takes over 2 minutes to execute when using the original table. I > created an exact copy of the table with the same indices and > constraints and inserted all the data from the original table into > it. Summing that column on the copied table only takes a few seconds. Try running VACUUM on your database. Your original table is probably badly fragmented and results in excessive disk seeking. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Slow performance with Sum function
I am trying to use the Sum function on a column in a table with ~450K rows in it. Select sum(Col4) from Table1 Where Table1 looks like this: Create TABLE Table1 ( Col1 INTEGER NOT NULL, Col2 INTEGER NOT NULL, Col3 INTEGER NOT NULL, Col4 BIGINT NOT NULL, Col5 BIGINT NOT NULL, Col6 BLOB NOT NULL, Col7 CHAR(1) DEFAULT '0', Col8 NUMERIC(2) NOT NULL, Col9 NUMERIC(2) NOT NULL, Col10 INTEGER NOT NULL, Col11 INTEGER NOT NULL, CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC) ); It takes over 2 minutes to execute when using the original table. I created an exact copy of the table with the same indices and constraints and inserted all the data from the original table into it. Summing that column on the copied table only takes a few seconds. I am guessing that using the copied table is faster because it has all of its data arranged contiguously, but that is just a guess. Can anyone shed some light on this? Making a copy of the table is not an option, so is there anything I can do to get better performance from the original table? Thanks The information contained in this email message and its attachments is intended only for the private and confidential use of the recipient(s) named above, unless the sender expressly agrees otherwise. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws by means of email or in an attachment to email you must select a more secure alternate means of transmittal that supports your obligations to protect such personal data. If the reader of this message is not the intended recipient and/or you have received this email in error, you must take no action based on the information in this email and you are hereby notified that any dissemination, misuse, copying, or disclosure of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email and delete the original message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow Performance for INSERT
On Sat, 24 Jan 2009 17:30:29 -0800 (PST), Wajih
wrote in General Discussion of
SQLite Database :
>
>Hello,
>
>I recently wrote a software which decodes some binary data and then insert
>as a record into sqlite database. I check the code execution time and
>following are the results:
>
>Note: FLAG-2 indicates execution time of different
> procedure to decode data
> FLAG-3 shows the time to insert the data
No, it doesn't.
What do FLAG-4 and FLAG-5 indicate?
> Number in front of procedure name shows the time in micro seconds
Not in front of, but behind.
>Can any expert guide me where I am doing wrong?
Since your specifications aren't exact, we have to guess a
lot. So, just some notes for now:
- You don't use transactions. The database will perform much
better if you BEGIN before the first INSERT and COMMIT after
the last one. If there is just one INSERT, it doesn't
matter.
- The performance isn't bad. Total times are 0.169 and 0.158
seconds, repsectively. A blink of an eye. For real time
datacollection of call detail records (or for tail -f of the
primary call detail log) of a large switch it is too slow
indeed.
- One decoding procedure is a candidate for optimizing:
> Flag-2 HEADER FORMAT_VERSION 168443
but perhaps that is a reporting artefact?
- You have a lot of columns in your table. It may be
beneficial to use a large page size.
Hope this helps.
>Execution time:
>
>Flag-2 HEADER CHARGING_BLOCK_SIZE 49
>Flag-2 HEADER TAPE_BLOCK_TYPE 47
>Flag-2 HEADER DATA_LENGTH_IN_BLOCK 46
>Flag-2 HEADER EXCHANGE_ID 52
>Flag-2 HEADER FIRST_RECORD_NUMBER 59
>Flag-2 HEADER BATCH_SEQ_NUMBER 57
>Flag-2 HEADER BLOCK_SEQ_NUMBER 44
>Flag-2 HEADER START_TIME 105
>Flag-2 HEADER FORMAT_VERSION 209
>Flag-2 HEADER FORMAT_VERSION 168443
>Flag-2 Total Time 169642
>Flag-2 46
>Flag-2 PTC RECORD_NUMBER 101
>Flag-2 PTC RECORD_STATUS 46
>Flag-2 PTC CHECK_SUM 1569
>Flag-2 PTC CALL_REFERENCE 72
>Flag-2 PTC EXCHANGE_ID 19
>Flag-2 PTC INTERMEDIATE_RECORD_NUMBER 23
>Flag-2 PTC INTERMEDIATE_CHARGING_IND 43
>Flag-2 PTC NUMBER_OF_SS_RECORDS 23
>Flag-2 PTC CALLING_NUMBER_TON 113
>Flag-2 PTC CALLING_NUMBER 37
>Flag-2 PTC CALLED_NUMBER_TON 98
>Flag-2 PTC CALLED_NUMBER 35
>Flag-2 PTC OUT_CIRCUIT_GROUP 38
>Flag-2 PTC OUT_CIRCUIT 38
>Flag-2 PTC IN_CHANNEL_ALLOCATED_TIME 73
>Flag-2 PTC CHARGING_START_TIME 70
>Flag-2 PTC CHARGING_END_TIME 76
>Flag-2 PTC CAUSE_FOR_TERMINATION 1406
>Flag-2 PTC CALL_TYPE 65
>Flag-2 PTC TICKET_TYPE 48
>Flag-2 PTC OAZ_CHRG_TYPE 84
>Flag-2 PTC OAZ_DURATION 53
>Flag-2 PTC OAZ_TARIFF_CLASS 45
>Flag-2 PTC OAZ_PULSES 39
>Flag-2 PTC CALLED_MSRN_TON 103
>Flag-2 PTC CALLED_MSRN 35
>Flag-2 PTC INTERMEDIATE_CHRG_CAUSE 197
>Flag-2 PTC LEG_CALL_REFERENCE 59
>Flag-2 PTC OUT_CHANNEL_ALLOCATED_TIME 72
>Flag-2 PTC BASIC_SERVICE_TYPE 43
>Flag-2 PTC BASIC_SERVICE_CODE 272
>Flag-2 PTC CALL_REFERENCE_TIME 72
>Flag-2 PTC CUG_INTERLOCK 70
>Flag-2 PTC CUG_OUTGOING_ACCESS 65
>Flag-2 PTC CUG_INFORMATION 44
>Flag-2 PTC SCP_CONNECTION 47
>Flag-2 PTC NUMBER_OF_IN_RECORDS 23
>Flag-2 PTC NUMBER_OF_ALL_IN_RECORDS 23
>Flag-2 PTC OUTSIDE_USER_PLANE_INDEX 39
>Flag-2 PTC OUTSIDE_CONTROL_PLANE_INDEX 38
>Flag-2 PTC OUT_BNC_CONNECTION_TYPE 69
>Flag-2 PTC LOC_ROUTING_NUMBER 39
>Flag-2 PTC LOC_ROUTING_NUMBER_TON 96
>Flag-2 PTC NPDB_QUERY_STATUS 71
>Flag-2 PTC IN_CIRCUIT_GROUP_NAME 26
>Flag-3 PTC OUT_CIRCUIT_GROUP_NAME 20
>Flag-4 Insert time 151590
>Flag-5 Total Time 158077
>
>
>My Code is as below:
>This is one time statement and does account for the
>long time as the table was created once only
># PSTN Terminating Call
>db1 eval {CREATE TABLE PTC(
> RECORD_LENGTH text,
> RECORD_TYPE text,
> RECORD_NUMBER text,
> RECORD_STATUS text,
> CHECK_SUM text,
> CALL_REFERENCE text,
> EXCHANGE_ID text,
> INTERMEDIATE_RECORD_NUMBER text,
> INTERMEDIATE_CHARGING_IND text,
> NUMBER_OF_SS_RECORDS text,
> CALLING_NUMBER_TON text,
> CALLING_NUMBER text,
> CALLED_NUMBER_TON text,
> CALLED_NUMBER text,
> OUT_CIRCUIT_GROUP text,
> OUT_CIRCUIT text,
> IN_CHANNEL_ALLOCATED_TIME text,
> CHARGING_START_TIME text,
> CHARGING_END_TIME text,
> CAUSE_FOR_TERMINATION text,
> CALL_TYPE text,
> TICKET_TYPE text,
> OAZ_CHRG_TYPE text,
> OAZ_DURATION text,
> OAZ_TARIFF_CLASS text,
> OAZ_PULSES text,
> CALLED_MSRN_TON text,
> CALLED_MSRN text,
> INTERMEDIATE_CHRG_CAUSE text,
> LEG_CALL_REFERENCE text,
> OUT_CHANNEL_ALLOCATED_TIME text,
> BASIC_SERVICE_TYPE text,
> BASIC_SERVICE_CODE text,
> CALL_REFERENCE_TIME text,
> CUG_INTERLOCK text,
> CUG_OUTGOING_ACCESS text,
> CUG_INFORMATION text,
> SCP_CONNECTION text,
> NUMBER_OF_IN_RECORDS text,
> NUMBER_OF_ALL_IN_RECORDS text,
> OUTSIDE_USER_PLANE_INDEX text,
> OUTSIDE_CONTROL_PLAN
[sqlite] Slow Performance for INSERT
Hello,
I recently wrote a software which decodes some binary data and then insert
as a record into sqlite database. I check the code execution time and
following are the results:
Note: FLAG-2 indicates execution time of different procedure to decode data
FLAG-3 shows the time to insert the data
Number in front of procedure name shows the time in micro seconds
Can any expert guide me where I am doing wrong?
Execution time:
Flag-2 HEADER CHARGING_BLOCK_SIZE 49
Flag-2 HEADER TAPE_BLOCK_TYPE 47
Flag-2 HEADER DATA_LENGTH_IN_BLOCK 46
Flag-2 HEADER EXCHANGE_ID 52
Flag-2 HEADER FIRST_RECORD_NUMBER 59
Flag-2 HEADER BATCH_SEQ_NUMBER 57
Flag-2 HEADER BLOCK_SEQ_NUMBER 44
Flag-2 HEADER START_TIME 105
Flag-2 HEADER FORMAT_VERSION 209
Flag-2 HEADER FORMAT_VERSION 168443
Flag-2 Total Time 169642
Flag-2 46
Flag-2 PTC RECORD_NUMBER 101
Flag-2 PTC RECORD_STATUS 46
Flag-2 PTC CHECK_SUM 1569
Flag-2 PTC CALL_REFERENCE 72
Flag-2 PTC EXCHANGE_ID 19
Flag-2 PTC INTERMEDIATE_RECORD_NUMBER 23
Flag-2 PTC INTERMEDIATE_CHARGING_IND 43
Flag-2 PTC NUMBER_OF_SS_RECORDS 23
Flag-2 PTC CALLING_NUMBER_TON 113
Flag-2 PTC CALLING_NUMBER 37
Flag-2 PTC CALLED_NUMBER_TON 98
Flag-2 PTC CALLED_NUMBER 35
Flag-2 PTC OUT_CIRCUIT_GROUP 38
Flag-2 PTC OUT_CIRCUIT 38
Flag-2 PTC IN_CHANNEL_ALLOCATED_TIME 73
Flag-2 PTC CHARGING_START_TIME 70
Flag-2 PTC CHARGING_END_TIME 76
Flag-2 PTC CAUSE_FOR_TERMINATION 1406
Flag-2 PTC CALL_TYPE 65
Flag-2 PTC TICKET_TYPE 48
Flag-2 PTC OAZ_CHRG_TYPE 84
Flag-2 PTC OAZ_DURATION 53
Flag-2 PTC OAZ_TARIFF_CLASS 45
Flag-2 PTC OAZ_PULSES 39
Flag-2 PTC CALLED_MSRN_TON 103
Flag-2 PTC CALLED_MSRN 35
Flag-2 PTC INTERMEDIATE_CHRG_CAUSE 197
Flag-2 PTC LEG_CALL_REFERENCE 59
Flag-2 PTC OUT_CHANNEL_ALLOCATED_TIME 72
Flag-2 PTC BASIC_SERVICE_TYPE 43
Flag-2 PTC BASIC_SERVICE_CODE 272
Flag-2 PTC CALL_REFERENCE_TIME 72
Flag-2 PTC CUG_INTERLOCK 70
Flag-2 PTC CUG_OUTGOING_ACCESS 65
Flag-2 PTC CUG_INFORMATION 44
Flag-2 PTC SCP_CONNECTION 47
Flag-2 PTC NUMBER_OF_IN_RECORDS 23
Flag-2 PTC NUMBER_OF_ALL_IN_RECORDS 23
Flag-2 PTC OUTSIDE_USER_PLANE_INDEX 39
Flag-2 PTC OUTSIDE_CONTROL_PLANE_INDEX 38
Flag-2 PTC OUT_BNC_CONNECTION_TYPE 69
Flag-2 PTC LOC_ROUTING_NUMBER 39
Flag-2 PTC LOC_ROUTING_NUMBER_TON 96
Flag-2 PTC NPDB_QUERY_STATUS 71
Flag-2 PTC IN_CIRCUIT_GROUP_NAME 26
Flag-3 PTC OUT_CIRCUIT_GROUP_NAME 20
Flag-4 Insert time 151590
Flag-5 Total Time 158077
My Code is as below:
This is one time statement and does account for the long time as the table
was created once only
# PSTN Terminating Call
db1 eval {CREATE TABLE PTC(
RECORD_LENGTH text,
RECORD_TYPE text,
RECORD_NUMBER text,
RECORD_STATUS text,
CHECK_SUM text,
CALL_REFERENCE text,
EXCHANGE_ID text,
INTERMEDIATE_RECORD_NUMBER text,
INTERMEDIATE_CHARGING_IND text,
NUMBER_OF_SS_RECORDS text,
CALLING_NUMBER_TON text,
CALLING_NUMBER text,
CALLED_NUMBER_TON text,
CALLED_NUMBER text,
OUT_CIRCUIT_GROUP text,
OUT_CIRCUIT text,
IN_CHANNEL_ALLOCATED_TIME text,
CHARGING_START_TIME text,
CHARGING_END_TIME text,
CAUSE_FOR_TERMINATION text,
CALL_TYPE text,
TICKET_TYPE text,
OAZ_CHRG_TYPE text,
OAZ_DURATION text,
OAZ_TARIFF_CLASS text,
OAZ_PULSES text,
CALLED_MSRN_TON text,
CALLED_MSRN text,
INTERMEDIATE_CHRG_CAUSE text,
LEG_CALL_REFERENCE text,
OUT_CHANNEL_ALLOCATED_TIME text,
BASIC_SERVICE_TYPE text,
BASIC_SERVICE_CODE text,
CALL_REFERENCE_TIME text,
CUG_INTERLOCK text,
CUG_OUTGOING_ACCESS text,
CUG_INFORMATION text,
SCP_CONNECTION text,
NUMBER_OF_IN_RECORDS text,
NUMBER_OF_ALL_IN_RECORDS text,
OUTSIDE_USER_PLANE_INDEX text,
OUTSIDE_CONTROL_PLANE_INDEX text,
OUT_BNC_CONNECTION_TYPE text,
LOC_ROUTING_NUMBER text,
LOC_ROUTING_NUMBER_TON text,
NPDB_QUERY_STATUS text,
IN_CIRCUIT_GROUP_NAME text,
OUT_CIRCUIT_GROUP_NAME text)}
Insert Statement (in TCL):
switch -exact -- $Variable_Record_Type {
HEADER { db1 eval "INSERT INTO HEADER($Variable_Column_Name) VALUES
($Variable_Column_Value)"}
TRAILER { db1 eval "INSERT INTO TRAILER($Variable_Column_Name) VALUES
($Variable_Column_Value)"
MOC { db1 eval "INSERT INTO MOC($Variable_Column_Name) VALUES
($Variable_Column_Value)"
MTC { db1 eval "INSERT INTO MTC($Variable_Column_Name) VALUES
($Variable_Column_Value)"
FORW{ db1 eval "INSERT INTO FORW($Variable_Column_Name) VALUES
($Variable_Column_Value)"
and so on... }
where $Variable_Column_Name and $Variable_Column_Values are list
--
View this message in context:
http://www.nabble.com/Slow-Performance-for-INSERT-tp21647577p21647577.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-us
Re: [sqlite] Slow performance - Unrealistic expectations?
Thom Ericson wrote: Marian Olteanu wrote: You should embed the inserts into a transaction. Otherwise every insert is a separate ACID transaction = 2 disk spins. I thought putting 128 of the inserts between "BEGIN;" and "COMMIT;" did that. Am I confused? Thom, You are correct about putting 128 inserts between the begin and commit. You may get even higher performance if you use a larger batch size of 500 or 1000 inserts. This amortizes the journal file creation, data copies, and deletion over more inserts. Right now you are doing about 4 transactions per second. With a batch size of 1000 you might get 1 transaction per second, but 1000 inserts per second. Its easy to do, so its probably worth a try. HTH Dennis Cote
Re: [sqlite] Slow performance - Unrealistic expectations?
> You should embed the inserts into a transaction. Otherwise every insert > is a > separate ACID transaction = 2 disk spins. > > > I thought putting 128 of the inserts between "BEGIN;" and "COMMIT;" did > that. Am I confused? No, what you did was correct. Just for grins: If you have a 7200rpm drive you should theoretically be able to get: 7200 rpm / 2 rotations per transaction = 3600 transactions / minute 3600 transactions per minute / 60 seconds per minute = 60 transactions per second. The limiting factor would seem to be how many inserts can you generate and push through in 1/60th of a second. > > This may have been the secret (and what Jay was trying to tell me > above). I pulled out the 'KEY' and the 'NOT NULL' on str and parent, > and added the PRIMARY KEY. Performance started out somewhat higher, > around 1000 inserts per second and seems to have leveled off at 500+ > inserts per second. I'm already up to 500,000 entries in my test. I'd > like even better performance, but this is passable. I have not started > the testing of my query code to see if there are any new problems. On very large loads it's much faster to turn off indexes, load the data, then create the index you'll use to read it. You don't pay for the index tree rebalancing over and over on all the inserts. You might not be able to do that in your case since your data is inter-related. Sorry if I was too opaque in my response! Glad you got it fixed.
Re: [sqlite] Slow performance - Unrealistic expectations?
See in-line comments below -- Jay Sprenkle wrote: Did you put an index on the table/columns the select uses to lookup the rowids of the parents? I though that was what declaring 'str' as KEY and 'parent' as KEY was supposed to do, but see Dennis Cote's response below: Marian Olteanu wrote: You should embed the inserts into a transaction. Otherwise every insert is a separate ACID transaction = 2 disk spins. I thought putting 128 of the inserts between "BEGIN;" and "COMMIT;" did that. Am I confused? Dennis Cote wrote: Try this table definition instead. CREATE TABLE xyz ( str TEXT, parent INTEGER, PRIMARY KEY(parent, str) ); This will create an index to greatly speed the lookups of the existing rows when you do your inserts. This may have been the secret (and what Jay was trying to tell me above). I pulled out the 'KEY' and the 'NOT NULL' on str and parent, and added the PRIMARY KEY. Performance started out somewhat higher, around 1000 inserts per second and seems to have leveled off at 500+ inserts per second. I'm already up to 500,000 entries in my test. I'd like even better performance, but this is passable. I have not started the testing of my query code to see if there are any new problems. Thanks T On 4/6/06, Thom Ericson <[EMAIL PROTECTED]> wrote: I am trying to pick a light weight database for a project. SQLite, on paper, seems like the right choice, but performance is rather disappointing. I hope it is just that I am doing something wrong. I have built SQLite for Solaris and Win32 environments and I get essentially the same results. As I started adding more rows to my single table the performance drops off (expected). I start off getting about 500 inserts per second, but after 180,000 rows, the performance has dropped to around 3 inserts per second. I had hoped to be able to handle 180,000,000 rows in my largest installation (that's gonna take a while). The table is very simple: sqlite3 testdb << EOF CREATE TABLE xyz ( str TEXT KEY NOT NULL, parent INTEGER KEY NOT NULL ); The "str" column is typically 10 to 20 characters and the "parent" column is the ROWID of some pre-existing row in the table (except for the first row, where the parent is zero). For each new row added to the table, there is, on average, one select performed that produces the rowid of another entry given specific values for str and parent. I enclose about 128 of these selects and insert within a "BEGIN;"/"COMMIT;" block which increased performance, but going to higher values does not seem to help much. With the Solaris installation, I don't see much disk activity (possibly all blocks are cached), on windows, I am seeing around 22,000 I/O Reads per second.
Re: [sqlite] Slow performance - Unrealistic expectations?
Thom Ericson wrote: I am trying to pick a light weight database for a project. SQLite, on paper, seems like the right choice, but performance is rather disappointing. I hope it is just that I am doing something wrong. I have built SQLite for Solaris and Win32 environments and I get essentially the same results. As I started adding more rows to my single table the performance drops off (expected). I start off getting about 500 inserts per second, but after 180,000 rows, the performance has dropped to around 3 inserts per second. I had hoped to be able to handle 180,000,000 rows in my largest installation (that's gonna take a while). The table is very simple: sqlite3 testdb << EOF CREATE TABLE xyz ( str TEXT KEY NOT NULL, parent INTEGER KEY NOT NULL ); The "str" column is typically 10 to 20 characters and the "parent" column is the ROWID of some pre-existing row in the table (except for the first row, where the parent is zero). For each new row added to the table, there is, on average, one select performed that produces the rowid of another entry given specific values for str and parent. I enclose about 128 of these selects and insert within a "BEGIN;"/"COMMIT;" block which increased performance, but going to higher values does not seem to help much. With the Solaris installation, I don't see much disk activity (possibly all blocks are cached), on windows, I am seeing around 22,000 I/O Reads per second. Any hints from anyone T Thom, Try this table definition instead. CREATE TABLE xyz ( str TEXT, parent INTEGER, PRIMARY KEY(parent, str) ); This will create an index to greatly speed the lookups of the existing rows when you do your inserts. HTH Dennis Cote
RE: [sqlite] Slow performance - Unrealistic expectations?
You should embed the inserts into a transaction. Otherwise every insert is a separate ACID transaction = 2 disk spins. -Original Message- From: Thom Ericson [mailto:[EMAIL PROTECTED] Sent: Thursday, April 06, 2006 10:18 AM To: sqlite-users@sqlite.org Subject: [sqlite] Slow performance - Unrealistic expectations? I am trying to pick a light weight database for a project. SQLite, on paper, seems like the right choice, but performance is rather disappointing. I hope it is just that I am doing something wrong. I have built SQLite for Solaris and Win32 environments and I get essentially the same results. As I started adding more rows to my single table the performance drops off (expected). I start off getting about 500 inserts per second, but after 180,000 rows, the performance has dropped to around 3 inserts per second. I had hoped to be able to handle 180,000,000 rows in my largest installation (that's gonna take a while). The table is very simple: sqlite3 testdb << EOF CREATE TABLE xyz ( str TEXT KEY NOT NULL, parent INTEGER KEY NOT NULL ); The "str" column is typically 10 to 20 characters and the "parent" column is the ROWID of some pre-existing row in the table (except for the first row, where the parent is zero). For each new row added to the table, there is, on average, one select performed that produces the rowid of another entry given specific values for str and parent. I enclose about 128 of these selects and insert within a "BEGIN;"/"COMMIT;" block which increased performance, but going to higher values does not seem to help much. With the Solaris installation, I don't see much disk activity (possibly all blocks are cached), on windows, I am seeing around 22,000 I/O Reads per second. Any hints from anyone T
Re: [sqlite] Slow performance - Unrealistic expectations?
Thom Ericson wrote: I had hoped to be able to handle 180,000,000 rows in my largest installation (that's gonna take a while). Any hints from anyone IMHO 180 mln rows is no small deal even for a big database - especially considering you have got text column in it. If you are trying to implement something like full-text search, then you are better off creating your own highly tuned for your purposes custom database. Alex
Re: [sqlite] Slow performance - Unrealistic expectations?
On 4/6/06, Thom Ericson <[EMAIL PROTECTED]> wrote: > I am trying to pick a light weight database for a project. SQLite, on > paper, seems like the right choice, but performance is rather > disappointing. I hope it is just that I am doing something wrong. > > I have built SQLite for Solaris and Win32 environments and I get > essentially the same results. As I started adding more rows to my > single table the performance drops off (expected). I start off getting > about 500 inserts per second, but after 180,000 rows, the performance > has dropped to around 3 inserts per second. I had hoped to be able to > handle 180,000,000 rows in my largest installation (that's gonna take a > while). The table is very simple: > > sqlite3 testdb << EOF > CREATE TABLE xyz > ( > str TEXT KEY NOT NULL, > parent INTEGER KEY NOT NULL > ); > > The "str" column is typically 10 to 20 characters and the "parent" > column is the ROWID of some pre-existing row in the table (except for > the first row, where the parent is zero). For each new row added to the > table, there is, on average, one select performed that produces the > rowid of another entry given specific values for str and parent. I > enclose about 128 of these selects and insert within a > "BEGIN;"/"COMMIT;" block which increased performance, but going to > higher values does not seem to help much. > > With the Solaris installation, I don't see much disk activity (possibly > all blocks are cached), on windows, I am seeing around 22,000 I/O Reads > per second. Did you put an index on the table/columns the select uses to lookup the rowids of the parents?
[sqlite] Slow performance - Unrealistic expectations?
I am trying to pick a light weight database for a project. SQLite, on paper, seems like the right choice, but performance is rather disappointing. I hope it is just that I am doing something wrong. I have built SQLite for Solaris and Win32 environments and I get essentially the same results. As I started adding more rows to my single table the performance drops off (expected). I start off getting about 500 inserts per second, but after 180,000 rows, the performance has dropped to around 3 inserts per second. I had hoped to be able to handle 180,000,000 rows in my largest installation (that's gonna take a while). The table is very simple: sqlite3 testdb << EOF CREATE TABLE xyz ( str TEXT KEY NOT NULL, parent INTEGER KEY NOT NULL ); The "str" column is typically 10 to 20 characters and the "parent" column is the ROWID of some pre-existing row in the table (except for the first row, where the parent is zero). For each new row added to the table, there is, on average, one select performed that produces the rowid of another entry given specific values for str and parent. I enclose about 128 of these selects and insert within a "BEGIN;"/"COMMIT;" block which increased performance, but going to higher values does not seem to help much. With the Solaris installation, I don't see much disk activity (possibly all blocks are cached), on windows, I am seeing around 22,000 I/O Reads per second. Any hints from anyone T
[sqlite] Slow performance - Unrealistic expectations?
I am trying to pick a light weight database for a project. SQLite, on paper, seems like the right choice, but performance is rather disappointing. I hope it is just that I am doing something wrong. I have built SQLite for Solaris and Win32 environments and I get essentially the same results. As I started adding more rows to my single table the performance drops off (expected). I start off getting about 500 inserts per second, but after 180,000 rows, the performance has dropped to around 3 inserts per second. I had hoped to be able to handle 180,000,000 rows in my largest installation (that's gonna take a while). The table is very simple: sqlite3 testdb << EOF CREATE TABLE xyz ( str TEXT KEY NOT NULL, parent INTEGER KEY NOT NULL ); The "str" column is typically 10 to 20 characters and the "parent" column is the ROWID of some pre-existing row in the table (except for the first row, where the parent is zero). For each new row added to the table, there is, on average, one select performed that produces the rowid of another entry given specific values for str and parent. I enclose about 128 of these selects and insert within a "BEGIN;"/"COMMIT;" block which increased performance, but going to higher values does not seem to help much. With the Solaris installation, I don't see much disk activity (possibly all blocks are cached), on windows, I am seeing around 22,000 I/O Reads per second. Any hints from anyone T
