Re: Long-running queries

In article <806612021snz@granby.demon.co.uk>, Bruce Horrocks <bh@granby.demon.co.uk> wrote: >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>) > Much of the discussion on this topic seems strange to me. The Data Warehouse that I worked on was for over 3 years of data. The online system had 90 days. It was clear that the online system was not enough. Also the way the data was accessed was completly different. Disk storage is not so expensive now that having seperate databases would be that expensive. There is still an issue with the size of the data and how much of the data gets refreshed and how often. But with a heavily partitioned database that unions the correct tables together it becomes a reasonable solution. Hope this helps mjr