加快在循环中计算文档类型的plpgsql? [英] Speed up plpgsql that counts doc types in a loop?

问题描述

有没有一种方法可以加快我们的plpgsql函数的使用，该函数在循环执行的一个查询中对某些类型的文档进行计数?全部查询一次?

Is there a way to speed up our plpgsql function that counts certain types of docs all in one query which is executed in a loop? ALL in one query?

validador := (select count(id_doc) from webdte.doc_tip_cifra
              where id_doc = id_documento and id_tipo_cifra = 901); 

validador2 := (select count(id_doc) from webdte.doc_tip_cifra
               where id_doc = id_documento and id_tipo_cifra = 902); 

validador3 := (select count(id_doc) from webdte.doc_tip_cifra
               where id_doc = id_documento and id_tipo_cifra = 905); 

validador4 := (select count(id_doc) from webdte.doc_tip_cifra
               where id_doc = id_documento and id_tipo_cifra = 907); 

推荐答案

在一个查询(仅对一个表或索引扫描)中分配所有四个变量应该更快:

It should be faster to assign all four variables in one query (only one table or index scan):

SELECT INTO validador, validador2, validador3, validador4
            sum(CASE id_tipo_cifra WHEN 901 THEN 1 ELSE 0 END)
           ,sum(CASE id_tipo_cifra WHEN 902 THEN 1 ELSE 0 END)
           ,sum(CASE id_tipo_cifra WHEN 905 THEN 1 ELSE 0 END)
           ,sum(CASE id_tipo_cifra WHEN 907 THEN 1 ELSE 0 END)
FROM   webdte.doc_tip_cifra
WHERE  id_doc = id_documento;

结果相同.

通常，您还必须另外检查id_doc中是否包含NULL，但是由于您具有带有=的WHERE条件，因此不能为NULL.

Normally you would have to check id_doc for NULL in addition, but since you have a WHERE condition with = on it, it cannot be NULL.

这篇关于加快在循环中计算文档类型的plpgsql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！