更高效的SQLite内部连接和多线程

Question

我的SQLite内部连接在每个数据集10Gb的数据上存在效率问题。你知道吗

我正在运行以下程序

CREATE TABLE merged AS 
  SELECT * FROM xrootd 
  INNER JOIN condor ON 
     SUBSTR(xrootd.[U.mAppInfo], 0, INSTR(xrootd.[U.mAppInfo], '_')) == 
        SUBSTR(condor.User, 6, INSTR(condor.User, '@'))
    AND abs(xrootd.[F.mOpenTime] - condor.JobStartDate) <= 60;

我让它在没有时间限制的情况下运行了一个周末，但是它没有完成，所以我停止了它来添加时间限制，因为我担心它可能会生成大量的数据来解释长时间的运行。你知道吗

SQL列看起来像

sqlite> SELECT [U.mAppInfo], [F.mOpenTime] FROM xrootd LIMIT 5;
U.mAppInfo|F.mOpenTime
drberry_ST_t-channel_4f_leptonDecays_13TeV_1fv53a_156_1_vrm|1439703600.0
drberry_QCD_Pt_600to800_TuneCUETP8M1_13TeV_7ys53a_79_1_oLf|1439705100.0
drberry_ST_t-channel_4f_leptonDecays_13TeV_1fv53a_176_1_uwo|1439703660.0
drberry_QCD_Pt_800to1000_TuneCUETP8M1_13TeV_r5d9t3_72_1_6S1|1439705050.0

sqlite> SELECT User, JobStartDate FROM condor LIMIT 5;
uscms5616@cms|1439764892.0
uscms5050@cms|1439615574.0
uscms5111@cms|1439965560.0
uscms3850@cms|1440081782.0
uscms3850@cms|1440081527.0

有更好的方法吗？我尝试过simple BASH（停止是因为它不是那么简单）和Python（内存溢出）。我可以从CERN获得根树格式的数据，但SQLite似乎更适合于此。你知道吗

我的想法

• 使用预字符串拆分生成新表，即所有SUBSTR（…，INSTR（…））
• 获取一个唯一的用户列表，这些用户同时存在于两个连接中并运行两个内部连接
• 同上，但次数相同
• SQLite多线程，如果有一个简单的方法来做到这一点

我宁愿不运行这些天不知道什么可能更好，但我离开它运行的同时。你知道吗

Answer 1

我首先想到的是，在JOIN中有一个WHERE子句：

AND abs(xrootd.[F.mOpenTime] - condor.JobStartDate) <= 60;

试着把它移到WHERE子句，看看它是否加快了速度：

CREATE TABLE merged AS
SELECT *
FROM xrootd
INNER JOIN condor
ON SUBSTR(xrootd.[U.mAppInfo], 0, INSTR(xrootd.[U.mAppInfo], '_')) ==  SUBSTR(condor.User, 6, INSTR(condor.User, '@'))
WHERE abs(xrootd.[F.mOpenTime] - condor.JobStartDate) <= 60;

---

为了进一步提供帮助，我们需要更多的细节。你知道吗

你声明了什么索引？你知道吗

关于用已经计算的子字符串创建一个新表的观点听起来很有价值，特别是如果您插入一些适当的索引。你知道吗

Answer 2

有没有更好的办法？是的，当您的数据这么大时，不要使用SQLite。使用功能齐全的DBMS。你知道吗