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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
