Re: Long-running queries

In article <3uk28e$ccg@lennon.cc.gatech.edu> gukal@cc.gatech.edu "Sreenivas Gukal" writes: >Hi Folks >Part of my Ph.D. thesis deals with efficiently supporting long-running >queries (maybe Decision Support System queries) without affecting >concurrent transactions in an OLTP environment. I dont know how important >and/or frequent a problem this is in real applications. If you >have encountered similar situations, can you please let me >know the context you had the problem in and what your solution was. As a designer of database systems I am seeing this problem coming up more and more often. I think that this is because customers are expecting and requiring more sophisticated systems nowadays, especially now that RDBMSs are mainstream rather than exotic technology. Mostly the requirement that I have to implement is for what I would call "direct access", e.g. looking up a customer's details while they're on the phone. While this does not necesssarily imply the same volume of transactions that I would expect with OLTP, it still means that any degradation in response time caused by a long-running query is unacceptable. The solution that I would most like to be able to offer is one where the database allows queries to be prioritised in the same way that an operating system allows processes to be prioritised. This would be particularly effective for trend or pattern analysis queries that could be set to run at a very low priority, or even during RDBMS idle-time. Being read-only and aimed at historic data they would cause the minimum of locking contention with "direct access" queries which are primarily involved with current data. As a safeguard, however, the database vendor could introduce the option to have the database abort a low-priority, long-running transaction rather than have it hold-up a higher priority one. The choice is then the system designer's. Any thoughts welcomed. Do any RDBMS vendors have plans to introduce such a facility? (or are all your development budgets tied-up with adding OO capabilities <g>) Regards, -- ------------------------------------------------------------------------------- Bruce Horrocks "Audiences are sometimes needed Hampshire, England for acoustical purposes." bh@granby.demon.co.uk Arnold Schoenburg