Обсуждение: Performance problems with arithmetic operators

Has anyone else experienced serious performance problems when using arithmetic
expressions in queries? We've been able to do queries that involve nested
cases on one of our database servers at rates of 300+ per second, but the
moment we try a query with an expression of the form

a - b / a * 100

performance plummets to 70-80 queries per second or so.

a and b are numeric(10, 3)

Is there a way to improve this? We were thinking of retrieving the columns one
by one and doing the arithmetic in python on the machine issuing the query.
I've written a program which demonstrates that you can do twice as many
queries by retrieving columns individually and then doing the arithmetic in
the host programming language.

Can anyone explain this behaviour? I thought perhaps SQL's semantics regarding
NULLs in arithmetic might complicated calculations somewhat, but haven't
confirmed this theory.

Thank you,
Mark aka Squeal Boy
rm brings pain. If you haven't filled the disk, don't take the risk.

> Has anyone else experienced serious performance problems when using arithmetic
> expressions in queries?
...
> a and b are numeric(10, 3)

numeric() is implemented with binary coded decimal representation, which
is substantially slower than direct processor supported math involving
just ints and floats. Try testing this with different types to see what
happens.

                       - Thomas