开发者

SQLServer index for between where the values are sums

If I have a table with three decimal columns x_coordinate, y_coordinate and proximity and a where clause that looks like either this:

@x_test between (x_coordinate - proximity) and (x_coordinate + proximity)
and
@y_test between (y_coordinate - proximity) and (y_coordinate + proximity)

or this:

power (@x_test - x_coordinate, 2) < power (proximity, 2)
and
power (@y_test - y_coordinate, 2) < power (proximity, 2)

Short of storing values in the table for x_min, x_max, y_min and y_max (I don't have the authority to change to database layout), can an index be constructed that will speed this up? My understanding of how indices work makes me think that due to the fact that the value in the table I'm comparing against is computed that there are not.

This logic is currently done by getting all of the results from a particular date range from the database (I left that portion out of the example queries), and filtering out by proximity in the client program. This was apparently done in the past for开发者_如何学C speed reasons, but with database technology nowadays I was going to perform some speed tests to see if that was still true.


You can add a computed column and create an index on it; there are some restrictions, check here. If you are not allowed to change table design, you can always create a VIEW with computed column and index it. Depends on SQLServer edition, this index might be used (Enterprise Edition) while querying underlying table implicitly.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