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.
