0

Many mathematical operation (such as division, multiplication, etc.) are supposed to be computed faster when dealing with power of two numbers (C++, C#?, ...)

For instance 15 * 256 = 0x0e right shifted (fast) 8 bits = 0x0e00 = 3840
Whereas 15 * 255 = 0x0e multiplied by (slow) 0xff = 0x0ef1 = 3825

Does this kind of optimization even happen in SQL Server?

I don't think there are, I tried measuring a difference of execution time of queries such as those:

SET STATISTICS TIME ON

SELECT
    AVG(N / 256)
FROM DBO.V_VIRTUAL_NUMBERS
WHERE N < 1048576


SELECT
    AVG(N / 255)
FROM DBO.V_VIRTUAL_NUMBERS
WHERE N < 1000000

SET STATISTICS TIME OFF

which resulted in:

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 207 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 218 ms,  elapsed time = 206 ms.
4
  • Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. Also see How to Ask
    – gnat
    Jun 25, 2014 at 8:50
  • 2
    Your initial claim (that many operations are faster with power of two) is probably wrong on today's systems. Did you benchmark it? Jun 25, 2014 at 9:03
  • I have no idea how to benchmark it properly, I'm clueless about the internal mechanism of SQL Server engine. For instance, does AVG completly biase the restult? => I don't know.
    – Serge
    Jun 25, 2014 at 9:07
  • 1
    Performing x^y where x happens to be 2 is generally not faster. Compilers see this and optimize by shifting bits which is faster. However, you obviously cannot do this for any x and y value, so it ultimately depends on who's "running" your program.
    – Neil
    Jun 25, 2014 at 11:10

1 Answer 1

1

Many mathematical operation (such as division, multiplication, etc.) are supposed to be computed faster when dealing with power of two numbers.

For instance 15 * 256 = 0x0e right shifted (fast) 8 bits = 0x0e00 = 3840

Whereas 15 * 255 = 0x0e multiplied by (slow) 0xff = 0x0ef1 = 3825

Does this kind of optimization even happen in SQL Server?

I very much doubt it.

If you were to explicitly use bit-wise operators (can't recall if SqlServer even has them) then yes, you might see some improvement but just handing SqlServer an arithmetic expression to work with then no; it will simply perform the [floating-point] calculation and be done with it.

The alternative is to parse each and every expression looking for multiplication or division operations that involve values that just happen to be some power of two and then to rewrite that expression to use bit-wise operators.
Given modern processors, that's probably more of an overhead than just crunching the numbers in the first place.

1
  • SQL Server will infer types for constants in statements. Given SELECT 15 * 256 it will perform integer math. One can force decimal (precise) calculation by adding a decimal point e.g. SELECT 15.0 * 256. Oct 5, 2015 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.