# HG changeset patch
# User Tim Englich <tim.englich@intevation.de>
# Date 1256308087 0
# Node ID d5cef9f17525b7caf1240743804ef045d7b5245f
# Parent  a33d59f5791a944286a400038542916fdd42b127
gnv-artifacts/trunk@265 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r a33d59f5791a -r d5cef9f17525 gnv-artifacts/ChangeLog
--- a/gnv-artifacts/ChangeLog	Fri Oct 23 14:26:45 2009 +0000
+++ b/gnv-artifacts/ChangeLog	Fri Oct 23 14:28:07 2009 +0000
@@ -1,5 +1,10 @@
 2009-10-23  Tim Englich  <tim.englich@intevation.de>
 
+	* doc/conf/queries.properties:
+	  Some Queryimprofments done. Inner-Selects has been removed where it was possible.
+	
+2009-10-23  Tim Englich  <tim.englich@intevation.de>	
+	
 	* src/main/java/de/intevation/gnv/artifacts/GNVArtifactBase.java, 
 	  src/main/java/de/intevation/gnv/timeseries/TimeSeriesArtifact.java (describe), 
 	  src/main/java/de/intevation/gnv/profile/verticalcrosssection/VerticalCrossSectionMeshArtifact.java (describe), 
diff -r a33d59f5791a -r d5cef9f17525 gnv-artifacts/doc/conf/queries.properties
--- a/gnv-artifacts/doc/conf/queries.properties	Fri Oct 23 14:26:45 2009 +0000
+++ b/gnv-artifacts/doc/conf/queries.properties	Fri Oct 23 14:28:07 2009 +0000
@@ -16,47 +16,30 @@
 timeseries_parameter=SELECT DISTINCT \
         p.PARAMETERID KEY, \
         p.GERMANNAME VALUE \
-    from MEDIAN.PARAMETER p \
-    where p.PARAMETERID in \
-          (select distinct \
-              ts.PARAMETERID \
-           from MEDIAN.TIMESERIES ts \
-           where ts.TIMESERIESID in \
-               (select distinct \
-                   tsv.TIMESERIESID \
-                from MEDIAN.TIMESERIESVALUE tsv \
-                where tsv.MEASUREMENTID in \
-                    (select m.MEASUREMENTID \
-                     from MEDIAN.MEASUREMENT m, \
-                          MEDIAN.TIMESERIESPOINT tsp \
-                     where m.FEATUREID = tsp.FEATUREID and \
-                           tsp.FEATUREID IN ( ? )\
-                    )\
-               )\
-          ) \
-    ORDER BY p.GERMANNAME
-
+    FROM MEDIAN.PARAMETER P, \
+         MEDIAN.TIMESERIES TS, \
+         MEDIAN.TIMESERIESVALUE TSV, \
+         MEDIAN.MEASUREMENT M, \
+         MEDIAN.TIMESERIESPOINT TSP \
+    WHERE M.FEATUREID = TSP.FEATUREID AND \
+          M.MEASUREMENTID = TSV.MEASUREMENTID AND \
+          TS.TIMESERIESID = TSV.TIMESERIESID AND \
+          P.PARAMETERID = TS.PARAMETERID AND \
+          TSP.FEATUREID = ? \
+    ORDER BY P.GERMANNAME
+    
 timeseries_depth_height=SELECT DISTINCT \
