gpt4 book ai didi

sql - SQLite查询性能:UPDATE与JOIN

转载 作者:行者123 更新时间:2023-12-03 19:41:03 34 4
gpt4 key购买 nike

我已经为此奋斗了几周-如您所见herehere。尽管我得到的答案是正确的,但由于性能问题,它们对我不起作用。下面更多。

我正在使用SQLite。我有一张表,其中的行代表某个时间点的个人。我正在尝试使用另一列(statusLag)的滞后值创建列(status),如下所示:

workerID    timeVar     status      statusLag
---------------------------------------------
1 1 0 NULL
1 2 1 0
1 3 1 1
1 4 1 1
----------------------------------------------
2 3 1 NULL
2 4 0 1
2 5 1 0
2 6 0 1


我尝试过的

方法1:更新

UPDATE myTable
SET statusLag = (SELECT t2.status
FROM myTable t2
WHERE t2.workerID = myTable.workerID AND
t2.timeVar < myTable.timeVar
ORDER BY t2.timeVar desc
LIMIT 1
);


这可以完成我运行的测试。但是,我有一个很大的表-32亿行。我现在可以使用一张3亿行的表工作,此查询使用过多的RAM,超出了我的计算机(12GB)可以处理的内存。因此,出于所有(我的)实际目的,这是行不通的。

方法2:加入

在我看来,我可以参加。遵循以下原则

SELECT t1.*, t2.status as statusLag FROM myTable AS t1
LEFT JOIN myTable AS t2
ON t1.workerID=t2.workerID AND t1.timeVar=t2.timeVar+1
ORDER BY t1.workerID, t1.timeVar ;


我不清楚这是否会做。另外,这是我进行JOIN的第一时间,给我的印象是,仅此查询不会在myTable上插入或更新任何内容。我是否需要补充此查询以完成我在文章开头所解释的内容?

任何想法,任何帮助,不胜感激。我已经为此苦苦挣扎了两个星期,我需要完成它。

最佳答案

您可以尝试的一种策略是将其分解为多个较小的更新。

也就是说,您不会尝试一次更新32亿行。找到一种方法可以将其分成30个1亿行的组,并一次进行一组。

这有两个缺点:


一段时间后,您将更新某些行,而某些行未更新。
您必须找出一种将它们分解为可用块的方法。


但是最大的好处是它可能会(最终)起作用。

就像是:

UPDATE myTable
SET statusLag = (SELECT t2.status
FROM myTable t2
WHERE t2.workerID = myTable.workerID AND
t2.timeVar < myTable.timeVar
ORDER BY t2.timeVar desc
LIMIT 1
)
WHERE companyID = 1;


并为数据库中的每个companyID运行该代码。

要么

     WHERE companyID =>  0 AND companyID < 1000;


等等

重要的是让数据库执行更新并完成事务,然后再移至下一组要更新的记录。如果您尝试将所有事务包装在一个事务中,那么您将面临同样的问题,即必须在动态更新中管理对32亿行的更新。

您可能希望自动执行更新(例如编写一些Java或某些内容来循环浏览CompanyID),或者可以使用体面的文本编辑器简单地创建30或40条SQL语句,然后手动运行它们。

如果您需要维护此数据,那么建议您在创建行时包括滞后数据-一次这样的操作比像这样的大批量操作要容易得多。

关于sql - SQLite查询性能:UPDATE与JOIN,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29245289/

34 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com