subselect with distinct takes very long time
--------------------------------------------
Key: CORE-3608
URL: http://tracker.firebirdsql.org/browse/CORE-3608
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.0
Environment: Win XP, FB 2.5 RC2,
Query executed with FlameRobin
Reporter: Matthias Philipps
Priority: Minor
I've found some strange behaviour with destinct in a subselect: For both
statements the result of data ist the same, but execution time for the second
one is worse, where I expected to be rather better.
select
GPST_CHANGEDATE, GPST_CHANGEUSER, GPST_CREATEDATE, GPST_CREATEUSER, GPST_DATE,
GPST_FILENAME, GPST_ID, GPST_NAME, GPST_TRACK, GPST_USERGROUP_ID,
min(GPSW_TIME) AS MINTIME, max(GPSW_TIME) AS MAXTIME, count(*) AS WEGPUNKTE
from GPSTRACKS T
join GPSWaypoints W on W.GPSW_GPSTID = T.GPST_ID
where T.GPST_ID in
(
select ST.GPST_ID from GPSTRACKS ST
join GPSWaypoints SW on SW.GPSW_GPSTID = ST.GPST_ID
where SW.GPSW_TIME > '25.08.2011 09:30' and SW.GPSW_TIME < '25.08.2011 10:30'
)
group By
GPST_CHANGEDATE, GPST_CHANGEUSER, GPST_CREATEDATE, GPST_CREATEUSER, GPST_DATE,
GPST_FILENAME, GPST_ID, GPST_NAME, GPST_TRACK, GPST_USERGROUP_ID
takes 0,043 sec to execute whereas
select
GPST_CHANGEDATE, GPST_CHANGEUSER, GPST_CREATEDATE, GPST_CREATEUSER, GPST_DATE,
GPST_FILENAME, GPST_ID, GPST_NAME, GPST_TRACK, GPST_USERGROUP_ID,
min(GPSW_TIME) AS MINTIME, max(GPSW_TIME) AS MAXTIME, count(*) AS WEGPUNKTE
from GPSTRACKS T
join GPSWaypoints W on W.GPSW_GPSTID = T.GPST_ID
where T.GPST_ID in
(
select <b>distinct</b> ST.GPST_ID from GPSTRACKS ST
join GPSWaypoints SW on SW.GPSW_GPSTID = ST.GPST_ID
where SW.GPSW_TIME > '25.08.2011 09:30' and SW.GPSW_TIME < '25.08.2011 10:30'
)
group By
GPST_CHANGEDATE, GPST_CHANGEUSER, GPST_CREATEDATE, GPST_CREATEUSER, GPST_DATE,
GPST_FILENAME, GPST_ID, GPST_NAME, GPST_TRACK, GPST_USERGROUP_ID
takes over 8 Sec. with the same data
greetings
matthias
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
All of the data generated in your IT infrastructure is seriously valuable.
Why? It contains a definitive record of application performance, security
threats, fraudulent activity, and more. Splunk takes this data and makes
sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-d2dcopy2
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
