Changes between Version 94 and Version 95 of DatabaseBasedAnalysis


Ignore:
Timestamp:
08/08/18 10:09:54 (7 years ago)
Author:
tbretz
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseBasedAnalysis

    v94 v95  
    11241124
    11251125Almost as fast as the 4.5s with C++ where the C++ compiler optimizes the maths. The difference is most likely overhead for example from the SQL optimizer.
     1126
     1127Just for curiosity, the following is, what the optimizer makes out of this query:
     1128{{{!#sql
     1129WITH Table9 AS
     1130(
     1131    /* select#2 */
     1132    SELECT
     1133        COUNT(IF((Weight>0),1,NULL))    AS `Signal`,
     1134        COUNT(IF((Weight<0),1,NULL))/5  AS `Background`
     1135    FROM
     1136        RunInfo
     1137    JOIN
     1138        Events
     1139    JOIN
     1140        Position
     1141    JOIN
     1142        Wobble
     1143    WHERE
     1144        RunInfo.fRunTypeKey = 1
     1145    AND
     1146        RunInfo.fSourceKEY  = 5
     1147    AND
     1148        Position.EvtNumber = Events.EvtNumber
     1149    AND
     1150        Events.FileId = RunInfo.FileId
     1151    AND
     1152        Position.FileId=RunInfo.FileId
     1153    AND
     1154        (((((IF(((((`M3Long` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) * 0.0117193246260285378e0) < <cache>(-(0.07))) or (((((SQRT((((`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0)) * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + ((`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)) * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0))))) * 0.0117193246260285378e0) - 0.5e0) * 7.2e0) - ((`SlopeLong` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) / 0.0117193246260285378e0)) < 0)),-(((1.39252e0 + (0.154247e0 * ((`SlopeLong` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) / 0.0117193246260285378e0))) + (1.67972e0 * (1 - (1 / (1 + (4.86232e0 * `Leakage1`))))))),((1.39252e0 + (0.154247e0 * ((`SlopeLong` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) / 0.0117193246260285378e0))) + (1.67972e0 * (1 - (1 / (1 + (4.86232e0 * `Leakage1`))))))) * (1 - (`Width` / `Length`))) * (if(((((`M3Long` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) * 0.0117193246260285378e0) < <cache>(-(0.07))) or (((((SQRT((((`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0)) * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + ((`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)) * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0))))) * 0.0117193246260285378e0) - 0.5e0) * 7.2e0) - ((`SlopeLong` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) / 0.0117193246260285378e0)) < 0)),-(((1.39252e0 + (0.154247e0 * ((`SlopeLong` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) / 0.0117193246260285378e0))) + (1.67972e0 * (1 - (1 / (1 + (4.86232e0 * `Leakage1`))))))),((1.39252e0 + (0.154247e0 * ((`SlopeLong` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) / 0.0117193246260285378e0))) + (1.67972e0 * (1 - (1 / (1 + (4.86232e0 * `Leakage1`))))))) * (1 - (`Width` / `Length`)))) + ((SQRT((((`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0)) * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + ((`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)) * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0))))) * 0.0117193246260285378e0) * (SQRT((((`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0)) * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + ((`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)) * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0))))) * 0.0117193246260285378e0))) - (((2 * (if(((((`M3Long` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) * 0.0117193246260285378e0) < <cache>(-(0.07))) or (((((SQRT((((`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0)) * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + ((`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)) * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0))))) * 0.0117193246260285378e0) - 0.5e0) * 7.2e0) - ((`SlopeLong` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) / 0.0117193246260285378e0)) < 0)),-(((1.39252e0 + (0.154247e0 * ((`SlopeLong` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) / 0.0117193246260285378e0))) + (1.67972e0 * (1 - (1 / (1 + (4.86232e0 * `Leakage1`))))))),((1.39252e0 + (0.154247e0 * ((`SlopeLong` * SIGN(((`CosDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + (`SinDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))) / 0.0117193246260285378e0))) + (1.67972e0 * (1 - (1 / (1 + (4.86232e0 * `Leakage1`))))))) * (1 - (`Width` / `Length`)))) * (SQRT((((`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0)) * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + ((`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)) * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0))))) * 0.0117193246260285378e0)) * SQRT((1 - (least(greatest((((`CosDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0))) - (`SinDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0)))) / SQRT((((`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0)) * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + ((`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)) * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))),<cache>(-(1))),1) * least(greatest((((`CosDelta` * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0))) - (`SinDelta` * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0)))) / SQRT((((`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0)) * (`MeanX` - (((`cosa` * `X`) - (`sina` * `Y`)) / 1.02e0))) + ((`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)) * (`MeanY` - (((`cosa` * `Y`) + (`sina` * `X`)) / 1.02e0)))))),<cache>(-(1))),1)))))) < 0.024)
     1155    AND
     1156        PI()*Width*Length < log10(Size)*898e0 - 1535e0
     1157    AND
     1158        FileId BETWEEN 131101000 AND 131107000
     1159    AND
     1160        fZenithDistanceMax < 35
     1161    AND
     1162        fR750Cor>0.9e0*fR750Ref
     1163    AND
     1164        NumUsedPixels>5.5
     1165    AND
     1166        NumIslands<3.5
     1167    AND
     1168        Leakage1<0.1
     1169)
     1170
     1171/* select#1 */
     1172SELECT
     1173    '984'                   AS `Signal`,
     1174    '446.0000'              AS `Background`,
     1175    '984' - '446.0000'      AS `Excess`,
     1176    LiMa('984','446.0000')  AS `Significance`
     1177FROM DUAL
     1178}}}