Changes between Initial Version and Version 1 of DatabaseBasedAnalysis/Benchmarking

Timestamp:

08/04/18 15:46:16 (7 years ago)

Author:

tbretz

Comment:

--

DatabaseBasedAnalysis/Benchmarking

@@ +1 @@
+{{{
+SELECT
+   Events.*
+FROM RunInfo
+LEFT JOIN Events   USING (FileId)
+LEFT JOIN Position USING (FileId, EvtNumber)
+WHERE 
+   fSourceKey=5 
+AND 
+   fRunTypeKey=1 
+AND 
+   FileId BETWEEN 131101000 AND 131107000
+AND 
+   fZenithDistanceMax<35
+AND 
+   fR750Ref/fR750Cor>0.9
+}}}
+
+Without writing a file and without filling the root trees internally
+
+{{{
+------------------------ Rootify SQL -------------------------
+Reading query from file './crab-data-only.sql'.
+Connecting to database...
+Client Version: 5.7.23
+Server Version: 5.7.23-0ubuntu0.18.04.1
+Compression of databse connection is OFF
+Connection to databases is ENCRYPTED (DHE-RSA-AES256-SHA)
+Requesting data...
+Opening file '/dev/null' [compression=1]...
+Writing data to tree 'Result'
+Trying to setup 44 branches...
+Configured 44 branches.
+Filling branches...
+2305973 rows fetched.
+1 rows skipped due to NULL field.
+0 rows filled into tree.
+0 B written to disk.
+File closed.
+Execution time: 40.3481s (17.5 us/row)
+--------------------------------------------------------------
+
+
+Status                        Duration    CPU User  CPU System
+--------------------------------------------------------------
+[...]
+Sending data                 39.854647   29.566965    0.343076
+[...]
+--------------------------------------------------------------
+
+Bytes_sent       750 
+Bytes_received   833 M
+}}}
+
+Same query with writing a file
+
+{{{
+------------------------ Rootify SQL -------------------------
+Reading query from file './crab-data-only.sql'.
+Connecting to database...
+Client Version: 5.7.23
+Server Version: 5.7.23-0ubuntu0.18.04.1
+Compression of databse connection is OFF
+Connection to databases is ENCRYPTED (DHE-RSA-AES256-SHA)
+Requesting data...
+Opening file 'crab.root' [compression=1]...
+Writing data to tree 'Result'
+Trying to setup 44 branches...
+Configured 44 branches.
+Filling branches...
+2305973 rows fetched.
+1 rows skipped due to NULL field.
+2305972 rows filled into tree.
+355 MB written to disk.
+File closed.
+Execution time: 59.3526s (25.7 us/row)
+--------------------------------------------------------------
+
+
+Status                        Duration    CPU User  CPU System
+--------------------------------------------------------------
+[...]
+Sending data                 58.076298   29.343025    0.337940
+[...]
+--------------------------------------------------------------
+
+Bytes_sent       750 
+Bytes_received   833 M
+}}}
+
+Query calculating the source dependent parameters for the on source position only
+
+{{{
+SELECT
+   Events.*,
+   @DX      := MeanX-X/1.02,
+   @DY      := MeanY-Y/1.02,
+   @Norm    := SQRT(@DX*@DX + @DY*@DY),
+   @Dist    := @Norm*0.0117193246260285378 AS Dist,
+   PI()*Width*Length*0.0117193246260285378*0.0117193246260285378 AS Area,
+   @LX      := TRUNCATE((CosDelta*@DY - SinDelta*@DX)/@Norm, 6),
+   @LY      := TRUNCATE((CosDelta*@DX + SinDelta*@DY)/@Norm, 6),
+   @Alpha   := ASIN(@LX) AS Alpha,
+   @Sign    := SIGN(@LY) AS Sign,
+   @M3L     := M3Long*@Sign*0.0117193246260285378,
+   @Slope   := SlopeLong*@Sign/0.0117193246260285378 AS Slope,
+   @Xi      := 1.39 + 0.154*@Slope + 1.679*(1-1/(1+4.86*Leakage1)),
+   @Sign1   := @M3L+0.07,
+   @Sign2   := (@Dist-0.5)*7.2-@Slope,
+   @Disp    := IF (SIGN(@Sign1)<0 || SIGN(@Sign2)<0, -@Xi, @Xi) * (1-Width/Length),
+   @ThetaSq := (@Disp*@Disp + @Dist*@Dist - 2*@Disp*@Dist*SQRT(1-@LX*@LX)) AS ThetaSq
+FROM RunInfo
+LEFT JOIN Events   USING (FileId)
+LEFT JOIN Position USING (FileId, EvtNumber)
+WHERE 
+   fSourceKey=5 
+AND 
+   fRunTypeKey=1 
+AND 
+   FileId BETWEEN 131101000 AND 131107000
+AND 
+   fZenithDistanceMax<35
+AND 
+   fR750Ref/fR750Cor>0.9
+}}}
+
+Without writing to a file and without filling the root trees
+
+{{{
+------------------------ Rootify SQL -------------------------
+Reading query from file './crab-0deg.sql'.
+Connecting to database...
+Client Version: 5.7.23
+Server Version: 5.7.23-0ubuntu0.18.04.1
+Compression of databse connection is OFF
+Connection to databases is ENCRYPTED (DHE-RSA-AES256-SHA)
+Requesting data...
+Opening file '/dev/null' [compression=1]...
+Writing data to tree 'Result'
+Trying to setup 60 branches...
+10 branches skipped due to name starting with @.
+Configured 50 branches.
+Filling branches...
+2305973 rows fetched.
+1 rows skipped due to NULL field.
+0 rows filled into tree.
+0 B written to disk.
+File closed.
+Execution time: 74.8984s (32.5 us/row)
+--------------------------------------------------------------
+
+
+Status                        Duration    CPU User  CPU System
+--------------------------------------------------------------
+starting                      0.000125    0.000119    0.000004
+checking permissions          0.000004    0.000003    0.000001
+checking permissions          0.000002    0.000002    0.000000
+checking permissions          0.000003    0.000002    0.000000
+Opening tables                0.000018    0.000018    0.000000
+init                          0.000226    0.000000    0.000226
+System lock                   0.000008    0.000000    0.000008
+optimizing                    0.000016    0.000000    0.000016
+statistics                    0.000067    0.000000    0.000067
+preparing                     0.000016    0.000000    0.000016
+executing                     0.000003    0.000000    0.000002
+Sending data                 74.844972   74.051538    0.809659
+end                           0.000011    0.000010    0.000001
+query end                     0.000009    0.000009    0.000000
+closing tables                0.000007    0.000006    0.000000
+freeing items                 0.000048    0.000047    0.000002
+cleaning up                   0.000014    0.000013    0.000000
+--------------------------------------------------------------
+
+Bytes_sent       1.6 k
+Bytes_received   1.5 G
+}}}
+
+And with writing to a file
+
+{{{
+------------------------ Rootify SQL -------------------------
+Reading query from file './crab-0deg.sql'.
+Connecting to database...
+Client Version: 5.7.23
+Server Version: 5.7.23-0ubuntu0.18.04.1
+Compression of databse connection is OFF
+Connection to databases is ENCRYPTED (DHE-RSA-AES256-SHA)
+Requesting data...
+Opening file 'crab.root' [compression=1]...
+Writing data to tree 'Result'
+Trying to setup 60 branches...
+10 branches skipped due to name starting with @.
+Configured 50 branches.
+Filling branches...
+2305973 rows fetched.
+1 rows skipped due to NULL field.
+2305972 rows filled into tree.
+444 MB written to disk.
+File closed.
+Execution time: 84.1606s (36.5 us/row)
+--------------------------------------------------------------
+
+
+Status                        Duration    CPU User  CPU System
+--------------------------------------------------------------
+[...]
+Sending data                 83.440086   73.408153    0.794420
+[...]
+--------------------------------------------------------------
+
+Bytes_sent       1.6 k
+Bytes_received   1.5 G
+}}}
+
+Now doing a full analysis for all six wobble positions
+
+{{{
+#!/home/fact/FACT++/build/rootifysql --config=/home/fact/Analysis/crab.rc
+SELECT
+   Events.*,
+   Angle,
+   weight,
+   @PX      := cosa*X - sina*Y,
+   @PY      := cosa*Y + sina*X,
+   @DX      := MeanX-@PX/1.02,
+   @DY      := MeanY-@PY/1.02,
+   @Norm    := SQRT(@DX*@DX + @DY*@DY),
+   @Dist    := @Norm*0.0117193246260285378 AS Dist,
+   PI()*Width*Length*0.0117193246260285378*0.0117193246260285378 AS Area,
+   @LX      := TRUNCATE((CosDelta*@DY - SinDelta*@DX)/@Norm, 6),
+   @LY      := TRUNCATE((CosDelta*@DX + SinDelta*@DY)/@Norm, 6),
+   @Alpha   := ASIN(@LX) AS Alpha,
+   @Sign    := SIGN(@LY) AS Sign,
+   @M3L     := M3Long*@Sign*0.0117193246260285378,
+   @Slope   := SlopeLong*@Sign/0.0117193246260285378 AS Slope,
+   @Xi      := 1.39 + 0.154*@Slope + 1.679*(1-1/(1+4.86*Leakage1)),
+   @Sign1   := @M3L+0.07,
+   @Sign2   := (@Dist-0.5)*7.2-@Slope,
+   @Disp    := IF (SIGN(@Sign1)<0 || SIGN(@Sign2)<0, -@Xi, @Xi) * (1-Width/Length),
+   @ThetaSq := (@Disp*@Disp + @Dist*@Dist - 2*@Disp*@Dist*SQRT(1-@LX*@LX)) AS ThetaSq
+FROM RunInfo
+LEFT JOIN Events   USING (FileId)
+LEFT JOIN Position USING (FileId, EvtNumber)
+CROSS JOIN Wobble
+WHERE 
+   fSourceKey=5 
+AND 
+   fRunTypeKey=1 
+AND 
+   FileId BETWEEN 131101000 AND 131107000
+AND 
+   fZenithDistanceMax<35
+AND 
+   fR750Ref/fR750Cor>0.9
+}}}
+
+Without writing a file and without filling the tree
+
+{{{
+
+}}}
+