Aurimas Cernius wrote:
I'm interested, if there is possibility to reduce priority of query? The problem is, that when one user starts a long SELECT (5 minutes, but can be much longer), all other users hardly can do anything, because database gets very slow.
Ann W. Harrison answers:
As someone else said, the threading in superserver isn't great and you'll get better parallel performance with classic where the operating system can arrange priorities. Even with classic, trying to manage priorities directly is not a good idea.
Consider this case. You've got a low priority report running and it updates an interesting database page - the database header page, for example, which it has to update to start - or maybe a transaction inventory page that it has to update to close. Then a bunch of more important transactions try to run, but can't get that page because a low priority transaction has it locked. The low priority transaction can't release the page because it can't get in. Nothing happens.
The Firebird project is working on a better threading model for SuperServer which should solve the problem of one query blocking all others. Until the, try Classic. But do read about the resource use of classic and change the default page cache size.