Which is faster? Multiplication in code, or multiplication in SQL?

Question:

What's faster: Mul开发者_高级运维tiplying 2 doubles in a SQL table and return the table, or returning the table and multiplying two column values in code ?

You can assume that the two columns that need to be multiplied need to be returned anyway.

Multiplication is an extremely fast computation, and whether the chip is asked to do it from SQL or from another places shouldnt make any differnce. The thing that will probably make it quicker in SQL is that it can be done in a single pass (though that depends on how SQL implements it), where as if you do it in code you have to cycle through the result set, but then again you might be doing that anyway.

The real answer though is it really doesnt matter unless you plan to multiply 10's of millions of numbers at a time.

SQL is faster IF you are

• using appropriate data types with efficient hardware arithmetic (more)
• using join/view efficiently.

and generally - Avoid delimited fields (more)

The only time I've seen SQL process much slower than native code is when doing excessive powers and logs. Why, I don't know, as the CPU has the same amount of work to do either way.

The only possible reason is that T-SQL is a scripted language. You never mentioned what language you're using.

As mentioned by others, test.

The real answer is to only perform math on the records that need it. This you should achieve with SQL.

Of course, in the extreme, you could use SSE (or AVX with Sandy Bridge) in native code, which will of course yield significantly faster results. I don't believe SQL Server would be able to apply such optimizations.

SQL if you're doing it right!

In this case SQL, include the Mulitply as part of the select statement when you retrieve the resultset. If you are doing in code, then you will have to iterate through the resultset seperately which will be time consuming.

On the other hand if you are just returning two values and want to multiply that, it doesn't matter if its code or sql :)

The answer is to do it in code because the DBMS might be wrong. SQL Server is known to get it wrong.

Try this and notice the scary difference between the two results: select 1.0 * 3/2 select 3/2 * 1.0