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] -----------------------------------------------------------------------------
