Re: AW: [firebird-support] Optimize query for date ranges
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
>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: AW: [firebird-support] Optimize query for date ranges
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" > > > ++ > > 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
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" > > > ++ > > 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 <*> 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
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
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/
AW: [firebird-support] Optimize query for date ranges
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] 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]
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
[firebird-support] Optimize query for date ranges
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