| Version 2 (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 -------------------------------------------------------------- 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
------------------------ 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
Note:
See TracWiki
for help on using the wiki.
