> Den 2011-08-23 12:16 skrev Vander Clock Stephane såhär: >> > I have a database that's about 56 Gbyte and has 150 million or more >> > records (each) in two of the tables. >> > >> > I find query performance to be very agreable, provided an index can be >> >> yes, but when you need order by, even with index it's not funny at all :( >> did we need to use the index on the query filter or on the order by filter ? >> this off course depend how many record the query filter will return ... >> with few reccord returned better to use the index on the query filter, >> with lot of >> reccord returned better to use the index on the order by ... but this >> the optimization >> engine can not know by advance .... > > Not sure what you mean here, but have you tired adding both an ascending > as well as a descending index on all columns that are involved in > (possibly descending) sorts? > > As far as I know, FB will use any relevant combination of single-column > indices for equality where criteria. > > Are you sure you've got the temp space for sorting setup in a good way? > Perhaps put it on a RAM disk or fast SSD? > > Is your page cache large enough? I'd recommend superclassic to be able > to utilize a very large page cache, e.g. 20 Gbyte, that's shared for all > connections. Classic has separate page cache per connection so cache > can't be too large, and superserver has problems with multiple cpu:s/cores.
SuperClassic has a separate page cache per connection as well. -- With regards, Thomas Steinmaurer Upscene Productions http://www.upscene.com http://blog.upscene.com/thomas/ Download LogManager Series, FB TraceManager today! Continuous Database Monitoring Solutions supporting Firebird, InterBase, Advantage Database, MS SQL Server and NexusDB! ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 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: http://docs.yahoo.com/info/terms/
