Changes between Version 73 and Version 74 of DatabaseBasedAnalysis

Timestamp:

08/06/18 16:34:42 (6 years ago)

Author:

tbretz

Comment:

--

DatabaseBasedAnalysis

@@ -771 +771 @@
 Generally, it is '''not recommended''' to run the full analysis on the server - as long as you want to optimize or change things. Running it on the server only makes sense if you have finished optimizing your analysis and want to process a lot of data (as in an automatic process). 
 }}}
+
+== Optimization and precision maths ==
+
+A very interesting read is https://dev.mysql.com/doc/refman/5.7/en/precision-math.html (and its sub-sections: Click on ''Next'').
+
+In short: A value like 1.234 is an ''exact'' value and exact math calculation are performed. If a value is given as an approximate ''floating-point'' value (which all '''DOUBLE''' columns are), for example 1.234e0, the calculation is ''not exact''. ''Not exact'' means that it using (faster) ''floating-point'' arithmetic rather than (slower) ''exact'' arithmetic.
+
+Now, any mathematical operation which at least contains one ''floating-point'' number is evaluated as a floating-point approximation. This sounds very simple and as if it has nothing to do with our analysis, but it has:
+
+All the numbers in the previous SQL queries were given as ''exact'' numbers and thus stored by the engine as ''exact'' numbers. Whenever they take place in one of our calculations, they have to be converted to a floating-point value first (it seems this does not happen in advance but during run-time), which is an operation which is obviously not for free.
+
+So as a test, let's append a `e0` to all fractional values in the query, e.g. instead of `0.5`, let's write `0.5e0` (or `5e-1`). This essentially tells the SQL engine to store the value as an approximate  ''floating-point'' value and not as an ''exact'' value. Now, let's run the query again...
+
+And voila!
+
+{{{
+Execution time: 10.1325s
+}}}
+
+''This is again much faster!''
+
+{{{#!div style="background: #8d8; border: 3px ridge"
+Conclusion: Wherever you use floating-point values in your query and you are not interested in a 100% machine independent result, write them as ''floating-point'' values by appending `e0` instead of ''exact'' values. 
+}}}