基于以下集合:
data_invoices (document, 100,000 total records, 2 tenants)
hash: tenantId
persistent: createdOn
data_jobs (document, 10,000 total records, 2 tenants)
hash: tenantId
persistent: createdOn
data_links (edge, 100,000 total records)
persistent: createdOn
persistent (sparse): replacedOn
链接集合会将一张发票连接到随机作业,因此一个作业可能具有零张或多张发票。 发票应具有一个或多个作业,但在我的数据中,每张发票仅与一个作业匹配。date
过滤器实际上不会过滤掉任何数据(它们都小于指定的日期值),tenantId
过滤器也不会过滤,因为所有数据都是xxx
或yyy
的。
data_jobs和data_invoices的通用结构为:
tenantId: string;
createdOn: number;
data: [{
createdOn: number;
values: {
...collection specific data here...
};
}];
data_invoices
的集合特定数据结构为:
number: number;
amount: number;
data_jobs
的集合特定数据结构为:
name: string;
data_links表的结构为:
createdOn: number;
replacedOn?: number; // though I don't have any records with this value set
createdOn
字段是表示为 1970 年刻度的日期值,是从 2000 年 1 月 1 日至今的随机日期。
amount
字段是从 10 到 10,000 的随机货币值(小数点后 2 位)。
number
字段是自动编号类型字段。
我有两个非常相似的(在我看来)查询,一种方式(作业到发票)非常非常快地工作,另一种方式需要很长时间。
此查询需要 1.85 秒:
LET date = 1495616898128
FOR job IN data_jobs
FILTER job.tenantId IN ['xxx', 'yyy']
FILTER job.createdOn<=date
LET jobData = (job.data[* FILTER CURRENT.createdOn<=date LIMIT 1])[0]
FILTER CONTAINS(jobData.values.name, 'a')
LET invoices = (
FOR invoice, link IN 1 INBOUND job data_links
FILTER link.createdOn<=date AND (link.replacedOn == NULL OR
link.replacedOn>date)
LET invoiceData = (invoice.data[* FILTER CURRENT.createdOn<=date LIMIT 1])[0]
FILTER invoiceData.values.amount>1000
COLLECT WITH COUNT INTO count
RETURN {
count
}
)[0]
FILTER invoices.count>0
SORT jobData.values.name ASC
LIMIT 0,8
RETURN job
此查询需要 8.5 秒:
LET date = 1495616898128
FOR invoice IN data_invoices
FILTER invoice.tenantId IN ['xxx', 'yyy']
FILTER invoice.createdOn<=date
LET invoiceData = (invoice.data[* FILTER CURRENT.createdOn<=date LIMIT 1])[0]
FILTER invoiceData.values.amount>1000
LET jobs = (
FOR job, link IN 1 OUTBOUND invoice data_links
FILTER link.createdOn<=date AND (link.replacedOn == NULL
OR link.replacedOn>date)
LET jobData = (job.data[* FILTER CURRENT.createdOn<=date LIMIT 1])[0]
FILTER CONTAINS(jobData.values.name, 'a')
COLLECT WITH COUNT INTO count
RETURN {
count
}
)[0]
FILTER jobs.count>0
SORT invoiceData.values.amount ASC
LIMIT 0,8
RETURN invoice
我意识到两个查询都提供不同的数据,但处理时间应该相同,不是吗? 它们都通过链接表筛选两个表,并在另一个表上执行聚合。 我不明白为什么一种方法比另一种方式快得多。 请问我能做些什么来提高这些查询的性能吗?
好吧,很奇怪,但我偶然发现了一个非常违反直觉(至少对我来说)的解决方案。 先排序,然后过滤...???
此查询现在需要 1.4 秒:
LET date = 1495616898128
FOR invoice IN data_invoices
FILTER invoice.tenantId IN ['xxx', 'yyy']
FILTER invoice.createdOn<=date
LET invoiceData = (invoice.data[* FILTER CURRENT.createdOn<=date LIMIT 1])[0]
SORT invoiceData.values.amount ASC
FILTER invoiceData.values.amount>1000
LET jobs = (
FOR job, link IN 1 OUTBOUND invoice data_links
FILTER link.createdOn<=date AND (link.replacedOn == NULL
OR link.replacedOn>date)
LET jobData = (job.data[* FILTER CURRENT.createdOn<=date LIMIT 1])[0]
FILTER CONTAINS(jobData.values.name, 'a')
COLLECT WITH COUNT INTO count
RETURN {
count
}
)[0]
FILTER jobs.count>0
LIMIT 0,8
RETURN invoice
尽管在data[*].values.amount
上添加了一个持久索引,但它仍然不使用它(我什至尝试了SORT invoice.data[0].values.amount ASC
它似乎仍然没有使用该索引?
谁能解释一下?