IIF vs群组查询中的哪个更快？ [英] IIF vs Where in Group query, what is faster ?

问题描述

我想知道在这种情况下会更快：


我有table1 = {ID，Type，BData，Quantity}


有2种类型{A，B}


如果我想知道A的总和，B

我可以用这个

I want to know what will be faster in this situation:

I have table1 = {ID, Type, BData, Quantity}

There are 2 type {A, B}

if I want to know sum of A, B
I can use this

展开 | 选择 | Wrap | 行号

推荐答案

真正的测试多次运行，看看平均运行时间是多少。但你很可能会发现WHERE更快。

The true test is run both multiple times and see what the average run time is. But you will most likely find that WHERE is faster.

omg，我确实在查询中使用了很多sumiif函数... ==

我有吗将所有这些更改回Where方法？ ：（

omg, I did use a lot of sumiif function in query... ==
Do I have to change all of them back into Where Method ? :(

依赖...是在可接受的时间范围内返回数据。

如果是这样，那么请不要理会

如果没有，那就改变一下。


人们会发现 WHERE 子句往往工作得更快（但在使用小的时候并不明显）数据集）。
IIF（）， [field] IS NULL ，以及其他SQL类型命令将使用小数据集时，比VBA NZ（）或任何域函数（如 DCOUNT（）<更快，但工作速度更快; / code>或 DSUM - 再一次，用户不会注意到性能影响的小型数据集。事实上，我很早就有了在超过5000条记录上使用域功能的数据库，返回时间不到5秒......过去小于2;但是，后端现在在服务器上。


在这种情况下，我认为除非Rabbit，Neopa或其他更多的实验，否则这将是您的判断。 DBA / DD有更好的洞察力。

depends... is the return of data within an acceptable timeframe.
If so, then leave it alone
If not, then change things around.

One will find that WHERE clauses tend to work faster (but not noticeably when using small data sets).
IIF(), [field] IS NULL, and other SQL type commands will tend to work faster, but not noticeably when using small data sets, than the VBA NZ(), or any domain function such as DCOUNT() or DSUM - once again, small datasets the performance hit isn''t likely to be noticed by the user. In-fact, I have an early DB that uses domain functions on over 5000 records and the return is under 5 seconds... used to be less than 2; however, the back-end is on the server now.

In this case, I think that this will have to be a judgment call on your part unless Rabbit, Neopa, or one of the other more experienced DBA/DD have better insight.

这篇关于IIF vs群组查询中的哪个更快？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！