Hello Thomas,

the result set contains 3 record for criteria.

Records DATUM >= '5.10.2015' = 102
Records DATUM <= '11.10.2015' = 26

It seems that both queries need to be executed and then the subset of
matching data for both date conditions is provided.

The performance is not my main concern in this case - it´s more about to
learn to improve queries.

Niko

-----Ursprüngliche Nachricht-----
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Gesendet: Dienstag, 13. Oktober 2015 15:27
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] Optimize query for date ranges

Niko,

> the execution is currently not the problem, because the number of
> records is too small.
>
> I have the impression that the first query looks for DATUM >= x and
> the second one for DATUM <= y and finally delivers the number of
> records that is included in both result sets.
>
> My main interesst is to understand if I can improve my query or have
> to accept it. In the second case I will try to run as many queries as
> possible without dateranges.

To be honest, I wouldn't bother too much. Firebird should handle such simple
statements with existing indexes fine.

What is the expected number of records in the result set for your date range
queries?


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

> Niko
>
> -----Ursprüngliche Nachricht-----
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Montag, 12. Oktober 2015 17:31
> An: firebird-support@yahoogroups.com
> Betreff: Re: [firebird-support] Optimize query for date ranges
>
> Niko,
>
>> Hello all,
>>
>> i am running a simple query where the result contains 3 records. The
>> query should provide all records in a specific week.
>>
>> SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND
>> WOCHE = '2015-41';
>>
>> Some times I dont have a week but two dates:
>>
>> In this case – the very same number of records = 3 is the result set I
>> see that the table Arbeitseinteilung gots 42 reads.
>>
>> SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND
>> a.DATUM >= '05.10.2015' AND a.DATUM <= '11.10.2015';
>>
>> How can I improve the query?
>
> Hmm, what is the execution time for both queries?
>
> 42 isn't a lot. This might also include accessing system tables ...
>
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird FB TraceManager, IB

LogManager,

> Database Health Check, Tuning etc.
>
>
> ------------------------------------
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Documentation item on the
> main (top) menu. Try FAQ and other links from the left-side menu there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
>
>
> ------------------------------------
> Posted by: "Parzival" <parzival1969@...>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu. Try FAQ and other links from the left-side menu

there.

>
> Also search the knowledgebases at

http://www.ibphoenix.com/resources/documents/

>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ------------------------------------
>
> Yahoo Groups Links
>
>
>

------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu
there.

Also search the knowledgebases at
http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links