Keeping a database optimized

> Even so, after several insertions (using XQUERY insert node) I see that the > UPTODATE flag is no longer checked

> and I have to run OPTIMIZE ALL to get > back my performance.

> a) Is there a way to keep UPTODATE checked under all circumstances? > And if not > b) Is is advisable to run OPTIMIZE ALL after every insertion? (I guess not)

> Hi Paul, > >> Even so, after several insertions (using XQUERY insert node) I see that the >> UPTODATE flag is no longer checked > Basically, this is fine, because only the value index structures will > be updated (excluding database statistics, meta data and the fulltext > index). > >> and I have to run OPTIMIZE ALL to get >> back my performance. > Maybe you can compare the query plans before and after an update and > see what has changed? > >> a) Is there a way to keep UPTODATE checked under all circumstances? >> And if not >> b) Is is advisable to run OPTIMIZE ALL after every insertion? (I guess not) > You can set AUTOOPTIMIZE to true [1]. This works fine as long as your > database doesn't grow too large. In every case, it's faster than > OPTIMIZE ALL, because only those data structures will be reorganized > that have become outdated. > > Besides that, using OPTIMIZE (without ALL) may already be sufficient. > > Hope this helps, > Christian > > [1] http://docs.basex.org/wiki/Options#AUTOOPTIMIZE

> So what database size do you consider to be "too large"? > 0.5 Gb? 1 Gb? Or ... ?

> > Paul > > >> Hi Paul, >> >>> Even so, after several insertions (using XQUERY insert node) I see that >>> the >>> UPTODATE flag is no longer checked >> >> Basically, this is fine, because only the value index structures will >> be updated (excluding database statistics, meta data and the fulltext >> index). >> >>> and I have to run OPTIMIZE ALL to get >>> back my performance. >> >> Maybe you can compare the query plans before and after an update and >> see what has changed? >> >>> a) Is there a way to keep UPTODATE checked under all circumstances? >>> And if not >>> b) Is is advisable to run OPTIMIZE ALL after every insertion? (I guess >>> not) >> >> You can set AUTOOPTIMIZE to true [1]. This works fine as long as your >> database doesn't grow too large. In every case, it's faster than >> OPTIMIZE ALL, because only those data structures will be reorganized >> that have become outdated. >> >> Besides that, using OPTIMIZE (without ALL) may already be sufficient. >> >> Hope this helps, >> Christian >> >> [1] http://docs.basex.org/wiki/Options#AUTOOPTIMIZE > >

>> So what database size do you consider to be "too large"? >> 0.5 Gb? 1 Gb? Or ... ? > Hm… It really depends on your use case (1 ms can be too slow for the > one use case, 1 second can be fast enough for another). Maybe you can > simply do some benchmarking and see how it works out for you? > > >> Paul >> >> >>> Hi Paul, >>> >>>> Even so, after several insertions (using XQUERY insert node) I see that >>>> the >>>> UPTODATE flag is no longer checked >>> Basically, this is fine, because only the value index structures will >>> be updated (excluding database statistics, meta data and the fulltext >>> index). >>> >>>> and I have to run OPTIMIZE ALL to get >>>> back my performance. >>> Maybe you can compare the query plans before and after an update and >>> see what has changed? >>> >>>> a) Is there a way to keep UPTODATE checked under all circumstances? >>>> And if not >>>> b) Is is advisable to run OPTIMIZE ALL after every insertion? (I guess >>>> not) >>> You can set AUTOOPTIMIZE to true [1]. This works fine as long as your >>> database doesn't grow too large. In every case, it's faster than >>> OPTIMIZE ALL, because only those data structures will be reorganized >>> that have become outdated. >>> >>> Besides that, using OPTIMIZE (without ALL) may already be sufficient. >>> >>> Hope this helps, >>> Christian >>> >>> [1] http://docs.basex.org/wiki/Options#AUTOOPTIMIZE >>