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

2015-10-13 Thread 'Parzival' parzival1...@gmx.at [firebird-support] 
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" <parzival1...@gmx.at>
> 
>
> ++
>
> 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

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

2015-10-13 Thread Ann Harrison aharri...@ibphoenix.com [firebird-support] 
On Tue, Oct 13, 2015 at 3:47 PM, 'Parzival' parzival1...@gmx.at
[firebird-support]  wrote:

> 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.
>

Here's the query:

SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND
a.DATUM >= '05.10.2015' AND a.DATUM <= '11.10.2015';

I don't think that's the way it works.  The query plan shows a one key
 lookup, because that's the main stream of the query execution.  However,
Firebird can use multiple indexes on a single query, which it will do this
time.  First it builds a bit map of the db_keys of records with baustelle =
12345.   It then uses one of the indexes (ascending one would hope) on
DATUM to get the db_keys of records with DATUM between 05.10.2015 and
11.10.2015 (which should be stored as a date type and not a string).
Firebird then ANDs the two bitmaps and returns the rows that match both
criteria.

You might get better performance on this query with a compound index on
baustelle and datum.  You'll pay for it when you insert, update, or delete
rows from arbeitseinteilung.

You seemed concerned about the number of reads this query requires.  Did
you measure the number of reads with a cold cache (i.e a freshly started
database) or one that had already done something with arbeitseinteilung?
 46 is a low number for a completely cold cache.

Good luck,

Ann

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

2015-10-13 Thread setysvar setys...@gmail.com [firebird-support] 
>SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND
>a.DATUM >= '05.10.2015' AND a.DATUM <= '11.10.2015';
>
>PLAN (A INDEX (IDX_ARBEITSEINTEILUNG1))
>
>there are two indices on the field DATUM: One ascending, the other one 
>descending.
>
>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.

No, Firebird isn't as dumb as that. Your plan says that IDX_ARBEITSEINTEILUNG1 
is the only index that is used, and I'm pretty certain that index is your 
ascending date index (unless the datum field has lousy selectivity and 
IDX_ARBEITSEINTEILUNG1 is an index for a.baustelle). I think the optimizer 
changes your date comparison to a.DATUM BETWEEN '05.10.2015' AND '11.10.2015'.

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

You're likely to experience queries that needs to be improved, but I don't 
think the dates in your example can be improved.

HTH,
Set







++

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

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

2015-10-12 Thread 'Parzival' parzival1...@gmx.at [firebird-support] 
Hi,

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.

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

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

2015-10-12 Thread Thomas Steinmaurer t...@iblogmanager.com [firebird-support] 
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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Optimize query for date ranges

2015-10-11 Thread '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

2015-10-11 Thread 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

2015-10-11 Thread '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 
<mailto:parzival1...@gmx.at>  [firebird-support] 
<firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> > 
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]