| Version 5 (modified by , 6 years ago) ( diff ) |
|---|
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 -------------------------------------------------------------- [...] Sending data 74.844972 74.051538 0.809659 [...] -------------------------------------------------------------- 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
------------------------ Rootify SQL ------------------------- Reading query from file './crab.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 64 branches... 12 branches skipped due to name starting with @. Configured 52 branches. Filling branches... 13835838 rows fetched. 6 rows skipped due to NULL field. 0 rows filled into tree. 0 B written to disk. File closed. Execution time: 476.714s (34.5 us/row) -------------------------------------------------------------- Status Duration CPU User CPU System -------------------------------------------------------------- [...] Sending data 476.662496 471.159646 5.607350 [...] -------------------------------------------------------------- Bytes_sent 1.7 k Bytes_received 9 G
With writing a file
------------------------ Rootify SQL ------------------------- Reading query from file './crab.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 64 branches... 12 branches skipped due to name starting with @. Configured 52 branches. Filling branches... 13835838 rows fetched. 6 rows skipped due to NULL field. 13835832 rows filled into tree. 2.7 GB written to disk. File closed. Execution time: 540.888s (39.1 us/row) -------------------------------------------------------------- Status Duration CPU User CPU System -------------------------------------------------------------- [...] Sending data 540.059249 473.963838 5.318202 [...] -------------------------------------------------------------- Bytes_sent 1.7 k Bytes_received 9 G
---
SELECT Events.* FROM Events WHERE FileId IN ($LIST) --
------------------------ Rootify SQL -------------------------
Reading query from file './crab-data-only.sql'.
Found 317 list element(s) for ${LIST}
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...
2305972 rows fetched.
0 rows filled into tree.
0 B written to disk.
File closed.
Execution time: 40.2818s (17.5 us/row)
--------------------------------------------------------------
Status Duration CPU User CPU System
--------------------------------------------------------------
[...]
Sending data 39.723849 27.236133 0.226700
[...]
--------------------------------------------------------------
Bytes_sent 4 k
Bytes_received 833 M
---
SELECT Events.* FROM Events LEFT JOIN RunInfo USING (FileId) WHERE fSourceKey=5 AND fRunTypeKey=1 AND FileId BETWEEN 131101000 AND 131107000 AND fZenithDistanceMax<35 AND fR750Ref/fR750Cor>0.9
------------------------ Rootify SQL -------------------------
Reading query from file './crab-data-only.sql'.
Found 317 list element(s) for ${LIST}
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...
2305972 rows fetched.
0 rows filled into tree.
0 B written to disk.
File closed.
Execution time: 40.5847s (17.6 us/row)
--------------------------------------------------------------
Status Duration CPU User CPU System
--------------------------------------------------------------
[...]
Sending data 40.108342 26.946160 0.219184
[...]
--------------------------------------------------------------
Bytes_sent 4 k
Bytes_received 833 M
---
SELECT Events.*, Position.X, Position.Y FROM Events LEFT JOIN RunInfo 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
------------------------ Rootify SQL -------------------------
Reading query from file './crab-data-only.sql'.
Found 317 list element(s) for ${LIST}
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 46 branches...
Configured 46 branches.
Filling branches...
2305972 rows fetched.
0 rows filled into tree.
0 B written to disk.
File closed.
Execution time: 41.8071s (18.1 us/row)
--------------------------------------------------------------
Status Duration CPU User CPU System
--------------------------------------------------------------
[...]
Sending data 41.283198 30.585062 0.342441
[...]
--------------------------------------------------------------
Bytes_sent 4 k
Bytes_received 872 M
Note:
See TracWiki
for help on using the wiki.
