edit: I need to mention that BEGIN and COMMIT were already included in
my insert.
On Fri, Dec 30, 2011 at 10:53 PM, Rick Guizawa <guizaw...@gmail.com> wrote:
> Hi all, I have the following tables:
>
> CREATE TABLE [a] (
> [ItemType] VARCHAR(10) NOT NULL,
> [Item] VARCHAR(60) NOT NULL,
> [LastDate] DATE NOT NULL,
> [NextDate] DATE NOT NULL,
> [Probability] FLOAT NOT NULL,
> [Frequency] INTEGER NOT NULL,
> [TotalFrequency] INTEGER NOT NULL
> )
>
> CREATE TABLE [b] (
> [ID] INTEGER NOT NULL,
> [TheDate] DATE NOT NULL,
> [Item] VARCHAR(35) NOT NULL
> )
>
>
> CREATE TABLE [c] (
> [ItemType] VARCHAR(10) NOT NULL,
> [Item] VARCHAR(60) NOT NULL,
> [SkippedWeeks] INTEGER NOT NULL,
> [Frequency] INTEGER NOT NULL
> )
>
>
> My insert is as follow:
>
> INSERT INTO a
> SELECT 'Item',
> x.Item,
> (SELECT strftime('%Y-%m-%d',TheDate) FROM b WHERE TheDate = (SELECT
> MAX(TheDate) FROM b WHERE Item = x.Item)),
> strftime('%Y-%m-%d', (y.skippedweeks * 7 + (SELECT strftime('%J',
> TheDate) from b WHERE TheDate = (SELECT MAX(TheDate) from b WHERE
> Item = x.Item)))),
> ROUND((CAST (y.frequency AS REAL)/CAST ((SELECT SUM(Frequency) from c
> WHERE item = x.Item) AS REAL) * 100) , 4),
> y.frequency,
> (SELECT SUM(Frequency) from c WHERE item = x.Item)
> FROM b x, c y
> WHERE x.Item = y.Item AND
> y.Frequency = (SELECT MAX(Frequency) FROM c WHERE item = x.Item) GROUP
> BY x.Item;
>
> Using shell sqlite3.exe to process the insert took a long time to
> complete. Table b has about 50000 rows and table c has about 80000
> rows of data. How can I speed up my insert? Please, really appreciate
> any help on this. Thank you.
>
> Cheers,
> Ric
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
