Help with Union Query Optimisation
Hello,
I have an issue with a query taking a long time to process and was wondering
if there is a more efficient way to write this query in Informix SQL. It must
remain as a single query to be executed because of software constraints.
Select datetime_col, $data
From tableName
Where group = $group
And datetime_col = (
select MAX(datetime_col)
from tableName
where group = $group
and datetime_col <= '$startTime'
)
UNION
Select datetime_col, $data
From tableName
Where group = $group
And datetime_col > '$startTime'
And datetime_col <= '$endTime'
Order by datetime_col
$ indicated parameter to be filled in by software.$data - column name for table
$group - integer
datetime_col is YEAR TO SECOND
Thanks,
Steve
STEPHEN PACKER wrote:
It seems to me that if you are doing a union of the identical query but
the first part filters for dates less than or equal to the greatest date
less than or equal to some starting date and the second part filters for
all dates greater than the start date and less than or equal to the end
date that a single query filtering for dates less than or equal to the
end date, with a DISTINCT clause to filter out any possible duplicate
rows if the date and other columns selected in $data aren't unique (the
UNION would filter dups also). So:
SELECT distinct datetime_col, $data
FROM tableName
WHERE group = $group
AND datetime_col >= (
select MAX(datetime_col)
from tableName
where group = $group
and datetime_col <= '$startTime'
) and datetime_col <= $endTime;
Art S. Kagel
Oninit
> Hello,
> I have an issue with a query taking a long time to process and was wondering
> if there is a more efficient way to write this query in Informix SQL. It must
> remain as a single query to be executed because of software constraints.
>
> Select datetime_col, $data
> >From tableName
> Where group = $group>
> And datetime_col = (
> select MAX(datetime_col)
> from tableName
> where group = $group
> and datetime_col <= '$startTime'
>
> )
> UNION
> Select datetime_col, $data>
> >From tableName
>
> Where group = $group
>
> And datetime_col > '$startTime'
>
> And datetime_col <= '$endTime'
>
> Order by datetime_col
>
> $ indicated parameter to be filled in by software.> $data - column name for table
> $group - integer
> datetime_col is YEAR TO SECOND
>
> Thanks,
> Steve
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
>
>
>
Nice answer It is strange that union filters duplicates I always believed that
it only resolved overlaps between the two selects but I checked it and you are
right.
Note that both of these queries cannot have overlaps because dateteime_col <=
'$startTime' in one and datetime_col > '$startTime' in the other and I don't
believe any date can be both. If you don't have to don't use the distinct. You
won't have to if the query always selected in $data something that made the
row unique.
Finally, you didn't show an explain plan. Have you looked at it? What is going
on? It may be a case of scanning a large table if you don't have an index on
group or datetime_col and you won't be able to make that fast. An index on
group and datetime_col could speed things up a bunch.
> Select datetime_col, $data
> >From tableName
> Where group = $group
> And datetime_col = (
> select MAX(datetime_col)
> from tableName
> where group = $group
> and datetime_col <= '$startTime'
> )
> UNION
> Select datetime_col, $data
> >From tableName
> Where group = $group
> And datetime_col > '$startTime'
> And datetime_col <= '$endTime'
> Order by datetime_col