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: [email protected] > [mailto:[email protected]] > Gesendet: Montag, 12. Oktober 2015 17:31 > An: [email protected] > 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" <[email protected]> > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > 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: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
