If you always include the same calculations in your queries (same field + or - the same other field, etc) you can create computed columns with persisted values.
Currently your queries will be slow because the engine is running a complicated mathematical operation for each row.
If you add a column with the results, the math is all done once and then it will be a lot faster to run queries.
You can precompute c = 1 + c1 + ... + c50 on the database side and a = a1 + ... + a50 on the client side. Then the query then reduces to ... WHERE @a > c. This opens an opportunity to use an index.
However, floating point numbers do not index well in most databases (including SQL Server). If we can make some assumptions about the data, we might be able to work around this. For example, if the numbers are only stored to two digits of precision as in the example, then we can multiply all the numbers by 100 to obtain integers. Then, indexing will work well. Reasonably well, that is... it depends on how many rows meet the condition. Half of "millions of rows" is still a lot of rows.
Even if the values have truly variable precision, so two digits are not accurate enough, it might still make sense to create the integer index to reduce the rows that need to be checked. The query can check both the approximate value (to hit the index) and the exact value (to get the precise result). If you do that, make sure that the original values are rounded in the right direction to avoid losing precise results.
发布评论
评论（4）
您可以继续使用 SQL Server 并使用持久计算列来计算总和所有的值和索引。
然后，您可以将查询重新排列为：
该查询将能够使用计算列上的索引，并且应该快速找到相关行。
You can continue using SQL Server and use a persisted computed column that calculates the sum of all the values and index that.
Then you can rearrange your query as:
This query will be able to use the index on the computed column and should find the relevant rows quickly.
要坚持使用 SQL Server：
如果您始终在查询中包含相同的计算（相同的字段 + 或 - 相同的其他字段等），您可以创建 具有持久值的计算列。
目前，您的查询速度会很慢，因为引擎正在为每一行运行复杂的数学运算。
如果您添加包含结果的列，则所有数学运算都完成一次，然后运行查询会快得多。
To stick with SQL Server:
If you always include the same calculations in your queries (same field + or - the same other field, etc) you can create computed columns with persisted values.
Currently your queries will be slow because the engine is running a complicated mathematical operation for each row.
If you add a column with the results, the math is all done once and then it will be a lot faster to run queries.
内存数据库是最好的。看看 http://hsqldb.org/
取决于您有多少百万行......
An in memory database would be best. Have a look at http://hsqldb.org/
Depending on how many millions of rows you have...
您的查询条件可以重写为：
您可以在数据库端预先计算
c = 1 + c1 + ... + c50，并a = a1 + ... + a50在客户端。然后查询简化为... WHERE @a > c.这为使用索引提供了机会。但是，浮点数在大多数数据库（包括 SQL Server）中都不能很好地建立索引。如果我们可以对数据做出一些假设，我们也许可以解决这个问题。例如，如果像示例中的数字仅存储两位数的精度，那么我们可以将所有数字乘以 100 以获得整数。然后，索引就会很好地工作。还算不错，那就是……取决于有多少行满足条件。 “数百万行”的一半仍然是很多行。
即使这些值具有真正可变的精度，因此两位数不够准确，创建整数索引以减少需要检查的行仍然可能有意义。查询可以检查近似值（以命中索引）和精确值（以获取精确结果）。如果这样做，请确保原始值按正确方向舍入，以避免丢失精确结果。
Your query condition can be rewritten as:
You can precompute
c = 1 + c1 + ... + c50on the database side anda = a1 + ... + a50on the client side. Then the query then reduces to... WHERE @a > c. This opens an opportunity to use an index.However, floating point numbers do not index well in most databases (including SQL Server). If we can make some assumptions about the data, we might be able to work around this. For example, if the numbers are only stored to two digits of precision as in the example, then we can multiply all the numbers by 100 to obtain integers. Then, indexing will work well. Reasonably well, that is... it depends on how many rows meet the condition. Half of "millions of rows" is still a lot of rows.
Even if the values have truly variable precision, so two digits are not accurate enough, it might still make sense to create the integer index to reduce the rows that need to be checked. The query can check both the approximate value (to hit the index) and the exact value (to get the precise result). If you do that, make sure that the original values are rounded in the right direction to avoid losing precise results.