-        m.MEASUREMENTID KEY, \
-        m.ZLOCATION VALUE \
-    from MEDIAN.MEASUREMENT m \
-    where m.MEASUREMENTID in \
-          (SELECT DISTINCT \
-              t_v.MEASUREMENTID \
-          from MEDIAN.TIMESERIESVALUE t_v \
-          where t_v.TIMESERIESID in \
-              (SELECT DISTINCT \
-                  t.TIMESERIESID \
-              from MEDIAN.TIMESERIES t \
-              where t.PARAMETERID in \
-                  (SELECT DISTINCT \
-                      p.PARAMETERID \
-                  from MEDIAN.PARAMETER p \
-                  where m.FEATUREID IN ( ? ) and \
-                        p.PARAMETERID IN (?)\
-                  )\
-              )\
-          )\
+        M.MEASUREMENTID KEY, \
+        M.ZLOCATION VALUE \
+    FROM MEDIAN.MEASUREMENT M, \
+         MEDIAN.TIMESERIESVALUE TSV, \
+         MEDIAN.TIMESERIES T, \
+         MEDIAN.PARAMETER P \
+    WHERE M.MEASUREMENTID = TSV.MEASUREMENTID AND \
+          TSV.TIMESERIESID = T.TIMESERIESID AND \
+          T.PARAMETERID = P.PARAMETERID AND \
+          M.FEATUREID = ? AND \
+          P.PARAMETERID IN (?)\
     ORDER BY m.ZLOCATION DESC
 
 timeseries_interval=select min(tv.TIMEVALUE) MIN, \
@@ -95,31 +78,27 @@
     FROM MEDIAN.MESH m \
     WHERE SOURCEID IN (?) \
     ORDER BY m.NAME
-timeseries_meshpoint = SELECT FEATUREID, \
+timeseries_meshpoint = SELECT MEDIAN.MESHPOINT.FEATUREID, \
         ST_ASTEXT(SHAPE) \
-    FROM MEDIAN.MESHPOINT \
-    WHERE MESHID  in \
-        (SELECT DISTINCT MESHID \
-         FROM MEDIAN.MESH \
-         WHERE OBJECTID = ?) AND \
+    FROM MEDIAN.MESHPOINT, \
+         MEDIAN.MESH M \
+    WHERE MEDIAN.MESHPOINT.MESHID = M.MESHID AND \
          KPOSITION = 1 AND \
+         M.OBJECTID = ? AND \
          INTERSECTS(SHAPE,"?")
 
-timeseries_meshpoint_depth = select mp.FEATUREID KEY, \
-        'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE \
-    from MEDIAN.MESHLAYER ml, \
-         MEDIAN.MESHPOINT mp \
-    where ml.KPOSITION = mp.KPOSITION and \
-          ml.MESHID = mp.MESHID and \
-          mp.FEATUREID in \
-    ( select FEATUREID \
-      from MEDIAN.MESHPOINT mp, \
-           MEDIAN.MESH m \
-      where m.OBJECTID = ? AND \
-            mp.MESHID = m.MESHID  AND \
-            IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
-            JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
-    order by ml.UPPERZLOCATION desc
+timeseries_meshpoint_depth = SELECT MP.FEATUREID KEY, \
+        'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
+    from MEDIAN.MESHLAYER ML, \
+         MEDIAN.MESHPOINT MP, \
+         MEDIAN.MESH M \
+    WHERE ML.KPOSITION = MP.KPOSITION AND \
+          ML.MESHID = MP.MESHID AND \
+          M.OBJECTID = ? AND \
+          MP.MESHID = M.MESHID  AND \
+          IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
+          JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) \
+    ORDER BY ML.UPPERZLOCATION DESC
 
 timeseries_mesh_parameter=SELECT distinct \
         p.PARAMETERID KEY , \
@@ -556,4 +535,7 @@
              msv.PARAMETERID, \
              MEDIAN.MESHPOINT.JPOSITION , \
              MEDIAN.MESHPOINT.IPOSITION, \
-             MEDIAN.MESHPOINT.KPOSITION
\ No newline at end of file
+             MEDIAN.MESHPOINT.KPOSITION
+             
+             
+    select mp.FEATUREID KEY, 'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE from MEDIAN.MESHLAYER ml, MEDIAN.MESHPOINT mp, MEDIAN.MESH M where ml.KPOSITION = mp.KPOSITION and ml.MESHID = mp.MESHID and m.OBJECTID = 3 AND mp.MESHID = m.MESHID  AND IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771) and JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771) order by ml.UPPERZLOCATION desc
\ No newline at end of file