[firebird-support] Optimize query for date ranges

['Parzival' parzival1...@gmx.at [firebird-support]]

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?

 

Thanks

 

Niko

Re: [firebird-support] Optimize query for date ranges

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

> On Oct 11, 2015, at 5:00 AM, 'Parzival' parzival1...@gmx.at 
> [firebird-support]  wrote:
> 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?
> 

Do you have an index on DATUM?  What ate the plans for the two queries?

Good luck,

Ann

AW: [firebird-support] Optimize query for date ranges

['Parzival' parzival1...@gmx.at [firebird-support]]

Hello,

 

there are two indices on the field DATUM: One ascending, the other one 
descending.

 

Plan:

PLAN (A INDEX (IDX_ARBEITSEINTEILUNG1))

Adapted plan:

PLAN (A INDEX (IDX_ARBEITSEINTEILUNG1))

 

Niko

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Sonntag, 11. Oktober 2015 18:52
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Optimize query for date ranges

 

  

 

On Oct 11, 2015, at 5:00 AM, 'Parzival' parzival1...@gmx.at 
  [firebird-support] 
 > 
wrote:

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?

 

Do you have an index on DATUM?  What ate the plans for the two queries?

 

Good luck,

 

Ann





[Non-text portions of this message have been removed]