RE: [sqlite] Insert statement taking too long
You need to create an index on the columns you're joining. Otherwise I believe 100,000,000 rows (10k x 10k) in table 2 will be scanned while SQLite looks for matches against the 10,000 rows in table 1. > -Original Message- > From: Unit 5 [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 08, 2006 10:47 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Insert statement taking too long > > Hello, > > I am seeing an interesting performance issue with > INSERT statements. I am using Sqlite 3.3.5 on a > Windows XP box. Here is a brief summary of the > situation: > > Insert statements that require no join are quite fast: > a) I create an empty table (drop it first if it > exists). > b) INSERT ... SELECT ... FROM another-table WHERE ... > > > But, if the Insert statement requires a join, the > performance degrades drastically: > > a) I create an empty table (drop it first if it > exists). > b) INSERT ... SELECT ... FROM table-1 JOIN table-2 > WHERE ... > > Even when the two joined tables have 10,000 records > each and those records match one to one, the query > goes from taking a second or so in the first case to > over 30-40 minutes in the second case. The processing > is CPU intensive and pretty much locks down the PC > during this process. > > Is this a common experience or do I need to play > around with the configuration options? > > > > > > > __ > __ > Want to start your own business? > Learn how on Yahoo! Small Business. > http://smallbusiness.yahoo.com/r-index > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Insert statement taking too long
--- Robert Simpson <[EMAIL PROTECTED]> wrote: > You need to create an index on the columns you're > joining. Otherwise I > believe 100,000,000 rows (10k x 10k) in table 2 will > be scanned while SQLite > looks for matches against the 10,000 rows in table > 1. While that makes sense, I suspect there is something else going on. I did a few more tests. For example, if I remove the INSERT but keep the exact same SELECT statement with the joins, it is fast again. So, it seems that it is quite slow when doing the insert's. I was thinking that perhaps the statement was not in a transaction, but I tried that too. Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Insert statement taking too long
Unit 5 uttered: --- Robert Simpson <[EMAIL PROTECTED]> wrote: You need to create an index on the columns you're joining. Otherwise I believe 100,000,000 rows (10k x 10k) in table 2 will be scanned while SQLite looks for matches against the 10,000 rows in table 1. While that makes sense, I suspect there is something else going on. I did a few more tests. For example, if I remove the INSERT but keep the exact same SELECT statement with the joins, it is fast again. So, it seems that it is quite slow when doing the insert's. I was thinking that perhaps the statement was not in a transaction, but I tried that too. Try both the INSERT and the plain SELECT using EXPLAIN QUERY PLAN, which will give an indication of which indexes are being used. I'd hazard a guess that the INSERT case is not using the same query plan as the plain select case. sqlite> EXPLAIN QUERY PLAN INSERT ... SELECT ... FROM table-1 JOIN table-2 ... sqlite> EXPLAIN QUERY PLAN SELECT ... FROM table-1 JOIN table-2 ... Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Insert statement taking too long
- Original Message From: Unit 5 <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, November 9, 2006 8:02:51 AM Subject: RE: [sqlite] Insert statement taking too long > --- Robert Simpson <[EMAIL PROTECTED]> wrote: > > You need to create an index on the columns you're > > joining. Otherwise I > > believe 100,000,000 rows (10k x 10k) in table 2 will > > be scanned while SQLite > > looks for matches against the 10,000 rows in table > > 1. > While that makes sense, I suspect there is something > else going on. > > I did a few more tests. For example, if I remove the > INSERT but keep the exact same SELECT statement with > the joins, it is fast again. So, it seems that it is > quite slow when doing the insert's. I was thinking > that perhaps the statement was not in a transaction, > but I tried that too. Could it just be that your data set is just too big and doesn't fit in memory? Your statement most likely results in random inserts in the target table. Talking about this, is there a way to tell sqlite to put "holes" in the file so that when doing random inserts (even in a transaction), only portions of the file need to be moved around? It would waste some disk space, but for improved performance (it's a trade-off), I would be willing to give away large amount of disk. I know this is quite the opposite of what (auto) vacuum does but when data doesn't fit in memory and most access is random there is not much performance benefit in having the data not sparse in the DB file. The "holes" could be recreated from time to time to ensure the sparseness of the db file (hence giving a guaranty on insert times). Nicolas
